ado.net学习笔记入门

1.ado.net

     程序要和数据库交互要通过ado.net进行,通过ado.net就能在程序中执行SQL了.ado.net

     在拷贝数据库的时候要注意,要先关闭连接

    

     神奇的代码

      string dataDir = AppDomain.CurrentDomain.BaseDirectory;

            if (dataDir.EndsWith(@"\bin\Debug\" )

                  || dataDir.EndsWith( @"\bin\Release\"))

            {

                dataDir = System.IO. Directory.GetParent(dataDir).Parent.Parent.FullName;

                AppDomain.CurrentDomain.SetData("DataDirectory" , dataDir);

            }

            //以上是必写代码,具体参考http://www.rupeng.com/forum/thread-11988-1-1.html

         //防止数据库源文件选择出错

2.连接SQLServer

     连接字符串,程序通过连接字符串指定要连哪个势力的哪个数据库,用什么用户名密码.

     项目内嵌mdf文件形式的连接字符串

     using(SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

            }

            Console.WriteLine("打开数据库成功" );

            Console.ReadKey();

     ado.net中通过sqlconnection类创建到SQLServer的连接,SqlConnection代表数据库连接,ado.net中的连接等资源都实现IDisposable接口,可以使用using进行管理,using资源释放

     别忘了using System.Data.Sqlclint

        

3.cmd     

     cmd.CommandText = "INSERT INTO MyTable1(Name)VALUES('zjh')" ;

        cmd.ExecuteNonQuery();

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

4.简单的用户输入用户名,密码,再插入到数据库中

     //用户写入用户名,密码,插入数据

            Console.Write("请输入用户名:" );

            string UserName = Console .ReadLine();

            Console.Write("请输入密码:" );

            string Password = Console .ReadLine();

            //读取数据完毕,下面链接数据库,插入数据

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "INSERT INTO T_User(UserName,Password)VALUES('" + UserName + "','" + Password + "')" ;

                    cmd.ExecuteNonQuery();

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

                }

            }

5.取表的第一行第一列

     using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "select count(*) from T_User" ;

                    int i = Convert .ToInt32(cmd.ExecuteScalar()); //返回表的行数,取结果的第一行的第一列

                    Console.WriteLine(i);

                }

            }

6.得到自增字段的值   OUTPUT  inserted.Id

     //用户写入用户名,密码,插入数据

            Console.Write("请输入用户名:" );

            string UserName = Console .ReadLine();

            Console.Write("请输入密码:" );

            string Password = Console .ReadLine();

            //读取数据完毕,下面链接数据库,插入数据并且返回自增字段的值

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "insert into T_User(UserName,Password) output inserted.id values('" + UserName + "','" + Password + "')";

                    //得到自增字段的值

                    int id = Convert .ToInt32(cmd.ExecuteScalar());

                    Console.WriteLine("插入成功\n新插入的主键为:{0}" , id);

                }

            }

7.ExecuteReader

执行多行结果集的用ExecuteReader

     using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "SELECT * FROM MyTable1" ;

                    using (SqlDataReader dr = cmd.ExecuteReader())//只能逐行向前,无法回头,轻量级

                    {

                        while (dr.Read())

                        {

                            //Console.WriteLine(dr.GetString(0));

                            string userName = dr.GetString(dr.GetOrdinal("Name"));

                            int Id = dr.GetInt32(dr.GetOrdinal("Id" ));

                            string password = dr.GetString(dr.GetOrdinal("Password"));

                            Console.WriteLine("Id={0},UserName={1},Password={2}" , Id, userName, password);

                            //Console.WriteLine(dr.GetString(dr.GetOrdinal("Password")));

                        }

                    }   

                }

reader的GetString,GetInt32,等方法只接受整数参数,也就是序列号,用GetOrdinal方法根据列名动态得到序列号

8.登录练习

     //用户写入用户名,密码,插入数据

            Console.Write("请输入用户名:" );

            string UserName = Console .ReadLine();

            Console.Write("请输入密码:" );

            string Password = Console .ReadLine();

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "select * from T_User where UserName='" + UserName + "'";

                    //先到表中查询用户输入的用户名对应的信息

                    using (SqlDataReader reader = cmd.ExecuteReader())

                    {

                        if (reader.Read()) //个人感觉用reader.HasRows()比较好

                        {

                            //用户存在

                            string dbpassword = reader.GetString(reader.GetOrdinal("Password"));

                            if (Password == dbpassword)

                            {

                                Console.WriteLine("密码正确,登录成功" );

                            }

                            else

                            {

                                Console.WriteLine("密码错误,登录失败" );

                            }

                        }

                        else//reader返回false,就是没有查找到这个用户名

                        {

                            Console.WriteLine("用户名错误" );

                        }

                    }

                }

            }

9.close和dispose的区别

     sql的close连接关闭后还可以打开,必须Dispose注销掉就不能打开

     using在除了作用域以后调用Dispose, SqlConnection,FileStream等的Dispose内部都会做这样的判断:判断有没有close,如果没有Close就是先Close后再Dispose

10.注入漏洞与参数化查询

     using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "select count(*) from T_User where UserName=@username and Password=@password";

                    //先到表中查询用户输入的用户名对应的信息

                    cmd.Parameters.Add( new SqlParameter ("username",UserName));

                    cmd.Parameters.Add( new SqlParameter ("password",Password));

                    int i = Convert .ToInt32(cmd.ExecuteScalar());

                    if (i > 0)

                    {

                        Console.WriteLine("登陆成功" );

                    }

                    else

                    {

                        Console.WriteLine("登录失败" );

                    }

                   

                }

            }

11.案例:用户登陆,登陆三次禁止登陆,用数据库记录错误次数

      private void IntErrorTime()

        { //密码错误,进行errortime加1操作

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand updatecmd = conn.CreateCommand())

                {

                    updatecmd.CommandText = "update T_User set ErrorTime=ErrorTime+1 where UserName=@userName";

                    updatecmd.Parameters.Add( new SqlParameter ("username", txtbxUserName.Text));

                    updatecmd.ExecuteNonQuery();

                }

            }

        }

        private void ClearErroeTime()

        { //登录成功,清楚errortime的值,恢复0

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand updatecmd = conn.CreateCommand())

                {

                    updatecmd.CommandText = "update T_User set ErrorTime=0 where UserName=@userName" ;

                    updatecmd.Parameters.Add( new SqlParameter ("username", txtbxUserName.Text));

                    updatecmd.ExecuteNonQuery();

                }

            }

        }

        private void btnLand_Click(object sender, EventArgs e)

        {

            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|\MYDB.mdf;Integrated Security=True;User Instance=True;"))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "select * from T_User where UserName=@username" ;

                    cmd.Parameters.Add( new SqlParameter ("username", txtbxUserName.Text));

                    using (SqlDataReader reader = cmd.ExecuteReader())

                    {

                        if (reader.Read())

                        {

                            int errortime = reader.GetInt32(reader.GetOrdinal("ErrorTime"));

                            if (errortime > 3)

                            {

                                MessageBox.Show("登录错误次数过多,禁止登录" );

                                return;

                            }

                            string dbpassword = reader.GetString(reader.GetOrdinal("Password"));

                            if (dbpassword == txtbxPassword.Text)

                            {

                                ClearErroeTime();

                                MessageBox.Show("登录成功" );

                            }

                            else

                            {

                                //在同一个连接中如果SqlDataReader没有关闭,那么是不能执行update之类的语句的

                                //using (SqlCommand updatecmd = conn.CreateCommand())

                                //{

                                //    updatecmd.CommandText = "update T_User ErrorTime=ErrorTime+1 where UserName=@userName ";

                                //    updatecmd.Parameters.Add(new SqlParameter("username", txtbxUserName.Text));

                                //    updatecmd.ExecuteNonQuery();

                                //}

                                IntErrorTime();

                                MessageBox.Show("登录失败" );

                            }

                        }

                        else

                        {

                            MessageBox.Show("用户名不存在" );

                        }

                    }

                }

            }

        }

12.案例手机号码归属地

  数据库乱码要注意:  设置类型nvarchar(50), 名字要设置N'浙江省'

  数据库连接字符串要写在配置文件,在使用是要先引用sys.configuration,再解析

     public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            //设定CombBox的DisplayMember属性为Name

            using (SqlConnection conn = new SqlConnection(ConfigurationManager .ConnectionStrings["ConnStr"].ToString()))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "SELECT * FROM promary";

                    using (SqlDataReader dr = cmd.ExecuteReader())//只能逐行向前,无法回头,轻量级

                    {

                        while (dr.Read())

                        {

                            ProvinceItem item = new ProvinceItem();

                            item.Name = dr.GetString(dr.GetOrdinal( "proName"));

                            item.Id = dr.GetInt32(dr.GetOrdinal( "proID"));

                            cmbbxPromary.Items.Add(item);

                        }

                    }

                }

            }

        }

        private void cmbbxPromary_SelectedIndexChanged( object sender, EventArgs e)

        {

            cmbbxCity.Items.Clear();

            cmbbxCity.SelectedItem = null;

            this.Refresh();

            //清空

            ProvinceItem item = (ProvinceItem )cmbbxPromary.SelectedItem;

            int proid = item.Id;

            //MessageBox.Show(item.Id.ToString());

            using (SqlConnection conn = new SqlConnection(ConfigurationManager .ConnectionStrings["ConnStr"].ToString()))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "SELECT * FROM city where proID=@proid" ;

                    cmd.Parameters.Add( new SqlParameter ("proid",proid));

                    using (SqlDataReader dr = cmd.ExecuteReader())//只能逐行向前,无法回头,轻量级

                    {

                        while (dr.Read())

                        {

                            cmbbxCity.Items.Add(dr.GetString(dr.GetOrdinal( "cityName")));

                        }

                    }

                }

            }

        }

    }

    public class ProvinceItem

    {

        public string Name { get; set; }

        public int Id { get; set; }

    }