C# SqlDataAdapter.Update方法代码示例

        public static void DataAdapterExample()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                var sqlCommandText = "SELECT * FROM BrokenCars;";
                var dataAdapter = new SqlDataAdapter(sqlCommandText, sqlConnection);

                var dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                var insertText = "INSERT INTO BrokenCars(name) VALUES(@Name)";
                var insertCommand = new SqlCommand(insertText, sqlConnection);
                insertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 255, "name");
                dataAdapter.InsertCommand = insertCommand;
                var row = dataTable.NewRow();
                row["Name"] = "ZAZ";
                dataTable.Rows.Add(row);
                dataAdapter.Update(dataTable);

                //var deleteRow = dataTable.Rows[4];
                //dataTable.Rows.Remove(deleteRow);
                //dataAdapter.Update(dataTable);

                sqlConnection.Open();
                var delFromTable = @"DELETE FROM BrokenCars WHERE ID > 4;";
                var delCommand = new SqlCommand(delFromTable, sqlConnection);
                dataAdapter.DeleteCommand = delCommand;
                dataAdapter.DeleteCommand.ExecuteNonQuery();
                dataAdapter.Update(dataTable);
            }
        }

        private void BindGrid()
        {
            var ds = new DataSet();
            var cs = ConfigurationManager.ConnectionStrings["Msts"].ConnectionString;
            var cnn = new SqlConnection(cs);
            var cmd = new SqlCommand("select * from jobs", cnn) { CommandType = CommandType.Text };
            var sa = new SqlDataAdapter(cmd) { UpdateBatchSize = 3 };
            var scb = new SqlCommandBuilder(sa);

            sa.RowUpdated += (x, y) =>
            {
                this.msg.Text += "<br />Rows updated: " + y.RecordsAffected.ToString();
            };

            sa.Fill(ds, "jobs");

            this.gvpicker.DataSource = (from DataRow j in ds.Tables["jobs"].Rows
                                        where j.Field<string>("job_desc").Contains("a")
                                        select j).CopyToDataTable().DefaultView;
            this.gvpicker.DataBind();

            foreach (DataRow item in ds.Tables["jobs"].Rows)
            {
                item["job_desc"] = item["job_desc"].ToString() + "plop";
            }

            sa.Update(ds, "jobs");

            foreach (DataRow item in ds.Tables["jobs"].Rows)
            {
                item["job_desc"] = item["job_desc"].ToString().Replace("plop", string.Empty);
            }

            sa.Update(ds, "jobs");
        }

        public bool UpdateAdmins(DataSet ds)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
                {
                    //declare adapter
                    SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from Admins", conn);
                    //set commands
                    dataAdapter.UpdateCommand = new SqlCommand("Update Admins Set" +
                        " Password = @Password, Salt = @Salt" +
                        " Where Username = @Username", conn);

                    dataAdapter.DeleteCommand = new SqlCommand("delete from Admins where Username = @Username", conn);

                    dataAdapter.InsertCommand = new SqlCommand("insert into Admins values(@Username, @password, @Salt)", conn);

                    //add parameters
                    dataAdapter.UpdateCommand.Parameters.Add("@Password", SqlDbType.Char, 100, "Password");
                    dataAdapter.UpdateCommand.Parameters.Add("@Salt", SqlDbType.Char, 50, "Salt");

                    dataAdapter.InsertCommand.Parameters.Add("@Username", SqlDbType.Char, 50, "Username");
                    dataAdapter.InsertCommand.Parameters.Add("@Password", SqlDbType.Char, 100, "Password");
                    dataAdapter.InsertCommand.Parameters.Add("@Salt", SqlDbType.Char, 50, "Salt");

                    //add sql parameters
                    SqlParameter parameter = dataAdapter.UpdateCommand.Parameters.Add("@Username", SqlDbType.Char);
                    parameter.SourceColumn = "Username";
                    parameter.SourceVersion = DataRowVersion.Original;

                    SqlParameter parameter2 = dataAdapter.DeleteCommand.Parameters.Add("@Username", SqlDbType.Char);
                    parameter2.SourceColumn = "Username";
                    parameter2.SourceVersion = DataRowVersion.Original;

                    //execute delete and update
                    int deleted = dataAdapter.Update(ds.Tables[0].Select(null, null, DataViewRowState.Deleted));
                    int updated = dataAdapter.Update(ds.Tables[0].Select(null, null, DataViewRowState.ModifiedCurrent));
                    int added = dataAdapter.Update(ds.Tables[0].Select(null, null, DataViewRowState.Added));

                    //if nothing was updated return false
                    if (deleted == 0 && updated == 0 && added == 0)
                        return false;
                }
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
        }

         public static int ExecuteSQLUpdate(DataTable dt, string sqlsr)
         {

             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISBEPI_DEV"].ToString());
             SqlDataAdapter da = new SqlDataAdapter(sqlsr,conn);            
             using (new SqlCommandBuilder(da))
             {
                 try
                 {
                     DataTable updt = new DataTable();
                     da.Fill(updt);
                     updt.Rows[0]["PD_EDUCATIONANDEXPERIENCE_Q12_1"] = dt.Rows[0]["PD_EDUCATIONANDEXPERIENCE_Q12_1"].ToString();
                     updt.Rows[0]["PD_EDUCATIONANDEXPERIENCE_Q12_2"] = dt.Rows[0]["PD_EDUCATIONANDEXPERIENCE_Q12_2"].ToString();
                     updt.Rows[0]["PD_EDUCATIONANDEXPERIENCE_Q12_3"] = dt.Rows[0]["PD_EDUCATIONANDEXPERIENCE_Q12_3"].ToString();
                     updt.Rows[0]["PD_EDUCATION_Q13"] = dt.Rows[0]["PD_EDUCATION_Q13"].ToString();
                     updt.Rows[0]["PD_EDUCATION_Q14"] = dt.Rows[0]["PD_EDUCATION_Q14"].ToString();
                     updt.Rows[0]["PD_EDUCATION_Q15"] = dt.Rows[0]["PD_EDUCATION_Q15"].ToString();
                     updt.Rows[0]["PD_EDUCATION_Q16"] = dt.Rows[0]["PD_EDUCATION_Q16"].ToString();
                     updt.Rows[0]["PD_EDUCATION_Q17"] = dt.Rows[0]["PD_EDUCATION_Q17"].ToString();
                     updt.Rows[0]["PD_EDUCATION_Q17"] = dt.Rows[0]["PD_EDUCATION_Q17"].ToString();
                     conn.Open();
                     int message = da.Update(updt);                     
                     conn.Close();
                     return message;
                 }
                 catch
                 {
                     return 0;
                 }
             }

         }    

        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 TestBook()
        {
            try
            {
                DBManager manager1 = new DBManager();
                SqlConnection connection = manager1.Connection();
                string seletQuery = "select * from Books Order by Name";
                SqlCommand selectCmd = new SqlCommand(seletQuery, connection);
                connection.Open();

                SqlDataAdapter myAdapter = new SqlDataAdapter();
                myAdapter.SelectCommand = selectCmd;

                dt = new DataTable();
                myAdapter.Fill(dt);
                BindingSource bSource = new BindingSource();
                bSource.DataSource = dt;
                dataGridView1.DataSource = dt;
                myAdapter.Update(dt);



                //return null;
            }
            catch (Exception obj)
            {

                throw new Exception("Error", obj);
            }
        }

       public void LoadAllBook()    
        {
            try
            {
                DBManager manager = new DBManager();
                SqlConnection connection = manager.Connection();

                string selectQuery = "SELECT * From Books";
                SqlCommand cmd = new SqlCommand(selectQuery, connection);
                //connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                dataTable = new DataTable();
                adapter.Fill(dataTable);
                BindingSource binding = new BindingSource();
                binding.DataSource = dataTable;
                dataGridView1.DataSource = dataTable;
                adapter.Update(dataTable);


            }
            catch ( SqlException exception)
            {

                MessageBox.Show(exception.Message);
            }
        }

        /// <summary>
        /// Xóa một người dùng khỏi hệ thống
        /// </summary>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public int DeletePayrollItem(DataSet dataSet)
        {
            conn = WorkingContext.GetConnection();

            sqlCommand = new SqlCommand("DeletePayrollItem", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.Add(new SqlParameter("@PayrollItemID", SqlDbType.VarChar, 20, "PayrollItemID"));

            dataAdapter = new SqlDataAdapter();
            dataAdapter.DeleteCommand = sqlCommand;

            try
            {
                conn.Open();
                return dataAdapter.Update(dataSet.Tables[0]);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return 0;
            }
            finally
            {
                conn.Close();
            }
        }

 protected void Add_Click(object sender, EventArgs e)
 {
     DateTime dt = DateTime.Now;
     SqlCommand cmd = new SqlCommand();
     cmd.CommandText = "select * from Company";
     cmd.Connection = con;
     SqlDataAdapter da = new SqlDataAdapter();
     da.SelectCommand = cmd;
     DataSet ds = new DataSet();
     da.Fill(ds, "Company");
     SqlCommandBuilder cb = new SqlCommandBuilder(da);
     DataRow drow = ds.Tables["Company"].NewRow();
     drow["User_Id"] = UserId.Text;
     drow["Category"] = Category.SelectedItem;
     drow["Person"] = ContactPerson.Text;
     drow["C_Name"] = CompanyName.Text;
     drow["Mobile_No"] = Mobile.Text;
     drow["Tagline"] = Tagline.Text;
     drow["Phone"] = Phone.Text;
     drow["Email_Id"] = Email.Text;
     drow["Website"] = Website.Text;
     drow["Date"] = dt.ToShortDateString();
     drow["Address"] = Address1.Text;
     drow["Shop"] = Shop.Text;
     drow["Gali"] = Gali.Text;
     ds.Tables["Company"].Rows.Add(drow);
     da.Update(ds, "Company");
     Panel1.Visible = false;
     Panel2.Visible = true;
     
 }

        /// <summary>
        /// Thêm mới kiểu hợp đồng
        /// </summary>
        /// <param name="dsContractType"></param>
        /// <returns></returns>
        public int AddContractTypeDO(DataSet dsDayType)
        {
            conn = WorkingContext.GetConnection();
            sqlCommand = new SqlCommand("AddContractType",conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            //sqlCommand.Parameters.Add(WorkingContext.CreateParam("@ContractID",SqlDbType.Int ,"ContractID"));
            sqlCommand.Parameters.Add(WorkingContext.CreateParam("@ContractName",SqlDbType.NVarChar ,"ContractName"));
            sqlCommand.Parameters.Add(WorkingContext.CreateParam("@InsurancePay", SqlDbType.Bit, "InsurancePay"));
            sqlCommand.Parameters.Add(WorkingContext.CreateParam("@Note",SqlDbType.NVarChar,"Note"));
            SqlParameter result = new SqlParameter("@ReturnValue",SqlDbType.Int);
            result.Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add(result);

            dadapter = new SqlDataAdapter();
            dadapter.InsertCommand = sqlCommand;
            try
            {
                conn.Open();
                dadapter.Update(dsDayType,"tblContract");
                return (int)result.Value;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return 0;
            }
            finally
            {
                conn.Dispose();
                conn.Close();
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
             string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
             using (SqlConnection con = new SqlConnection(cs)) 
             {
                 SqlDataAdapter da = new SqlDataAdapter();
                 da.SelectCommand = new SqlCommand((string)ViewState["SQL_QUERY"], con);

                 SqlCommandBuilder builder = new SqlCommandBuilder();
                 builder.DataAdapter = da;
                 
                 DataSet ds = (DataSet)ViewState["DATASET"];

                 if (ds.Tables["Students"].Rows.Count > 0) 
                 {
                     DataRow dr = ds.Tables["Students"].Rows[0];
                     dr["Name"] = txtStudentName.Text;
                     dr["Gender"] = ddlGender.SelectedValue;
                     dr["TotalMarks"] = txtTotalMarks.Text;
                 }
                 //this line not working saying command error.
                 int rowsupdated = da.Update(ds, "Students");
                 if(rowsupdated > 0)
                 {
                  lblStatus.ForeColor = System.Drawing.Color.Green;
                  lblStatus.Text = rowsupdated.ToString() + "rows(s) updated";
                 }
                 else
                 {
                     lblStatus.ForeColor = System.Drawing.Color.Red;
                     lblStatus.Text = " No rows(s) updated";

                 }
             }
        }

        public static int ExecuteNonQuery(string connectionString, string cmdText, DataSet ds, string s, params SqlParameter[] commandParameters)
        {
            // Create a new Sql command
            SqlCommand cmd = new SqlCommand();

            //Create a connection
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                //Prepare the command
                cmd.CommandText = cmdText;
                cmd.Connection = connection;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter e in commandParameters)
                { cmd.Parameters.Add(e); }
                SqlDataAdapter da = new SqlDataAdapter();
                da.InsertCommand = cmd;

                //Execute the command
                int val = da.Update(ds, s);
                connection.Close();
                cmd.Parameters.Clear();
                return val;
            }
        }

        static void Main(string[] args)
        {
            // string connectionString = @"data source=(localDB)\v11.0; Initial catalog=TrainingDB; integrated security=SSPI";

            //string connectionString = "data source=192.168.20.125;database=SMSDB;Integrated Security=false; user id=sa; [email protected]";

            string connectionString = @"Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\TrainingDB.mdf;Initial Catalog=TrainingDB;Integrated Security=True";

            string commandString = @"SELECT * FROM Shippers";

            SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, connectionString);

            SqlCommandBuilder scb = new SqlCommandBuilder(dataAdapter);

            DataSet myDataSet = new DataSet();
            dataAdapter.Fill(myDataSet, "Shippers");

            DataTable sTable = myDataSet.Tables["Shippers"];

            // add data
            object[] o = { 1, "General", "555-1212" };
            sTable.Rows.Add(o);

            dataAdapter.Update(myDataSet, "Shippers");

            myDataSet.AcceptChanges();

            Console.ReadKey();
        }

        public void BorrarPersonal(ArrayList arraydni)
        {
            DataSet dsPersonal = new DataSet();
            try
            {
                string frase="";
                for (int i = 0; i < arraydni.Count; i++)
                {
                    if (i == 0){ frase += "'" + arraydni[i] + "'"; }
                    else
                    {
                        frase += ",";
                        frase += "'" + arraydni[i] + "'";
                    }
                }

                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select * from Personal where DNI in ("+frase+")";
                SqlDataAdapter daPersonal = new SqlDataAdapter(consulta, conexion);
                daPersonal.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daPersonal.Fill(dsPersonal, nombreTabla);
                for (int i = 0; i < arraydni.Count; i++)//lo hacemos parar borrar las filas que corresponden con los dnis
                {
                    dsPersonal.Tables["Personal"].Rows[i].Delete();
                }
                SqlCommandBuilder cbuilder = new SqlCommandBuilder(daPersonal);
                daPersonal.Update(dsPersonal, "Personal");

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public int ExecuteNoneQuery()
        {
            int result = 0;
            SqlTransaction tr = null;
            try
            {
                tr = m_Connection.BeginTransaction();

                m_Command.Connection = m_Connection;
                m_Command.Transaction = tr;

                m_DataAdapter = new SqlDataAdapter();
                m_DataAdapter.SelectCommand = m_Command;

                SqlCommandBuilder builder = new SqlCommandBuilder(m_DataAdapter);

                result = m_DataAdapter.Update(this);
                tr.Commit();
            }
            catch (System.Exception ex)
            {
                if (tr != null) tr.Rollback();
            }
            return result;
        }