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

namespace DataAdapter
{
   class Program
   {
      static void Main(string[] args)
      {
         SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();
         scb.DataSource = @".\SQLExpress";
         scb.InitialCatalog = "TestDB";
         scb.IntegratedSecurity = true;
         SqlConnection conn = new SqlConnection(scb.ConnectionString);
         conn.Open();

         SqlCommand selectCommand = conn.CreateCommand();
         selectCommand.CommandText = "SELECT * From Users";

         SqlCommand updateCommand = conn.CreateCommand();
         updateCommand.CommandText =
           "UPDATE Users SET UserName=@UserName, FirstName=@FirstName, " +
           "LastName=@LastName WHERE UserID = @UserID";
         updateCommand.Parameters.Add(
           new SqlParameter("UserName", SqlDbType.VarChar, 50, "UserName"));
         updateCommand.Parameters.Add(
           new SqlParameter("FirstName", SqlDbType.VarChar, 50, "FirstName"));
         updateCommand.Parameters.Add(
           new SqlParameter("LastName", SqlDbType.VarChar, 50, "LastName"));
         updateCommand.Parameters.Add(
           new SqlParameter("UserID", SqlDbType.Int, 4, "UserID"));

         SqlCommand deleteCommand = conn.CreateCommand();
         deleteCommand.CommandText = "DELETE Users WHERE UserID = @UserID";
         deleteCommand.Parameters.Add(new SqlParameter("@UserID",
           SqlDbType.Int, 4, "UserID"));

         SqlCommand insertCommand = conn.CreateCommand();
         insertCommand.CommandText = "INSERT INTO Users(UserName, FirstName, LastName) " +
           "VALUES(@UserName, @FirstName, @LastName)";
         insertCommand.Parameters.Add(
           new SqlParameter("@UserName", SqlDbType.VarChar, 50, "UserName"));
         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("@UserID", SqlDbType.Int, 4, "UserID"));

         DataSet ds = new DataSet();
         SqlDataAdapter sqlDa = new SqlDataAdapter(selectCommand);
         sqlDa.UpdateCommand = updateCommand;
         sqlDa.DeleteCommand = deleteCommand;
         sqlDa.InsertCommand = insertCommand;
         sqlDa.Fill(ds);

         // uaktualnij istniejący wiersz
         DataTable users = ds.Tables[0];
         users.Rows[0]["FirstName"] = "Zmodyfikowane";

         // usuń istniejący wiersz
         users.Rows[1].Delete();

         // utwórz nowy wiersz
         DataRow newUser = users.NewRow();
         newUser["UserName"] = "dodany";
         newUser["FirstName"] = "Zupełnie";
         newUser["LastName"] = "Nowy";
         users.Rows.Add(newUser);

         // wywołaj metodę Update, aby wykonać polecenie Update,
         // polecenie Delete oraz polecenie Insert
         // na przyłączonym źródle danych poprzez DataAdapter
         sqlDa.Update(ds);
         Console.WriteLine("Uaktualnianie Zakończone");
         Console.ReadLine();
      }
   }
}
