using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace BatchUpdate
{
   class Program
   {
      static void Main(string[] args)
      {
         SqlConnection conn = new SqlConnection(
           @"data source=.\SQLExpress; initial catalog=SampleDB; Integrated Security=SSPI;");
         conn.Open();
         SqlCommand selectCmd = conn.CreateCommand();
         selectCmd.CommandText = "SELECT ID, FirstName, LastName, MiddleInitial FROM Customers";

         SqlCommand updateCmd = conn.CreateCommand();
         updateCmd.CommandText = "UPDATE Customers 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 Customers(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;  // wymagane do użycia trybu wsadowego


         SqlDataAdapter da = new SqlDataAdapter(selectCmd);
         da.UpdateCommand = updateCmd;
         da.InsertCommand = insertCommand;
         da.UpdateBatchSize = 10;

         DataSet ds = new DataSet();
         da.Fill(ds, "Customers");

         // zmień dane pierwszego klienta
         ds.Tables[0].Rows[0]["FirstName"] = "Zmodyfikowany!";

         // dodaj parę klientów
         DataRow newCust = ds.Tables[0].NewRow();
         newCust["FirstName"] = "Batch1";
         newCust["LastName"] = "Batch";
         newCust["MiddleInitial"] = 'Q';
         ds.Tables[0].Rows.Add(newCust);
         newCust = ds.Tables[0].NewRow();
         newCust["FirstName"] = "Batch2";
         newCust["LastName"] = "Batch";
         newCust["MiddleInitial"] = 'J';
         ds.Tables[0].Rows.Add(newCust);

         da.Update(ds, "Customers");

         Console.WriteLine("Wykonano uaktualnienie w trybie wsadowym.");
         Console.ReadLine();
      }
   }
}
