Csharp/CSharp Tutorial/ADO.Net/SqlCommandBuilder

Delete Data using CommandBuilder

using System;
using System.Data;
using System.Data.SqlClient;
  class MainClass 
  {
    static void Main(string[] args)
    {
      SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
      SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT * FROM Employee", MyConnection);
      SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter);
      DataSet MyDataSet = new DataSet();
      MyDataAdapter.Fill(MyDataSet);
      DataColumn[] MyKey = new DataColumn[1];
      MyKey[0] = MyDataSet.Tables[0].Columns[0];
      MyDataSet.Tables[0].PrimaryKey = MyKey;
      DataRow FindMyRow = MyDataSet.Tables[0].Rows.Find(1);
      FindMyRow.Delete();
      MyDataAdapter.Update(MyDataSet);
    }
  }

Insert Data using SqlCommandBuilder

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
  static void Main(string[] args)
  {
    SqlConnection MyConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlDataAdapter MyDataAdapter = new SqlDataAdapter("SELECT ID, FirstName, LastName FROM Employee", MyConnection);
    SqlCommandBuilder MyCmd = new SqlCommandBuilder(MyDataAdapter);
    DataSet MyDataSet = new DataSet();
    MyDataAdapter.Fill(MyDataSet);
    DataRow MyRow = MyDataSet.Tables[0].NewRow();
    MyRow["ID"] = 200;
    MyRow["FirstName"] = "G";
    MyRow["LastName"] = "M";
    MyDataSet.Tables[0].Rows.Add(MyRow);
    MyDataAdapter.Update(MyDataSet);
  }
}

Show SQL

using System;
using System.Data;            // Use ADO.NET namespace
using System.Data.SqlClient;  // Use SQL Server data provider namespace
using System.Collections.Generic;
using System.Text;
class Program {
    static void Main(string[] args) {
        SqlConnection thisConnection = new SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
            "Database=northwind");
        thisConnection.Open();
        SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID from Customers", thisConnection);
        SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
        Console.WriteLine("SQL SELECT Command is:\n{0}\n", thisAdapter.SelectCommand.rumandText);
        SqlCommand updateCommand = thisBuilder.GetUpdateCommand();
        Console.WriteLine("SQL UPDATE Command is:\n{0}\n",updateCommand.rumandText);
        SqlCommand insertCommand = thisBuilder.GetInsertCommand();
        Console.WriteLine("SQL INSERT Command is:\n{0}\n", insertCommand.rumandText);
        SqlCommand deleteCommand = thisBuilder.GetDeleteCommand();
        Console.WriteLine("SQL DELETE Command is:\n{0}",deleteCommand.rumandText);
        thisConnection.Close();
    }
}

Show SQL from SqlCommandBuilder

using System;
using System.Data;            
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection thisConnection = new SqlConnection(
                @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename="NORTHWND.MDF";" +
                @"Integrated Security=True;Connect Timeout=30;User Instance=true");
      thisConnection.Open();
      SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID from Customers", thisConnection);
      SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
      Console.WriteLine(thisAdapter.SelectCommand.rumandText);
      SqlCommand updateCommand = thisBuilder.GetUpdateCommand();
      Console.WriteLine(updateCommand.rumandText);
      SqlCommand insertCommand = thisBuilder.GetInsertCommand();
      Console.WriteLine(insertCommand.rumandText);
      SqlCommand deleteCommand = thisBuilder.GetDeleteCommand();
      Console.WriteLine(deleteCommand.rumandText);
      thisConnection.Close();
    }
  }