ADO.Net

今天在云和学院学习了ADO.Net

Connection：用来连接数据库

Command：用来执行SQL语句

DataReader：数据读取器，只读、只进的结果集，一条一条读取数据

DataAdapter：数据适配器，一个封装了上面3个对象的对象

—数据集(DataSet)

在内存中的临时数据库

断开式数据操作

—Parameter//带参数的SQL语句

—ADO.Net访问数据的方式

方式一：

1.连接数据用Connection

2.执行SQL语句Command

3.执行完毕之后将结果一条一条返回。DataReader

—方式二：

使用DataAdapter+DataSet,这种方法本质还是通过Connection、Command、DataReader将数据全部取出来然后放到了DataSet中

读取职工表中的数据

string connstring = "Data Source=.;Initial Catalog=db_buiness;Integrated Security=True";
            using(SqlConnection conn=new SqlConnection(connstring))
            {
                conn.Open();
                string sql = "select * from 职工";
                using(SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    using(SqlDataReader sda=cmd.ExecuteReader())
                    {
                        while(sda.Read())
                        {
                            Console.WriteLine("职工ID{0}，职工号{1}，仓库号{2}，姓名{3}，性别{4}，工资{5}",sda[0],sda[1],sda[2],sda[3],sda[4],sda[5]);
                        }
                       
                    }
                   
                }
                Console.ReadKey();
            }

 

向数据表插入数据

 string connstring = "Data Source=.;Initial Catalog=db_buiness;Integrated Security=True";
           using(SqlConnection conn = new SqlConnection(connstring))
           {
               conn.Open();
               string conntext = "insert into 职工(职工号,仓库号,姓名,性别,工资) values('zg19','wh5','王丽','女',1200)";              
               using(SqlCommand comm=new SqlCommand(conntext,conn))
               {
                   int num = comm.ExecuteNonQuery();
                   if(num>0)
                   {
                       Console.WriteLine("插入成功");
                   }
                   else
                   {
                       Console.WriteLine("插入失败");
                   }
               }
           }
           Console.ReadKey();

更新数据

 string connstring = "Data Source=.;Initial Catalog=db_buiness;Integrated Security=True";
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                conn.Open();
                string conntext = "update 职工 set 职工号='zg20'  where 职工ID=19";
                using (SqlCommand comm = new SqlCommand(conntext, conn))
                {
                    int num = comm.ExecuteNonQuery();
                    if (num > 0)
                    {
                        Console.WriteLine("更新成功");
                    }
                    else
                    {
                        Console.WriteLine("更新失败");
                    }
                }
            }
            Console.ReadKey();

 

删除数据

                string connstring = "Data Source=.;Initial Catalog=db_buiness;Integrated Security=True";
                using (SqlConnection conn = new SqlConnection(connstring))
                {
                    conn.Open();
                    string conntext = "delete from 职工 where 职工ID=19";
                    using (SqlCommand comm = new SqlCommand(conntext, conn))
                    {
                        int num = comm.ExecuteNonQuery();
                        if (num > 0)
                        {
                            Console.WriteLine("删除成功");
                        }
                        else
                        {
                            Console.WriteLine("删除失败");
                        }
                    }
                }                             
            Console.ReadKey();

 

查询操作

private void btnlogin_Click(object sender, EventArgs e)
        {
            string name = this.txtname.Text;
            string pwd = this.txtpwd.Text;
            string connectstring = "Data Source=.;Initial Catalog=db_buiness;Integrated Security=True";
            using(SqlConnection conn=new SqlConnection(connectstring))
            {
                conn.Open();
                string sql = "select name,pwd from 用户 where name=@name and pwd=@pwd";
                SqlParameter parms = new SqlParameter("@name",name);
                SqlParameter parms1 = new SqlParameter("@pwd",pwd);
                using(SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    cmd.Parameters.Add(parms);
                    cmd.Parameters.Add(parms1);
                    DataTable dt = new DataTable();
                    using(SqlDataAdapter sda=new SqlDataAdapter(cmd))
                    {
                        sda.Fill(dt);
                    }
                    if(dt.Rows.Count>0)
                    {
                        MessageBox.Show("登陆成功");
                    }
                    else
                    {
                        MessageBox.Show("登陆失败");
                    }
                }
            }
        }

 

查找出表中所有记录

 protected void Page_Load(object sender, EventArgs e)
        {
            string connectstring = "Data Source=.;Initial Catalog=db_buiness;Integrated Security=True";
            using(SqlConnection conn=new SqlConnection(connectstring))
            {
                conn.Open();
                string sql = "select * from 职工";
                using(SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    DataTable dt = new DataTable();
                    using(SqlDataAdapter sda=new SqlDataAdapter(cmd))
                    {
                        sda.Fill(dt);
                    }
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }