SqlCommand

Call stored procedure with no parameter
using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
   static void Main()
   {
      SqlConnection conn = new SqlConnection(@"server = .\");

      try
      {
         conn.Open();
         SqlCommand cmd = conn.CreateCommand();
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "sp_select_all_employees";
         SqlDataReader rdr = cmd.ExecuteReader();
         while (rdr.Read()){
            Console.WriteLine("{0} {1} {2}"
             , rdr[0].ToString().PadRight(5)
             , rdr[1].ToString()
             , rdr[2].ToString()
            );
         }
         rdr.Close();
      }
      catch (SqlException ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn.Close();
      }
   }
}
Call stored procedure with parameter and return value
using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
   static void Main()
   {
      SqlConnection conn = new SqlConnection(@"server = .\");

      try
      {
         conn.Open();

         SqlCommand cmd = conn.CreateCommand();

         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "sp_orders_by_employeeid2";

         SqlParameter inparm = cmd.Parameters.Add("@employeeid", SqlDbType.Int);
         inparm.Direction = ParameterDirection.Input;
         inparm.Value = 2;

         SqlParameter ouparm = cmd.Parameters.Add("@ordercount", SqlDbType.Int);
         ouparm.Direction = ParameterDirection.Output;

         SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int);
         retval.Direction = ParameterDirection.ReturnValue;

         SqlDataReader rdr = cmd.ExecuteReader();

         while (rdr.Read())
         {
            Console.WriteLine("{0} {1}"
             , rdr[0].ToString().PadRight(5)
             , rdr[1].ToString()
            );
         }
         rdr.Close();

         Console.WriteLine("The output parameter value is {0}", cmd.Parameters["@ordercount"].Value);

         Console.WriteLine("The return value is {0}", cmd.Parameters["return_value"].Value);
      }
      catch (SqlException ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn.Close();
      }
   }
}
Call storedprocedure and pass in the parameter
using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
    public static void Main()
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = @"Data Source = .\";
            con.Open();

            string category = "Seafood";
            string year = "1999";

            // Create and configure a new command.
            using (SqlCommand com = con.CreateCommand())
            {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "SalesByCategory";
    
                // Create a SqlParameter object for the category parameter.
                com.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = 
                    category;
    
                // Create a SqlParameter object for the year parameter.
                com.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = year;
    
                // Execute the command and process the results.
                using (IDataReader reader = com.ExecuteReader())
                {
                    Console.WriteLine("Sales By Category ({0}).", year);
    
                    while (reader.Read())
                    {
                        // Display the product details.
                        Console.WriteLine("  {0} = {1}",
                            reader["ProductName"],
                            reader["TotalPurchase"]);
                    }
                }
            }

        }
    }
}
Call stored procedure with parameters using SqlCommand
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

class MainClass
{
  static void Main(string[] args)
    {
        string SQL = "SELECT * FROM Orders";

        string ConnectionString ="Integrated ;";
        SqlConnection conn = new SqlConnection(ConnectionString);

        SqlCommand StoredProcedureCommand = new SqlCommand("Sales By Year", conn);
        StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter myParm1 = StoredProcedureCommand.Parameters.Add( "@Beginning_Date", SqlDbType.DateTime, 20); 
        myParm1.Value = "7/1/1996";
        SqlParameter myParm2 = StoredProcedureCommand.Parameters.Add("@Ending_Date", SqlDbType.DateTime, 20); 
        myParm2.Value = "7/31/1996";
        conn.Open();
        SqlDataReader TheReader = StoredProcedureCommand.ExecuteReader();
        string orderlist = "";
        while (TheReader.Read())
        {
      string nextID = TheReader["OrderID"].ToString();
      string nextSubtotal = TheReader["Subtotal"].ToString();
      orderlist += nextID + '\t' + nextSubtotal + '\n';
        }
        conn.Close();
    }
}
Call StoredProcedure with input and output parameters
using System;        
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

class MainClass {
    static void Main() {
        string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
        using ( SqlConnection conn = new SqlConnection( cstr ) )
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand( "QueryVendor", conn );
            cmd.CommandType = CommandType.StoredProcedure;

            // input parm
            SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 );
            name.Value = "Tom";

            // output parm
            SqlParameter vendor = cmd.Parameters.Add( "@vendor", SqlDbType.NVarChar, 15 );
            vendor.Direction = ParameterDirection.Output;

            // return value
            SqlParameter rowCount = cmd.Parameters.Add( "@rowCount", SqlDbType.Int );
            rowCount.Direction = ParameterDirection.ReturnValue;

            cmd.ExecuteNonQuery();

            if ( (int)rowCount.Value > 0 )
            {
                Console.WriteLine(" is available from " + vendor.Value );
            }
            else
            {
                Console.WriteLine(" not available from " + vendor.Value );
            }
        }    
        
    }
}


Catch exception when calling stored procedure
using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
   static void Main()
   {
         SqlConnection conn = new SqlConnection(@"data source = .\sqlexpress;integrated security = true;database = northwind");

         SqlCommand cmd = conn.CreateCommand();

         cmd.CommandType = CommandType.StoredProcedure;
         try
         {
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Close();
         }
         catch (System.Data.SqlClient.SqlException ex)
         {
            Console.WriteLine("Source: " + ex.Source);
            Console.WriteLine("Exception Message: " + ex.Message);
         }
         catch (System.Exception ex)
         {
            Console.WriteLine("Source: " + ex.Source);
            Console.WriteLine("Exception Message: " + ex.Message);
         }
         finally
         {
            if (conn.State == ConnectionState.Open)
            {
               Console.WriteLine("Finally block closing the connection");
               conn.Close();
            }
         }
   }
}
Retrieving a Return Value from a Stored Procedure
/*
CREATE PROCEDURE Person.GetContacts
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON
    SELECT * FROM Person.Contact
    SET @RowCount = @@ROWCOUNT
    RETURN @RowCount
*/


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;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                SqlCommand command =new SqlCommand("Person.GetContacts", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.Int);
                retParam.Direction = ParameterDirection.ReturnValue;

                connection.Open( );
                SqlDataReader dr = command.ExecuteReader( );
                Console.WriteLine("After execution, return value = {0}", retParam.Value);

                int rowCount = 0;
                while (dr.Read( )){
                    rowCount++;
                }
                dr.Close( );
                connection.Close( );
            }
        }
    }
Retrieving a Stored Procedure Output Parameter
/*
CREATE PROCEDURE Person.GetContacts
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON
    SELECT * FROM Person.Contact
    SET @RowCount = @@ROWCOUNT
    RETURN @RowCount
*/

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;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                SqlCommand command = new SqlCommand("Person.GetContacts", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =
                    ParameterDirection.Output;
                connection.Open( );
                SqlDataReader dr = command.ExecuteReader( );
                int rowCount = 0;
                while (dr.Read( ))
                {
                    rowCount++;
                }
                dr.Close( );
                connection.Close( );
            }
        }
    }
Retrieving Data Using a SQL Server Stored Procedure
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 = "uspGetEmployeeManagers";
            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 100;

            DataTable dt = new DataTable( );
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine(row["RecursionLevel"]);
                Console.WriteLine(row["EmployeeID"]);
                Console.WriteLine(row["LastName"]);
                Console.WriteLine(row["FirstName"]);
                Console.WriteLine(row["ManagerID"]);
                Console.WriteLine(row["ManagerLastName"]);
                Console.WriteLine(row["ManagerFirstName"]);
            }
        }
    }