Csharp/CSharp Tutorial/ADO.Net/SqlCommand

Add parameters to the SqlCommand

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main()
   {
      string fname = "Z";
      string lname = "Z";
      SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
      string sqlqry = @"select count(*) from employee";
      string sqlins = @"insert into employee(firstname,lastname)values(@fname, @lname)";
      string sqldel = @"delete from employee where firstname = @fname and lastname = @lname";
      SqlCommand cmdqry = new SqlCommand(sqlqry, conn);
      SqlCommand cmdnon = new SqlCommand(sqlins, conn);
      cmdnon.Parameters.Add("@fname", SqlDbType.NVarChar, 10);
      cmdnon.Parameters.Add("@lname", SqlDbType.NVarChar, 20);
      try
      {
         conn.Open();
         Console.WriteLine("Before INSERT: Number of employee {0}\n", cmdqry.ExecuteScalar());
         cmdnon.Parameters["@fname"].Value = fname;
         cmdnon.Parameters["@lname"].Value = lname;
         Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
         cmdnon.ExecuteNonQuery();
         Console.WriteLine("After INSERT: Number of employee {0}\n", cmdqry.ExecuteScalar());
         cmdnon.rumandText = sqldel;
         Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
         cmdnon.ExecuteNonQuery();
         Console.WriteLine("After DELETE: Number of employee {0}\n", cmdqry.ExecuteScalar());
      }
      catch (SqlException ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn.Close();
         Console.WriteLine("Connection Closed.");
      }
   }
}

Async Command Object Demo

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection cn = new SqlConnection();
      cn.ConnectionString = @"Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=YourDB;Asynchronous Processing=true";
      cn.Open();
      string strSQL = "WaitFor Delay "00:00:20";Select * From Inventory";
      SqlCommand myCommand = new SqlCommand(strSQL, cn);
      IAsyncResult itfAsynch = myCommand.BeginExecuteReader(CommandBehavior.CloseConnection);
      while (!itfAsynch.IsCompleted)
      {
        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();
    }
  }

Asynchronous Processing Sql command

using System;
using System.Data;
using System.Threading;
using System.Data.SqlClient;
class MainClass {
    public static void CallbackHandler(IAsyncResult result) {
        using (SqlCommand cmd = result.AsyncState as SqlCommand) {
            using (SqlDataReader reader = cmd.EndExecuteReader(result)) {
                lock (Console.Out) {
                    while (reader.Read()) {
                        Console.WriteLine("  {0} = {1}",
                            reader["ID"],
                            reader["FirstName"]);
                    }
                }
            }
        }
    }
    public static void Main()
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;Asynchronous Processing=true;";
            SqlCommand cmd = con.CreateCommand();
            cmd.rumandType = CommandType.Text;
            cmd.rumandText = "SELECT ID, FirstName FROM Employee";
            con.Open();
            cmd.BeginExecuteReader(CallbackHandler, cmd);
            // Continue with other processing.
            for (int count = 0; count < 10; count++)
            {
                lock (Console.Out)
                {
                    Console.WriteLine("{0} : Continue processing...",
                        DateTime.Now.ToString("HH:mm:ss.ffff"));
                }
                Thread.Sleep(500);
            }
        }
    }
}

Batch Update Demo

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");
            conn.Open();
            SqlCommand selectCmd = conn.CreateCommand();
            selectCmd.rumandText = "SELECT ID, FirstName, LastName, MiddleInitial FROM Employees";
            SqlCommand updateCmd = conn.CreateCommand();
            updateCmd.rumandText = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, MiddleInitial = @MiddleInitial WHERE ID = @ID";
            updateCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
            updateCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
            updateCmd.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));
            updateCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));
            updateCmd.UpdatedRowSource = UpdateRowSource.None;
            SqlCommand insertCommand = conn.CreateCommand();
            insertCommand.rumandText = "INSERT INTO Employees(FirstName, LastName, MiddleInitial) VALUES(@FirstName, @LastName, @MiddleInitial)";
            insertCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
            insertCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
            insertCommand.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));
            insertCommand.UpdatedRowSource = UpdateRowSource.None; 
            SqlDataAdapter da = new SqlDataAdapter(selectCmd);
            da.UpdateCommand = updateCmd;
            da.InsertCommand = insertCommand;
            da.UpdateBatchSize = 10;
            da.AcceptChangesDuringUpdate = true;
            DataSet ds = new DataSet();
            da.Fill(ds, "Employees");
            ds.Tables[0].Rows[0]["FirstName"] = "new";
            
            DataRow newCust = ds.Tables[0].NewRow();
            newCust["FirstName"] = "B";
            newCust["LastName"] = "C";
            newCust["MiddleInitial"] = "Q";
            ds.Tables[0].Rows.Add(newCust);
            newCust = ds.Tables[0].NewRow();
            newCust["FirstName"] = "B";
            newCust["LastName"] = "D";
            newCust["MiddleInitial"] = "J";
            ds.Tables[0].Rows.Add(newCust);
            
            da.Update(ds, "Employees");
        }
   }

Create a SqlCommand

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main()
   {
      SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
      SqlCommand cmd = new SqlCommand();
      Console.WriteLine("Command created.");
      try
      {
         conn.Open();
      }
      catch (SqlException ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn.Close();
         Console.WriteLine("Connection Closed.");
      }
   }
}

Command created.
Connection Closed.

Create SqlCommand with both sql query and connection

using System;
using System.Data;
using System.Data.SqlClient;
class CommandScalar
{
   static void Main()
   {
      SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
      string sql = @"select count(*) from employee";
      SqlCommand cmd = new SqlCommand(sql, conn);
      Console.WriteLine("Command created and connected.");
      try
      {
         conn.Open();
         Console.WriteLine("Number of Employees is {0}", cmd.ExecuteScalar());
      }
      catch (SqlException ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn.Close();
         Console.WriteLine("Connection Closed.");
      }
   }
}

Command created and connected.
Number of Employees is 4
Connection Closed.

Execute Scalar with SqlCommand

using System;
using System.Data;            
using System.Data.SqlClient;  
using System.Collections.Generic;
using System.Text;
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection thisConnection = new
            SqlConnection(
                @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename="NORTHWND.MDF";" +
                @"Integrated Security=True;Connect Timeout=30;User Instance=true");
      thisConnection.Open();
      SqlCommand thisCommand = thisConnection.CreateCommand();
      thisCommand.rumandText = "SELECT COUNT(*) FROM Customers";
      Object countResult = thisCommand.ExecuteScalar();
      Console.WriteLine("Count of Customers = {0}", countResult);
      thisConnection.Close();
    }
  }

Execute the scalar SQL statement and store results

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT COUNT(*) FROM Person.Contact";
            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open( );
            int count = Convert.ToInt32(command.ExecuteScalar( ));
            connection.Close( );
            Console.WriteLine("Record count in Person.Contact = {0}", count);
        }
    }

Execute two sql commands

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
    public static void Main()
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI";
            using (SqlCommand com = con.CreateCommand())
            {
                com.rumandType = CommandType.Text;
                com.rumandText = "SELECT BirthDate,FirstName,LastName FROM "+
                    "Employees ORDER BY BirthDate;SELECT * FROM Employees";
                con.Open();
                using (SqlDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("  {0,18:D} - {1} {2}",
                            reader.GetDateTime(0),  // Retrieve typed data
                            reader["FirstName"],    // Use string index
                            reader[2]);             // Use ordinal index
                    }
                    Console.WriteLine(Environment.NewLine);
                    reader.NextResult();
                    Console.WriteLine("Employee Table Metadata.");
                    for (int field = 0; field < reader.FieldCount; field++)
                    {
                        Console.WriteLine("  Column Name:{0}  Type:{1}",
                            reader.GetName(field),
                            reader.GetDataTypeName(field));
                    }
                }
            }
        }
    }
}

Execute update statement with SqlCommand against MDF file

using System;
using System.Data;            
using System.Data.SqlClient;  
using System.Collections.Generic;
using System.Text;
  class Program
  {
    static void Main(string[] args)
    {
      SqlConnection thisConnection = new SqlConnection(
                @"Data Source=.\SQLEXPRESS;" +
                @"AttachDbFilename="NORTHWND.MDF";" +
                @"Integrated Security=True;Connect Timeout=30;User Instance=true");
            thisConnection.Open();
      SqlCommand thisCommand = thisConnection.CreateCommand();
      thisCommand.rumandText = "UPDATE Products SET UnitPrice=1";
      int rowsAffected = thisCommand.ExecuteNonQuery();
      Console.WriteLine("Rows Updated = {0}", rowsAffected);
      thisConnection.Close();
    }
  }

Executing a Parameterized Query with SqlCommand

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > @TotalDue";
            SqlConnection sqlConnection = new SqlConnection(sqlConnectString);
            SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);
            sqlCommand.Parameters.Add("@TotalDue", SqlDbType.Money);
            sqlCommand.Parameters["@TotalDue"].Value = 200000;
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCommand);
            DataTable sqlDt = new DataTable( );
            sqlDa.Fill(sqlDt);
            foreach (DataRow row in sqlDt.Rows){
                Console.WriteLine(row["SalesOrderID"]);
                Console.WriteLine(row["OrderDate"]);
                Console.WriteLine(row["TotalDue"]);
            }
        }
    }

Executing a Query That Returns a Single Value

using System;
using System.Data;
using System.Data.SqlClient;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
            string sqlSelect = "SELECT COUNT(*) FROM Person.Contact";
            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open( );
            int count = Convert.ToInt32(command.ExecuteScalar( ));
            connection.Close( );
            Console.WriteLine("Record count in Person.Contact = {0}", count);
        }
    }

SqlCommand: AutoClose

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

class MainClass
{
  [STAThread]
  static void Main(string[] args)
  {
    string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
    SqlConnection conn = new SqlConnection( cstr );
    conn.Open();
    SqlCommand cmd = new SqlCommand( "select * from Employee", conn );
    SqlDataReader rdr = cmd.ExecuteReader( CommandBehavior.CloseConnection );
    while ( rdr.Read() )
    {
      System.Console.WriteLine( "{0}", rdr.GetString( 1 ) );
    }
    rdr.Close();
  }
}

SqlCommand with Callback Handler

using System;
using System.Data;
using System.Threading;
using System.Data.SqlClient;
class MainClass {
    public static void CallbackHandler(IAsyncResult result) {
        using (SqlCommand cmd = result.AsyncState as SqlCommand) {
            using (SqlDataReader reader = cmd.EndExecuteReader(result)) {
                lock (Console.Out) {
                    Console.WriteLine("Price of the Ten Most Expensive Products:");
                    while (reader.Read()) {
                        Console.WriteLine("  {0} = {1}",
                            reader["TenMostExpensiveProducts"],
                            reader["UnitPrice"]);
                    }
                }
            }
        }
    }
    public static void Main() {
        using (SqlConnection con = new SqlConnection()) {
            con.ConnectionString = @"Data Source = .\sqlexpress;" +
                "Database = Northwind; Integrated Security=SSPI;" +
                "Asynchronous Processing=true";
            SqlCommand cmd = con.CreateCommand();
            cmd.rumandType = CommandType.StoredProcedure;
            cmd.rumandText = "Ten Most Expensive Products";
            con.Open();
            cmd.BeginExecuteReader(CallbackHandler, cmd);
            for (int count = 0; count < 10; count++) {
                lock (Console.Out) {
                    Console.WriteLine("{0} : Continue processing...",
                        DateTime.Now.ToString("HH:mm:ss.ffff"));
                }
                Thread.Sleep(500);
            }
        }
    }
}

SQL StoredProcedure

using System;
using System.Data;            
using System.Data.SqlClient;  
class Program {
    static void Main(string[] args) {
        SqlConnection thisConnection = new SqlConnection(
            @"Server=(local)\sqlexpress;Integrated Security=True;" +
            "Database=northwind");
        thisConnection.Open();
        SqlCommand thisCommand = thisConnection.CreateCommand();
        thisCommand.rumandType = CommandType.StoredProcedure;
        thisCommand.rumandText = "Ten Most Expensive Products";
        SqlDataReader thisReader = thisCommand.ExecuteReader();
        while (thisReader.Read()) {
            Console.WriteLine("\t{0}\t{1}",
            thisReader["TenMostExpensiveProducts"], thisReader["UnitPrice"]);
        }
        thisReader.Close();
        thisConnection.Close();
    }
}