从sqlcommandbuilder引出的小想法

来源：互联网发布：php大数据框架编辑：程序博客网时间：2024/05/22 15:19

数据库到底是怎么更新的

之前在看C#操作数据库的时候，大体流程都明白了，就是在看到对数据库的更新的时候：

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(myDataAdapter);  // 为myDataAdapter赋予SqlCommandBuilder功能myDataAdapter.Update(myDataSet, "表名");                   // 向数据库提交更改后的DataSet，第二个参数为DataSet中的存储表名，并非数据库中真实的表名（二者在多数情况下一致）

在看到新建了一个mysqlcommandbuilder对象，却没有使用到，为什么对于数据库的更新不能仅仅使用dataAdapter.Update()的方法呢？非要新建一个看起来后面根本没有用到的commandbuilder呢？

首先说明第一个问题：直接使用DataAdapter.Update()方法只能更新本地的数据库，即本地建立的DataSet的对象，这个本地数据库的更新并不会自动更新与之关联的数据库（SQL Sever）。如果要实现同步的更新，那么就得采用两种方法：一种是手动的写入sql命令，即通过设置DataAdapter的SelectCommand属性，将想要对数据库的操作写进去，示例代码如下（都是我抄的哈~）：

SqlConnection sqlCnt = new SqlConnection(connectString);sqlCnt.Open();// 创建SqlCommandSqlCommand mySqlCommand = new SqlCommand();mySqlCommand.CommandType = CommandType.Text;mySqlCommand.CommandText = "select * from product";mySqlCommand.Connection = sqlCnt;// 创建SqlDataAdapterSqlDataAdapter myDataAdapter = new SqlDataAdapter();myDataAdapter.SelectCommand = mySqlCommand;// 为SqlDataAdapter对象绑定所要执行的SqlCommand对象

当然也可以简写一下，即省略掉SqlCommand的定义，直接将操作数据库的语句写进去：

sqlCnt.Open();// 隐藏了SqlCommand对象的定义，同时隐藏了SqlCommand对象与SqlDataAdapter对象的绑定SqlDataAdapter myDataAdapter = new SqlDataAdapter("select * from product", sqlCnt);

如果设置了DataAdapter.SelectCommand属性，（则可以创建一个 SqlCommandBuilder 对象来自动生成用于单表更新的 Transact-SQL 语句。然后，SqlCommandBuilder 将生成其他任何未设置的 Transact-SQL 语句）这句话来自msdn,我猜他的意思是如果设置了这个属性，会自动为DataAdapter新建一个CommandBuilder吗？

而第二种方法就是显示的使用SqlCommandBuilder——他自动为与之相关联的DataAdapter生成相应的Transact-SQL 语句。在msdn中对于SqlCommandBuilder的的定义：自动生成单表命令，用于将对 DataSet 所做的更改与关联的 SQL Server 数据库的更改相协调。他采用的方法是：SqlCommandBuilder 将DataAdapter本身注册为 RowUpdating 事件的侦听器。一次只能将一个SqlDataAdapter 与一个 SqlCommandBuilder 对象（或相反）互相关联。

所以说，我所纠结的这个SQLCommandBuilder为啥只新建了一个对象而没有其他的作为，只是因为——他是作为一个监听器来监听DataAdapter的！如果我们在本地对DataSet有了改变，那么这个改变被监听到了，SqlCommandBuilder就会自动生成相应的SQL语句去更新数据库。

此处用到的设计模式

1.适配器设计模式，即此处用到的DataAdapter。这个模式的含义即为他的字面意思：类似电源的适配器，本质上只是做了一个接口的转换。这个类的功能是将使用sql语句去操作数据库（他的SelectCommand属性去操作DataSet类）

2.监听器模式，即此处用到的SqlCommandBuilder。个人觉得这里的监听器模式绝对不是设计模式里常说的观察者模式。因为观察者模式是针对一对多的一种状态强调的。此处只是一对一的——一个SqlCommandBuilder仅仅监听一个DataAdapter。（这也不是builder设计模式，和组装根本没有关系，，，只是恰好叫了这个名字）

SqlDataAdapter 不会自动生成实现DataSet 的更改与关联的 SQL Server 实例之间的协调所需的 Transact-SQL 语句。但是，如果设置了 SqlDataAdapter 的 SelectCommand 属性，则可以创建一个 SqlCommandBuilder 对象来自动生成用于单表更新的 Transact-SQL 语句。然后，SqlCommandBuilder 将生成其他任何未设置的 Transact-SQL 语句。

每当设置了 DataAdapter 属性，SqlCommandBuilder 就将其本身注册为 RowUpdating 事件的侦听器。一次只能将一个 SqlDataAdapter 与一个 SqlCommandBuilder 对象（或相反）互相关联。

为了生成 INSERT、UPDATE 或 DELETE 语句，SqlCommandBuilder 会自动使用 SelectCommand 属性来检索所需的元数据集。如果在检索到元数据后（例如在第一次更新后）更改 SelectCommand，则应调用RefreshSchema 方法来更新元数据。

SelectCommand 还必须至少返回一个主键列或唯一的列。如果什么都没有返回，就会产生 InvalidOperation 异常，不生成命令。

SqlCommandBuilder 还使用由 SelectCommand 引用的 Connection、CommandTimeout 和Transaction 属性。如果修改了这些属性中的一个或多个，或者替换了 SelectCommand 本身，用户则应调用RefreshSchema。否则，InsertCommand、UpdateCommand 和 DeleteCommand 属性都保留它们以前的值。

如果调用 Dispose，则会解除 SqlCommandBuilder 与 SqlDataAdapter 的关联，并且不再使用生成的命令。

示例

下面的示例结合 SqlDataAdapter 和 SqlConnection，使用 SqlCommand 从数据源中选择行。向该示例传递了一个连接字符串、一个查询字符串（它是一个 Transact-SQL SELECT 语句）和一个数据库表名称字符串。然后该示例创建一个SqlCommandBuilder。

复制

public static DataSet SelectSqlRows(string connectionString,    string queryString, string tableName){    using (SqlConnection connection = new SqlConnection(connectionString))    {        SqlDataAdapter adapter = new SqlDataAdapter();        adapter.SelectCommand = new SqlCommand(queryString, connection);        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);        connection.Open();        DataSet dataSet = new DataSet();        adapter.Fill(dataSet, tableName);        //code to modify data in DataSet here        //Without the SqlCommandBuilder this line would fail        adapter.Update(dataSet, tableName);        return dataSet;    }}

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

//批量增加多笔记录
[html] view plain copy
 print?
 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查询命令，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();                }            }
//增删改查 
[html] view plain copy
 print?
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();  
}

有没有人遇到这种情况，用 SqlDataAdapter.Update(ds)更新时出错?

answer: 一般是这样的，如果用设计器将SqlDataAdapter拖到页面中使用时，不会出现这种情况，因为

系统会自动生成SqlDataAdapter的属性命令，比如: .UpdateCommane insertCommand

selectCommand等。但是有些程序员不喜欢用设计器，或者是有些地方没必要拖动

SqlDataAdapter这么个庞大物来实现,那么SqlDataAdapter就不会自动生成相关的查询或更新

语句了. 所以当执行到SqlDataAdapter.Update(ds)语句时，SqlDataAdapter桥接器不知道更

新哪个表.就报错了.

(二)

解决方法:

用SqlCommandBuilder 实现批量更新

1.功能：

可以实现你对DataSet在UI层做任意操作后，直接丢给这个方法，这个方法就可以自动把你的修改更新到数据库中,而没必要每次都更新到

数据库

2.使用方法 public DataSetUpdateByDataSet(DataSet ds,string strTblName,string strConnection) { SqlConnection conn = new SqlConnection(strConnection)); SqlDataAdapter myAdapter = new SqlDataAdapter(); SqlCommand myCommand = new SqlCommand("select * from "+strTblName),(SqlConnection) conn); myAdapter.SelectCommand = myCommand; SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); try

{

lock(this) //处理并发情况(分布式情况)

{

myAdapter.Update(ds,strTblName);

}

} catch(Exception err) {

conn.Close(); throw new BusinessException(err); }

return ds; //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds }

或

public DataSet UpdateByDataSet(DataSet ds,string strTblName,string strConnection) { SqlConnection conn = new SqlConnection(strConnection)); SqlCommand myCommand = new SqlCommand("select * from "+strTblName),(SqlConnection) conn);

SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand );

SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); myAdapter.InsertCommand = myCommandBuilder .GetInsertCommand();

myAdapter.UpdateCommand = myCommandBuilder .GetUpdateCommand();

myAdapter.DeleteCommand = myCommandBuilder .GetDeleteCommand();

try

{

lock(this) //处理并发情况(分布式情况)

{

conn.Open();

myAdapter.Update(ds,strTblName);

conn.Close();

} return ds; //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds } catch(Exception err) {

conn.Close(); throw new BusinessException(err); } }

直接调用这两个方法的任意一个就OK啦,说明的一点是select * from "+strTblName是一定要的，作用大家也应该想到了，主要是告诉 SqlDataAdapter更新哪个表

3.什么时候用?

a. 有时候需要缓存的时候，比如说在一个商品选择界面，选择好商品，并且进行编辑/删除/更新后，

最后一并交给数据库，而不是每一步操作都访问数据库，因为客户选择商品可能进行n次编辑/删除

更新操作，如果每次都提交，不但容易引起数据库冲突,引发错误，而且当数据量很大时在用户执行

效率上也变得有些慢

b.有的界面是这样的有的界面是这样的，需求要求一定用缓存实现，确认之前的操作不提交到库,点击

页面专门提交的按钮时才提交商品选择信息和商品的其它信息. 我经常遇到这样的情况

c.有些情况下只往数据库里更新，不读取. 也就是说没有从数据库里读，SqlDataAdapter也就不知道是更新哪张表了，调用Update就很可能出错了。这样的情况下可以用SqlCommandBuilder 了.

4. 注意点: 1.只能更新一个表，如果此数据集是从两个或者两个以上的表关联而来的,则不能用此方法自动更新 2.表中必须设置主键 3.更新的表中字段不能有image类型的

5.优点:

节省代码量，节省时间，这个方法可以代替所有的: 更新/删除/插入操作语句

6.缺点: 访问两次数据库(select * TableName,就是这句，要确认是哪个表，除非是很大的数据量，一般是感觉不到的)，效率稍微有些慢

阅读全文

0 0