Csharp/C Sharp/Database ADO.net/SqlCommand

Async Command Object

 
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Remoting.Messaging;
using System.Threading;
class Program {
    static void Main(string[] args) {
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "uid=sa;pwd=;Initial Catalog=Cars;Asynchronous Processing=true;Data Source=(local)";
        cn.Open();
        string strSQL = "WaitFor Delay "00:00:02";Select * From Inventory";
        SqlCommand myCommand = new SqlCommand(strSQL, cn);
        IAsyncResult itfAsynch;
        itfAsynch = myCommand.BeginExecuteReader(CommandBehavior.CloseConnection);
        while (!itfAsynch.IsCompleted) {
            Console.WriteLine("Working on main thread...");
            Thread.Sleep(1000);
        }
        SqlDataReader myDataReader = myCommand.EndExecuteReader(itfAsynch);
        while (myDataReader.Read()) {
            Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.",
              myDataReader["Make"].ToString().Trim(),
              myDataReader["PetName"].ToString().Trim(),
              myDataReader["Color"].ToString().Trim());
        }
        myDataReader.Close();
    }
}

control SqlCommand to return a single row

 
using System;
using System.Data;
using System.Data.SqlClient;
class SingleRowCommandBehavior {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products";
        mySqlConnection.Open();
        SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);
        while (mySqlDataReader.Read()) {
            Console.WriteLine("mySqlDataReader[\" ProductID\"] = " + mySqlDataReader["ProductID"]);
            Console.WriteLine("mySqlDataReader[\" ProductName\"] = " + mySqlDataReader["ProductName"]);
            Console.WriteLine("mySqlDataReader[\" QuantityPerUnit\"] = " + mySqlDataReader["QuantityPerUnit"]);
            Console.WriteLine("mySqlDataReader[\" UnitPrice\"] = " + mySqlDataReader["UnitPrice"]);
        }
        mySqlDataReader.Close();
        mySqlConnection.Close();
    }
}

Delete data from database using SqlCommand

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.rumandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ("Z", "Z")";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery());
            Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar());
            nonqueryCommand.rumandText = "DELETE FROM Employee WHERE Firstname="Z" AND Lastname="Z"";
            Console.WriteLine(nonqueryCommand.rumandText);
            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.");
         }
      }
   }

execute multiple SELECT statements(select) using a SqlCommand object and read the results using a SqlDataReader object

 
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteSelect {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 5 ProductID, ProductName " +
          "FROM Products " +
          "ORDER BY ProductID;" +
          "SELECT TOP 3 CustomerID, CompanyName " +
          "FROM Customers " +
          "ORDER BY CustomerID;" +
          "SELECT TOP 6 OrderID, CustomerID " +
          "FROM Orders " +
          "ORDER BY OrderID;";
        mySqlConnection.Open();
        SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
        do {
            while (mySqlDataReader.Read()) {
                Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]);
                Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]);
            }
        } while (mySqlDataReader.NextResult());
        mySqlDataReader.Close();
        mySqlConnection.Close();
    }
}

Execute multiple SQL statements(insert) using a SqlCommand object

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

Get row count by "ExecuteScalar"

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

Get row count from SqlCommand

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.rumandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ("Z", "Z")";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery());
            Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar());
            nonqueryCommand.rumandText = "DELETE FROM Employee WHERE Firstname="Z" AND Lastname="Z"";
            Console.WriteLine(nonqueryCommand.rumandText);
            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.");
         }
      }
   }

How to use the ExecuteScalar() method to run a SELECT statement that returns a single value

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.rumandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();
    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);
    mySqlConnection.Close();
  }
}

Pass parameters to SqlCommand

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.rumandText = "CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            nonqueryCommand.rumandText = "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.rumandText);
               Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            }
         } catch (SqlException ex) {
            Console.WriteLine(ex.ToString());
         } finally {  
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }

Populate a DataSet object using a SELECT statement

 
using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingSelect {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 5 ProductID, ProductName, UnitPrice " +
          "FROM Products " +
          "ORDER BY ProductID";
        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        DataSet myDataSet = new DataSet();
        mySqlConnection.Open();
        Console.WriteLine("Retrieving rows from the Products table");
        int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");
        Console.WriteLine("numberOfRows = " + numberOfRows);
        mySqlConnection.Close();
        DataTable myDataTable = myDataSet.Tables["Products"];
        foreach (DataRow myDataRow in myDataTable.Rows) {
            Console.WriteLine("ProductID = " + myDataRow["ProductID"]);
            Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
            Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]);
        }
    }
}

Use ExecuteXmlReader() to run a SELECT statement that returns XML

 
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
class ExecuteXmlReader {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 5 ProductID, ProductName, UnitPrice " +
          "FROM Products " +
          "ORDER BY ProductID " +
          "FOR XML AUTO";
        mySqlConnection.Open();
        XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader();
        myXmlReader.Read();
        while (!myXmlReader.EOF) {
            Console.WriteLine(myXmlReader.ReadOuterXml());
        }
        myXmlReader.Close();
        mySqlConnection.Close();
    }
}

Use SqlCommand to call SQL and insert data to database table

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.rumandText = "CREATE TABLE MynfexTable1 (intColumn integer)";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            nonqueryCommand.rumandText = "INSERT INTO MynfexTable1 VALUES (99)";
            Console.WriteLine(nonqueryCommand.rumandText);
            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.");
         }
      }
   }

Use the ExecuteNonQuery() method to run INSERT, UPDATE, and DELETE statements

using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteInsertUpdateDelete
{
  public static void DisplayRow(SqlCommand mySqlCommand, string ID){
    mySqlCommand.rumandText ="SELECT ID, FirstName FROM Employee WHERE ID = "" + ID + """;
    SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
    while (mySqlDataReader.Read())
    {
      Console.WriteLine("mySqlDataReader[\" ID\"] = " +
        mySqlDataReader["ID"]);
      Console.WriteLine("mySqlDataReader[\" FirstName\"] = " +
        mySqlDataReader["FirstName"]);
    }
    mySqlDataReader.Close();
  }
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText ="INSERT INTO Employee (ID, FirstName) VALUES (" +
      "  9, "Jason")";
    mySqlConnection.Open();
    int numberOfRows = mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Number of rows added = " + numberOfRows);
    DisplayRow(mySqlCommand, "9");
    mySqlCommand.rumandText = "UPDATE Employee SET FirstName = "New" WHERE ID = "9"";
    numberOfRows = mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Number of rows updated = " + numberOfRows);
    DisplayRow(mySqlCommand, "9");
    mySqlCommand.rumandText ="DELETE FROM Employee WHERE ID = "9"";
    numberOfRows = mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Number of rows deleted = " + numberOfRows);
    mySqlConnection.Close();
  }
}

Use the GetOrdinal() method of a DataReader object to get the numeric positions of a column

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