Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Output Parameters with Stored Proc ADO.NET

Posted by Ravi Varma Thumati on May 13, 2010

Here is a sample sproc that populates output parameters from the Northwind Products table:

< CustOrderOne>

CREATE PROCEDURE CustOrderOne
@CustomerID nchar(5),
@ProductName varchar(50) output,
@Quantity int output

AS
SELECT TOP 1 @ProductName=PRODUCTNAME, @Quantity =quantity
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

And here is an example of some C# code to return and display the output parameters:

using System;
using System.Data;
using System.Data.SqlClient;
namespace OutPutParms
{
class OutputParams
{
[STAThread]
static void Main(string[] args)
{
using( SqlConnection cn = new SqlConnection(“server=(local);Database=Northwind;user    id=sa;password=;”))
{
SqlCommand cmd = new SqlCommand(“CustOrderOne”, cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter(“@CustomerID”,SqlDbType.NChar) ;
parm.Value=”ALFKI”;
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new     SqlParameter(“@ProductName”,SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter(“@Quantity”,SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters[“@ProductName”].Value);
Console.WriteLine(cmd.Parameters[“@Quantity”].Value.ToString());
Console.ReadLine();
}
}
}
}

Advertisements

One Response to “Output Parameters with Stored Proc ADO.NET”

  1. I¡¯m delighted that I’ve noticed this weblog. Lastly something not a junk, which we go through extremely frequently. The web site is lovingly serviced and stored up to date. So it should be, thanks for sharing this with us.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: