C# System.Data.SqlClient.SqlDataAdapter.Update方法代码示例

        private void btnOk_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlTransaction tran = null;
            try{
                this.receipt["Vendor"] = this.cbxVendors.SelectedValue;
                this.receipt["VendorName"] = this.cbxVendors.Text;

                this.receipt["Payed"] = dtpPeceiptDate.Value;
                this.receipt["Price"] = this.total_price;
                this.receipt["Discount"] = this.total_discount;
                if ( ( this.cbxDiscountCards.SelectedValue == null ) ||
                    System.Convert.IsDBNull( this.cbxDiscountCards.SelectedValue ) )
                    this.receipt["DiscountCard"] = System.Convert.DBNull;
                else
                    this.receipt["DiscountCard"] = this.cbxDiscountCards.SelectedValue;

                this.receipt["Number"] = this.tbxReceiptNumber.Text;
                this.receipt["Comment"] = this.tbxComment.Text;

                this.receipt["Created"] = System.DateTime.Now;
                this.receipt["Updated"] = System.DateTime.Now;

                this.cConnection.Open();
                using ( tran = this.cConnection.BeginTransaction()){
            //            using ( System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope() ){
            //                System.Data.SqlClient.SqlDataAdapter sdarc = new System.Data.SqlClient.SqlDataAdapter();
            //                System.Data.SqlClient.SqlDataAdapter rsda = new System.Data.SqlClient.SqlDataAdapter();
                    System.Data.SqlClient.SqlCommand rcmd = new System.Data.SqlClient.SqlCommand();
                    if (this.isNew){
                        //sdarc.InsertCommand = Purchases.Receipt.InsertCommand(this.receipt);
                        //sdarc.InsertCommand.Connection = this.cConnection;
                        //sdarc.InsertCommand.Transaction = tran;
                        System.Data.SqlClient.SqlCommand idcmd = new System.Data.SqlClient.SqlCommand("SELECT NEWID() AS MaxID FROM Purchases.Receipts", this.cConnection, tran);
            //                    int receipt_id = (int)idcmd.ExecuteScalar();
                        this.receipt["ReceiptID"] = idcmd.ExecuteScalar();
                        rcmd = Purchases.Receipt.InsertCommand(this.receipt);

                        for (int k = 0; k < this.contents.Rows.Count; k++){
                            this.contents.Rows[k]["ReceiptID"] = this.receipt["ReceiptID"];
                        }
                    }else{
                        //sdarc.UpdateCommand = Purchases.Receipt.UpdateCommand(this.receipt);
                        //sdarc.UpdateCommand.Connection = this.cConnection;
                        //sdarc.UpdateCommand.Transaction = tran;
                        rcmd = Purchases.Receipt.UpdateCommand(this.receipt);
            //                    rsda.UpdateCommand = rcmd;
                    }
                    rcmd.Connection = this.cConnection;
                    rcmd.Transaction = tran;
                    rcmd.ExecuteNonQuery();
            //                int id = (int)rcmd.ExecuteScalar();
            //                System.Data.SqlClient.SqlParameter p = rcmd.Parameters.Add("@ReceiptID", SqlDbType.Int);
            //                p.Direction = ParameterDirection.Output;
            //                rsda.Update(new DataRow[] { this.receipt });

            //                sdarc.Update(new DataRow[] { this.receipt } );

                    System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
                    sda.InsertCommand = Purchases.ReceiptContent.InsertCommand();
                    sda.InsertCommand.Connection = this.cConnection;
                    sda.InsertCommand.Transaction = tran;
                    sda.UpdateCommand = Purchases.ReceiptContent.UpdateCommand();
                    sda.UpdateCommand.Connection = this.cConnection;
                    sda.UpdateCommand.Transaction = tran;
                    sda.DeleteCommand = Purchases.ReceiptContent.DeleteCommand();
                    sda.DeleteCommand.Connection = this.cConnection;
                    sda.DeleteCommand.Transaction = tran;
                    sda.Update(this.contents);
                    //TODO Update discount card
                    tran.Commit();
                }
                this.cConnection.Close();
                this.DialogResult = DialogResult.OK;
            }catch( System.Exception ex ){
                MessageBox.Show(ex.Message);
                if( tran != null ) tran.Rollback();
            }finally{
                if (this.cConnection.State == ConnectionState.Open) this.cConnection.Close();
            }
        }

        public DataTable SaveData()
        {
            _detailRow.EndEdit();
            //Will return a data table so
            //can merge with typed data table

            //Todo: Refactor
            string connectionString = string.Format(
                @"Data Source=.\sqlexpress; Initial Catalog={0}; Integrated Security=true", "Northwind");
            string commandText = "select * from customers";
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(commandText,connectionString);
            //http://stackoverflow.com/q/6569012/139698
            System.Data.SqlClient.SqlCommandBuilder projectBuilder = new System.Data.SqlClient.SqlCommandBuilder(da);
            DataTable newDt = _dataSources[0].GetChanges(DataRowState.Modified);
            da.Update(newDt);

            _dataSources[0].AcceptChanges();

            return _dataSources[0];
        }

        bool ImportSaved(string filename, out string error)
        {
            bool done = false;
            error = "";
            try
            {
                System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
                command.CommandText = "SELECT * FROM Purchases.Receipts\n" +
                                      "SELECT * FROM Purchases.ReceiptContents\n" +
                                      "SELECT * FROM Producer.Products\n" +
                                      "SELECT * FROM Producer.ProductTypes\n" +
                                      "SELECT * FROM Producer.Categories\n" +
                                      "SELECT * FROM Purchases.Vendors\n" +
                                      "SELECT * FROM Purchases.DiscountCards\n" +
                                      "SELECT * FROM Purchases.CardBalance\n" +
                                      "SELECT * FROM Producer.Makers\n" +
                                      "SELECT * FROM Persons.Users\n"
                                      ;
                command.Connection = this.cConnection;
                System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(command);
                string table = "Receipts";
                System.Data.DataSet dst_ds = new System.Data.DataSet(table);
                sda.Fill(dst_ds);
                dst_ds.Tables[0].TableName = "Receipts";
                dst_ds.Tables[1].TableName = "ReceiptContents";
                dst_ds.Tables[2].TableName = "Products";
                dst_ds.Tables[3].TableName = "ProductTypes";
                dst_ds.Tables[4].TableName = "Categories";
                dst_ds.Tables[5].TableName = "Vendors";
                dst_ds.Tables[6].TableName = "DiscountCards";
                dst_ds.Tables[7].TableName = "CardBalance";
                dst_ds.Tables[8].TableName = "Makers";
                dst_ds.Tables[9].TableName = "Users";

                System.Data.DataSet rec_ds = new System.Data.DataSet(table);
                string xsd_file = "";
                string xml_file = "";
                if (filename.EndsWith(".xsd", true, System.Globalization.CultureInfo.CurrentCulture))
                {
                    xsd_file = filename;
                    xml_file = filename.Substring(0, filename.Length - 4) + ".xml";
                }
                else if (filename.EndsWith(".xml", true, System.Globalization.CultureInfo.CurrentCulture))
                {
                    xml_file = filename;
                    xsd_file = filename.Substring(0, filename.Length - 4) + ".xsd";
                }
                else
                {
                    xml_file = filename + ".xml";
                    xsd_file = filename + ".xsd";
                }
                rec_ds.ReadXmlSchema(xsd_file);
                rec_ds.ReadXml(xml_file);

                dst_ds.Merge(rec_ds, true);
                //                dst_ds.
                System.Data.DataSet cng_ds = dst_ds.GetChanges(DataRowState.Added);
                if (cng_ds != null)
                {
                    this.cConnection.Open();
                    System.Data.SqlClient.SqlTransaction tran = this.cConnection.BeginTransaction();
                    sda = new System.Data.SqlClient.SqlDataAdapter();

                    // Merging vendors
                    sda.InsertCommand = Vault.Users.Insert();
                    sda.InsertCommand.Connection = this.cConnection;
                    sda.InsertCommand.Transaction = tran;
                    sda.UpdateCommand = Vault.Users.Update();
                    sda.UpdateCommand.Connection = this.cConnection;
                    sda.UpdateCommand.Transaction = tran;
                    sda.Update(cng_ds.Tables["Users"]);

                    // Merging vendors
                    sda.InsertCommand = Purchases.Vendor.Insert();
                    sda.InsertCommand.Connection = this.cConnection;
                    sda.InsertCommand.Transaction = tran;
                    sda.UpdateCommand = Purchases.Vendor.Update();
                    sda.UpdateCommand.Connection = this.cConnection;
                    sda.UpdateCommand.Transaction = tran;
                    sda.Update(cng_ds.Tables["Vendors"]);

                    // Merging discount cards
                    sda.InsertCommand = Purchases.DiscountCard.Insert();
                    sda.InsertCommand.Connection = this.cConnection;
                    sda.InsertCommand.Transaction = tran;
                    sda.UpdateCommand = Purchases.DiscountCard.Update();
                    sda.UpdateCommand.Connection = this.cConnection;
                    sda.UpdateCommand.Transaction = tran;
                    sda.Update(cng_ds.Tables["DiscountCards"]);

                    // Merging discount cards balance
                    sda.InsertCommand = Purchases.CardBalance.Insert();
                    sda.InsertCommand.Connection = this.cConnection;
                    sda.InsertCommand.Transaction = tran;
                    sda.UpdateCommand = Purchases.CardBalance.Update();
                    sda.UpdateCommand.Connection = this.cConnection;
                    sda.UpdateCommand.Transaction = tran;
                    sda.Update(cng_ds.Tables["CardBalance"]);

//.........这里部分代码省略.........

        private void btnOk_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlTransaction tran = null;
            try{
                this.receipt["Vendor"] = this.cbxVendors.SelectedValue;
                this.receipt["VendorName"] = this.cbxVendors.Text;

                this.receipt["Paid"] = this.dtpPeceiptDate.Value;
                this.receipt["Price"] = this.total_price;
                this.receipt["Discount"] = this.total_discount;
                if ( ( this.cbxDiscountCards.SelectedValue == null ) ||
                    System.Convert.IsDBNull( this.cbxDiscountCards.SelectedValue ) )
                    this.receipt["DiscountCard"] = System.Convert.DBNull;
                else
                    this.receipt["DiscountCard"] = this.cbxDiscountCards.SelectedValue;

                this.receipt["Number"] = this.tbxReceiptNumber.Text;
                this.receipt["Comment"] = this.tbxComment.Text;

                this.receipt["Created"] = System.DateTime.Now;
                this.receipt["Updated"] = System.DateTime.Now;

                this.cConnection.Open();
                using ( tran = this.cConnection.BeginTransaction()){
                    System.Data.SqlClient.SqlCommand rcmd = new System.Data.SqlClient.SqlCommand();
                    if (this.type == ReceiptType.New ||
                        this.type == ReceiptType.Cloned ){
                        rcmd = Purchases.Receipt.InsertCommand(this.receipt);
                    }else{
                        rcmd = Purchases.Receipt.UpdateCommand(this.receipt);
                    }
                    rcmd.Connection = this.cConnection;
                    rcmd.Transaction = tran;
                    rcmd.ExecuteNonQuery();

                    System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter();
                    sda.InsertCommand = Purchases.ReceiptContent.InsertCommand();
                    sda.InsertCommand.Connection = this.cConnection;
                    sda.InsertCommand.Transaction = tran;
                    sda.UpdateCommand = Purchases.ReceiptContent.UpdateCommand();
                    sda.UpdateCommand.Connection = this.cConnection;
                    sda.UpdateCommand.Transaction = tran;
                    sda.DeleteCommand = Purchases.ReceiptContent.DeleteCommand();
                    sda.DeleteCommand.Connection = this.cConnection;
                    sda.DeleteCommand.Transaction = tran;
                    sda.Update(this.contents);
                    //TODO Update discount card balance
                    tran.Commit();
                }
                this.cConnection.Close();
                this.DialogResult = DialogResult.OK;
            }catch( System.Exception ex ){
                MessageBox.Show(ex.Message);
                if( tran != null ) tran.Rollback();
            }finally{
                if (this.cConnection.State == ConnectionState.Open) this.cConnection.Close();
            }
        }

        public static void UpdateDataTable(System.Data.DataTable dt, string strSQL)
        {
            using (System.Data.Common.DbConnection connection = GetConnection())
            {

                using (System.Data.Common.DbDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter())
                {

                    using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = strSQL;

                        System.Data.Common.DbCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder();
                        cb.DataAdapter = daInsertUpdate;

                        daInsertUpdate.SelectCommand = cmdSelect;
                        daInsertUpdate.UpdateCommand = cb.GetUpdateCommand();
                        daInsertUpdate.DeleteCommand = cb.GetDeleteCommand();
                        daInsertUpdate.InsertCommand = cb.GetInsertCommand();

                        daInsertUpdate.Update(dt);
                    } // End Using cmdSelect

                } // End Using daInsertUpdate

            } // End Using con
        }

        public static void InsertUpdateDataTable(string strTableName, System.Data.DataTable dt)
        {
            string connectionString = GetConnectionString();
            string strSQL = string.Format("SELECT * FROM [{0}] WHERE 1 = 2 ", strTableName.Replace("]", "]]"));

            using (System.Data.Common.DbConnection connection = GetConnection())
            {

                using (System.Data.Common.DbDataAdapter daInsertUpdate = new System.Data.SqlClient.SqlDataAdapter())
                {

                    using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = strSQL;

                        System.Data.Common.DbCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder();
                        cb.DataAdapter = daInsertUpdate;

                        daInsertUpdate.SelectCommand = cmdSelect;
                        daInsertUpdate.InsertCommand = cb.GetInsertCommand();
                        daInsertUpdate.UpdateCommand = cb.GetUpdateCommand();
                        daInsertUpdate.DeleteCommand = cb.GetDeleteCommand();

                        daInsertUpdate.Update(dt);
                    } // End Using cmdSelect

                } // End Using daInsertUpdate

            } // End Using connection
        }