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

class ExecuteMultipleSQL
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
      "INSERT INTO Employee (ID, FirstName) " +
      "VALUES (11, 'Jason');" +
      "SELECT ID, FirstName " +
      "FROM Employee " +
      "WHERE ID = 11;" +
      "UPDATE Employee " +
      "SET FirstName = 'Jason 2' " +
      "WHERE ID = 11;" +
      "SELECT ID, FirstName " +
      "FROM Employee " +
      "WHERE ID = 11;" +
      "DELETE FROM Employee " +
      "WHERE ID = 11;";

    mySqlConnection.Open();

    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();

    do
    {
      while (mySqlDataReader.Read())
      {
        Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
        Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
      }
      Console.WriteLine(""); // visually split the results
    } while (mySqlDataReader.NextResult());

    mySqlDataReader.Close();
    mySqlConnection.Close();
  }
}


           
          

   

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

   class CommandExampleScalar
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

         SqlCommand thisCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection);

         try {
            thisConnection.Open();

            Console.WriteLine("Number of Employees is: {0}",
               thisCommand.ExecuteScalar());
         }
         catch (SqlException ex) 
         {
            Console.WriteLine(ex.ToString());
         }
         finally 
         {
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }



           
          

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

class CommandExampleParameters
{
static void Main()
{
SqlConnection thisConnection = new SqlConnection(“server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI”);

SqlCommand nonqueryCommand = thisConnection.CreateCommand();

try {
thisConnection.Open();

nonqueryCommand.CommandText = “CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)”;
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine(“Number of Rows Affected is: {0}”, nonqueryCommand.ExecuteNonQuery());

nonqueryCommand.CommandText = “INSERT INTO MyTable VALUES (@MyName, @MyNumber)”;

nonqueryCommand.Parameters.Add(“@MyName”, SqlDbType.VarChar, 30);
nonqueryCommand.Parameters.Add(“@MyNumber”, SqlDbType.Int);

nonqueryCommand.Prepare();

string[] names = { “A”, “B”, “C”, “D” } ;
int i;
for (i=1; i<=4; i++) { nonqueryCommand.Parameters["@MyName"].Value = names[i-1]; nonqueryCommand.Parameters["@MyNumber"].Value = i; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); } } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } } [/csharp]

   

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.");
         }
      }
   }



           
          

   

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

   class CommandExampleCreate {
      static void Main() {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

         SqlCommand nonqueryCommand = thisConnection.CreateCommand();

         try {
            thisConnection.Open();

            nonqueryCommand.CommandText = "CREATE TABLE MyJava2sTable1 (intColumn integer)";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());

            nonqueryCommand.CommandText = "INSERT INTO MyJava2sTable1 VALUES (99)";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}",
               nonqueryCommand.ExecuteNonQuery());
         } 
         catch (SqlException ex) 
         {
            Console.WriteLine(ex.ToString());
         }
         finally 
         {  
            thisConnection.Close();  // close connection
            Console.WriteLine("Connection Closed.");
         }
      }
   }



           
          

   


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

class ExecuteScalar
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();

    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);

    mySqlConnection.Close();
  }
}