Csharp/C Sharp/Database ADO.net/SqlCommand

Async Command Object

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

}

</source>

control SqlCommand to return a single row

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

}

</source>

Delete data from database using SqlCommand

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

      </source>

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

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

}

</source>

Execute multiple SQL statements(insert) using a SqlCommand object

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

}

      </source>

Get row count by "ExecuteScalar"

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

      </source>

Get row count from SqlCommand

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

      </source>

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

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

}

      </source>

Pass parameters to SqlCommand

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

      </source>

Populate a DataSet object using a SELECT statement

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

}

</source>

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

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

}

</source>

Use SqlCommand to call SQL and insert data to database table

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

      </source>

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

   <source lang="csharp">

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

}

      </source>
   
  

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

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

}

      </source>