Csharp/CSharp Tutorial/ADO.Net/SqlCommandBuilder

Delete Data using CommandBuilder

<source lang="csharp">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);
   }
 }</source>

Insert Data using SqlCommandBuilder

<source lang="csharp">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);
 }

}</source>

Show SQL

<source lang="csharp">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();
   }

}</source>

Show SQL from SqlCommandBuilder

<source lang="csharp">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();
   }
 }</source>