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; }
    }









 

 

posted @ 2013-01-27 06:17  朱京辉  阅读(222)  评论(0)  编辑  收藏  举报