Delete data from database using SqlCommand

image_pdfimage_print
   

using System;
using System.Data;
using System.Data.SqlClient;

   class CommandExampleNonQuery
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         
         SqlCommand selectCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection);
         
         SqlCommand nonqueryCommand = thisConnection.CreateCommand();

         try {
            thisConnection.Open();

            Console.WriteLine("Before INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar());
            nonqueryCommand.CommandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ('Z', 'Z')";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery());
            Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar());
            nonqueryCommand.CommandText = "DELETE FROM Employee WHERE Firstname='Z' AND Lastname='Z'";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            Console.WriteLine("After DELETE: Number of Employee is: {0}", selectCommand.ExecuteScalar());
         } 
         catch (SqlException ex) 
         {
            Console.WriteLine(ex.ToString());
         }
         finally 
         {  
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }