C# SqlCommandBuilder.GetInsertCommand方法代码示例

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

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

        private void button2_Click(object sender, EventArgs e) // Ìí¼Ó
        {
            UpdateGVData();

            string sql = string.Format("SELECT map, Model FROM npc WHERE RepresentID = {0}", m_RepresentID);
            DataTable tbl_trash = Helper.GetDataTable(sql, Conn);
            string strMap = tbl_trash.Rows[0]["map"].ToString().Trim();
            string strName = tbl_trash.Rows[0]["Model"].ToString().Trim();

            sql = string.Format("SELECT MAX(_index) FROM dic_npc_socket_desc");
            tbl_trash = Helper.GetDataTable(sql, Conn);
            int newIndex = tbl_trash.Rows[0][0] == DBNull.Value ? 1 : Convert.ToInt32(tbl_trash.Rows[0][0]) + 1;

            DataTable tbl = this.dataGridViewX1.DataSource as DataTable;
            DataRow row = tbl.NewRow();
            row["_index"] = newIndex;
            row["Map"] = strMap;
            row["RepresentID"] = m_RepresentID;
            row["Name"] = strName;
            row["Socket"] = m_Socket;
            row["FileName"] = this.textBox1.Text;
            tbl.Rows.Add(row);

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_adp);
            m_adp.InsertCommand = cmdBuilder.GetInsertCommand();

            int val = m_adp.Update(tbl);
            tbl.AcceptChanges();
        }

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

        protected void btnAddRates_Click(object sender, EventArgs e)
        {
            try
            {

                DataSet rateds = new DataSet();
                rateadp.Fill(rateds, "Rate");
                DataRow dq = rateds.Tables["Rate"].NewRow();

                //Rates Table
                dq[1] = txtProductID.Text;
                dq[2] = double.Parse(txtamount.Text);
                dq[3] = ddstate.Text;
                dq[4] = txtEffDate.Text;
                dq[5] = txtEndDate.Text;
                dq[6] = "N";
                dq[8] = DateTime.Now.ToString();
                dq[9] = Int32.Parse(txtDiscount.Text);

                rateds.Tables["Rate"].Rows.Add(dq);

                SqlCommandBuilder com1 = new SqlCommandBuilder(rateadp);
                rateadp.InsertCommand = com1.GetInsertCommand();
                rateadp.Update(rateds, "Rate");

                ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Rate Added Successfully!');window.location='Show_Products.aspx';</script>'");

            }

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

                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
                        break;
                    case 547: // ForeignKey Violation

                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
                        break;
                    case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Product Id Already Exists!');window.location='Add_Product.aspx';</script>'");

                        break;
                    case 2601: // Unique Index/Constriant Violation
                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
                        break;

                    default:

                        break;
                }
            }
        }

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

        protected void Button1_Click(object sender, EventArgs e)
        {
            try
            {

                DataSet ds = new DataSet();
                adp.Fill(ds, "Pcr");
                DataRow dr = ds.Tables["Pcr"].NewRow();

                dr[0] = txttitle.Text;
                dr[1] = ddservices.SelectedValue;

                ds.Tables["Pcr"].Rows.Add(dr);

                SqlCommandBuilder com = new SqlCommandBuilder(adp);
                adp.InsertCommand = com.GetInsertCommand();
                adp.Update(ds, "Pcr");

                value = ddservices.SelectedValue;
                titlevalue = txttitle.Text;
                Cache["V1"] = value;
                Cache["V2"] = titlevalue;
                Response.Redirect("Show_Products.aspx");

            }

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

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

                        ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');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('Title Already Exists!');window.location='NewPCR.aspx';</script>'");

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

                        break;
                }
            }
        }

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

        public void ImportFile()
        {
            // get db table
            SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM " + m_strTableName, Conn);
            adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet ds = new DataSet();
            adp.Fill(ds);
            System.Data.DataTable tbl = ds.Tables[0];

            // clear
            tbl.BeginInit();
            tbl.Rows.Clear();

            // 获得另外一张表npc, 读取name 和 map
            SqlDataAdapter adp2 = new SqlDataAdapter("SELECT * FROM npc", Conn);
            adp2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet ds2 = new DataSet();
            adp2.Fill(ds2);
            System.Data.DataTable tbl_npc = ds2.Tables[0];

            // import
            string fileContent = FileToString(m_strFileFullName);
            fileContent = fileContent.Trim();
            string[] arrContents = fileContent.Split(new string[] { "NpcPortraitCameraInfo", "[", "]", "{", "}", "=", ",", "\r\n", "\t", " " },
                StringSplitOptions.RemoveEmptyEntries);
            int arr_index = 0;
            for (int i = 0; i < arrContents.Length / 9; i++)
            {
                DataRow newRow = tbl.NewRow();
                newRow.BeginEdit();
                newRow["RepresentID"] = arrContents[arr_index++];
                newRow["PositionX"] = arrContents[arr_index++];
                newRow["PositionY"] = arrContents[arr_index++];
                newRow["PositionZ"] = arrContents[arr_index++];
                newRow["LookatX"] = arrContents[arr_index++];
                newRow["LookatY"] = arrContents[arr_index++];
                newRow["LookatZ"] = arrContents[arr_index++];
                newRow["Width"] = arrContents[arr_index++];
                newRow["Height"] = arrContents[arr_index++];
                DataRow r = tbl_npc.Rows.Find(newRow["RepresentID"]);
                newRow["Map"] = r["map"].ToString().Trim();
                newRow["Name"] = r["Model"].ToString().Trim();
                tbl.Rows.Add(newRow);
                newRow.EndEdit();
            }

            // upload
            tbl.EndInit();
            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
            adp.InsertCommand = cmdBuilder.GetInsertCommand();
            int val = adp.Update(tbl);
            tbl.AcceptChanges();
        }

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