Mega Code Archive

 

Categories / C# Tutorial / ADO Net

 

Batch Update Demo

using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Text;     class Program     {         static void Main(string[] args)         {             SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");             conn.Open();             SqlCommand selectCmd = conn.CreateCommand();             selectCmd.CommandText = "SELECT ID, FirstName, LastName, MiddleInitial FROM Employees";             SqlCommand updateCmd = conn.CreateCommand();             updateCmd.CommandText = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, MiddleInitial = @MiddleInitial WHERE ID = @ID";             updateCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));             updateCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));             updateCmd.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));             updateCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));             updateCmd.UpdatedRowSource = UpdateRowSource.None;             SqlCommand insertCommand = conn.CreateCommand();             insertCommand.CommandText = "INSERT INTO Employees(FirstName, LastName, MiddleInitial) VALUES(@FirstName, @LastName, @MiddleInitial)";             insertCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));             insertCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));             insertCommand.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));             insertCommand.UpdatedRowSource = UpdateRowSource.None;              SqlDataAdapter da = new SqlDataAdapter(selectCmd);             da.UpdateCommand = updateCmd;             da.InsertCommand = insertCommand;             da.UpdateBatchSize = 10;             da.AcceptChangesDuringUpdate = true;             DataSet ds = new DataSet();             da.Fill(ds, "Employees");             ds.Tables[0].Rows[0]["FirstName"] = "new";                          DataRow newCust = ds.Tables[0].NewRow();             newCust["FirstName"] = "B";             newCust["LastName"] = "C";             newCust["MiddleInitial"] = 'Q';             ds.Tables[0].Rows.Add(newCust);             newCust = ds.Tables[0].NewRow();             newCust["FirstName"] = "B";             newCust["LastName"] = "D";             newCust["MiddleInitial"] = 'J';             ds.Tables[0].Rows.Add(newCust);                          da.Update(ds, "Employees");         }    }