張亮的博客園

1 在項目中添加數據庫，當要打包項目給別人的時候，需要斷開數據庫的連接

2  連接SQLserver:連接到那臺服務器上，那個實例那個數據庫，用戶名和密碼

“Data Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True”

連接數據庫語句：SqlConnection需要using

stringconStr=@"Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True";

            using(SqlConnectionconn=newSqlConnection(conStr)){

            if (conn!=null)

            {

                conn.Open();

                Console.WriteLine("Success!");               

            }}

3 插入操作：
           using( SqlConnection conn = new SqlConnection(conStr) ){

            if (conn != null)

            {

                conn.Open();

                Console.WriteLine("連接 Success!");

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "insert into Person(name,age)values('yy',29) ";

                    cmd.ExecuteNonQuery();

                    Console.WriteLine("插入成功！");

                }

}

       }

4 登錄練習：
           /// <summary>

        /// 登錄方法

        /// </summary>

        static void LoginMethod(string conStr) {

            Console.WriteLine("輸入用戶名：");

            string name = Console.ReadLine();

            Console.WriteLine("輸入密碼：");

            string pwd = Console.ReadLine();           

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd=conn.CreateCommand())

                {

                    cmd.CommandText = "select * from T_user where name='"+name+"'";

                    using (SqlDataReader reader=cmd.ExecuteReader())

                    {

                        if (reader.Read())//有該用戶

                        {

                            if (reader.GetString(reader.GetOrdinal("password"))==pwd.Trim())//密碼一致

                            {

                                Console.WriteLine("登錄成功！");

                            }

                            else

                            {

                                Console.WriteLine("登錄失敗！");

                            }

                        }

                        else

                        {

                            Console.WriteLine("用戶不存在！");

                        }

                    }

                }

            }

5        用戶界面插入數據

        /// <summary>

        /// 插入新用戶信息

        /// </summary>

        /// <param name="conStr"></param>

        static void InsertUserInfo(string conStr) {

            Console.WriteLine("輸入要插入的用戶名：");

            string name = Console.ReadLine();

            Console.WriteLine("輸入密碼：");

            string pwd = Console.ReadLine();   

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd=conn.CreateCommand())

                {

                   // cmd.CommandText = "insert into T_user(name,password)values('" + name + "','" + pwd + "')";

                    cmd.CommandText = string.Format("insert into T_user(name,password)values('{0}','{1}')",name,pwd);

                    cmd.ExecuteNonQuery();

                    Console.WriteLine("新用戶信息插入成功！");

                }

            }

        }

6        ExecuteScalar返回第一行和第一列的結果 ：cmd.CommandText=“select cout(*) from T_user”;  cmd.ExecuteScalar();

7 獲取插入數據的id值：
                    cmd.CommandText=string.Format("insert into T_user(name,password) output inserted.id values('{0}','{1}')",name,pwd);                                     Console.WriteLine(cmd.ExecuteScalar());//返回插入時的id值

8   close()與dispose()的區別： close() 后可以在Open,而dispose()后不能在open()

9  防注入漏洞攻擊

        /// <summary>

        /// 防注冊漏洞攻擊

        /// </summary>

        /// <param name="conStr"></param>

        static void CheckUserProtected(string conStr)

        {

            string name, pwd;

            Console.WriteLine("Name:");

            name = Console.ReadLine();

            Console.WriteLine("Password:");

            pwd = Console.ReadLine();

        using(SqlConnection conn=new SqlConnection(conStr)){

            conn.Open();

            using (SqlCommand cmd=conn.CreateCommand())

            {

                cmd.CommandText = "select * from T_user where name=@Name and password=@Password";

                cmd.Parameters.Add("@Name",name);

                cmd.Parameters.Add("@Password",pwd);

                if (Convert.ToInt32(cmd.ExecuteScalar())>0)

                {

                    Console.WriteLine("登錄成功！");

                }

                else

                {

                    Console.WriteLine("登錄失??！");

                }

 

            }

        }

   }