.NET动态生成DataSet下使用DataAdapter保存数据时，如何生成command语句及使用事务(转)

在.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
            {
            }

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/hy_ghb/archive/2006/07/01/860053.aspx