C# SqlCommandBuilder.GetUpdateCommand方法代码示例

        private static void AtualizarPrecosViaAdapter(SqlConnection conn)
        {
            var cmd = conn.CreateCommand();

            cmd.CommandText = "SELECT * FROM Produto";

            using (var da = new SqlDataAdapter(cmd))
            {
                var builder = new SqlCommandBuilder(da);

                builder.ConflictOption = ConflictOption.CompareRowVersion;

                da.UpdateCommand = builder.GetUpdateCommand();

                DataSet ds = new DataSet();

                da.Fill(ds);

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    var preco = double.Parse(row["Preco"].ToString());

                    preco += 10;

                    row["Preco"] = preco;
                }

                da.Update(ds);
            }
        }

        public void test()
        {       
            try
            {
                DataTable dt = new DataTable();
                conn.Connection.Open();

                SqlCommand comm = new SqlCommand("select * from user_login", conn.Connection);
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);

                //将结果存储在DataTable中
                adapter.Fill(dt);

                //对DataTable任意修改
                //....code...
                //dt.Rows[0].Delete();

                //缺少这个就会更新失败
                adapter.UpdateCommand = cmdBuilder.GetUpdateCommand();

                //更新数据库
                adapter.Update(dt);

                dt.AcceptChanges();
            }
            finally
            {
                if (conn.Connection.State == ConnectionState.Open)
                    conn.Connection.Close();
            }
        }

        public static void BuildCommandObjects(string conString, string cmdtxt, ref SqlCommand insertCmd, ref SqlCommand updateCmd, ref SqlCommand deleteCmd)
        {
            if ((conString == null) || (conString.Trim().Length == 0)) throw new ArgumentNullException( "conString" );
            if ((cmdtxt == null) || (cmdtxt.Length == 0)) throw new ArgumentNullException( "cmdtxt" );

            try
            {
                using (SqlConnection sqlConnection = new SqlConnection(conString))
                {
                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdtxt, sqlConnection))
                    {
                        using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
                        {
                            insertCmd = cmdBuilder.GetInsertCommand();
                            updateCmd = cmdBuilder.GetUpdateCommand();
                            deleteCmd = cmdBuilder.GetDeleteCommand();
                        }
                    }
                }
            }
            catch //(Exception ex)
            {
                throw;// new MyException(string.Format("Building command objects for table {0} failed", tableName), ex);
            }
        }

        static void Main(string[] args)
        {
            string theConnectionString = "Data Source=86BBAJI3MJ0T1JY;Initial Catalog=VideoGameStoreDB;Integrated Security=SSPI;";

            SqlConnection theConnection = new SqlConnection(theConnectionString);
            theConnection.Open();//打开数据库连接

            if (theConnection.State == ConnectionState.Open)
                Console.WriteLine("Database Connection is open!\n");
            SqlCommand theCommend = new SqlCommand();

            theCommend.Connection = theConnection;
            theCommend.CommandText = "SELECT * FROM product";
            try
            {
                theCommend.CommandType = CommandType.Text;
                SqlDataAdapter theDataAdapter = new SqlDataAdapter(theCommend);
                SqlCommandBuilder theCommendBuilder = new SqlCommandBuilder(theDataAdapter);
                Console.WriteLine(theCommendBuilder.GetInsertCommand().CommandText + "\n\n");
                Console.WriteLine(theCommendBuilder.GetUpdateCommand().CommandText + "\n\n");
                Console.WriteLine(theCommendBuilder.GetDeleteCommand().CommandText + "\n\n");
            }
            catch (SqlException sqlexception)
            {
                Console.WriteLine(sqlexception);
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
            }
        }

		/// <summary>
		/// Permet de mettre a jour la base de donnees
		/// </summary>
		/// <param name="request">requete ayant permis d'avoir la table de base</param>
		/// <param name="table">la table dans laquelle il y a les modifications</param>
		/// <returns>Le nombre de lignes traitees</returns>
		private int UpdateCommandBuilder(string request, DataTable table) {
			int res = 0;
			
			using(SqlConnection sqlConnection = new SqlConnection(ConnectString)) {
				sqlConnection.Open();
				SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
				SqlCommand sqlCommand = new SqlCommand(request, sqlConnection, sqlTransaction);
				SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

				SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

				sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
				sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand();
				sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();

				sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

				try {
					res = sqlDataAdapter.Update(table);
					sqlTransaction.Commit();
				} catch (System.Data.SqlClient.SqlException exc) {
					sqlTransaction.Rollback();
				}
			}
				return res;
		}

 /// <summary>
 /// 保存表修改到数据库
 /// </summary>
 /// <returns>保存的行数</returns>
 public int SaveTable()
 {
     int nRet = 0;
     if (mTableChanged || true)
     {
         try
         {
             mAdapter = new SqlDataAdapter(mSqlCmd, mConn);
             SqlCommandBuilder cmd = new SqlCommandBuilder(mAdapter);
             mAdapter.UpdateCommand = cmd.GetUpdateCommand();
             DataTable tbl = mTable.GetChanges();
             if (tbl != null && tbl.Rows.Count > 0)
             {
                 nRet = mAdapter.Update(tbl);
             }
             mTable.AcceptChanges();
             mTableChanged = false;
         }
         catch(Exception ex)
         {
             throw ex;
         }
     }
     return nRet;
 }

        /// <summary>
        /// Permet de faire une requète de type UPDATE ou INSERT INTO
        /// </summary>
        /// <param name="rqt"> Requète</param>
        /// <param name="dt"> Database à modifier</param>
        /// <returns></returns>
        public int Update(string rqt, DataTable dt)
        {
            if (sqlConnect != null)
            {
                SqlTransaction trans = sqlConnect.BeginTransaction();
                SqlCommand sqlCmd = new SqlCommand(rqt, sqlConnect, trans);
                SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
                SqlCommandBuilder build = new SqlCommandBuilder(sqlDA);
                sqlDA.UpdateCommand = build.GetUpdateCommand();
                sqlDA.InsertCommand = build.GetInsertCommand();
                sqlDA.DeleteCommand = build.GetDeleteCommand();

                sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                try
                {
                    int res = sqlDA.Update(dt);
                    trans.Commit();
                    return res;
                }
                catch (DBConcurrencyException)
                {
                    trans.Rollback();
                }
            }
            return 0;
        }

        public void test()
        {
            SqlConnection cn = new SqlConnection();
            DataSet MesInscriptionsDataSet = new DataSet();
            SqlDataAdapter da;
            SqlCommandBuilder cmdBuilder;

            //Set the connection string of the SqlConnection object to connect
            //to the SQL Server database in which you created the sample
            //table.
            cn.ConnectionString = Splash.STR_CON;
            cn.Open();

            //Initialize the SqlDataAdapter object by specifying a Select command
            //that retrieves data from the sample table.
            da = new SqlDataAdapter("select * from Inscription", cn);

            //Initialize the SqlCommandBuilder object to automatically generate and initialize
            //the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
            cmdBuilder = new SqlCommandBuilder(da);

            //Populate the DataSet by running the Fill method of the SqlDataAdapter.
            da.Fill(MesInscriptionsDataSet, "Inscription");

            //Display the Update, Insert, and Delete commands that were automatically generated
            //by the SqlCommandBuilder object.
            tbx_debug.Text = "Update command Generated by the Command Builder : \r\n";
            tbx_debug.Text += "================================================= \r\n";
            tbx_debug.Text += cmdBuilder.GetUpdateCommand().CommandText;
            tbx_debug.Text += "         \r\n";

            tbx_debug.Text += "Insert command Generated by the Command Builder : \r\n";
            tbx_debug.Text += "================================================== \r\n";
            tbx_debug.Text += cmdBuilder.GetInsertCommand().CommandText;
            tbx_debug.Text += "         \r\n";

            tbx_debug.Text += "Delete command Generated by the Command Builder : \r\n";
            tbx_debug.Text += "================================================== \r\n";
            tbx_debug.Text += cmdBuilder.GetDeleteCommand().CommandText;
            tbx_debug.Text += "         \r\n";

            //Write out the value in the CustName field before updating the data using the DataSet.
            tbx_debug.Text += "Année before Update : " + MesInscriptionsDataSet.Tables["Inscription"].Rows[0]["année"] + "\r\n";

            //Modify the value of the CustName field.
            MesInscriptionsDataSet.Tables["Inscription"].Rows[0]["année"] = "2099" + "\r\n";

            //Post the data modification to the database.
            da.Update(MesInscriptionsDataSet, "Inscription");

            tbx_debug.Text += "Année updated successfully \r\n";

            //Close the database connection.
            cn.Close();

            //Pause
            Console.ReadLine();
        }

    //Generic SQL Delete method
    public int Delete(string strTable, string strWhere)
    {
        SqlDataAdapter da;
        SqlCommandBuilder cb;
        DataSet ds;
        DataRow[] aDr = null;
        string strSQL = "";
        int intResult = 0;

        try
        {
            if (CreateConnection())
            {
                strSQL = "SELECT * FROM " + strTable;
                da = new SqlDataAdapter();

                //Get the dataset using the Select query that has been composed
                ds = GetDataSet(strTable, strSQL, ref da);

                //use the command builder to generate the Delete and Update commands that we'll need later
                cb = new SqlCommandBuilder(da);
                da.DeleteCommand = cb.GetDeleteCommand();
                da.UpdateCommand = cb.GetUpdateCommand();

                //Get the row to Delete using where clause, if none supplied get all rows
                if (strWhere.Length > 0)
                {
                    aDr = ds.Tables[strTable].Select(strWhere);
                }
                else
                {
                    aDr = ds.Tables[strTable].Select();
                }

                if (aDr != null)
                {
                    //Loop through each row and Delete it
                    foreach (DataRow dr in aDr)
                    {
                        dr.Delete();
                    }
                }

                //Update table and determine number of rows affected
                intResult = da.Update(ds, strTable);
            }
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            CloseConnection();
        }

        return intResult;
    }

       public object Execute(SqlCommand Command){
            /* It will let user close connection when finished */
           SqlDataAdapter ResultantAdapter = new SqlDataAdapter(Command);

           SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(ResultantAdapter);
           ResultantAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
           ResultantAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand(true);
           ResultantAdapter.InsertCommand = cmdBuilder.GetInsertCommand(true);

           return ResultantAdapter;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {
                Adp = new SqlDataAdapter("select * from Product_Avail where [email protected] and PROD='N'", con);
                Adp.SelectCommand.Parameters.AddWithValue("@p", ddtitle.Text);
                DataSet ds = new DataSet();
                Adp.Fill(ds, "prod");

                DataRow dr = ds.Tables["prod"].Rows[0];

                dr[4] = "Y";

                SqlCommandBuilder cb = new SqlCommandBuilder(Adp);
                Adp.UpdateCommand = cb.GetUpdateCommand();
                Adp.Update(ds, "prod");

                ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Product Moved To Production!');window.location='Homepage.aspx';</script>'");
            }

            catch (SqlException ex)
            {
                switch (ex.Number)
                {
                    case 4060: // Invalid Database
                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully !');window.location='Homepage.aspx';</script>'");
                        break;
                    case 18456: // Login Failed

                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
                        break;
                    case 547: // ForeignKey Violation

                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
                        break;
                    case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully!');window.location='Homepage.aspx';</script>'");

                        break;
                    case 2601: // Unique Index/Constriant Violation
                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
                        break;
                    default:
                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
                        break;
                }
            }

            catch (IndexOutOfRangeException)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Moved Successfully');window.location='Homepage.aspx';</script>'");
            }
        }

    public void InitializeDataAdapter()
    {
        connection.ConnectionString = connectionString ;

        SqlCommand selectCommand = new SqlCommand("SELECT * FROM Student", connection);
        dataAdapter.SelectCommand = selectCommand;

        SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
        dataAdapter.DeleteCommand = builder.GetDeleteCommand();
        dataAdapter.UpdateCommand = builder.GetUpdateCommand();
        dataAdapter.InsertCommand = builder.GetInsertCommand();
    }

 /// <summary>
 /// 这个静态构造器将读取web.config中定义的全部连接字符串. 
 /// 链接和适配器将同时指向同一db, 因此只需要创建一次.
 /// </summary>
 static DBProcess()
 {
     string constr = ConfigurationManager.ConnectionStrings["MyConn"]
                                         .ConnectionString;
     conn = new SqlConnection(constr);
     string command = "select * from tb_personInfo";
     adapter = new SqlDataAdapter(command, conn);
     SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
     builder.GetDeleteCommand(true);
     builder.GetInsertCommand(true);
     builder.GetUpdateCommand(true);
 }

 private void btnCommandBuilder_Click(object sender, EventArgs e)
 {
     SqlConnection conn = new SqlConnection(@"Data Source=.\SqlExpress;Integrated Security=true;Initial Catalog=Northwind");
     string strSQL = "Select * from Categories";
     SqlDataAdapter dt = new SqlDataAdapter(strSQL, conn);
     DataSet ds = new DataSet();
     dt.Fill(ds);
     SqlCommandBuilder cb = new SqlCommandBuilder(dt);
     Console.WriteLine(cb.GetUpdateCommand().CommandText.ToString());
     conn.Close();
     grvDataView.DataSource = ds.Tables[0];
     //grvDataView.DataBind();
 }

 public RedTable(string tablename, string query, RedContext context, string searchQuery=null )
     : base(tablename)
 {
     this.query = query;
     context.Provider.OpenConnection();
     dataAdapter = new SqlDataAdapter(query, context.Provider.Connection);
     var builder = new SqlCommandBuilder(dataAdapter);
     FillAdapter(context);
     dataAdapter.UpdateCommand = builder.GetUpdateCommand();
     context.Provider.CloseConnection();
     ComboBoxes = new Dictionary<string, RedComboBox>();
     columnHeaders = new Dictionary<string, string>();
     SearchQuery = searchQuery;
 }