Call stored procedure with parameter and return value : SqlConnection Stored Procedure « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. SqlConnection Stored Procedure

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