.NET下使用DataAdapter保存数据时，如何生成command语句及使用事务-.NET教程,数据库应用

在.net下，运用dataset对数据进行批量更新时，需要与 dataadapter配合使用。dataadapter数据适配器的作用是生成数据更新时所需的insert,delete,update等语句，这些语句一般在开发阶段，在ide环境下生成，但是如果需要在程序中动态配置数据来源，那么dataadapter的insert,delete,update语句就需要动态生成了。这就是sqlcommandbuilder 对像了（以sqldataadapter对像为例，如果是别的dataadapter对像，则可改为dbcommandbuilder ）。例如有以下更新方法

        /// <summary>
        /// 根据传入的sql语句，为dataadapter动态生成command语句，并将数据保存到数据库
        /// </summary>
        /// <param name=”ds”>需操作的dataset</param>
        /// <param name=”connectstr”>数据库连接字符串</param>
        /// <param name=”tablename”>dataset中的指定需更新的表</param>
        /// <param name=”sqlstr”>数据来源sql语句，需要有主键才能更新</param>

        public void datasetupdate(dataset ds,string connectstr,string tablename,string sqlstr)
        {
           try
            {
                sqldataadapter adapter = new sqldataadapter(sqlstr, connectstr);   

                //使用sqlcommandbuilder  对像填充sqldataadapter 的insertcommand、deletecommand、updatecommand对像
                sqlcommandbuilder cmdbuilder = new sqlcommandbuilder(adapter);

                int val = adapter.update(ds, tablename);
                ds.acceptchanges();
            }
            catch
            {
                throw;
            }
        }

以上完成对单个数据表的保存，如果需要将多个数据表保存到数据库，就需要运用事务，将以上方法重载一次

        public void datasetupdate(dataset ds, sqlconnection sqlconnect, string tablename, string sqlstr,sqltransaction sqltrans)
        {
            try
            {
                sqldataadapter adapter = new sqldataadapter(sqlstr, sqlconnect);

                sqlcommandbuilder cmdbuilder = new sqlcommandbuilder(adapter);

               //创建sqldataadapter 对像的command对像，并将连接对像及事务对像绑定到command对像上

                adapter.deletecommand = new sqlcommand(“”, sqlconnect, sqltrans);
                adapter.insertcommand = new sqlcommand(“”, sqlconnect, sqltrans);
                adapter.updatecommand = new sqlcommand(“”, sqlconnect, sqltrans);
                adapter.selectcommand = new sqlcommand(sqlstr, sqlconnect, sqltrans);

               //使用getdeletecommand将相对应的sqlcommand对像传入

                adapter.deletecommand = cmdbuilder.getdeletecommand();
                adapter.insertcommand = cmdbuilder.getinsertcommand();
                adapter.updatecommand = cmdbuilder.getupdatecommand();
                //cmdbuilder.refreshschema();
                int val = adapter.update(ds, tablename);
                ds.tables[tablename].acceptchanges();
            }
            catch
            {
                throw;
            }
        }

通过以上方法，将sqldataadapter 对像的更新绑定到了事务，那么在外层调用如下

            system.data.sqlclient.sqlconnection sqlcon = new system.data.sqlclient.sqlconnection(connectionstring);
            system.data.sqlclient.sqltransaction sqltran;
            sqlcon.open();

            //将事务绑定到连接对像
            sqltran = sqlcon.begintransaction();
            try
            {
                db.datasetupdate(ds, sqlcon, “main”, “select lx as id,mc,xgrq from ddjgfl “,sqltran);
                db.datasetupdate(ds, sqlcon, “ciled”, “select ddbh,ksbh,ksmc,lsbm,mzbz,zybz,yjbz,czksbz,ykbz from ksdj”, sqltran);
                sqltran.commit();
            }
            catch(system.data.sqlclient.sqlexception sqlex)
            {
                sqltran.rollback();
                messagebox.show(sqlex.message);
            }
            catch (exception ex)
            {
                sqltran.rollback();
                messagebox.show(ex.message);
            }
            finally
            {
            }