Csharp/CSharp Tutorial/ADO.Net/SqlConnection Stored Procedure

Call a stored procedure

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
  static void Main(string[] args)
  {
    SqlConnection cn = new SqlConnection("Data Source=(local); Initial  Catalog = MyDatabase; User ID=sa;Password=");
    SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn);
    cmd.rumandType = CommandType.StoredProcedure;
    SqlParameter param = new SqlParameter("@ReturnValue", SqlDbType.Int);
    cmd.Parameters.Add(param);
    cmd.Parameters.Add("MyFirstParameter", SqlDbType.Int);
    cmd.Parameters.Add("MySecondParameter", SqlDbType.Int).Direction =
    ParameterDirection.Output;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
  }
}

Call 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;";
            string sqlSelect ="SELECT COUNT(*) FROM Person.Contact; SELECT * FROM Person.Contact;";
            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                SqlCommand command = new SqlCommand(sqlSelect, connection);
                connection.Open( );
                SqlDataReader dr = command.ExecuteReader( );
                dr.Read( );
                Console.WriteLine(dr.GetInt32(0));
                dr.NextResult( );
                int count = 0;
                while (dr.Read( )){
                    count++;
                }
                Console.WriteLine(count);
                dr.Close( );
                command = new SqlCommand("Person.GetContacts", connection);
                command.rumandType = CommandType.StoredProcedure;
                command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =ParameterDirection.Output;
                dr = command.ExecuteReader( );
                dr.Close( );
                Console.WriteLine("Record count, using @@ROWCOUNT = {0}",command.Parameters["@RowCount"].Value);
            }
        }
    }

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 = .\sqlexpress;Database = Northwind; Integrated Security=SSPI";
            con.Open();
            string category = "Seafood";
            string year = "1999";
            // Create and configure a new command.
            using (SqlCommand com = con.CreateCommand())
            {
                com.rumandType = CommandType.StoredProcedure;
                com.rumandText = "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 StoredProcedure with input and output parameters

using System;        
using System.Drawing;
using System.Collections;
using System.ruponentModel;
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.rumandType = 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 );
            }
        }    
        
    }
}

Call stored procedure with no parameter

/*
Quote from

Beginning C# 2005 Databases From Novice to Professional
# Paperback: 528 pages
# Publisher: Apress (December 18, 2006)
# Language: English
# ISBN-10: 159059777X
# ISBN-13: 978-1590597774
*/
using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main()
   {
      SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
      try
      {
         conn.Open();
         SqlCommand cmd = conn.CreateCommand();
         cmd.rumandType = CommandType.StoredProcedure;
         cmd.rumandText = "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();
      }
   }
}
/*
create procedure sp_Select_All_Employees
as
   select
      employeeid,
      firstname,
      lastname
   from
      employees
*/

Call stored procedure with parameter and return value

/*
Quote from

Beginning C# 2005 Databases From Novice to Professional
# Paperback: 528 pages
# Publisher: Apress (December 18, 2006)
# Language: English
# ISBN-10: 159059777X
# ISBN-13: 978-1590597774
*/

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
   static void Main()
   {
      SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
      try
      {
         conn.Open();
         SqlCommand cmd = conn.CreateCommand();
         cmd.rumandType = CommandType.StoredProcedure;
         cmd.rumandText = "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();
      }
   }
}
/*
create procedure sp_Orders_By_EmployeeId2
   @employeeid int,
   @ordercount int = 0 output
as
   select
      orderid,
      customerid
   from
      orders
   where
      employeeid = @employeeid;
   select
      @ordercount = count(*)
   from
      orders
   where
      employeeid = @employeeid
   return @ordercount
*/

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 Security=SSPI;Initial Catalog=Northwind;Data Source=localhost;";
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlCommand StoredProcedureCommand = new SqlCommand("Sales By Year", conn);
        StoredProcedureCommand.rumandType = 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 stored procedure with SqlCommand

using System;
using System.Data;            
using System.Data.SqlClient;  
  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.rumandType = CommandType.StoredProcedure;
      thisCommand.rumandText ="TenRecoreds";
      SqlDataReader thisReader = thisCommand.ExecuteReader();
      while (thisReader.Read())
      {
        Console.WriteLine(thisReader["UnitPrice"]);
      }
      thisReader.Close();
      thisConnection.Close();
    }
  }

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

Raising and Handling Stored Procedure Errors

/*
CREATE PROCEDURE RaiseError
    @Severity int,
    @State int = 1
AS
    RAISERROR ("Error of severity %d raised from stored procedure RaiseError.",
            @Severity, @State, @Severity)
    RAISERROR ("Error of severity %d raised from stored procedure RaiseError.",
            @Severity, @State, @Severity) WITH LOG
    RETURN
*/
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))
            {
                connection.InfoMessage += new SqlInfoMessageEventHandler(SqlMessageEventHandler);
                SqlCommand command = new SqlCommand("RaiseError", connection);
                command.rumandType = CommandType.StoredProcedure;
                command.Parameters.Add("@Severity", SqlDbType.Int);
                command.Parameters.Add("@State", SqlDbType.Int);
                for(int severity = -1; severity <= 26; severity++){
                    command.Parameters["@Severity"].Value = severity;
                    command.Parameters["@State"].Value = 0;
                    connection.Open( );
                    command.ExecuteNonQuery( );
                    connection.Close( );
                }
            }
        }
        private static void SqlMessageEventHandler(object sender,SqlInfoMessageEventArgs e)
        {
            Console.WriteLine("MESSAGE: {0}", e.Message);
        }
    }

Retrieves stored procedure parameter information

using System;
using System.Data;
using System.Data.SqlClient;
public class AddCustomer
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;Initial Catalog=store;Integrated Security=SSPI";
        string procedure = "CustomerAdd";
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(procedure, con);
        cmd.rumandType = CommandType.StoredProcedure;
        con.Open();
        SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters[1].Value = "name";
        cmd.Parameters[2].Value = "j@m.ru";
        cmd.Parameters[3].Value = "password";
        cmd.Parameters[4].Value = DBNull.Value;
        cmd.ExecuteNonQuery();
        con.Close();
        Console.WriteLine("New ID: " +cmd.Parameters[4].Value);
    }
}

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