Call stored procedure with parameter and return value : SqlConnection Stored Procedure : ADO.Net C# Examples

    
/*
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
*/