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();
  }
}
           
          

   

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

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText = "SELECT TOP 5 ID, FirstName, LastName FROM employee " +
      "ORDER BY ID";

    mySqlConnection.Open();

    SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();

    int idPos = productsSqlDataReader.GetOrdinal("ID");
    int firstNamePos = productsSqlDataReader.GetOrdinal("LastName");
    int lastNamePos = productsSqlDataReader.GetOrdinal("FirstName");

    while (productsSqlDataReader.Read())
    {
      Console.WriteLine("ID = " + productsSqlDataReader[idPos]);
      Console.WriteLine("FirstName = " + productsSqlDataReader[firstNamePos]);
      Console.WriteLine("LastName = " + productsSqlDataReader[lastNamePos]);
    }

    productsSqlDataReader.Close();
    mySqlConnection.Close();
  }
}



           
          

   


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

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

    mySqlConnection.Open();

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "INSERT INTO Employee (" +
      "  ID, FirstName, LastName" +
      ") VALUES (" +
      "  @ID, @FirstName, @LastName" +
      ")";

    mySqlCommand.Parameters.Add("@ID", SqlDbType.NChar, 2);
    mySqlCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10);
    mySqlCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 10);

    mySqlCommand.Parameters["@ID"].Value = "9";
    mySqlCommand.Parameters["@FirstName"].Value = "New";
    mySqlCommand.Parameters["@LastName"].IsNullable = true;
    mySqlCommand.Parameters["@LastName"].Value = "New";


    mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Successfully added row to Customers table");

    mySqlConnection.Close();
  }
}