C# SqlCommandBuilder.GetDeleteCommand方法代码示例

        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);
            }
        }

    protected void gridStudent_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        SqlConnection connection = SqlHelper.GetConnection();
        var dataAdapter = new SqlDataAdapter(selectCommandText, connection);
        var builder = new SqlCommandBuilder(dataAdapter);
        dataAdapter.DeleteCommand = builder.GetDeleteCommand();
        DataSet dataSet = SqlHelper.GetDataSetBySqlCommand(selectCommandText, connection);

        string studentID = (gridStudent.Rows[e.RowIndex].Cells[1].Controls[0] as TextBox).Text;
        string name = (gridStudent.Rows[e.RowIndex].Cells[2].Controls[0] as TextBox).Text;
        string gender = (gridStudent.Rows[e.RowIndex].Cells[3].Controls[0] as TextBox).Text;
        string dayOfBirth = (gridStudent.Rows[e.RowIndex].Cells[4].Controls[0] as TextBox).Text;
        string address = (gridStudent.Rows[e.RowIndex].Cells[5].Controls[0] as TextBox).Text;
        string department = (gridStudent.Rows[e.RowIndex].Cells[6].Controls[0] as TextBox).Text;

        //获取 DataSet 中与点击了 “编辑” 按钮的这行对应的数据行
        DataRow row = dataSet.Tables[0].Rows[e.RowIndex];
        //修改其中各字段的值
        row["StudentID"] = studentID;
        row["Name"] = name;
        row["Gender"] = gender;
        row["DayOfBirth"] = dayOfBirth;
        row["Address"] = address;
        row["Department"] = department;

        //提交到数据库中
        dataAdapter.Update(dataSet);
        gridStudent.EditIndex = -1;
        ShowData();
    }

		/// <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>
        /// 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 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);
            }
        }

    //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 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();
        }

       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;
        }

    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);
 }

        public void updateAll()
        {
            if (ds.HasChanges())
            {
                SqlConnection conexion = new SqlConnection(strConexion);
                conexion.Open();

                SqlDataAdapter ad = new SqlDataAdapter("select * from usuarios", conexion); // se hace la select para generar automáticamente le comando select, update y delete
                SqlCommandBuilder sqlcb = new SqlCommandBuilder(ad);
                ad.InsertCommand = sqlcb.GetInsertCommand();
                ad.UpdateCommand = sqlcb.GetUpdateCommand();
                ad.DeleteCommand = sqlcb.GetDeleteCommand();
                conexion.Close();
            }
        }

        public void ExtractTableParameters(string TableName, IDbDataAdapter adapter, 
            out DatabaseCache InsertCache, 
            out DatabaseCache DeleteCache,
            out DatabaseCache UpdateCache,
            out DatabaseCache IsExistCache,
            out DataTable dt
            )
        {
            adapter.SelectCommand.CommandText = "select top 1 * from " + TableName;

            DataSet ds = new DataSet();

            dt = adapter.FillSchema(ds, SchemaType.Source)[0];

            dt.TableName = TableName;

            SqlCommandBuilder builder = new SqlCommandBuilder(adapter as SqlDataAdapter);

            builder.ConflictOption = ConflictOption.OverwriteChanges;
            //builder.SetAllValues = false;
            SqlCommand InsertCmd = builder.GetInsertCommand(true);
            builder.ConflictOption = ConflictOption.OverwriteChanges;

            InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters);
            InsertCache.CurrentTable = dt;

            foreach (DataColumn c in dt.Columns)
            {
                if (c.AutoIncrement)
                {
                    InsertCache.IsHaveAutoIncrement = true;
                    InsertCache.SQL += ";Select @@IDENTITY;";
                    break;
                }
            }

            SqlCommand UpdateCmd = builder.GetUpdateCommand(true);
            UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters);
            UpdateCache.CurrentTable = dt;

            SqlCommand DeleteCmd = builder.GetDeleteCommand(true);
            DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
            DeleteCache.CurrentTable = dt;

            IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
            IsExistCache.CurrentTable = dt;
            IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) ");
        }

        public void updateAll()
        {
            if (ds.HasChanges())
            {
                SqlConnection conexion = new SqlConnection(strConexion);
                conexion.Open();

                SqlDataAdapter ad = new SqlDataAdapter("select * from usuarios left join Historiales on usuarios.NssUsuario = Historiales.usuario", conexion); // se hace la select para generar automáticamente le comando select, update y delete
                SqlCommandBuilder sqlcb = new SqlCommandBuilder(ad);
                ad.InsertCommand = sqlcb.GetInsertCommand();
                ad.UpdateCommand = sqlcb.GetUpdateCommand();
                ad.DeleteCommand = sqlcb.GetDeleteCommand();
                ad.Update(ds.Usuarios);
                conexion.Close();
                ds.AcceptChanges(); // Elimina las marcas de inserción, actualización o borrado del dataset
            }
        }

        private void buttonX1_Click(object sender, EventArgs e) // 删除所选
        {
            ArrayList lsIdToDel = new ArrayList();

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Selected)
                {
                    object v = row.Cells["ItemID"].Value;
                    if (v == null) continue;
                    int itemID = Convert.ToInt32(v);
                    lsIdToDel.Add(itemID);
                }
            }

            foreach (int i in lsIdToDel)
            {
                DataRow RowToDel = m_tbl.Rows.Find(i);
                if (RowToDel != null)
                    RowToDel.Delete();
            }

            int nAffectedRowCount = 0;
            try
            {
                string sql = "SELECT * FROM tbl_icon_item";
                SqlDataAdapter adp = new SqlDataAdapter(sql, Conn);
                SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
                adp.DeleteCommand = cmdBuilder.GetDeleteCommand();
                nAffectedRowCount = adp.Update(m_tbl);
                m_tbl.AcceptChanges();
                MessageBox.Show("数据删除成功，删除行数: " + nAffectedRowCount.ToString() + " 。",
                    "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                MessageBox.Show("数据更新时产生异常: " + ex.ToString() + "\r\n\r\n新增数据将不被保存",
                    "删除数据", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

 private void btnComandBuilder2_Click(object sender, EventArgs e)
 {
     String sql;
     //CommandBuilder doesn't support multiple table queries
     sql = @"Select Orders.OrderDate, Customers.CompanyName from Orders ";
     sql+="Join Customers On Orders.CustomerID=Customers.CustomerID";
     sql = @"SELECT* FROM Categories";
     using (SqlConnection conn = new SqlConnection(@"Data Source=.\SqlExpress;Integrated Security=true;Initial Catalog=Northwind"))
     {
         using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
         {
             //This is supported only against single tables.
             using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
             {
                 builder.QuotePrefix = "[";
                 builder.QuoteSuffix = "]";
                 Console.WriteLine(builder.GetUpdateCommand().CommandText);
                 Console.WriteLine(builder.GetInsertCommand().CommandText);
                 Console.WriteLine(builder.GetDeleteCommand().CommandText);
             }
         }
     }
 }