C#中SqlCommandBuilder批量数据增加修改

//批量增加多笔记录

 string connString = "Data Source=USER-20150605LO;Initial Catalog=JXC;Integrated Security=True";
           
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //设置select查询命令，SqlCommandBuilder要求至少有select命令
                SqlCommand selectCMD = new SqlCommand("select CPurchasePrice,CNum,CUnitPrice from tb_Commodity", conn);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(selectCMD);
                //上面的语句中使用select 0，不是为了查询出数据，而是要查询出表结构以向DataTable中填充表结构
                sda.Fill(dt);
                //给DataTable添加10条记录
                for (int i = 1025; i <= 1035; i++)
                {
                    dt.Rows.Add(new object[] { i, "DN" + i, 20 + i });
                    SqlCommandBuilder scb = new SqlCommandBuilder(sda);
                    //执行更新
                    sda.Update(dt.GetChanges());
                    //使DataTable保存更新
                    dt.AcceptChanges();
                }
            }

//增删改查

            string connString = "Data Source=USER-20150605LO;Initial Catalog=JXC;Integrated Security=True";
           
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //设置select查询命令
                SqlCommand selectCMD = new SqlCommand("select ID,CNum,CUnitPrice from tb_Commodity", conn);
                //Insert命令
                SqlCommand insertCMD = new SqlCommand("insert into tb_Commodity(ID,CNum,CUnitPrice)" +
                " values(@ID,@CNum,@CUnitPrice)", conn);
                //Update命令
                SqlCommand updateCMD = new SqlCommand("update Studnet Set ID=@ID,CNum=@CNum" +
                " Where CUnitPrice=@CUnitPrice", conn);
                //Delete命令
                SqlCommand deleteCMD = new SqlCommand("delete from Student where ID=@ID", conn);

                SqlParameter paraSNo1, paraSNo2, paraSNo3;
                paraSNo1 = new SqlParameter("@ID", "ID");
                paraSNo2 = new SqlParameter("@ID", "ID");
                paraSNo3 = new SqlParameter("@ID", "ID");
                paraSNo1.SourceVersion = DataRowVersion.Current;//指定SourceVersion确定参数值是列的当前值
                paraSNo2.SourceVersion = DataRowVersion.Current;
                paraSNo3.SourceVersion = DataRowVersion.Current;
                //(Current),还是原始值(Original),还是建议值(Proposed)

                SqlParameter paraSName1, paraSName2, paraSName3;
                paraSName1 = new SqlParameter("@CNum", "CNum");
                paraSName2 = new SqlParameter("@CNum", "CNum");
                paraSName3 = new SqlParameter("@CNum", "CNum");
                paraSName1.SourceVersion = DataRowVersion.Current;
                paraSName2.SourceVersion = DataRowVersion.Current;
                paraSName3.SourceVersion = DataRowVersion.Current;

                SqlParameter paraSAge1, paraSAge2, paraSAge3;
                paraSAge1 = new SqlParameter("@CUnitPrice", "CUnitPrice");
                paraSAge2 = new SqlParameter("@CUnitPrice", "CUnitPrice");
                paraSAge3 = new SqlParameter("@CUnitPrice", "CUnitPrice");
                paraSAge1.SourceVersion = DataRowVersion.Current;
                paraSAge2.SourceVersion = DataRowVersion.Current;
                paraSAge3.SourceVersion = DataRowVersion.Current;

                insertCMD.Parameters.AddRange(new SqlParameter[] { paraSNo1, paraSName1, paraSAge1 });
                updateCMD.Parameters.AddRange(new SqlParameter[] { paraSNo2, paraSName2, paraSAge2 });
                deleteCMD.Parameters.AddRange(new SqlParameter[] { paraSNo3, paraSName3, paraSAge3 });

                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(selectCMD);
                sda.Fill(dt);
                //插入2条数据
                dt.Rows.Add(new object[] { 23, "aa11", 31 });
                dt.Rows.Add(new object[] { 24, "aa12", 32 });

                //先更新第1,2条数据的SName和SAge
                dt.Rows[0]["CNum"] = "CCC";
                dt.Rows[0]["CUnitPrice"] = 55;
                dt.Rows[1]["CNum"] = "DDD";
                dt.Rows[1]["CUnitPrice"] = 66;

                //使用Delete删除第3,4条数据
                dt.Rows[2].Delete();
                dt.Rows[3].Delete();
                SqlCommandBuilder scb = new SqlCommandBuilder(sda);
                //执行更新
                sda.Update(dt.GetChanges());
                //使DataTable保存更新
                dt.AcceptChanges();
            }