ADO.NET批量操作

时间：2010-12-11 来源：hfliyi

查询

代码

 SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Select CustomerID,CompanyName,ContactName,ContactTitle from Customers$ ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            this.dataGridView1.DataSource = ds.Tables[0];

//插入

代码

 SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Select CustomerID,CompanyName,ContactName,ContactTitle from Customers$ ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataRow dr = ds.Tables[0].NewRow();
            dr["CustomerID"] = this.txtCustomerID.Text.Trim();
            dr["CompanyName"] = this.txtCompanyName.Text.Trim();
            dr["ContactName"] = this.txtContactName.Text.Trim();
            dr["ContactTitle"] = this.txtContactTitle.Text.Trim();
            ds.Tables[0].Rows.Add(dr);
            da.Update(ds.Tables[0]);
            this.dataGridView1.DataSource = ds.Tables[0];

//修改

代码

 SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Select CustomerID,CompanyName,ContactName,ContactTitle from Customers$ ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            int i = 0;
            for (i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (ds.Tables[0].Rows[i][0].ToString() == this.txtCustomerID.Text.Trim())
                {
                    break;
                }
            }
            ds.Tables[0].Rows[i][1] = this.txtCompanyName.Text;
            ds.Tables[0].Rows[i][2] = this.txtContactName.Text.Trim();
            ds.Tables[0].Rows[i][3] = this.txtContactTitle.Text.Trim();
            da.Update(ds.Tables[0]);
            this.dataGridView1.DataSource = ds.Tables[0];

//删除

代码

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Select CustomerID,CompanyName,ContactName,ContactTitle from Customers$ ", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlCommandBuilder sb = new SqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            int i = 0;
            for (i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (ds.Tables[0].Rows[i][0].ToString() == this.txtCustomerID.Text.Trim())
                {
                    break;
                }
            }
            ds.Tables[0].Rows[i].Delete();

            da.Update(ds.Tables[0]);
            this.dataGridView1.DataSource = ds.Tables[0];