Mega Code Archive
Call stored procedure with parameter and return value
<![CDATA[
/*
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
*/
]]>