黑马程序员+ADO.Net基础(上)

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("登录失败！");

                }

 

            }

        }

   }