?
Solved

ParameterDirection

Posted on 2004-11-19
4
Medium Priority
?
208 Views
Last Modified: 2012-05-05
Can someone explain to me the purpose of this:

public void CreateSqlParameter()
 {
    SqlParameter myParameter = new SqlParameter("@Description", SqlDbType.VarChar);
    myParameter.Direction = ParameterDirection.Output;
 }

When do you use the ParameterDirection.Output... or ParameterDirection.Input? When would you use these. I've read something in msdn but I still do not see the point...
0
Comment
Question by:dotnet22
  • 4
4 Comments
 
LVL 5

Accepted Solution

by:
volking earned 2000 total points
ID: 12630500
Sometimes a stored procedure can have OUTPUT parameters.

------------------------------
CREATE PROC PostNewInvoiceAndBlankBill(@CustID int, @ProdID int,  @InvoiceID int output, @BillID int output)  as

     Insert Invoices (CustomerID, ProductID) Values (@CustID, @ProdID)
     SELECT @InvoiceID = @@IDENTITY

     INSERT Bills(CustomerID, InvoiceID) VALUES (@CustID, @InvoiceID)
     SELECT @BillID = @@IDENTITY

RETURN

------------------------
In the above example, the stored proc doesn't actually "SELECT" anything for return to the caller. But, you'll notice two records are created, and each record's identity column is captured. You'll also notice that the Proc's declaration has the word "output" appended after @InvoiceID and @BillID, which tells SQL server to output whatever final values @InvoiceID and @BillID are set to when exiting the proc.

If you were using a TSQL stored Proc to call the above stored proc, you would call it like this:

     CREATE MyProc as
          DECLARE @IDinvoice int, @IDbill int
          EXEC PostNewInvoiceAndBlankBill 102936, 88266, @IDinvoice output, @IDbill output

in the TSQL script ... right here ... you would have access to the values OUTPUT by PostNewInvoiceAndBlankBill .... @IDinvoice and @IDbill

The ParameterDirection.Output allows your subsequent CMD.Execute to caputre the output.

Hope this helps

0
 
LVL 5

Assisted Solution

by:volking
volking earned 2000 total points
ID: 12630548
0
 
LVL 5

Assisted Solution

by:volking
volking earned 2000 total points
ID: 12630583
in the sample ... notice @Identity is declared by the stored proc as output ...
----------------------------------------------------------------------------------------
CREATE PROCEDURE InsertCategory
  @CategoryName nchar(15),
  @Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = @@Identity
RETURN @@ROWCOUNT

========================================
And in the C# code find the lines

myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;

which declare @identity as an expected output parameter


0
 
LVL 5

Assisted Solution

by:volking
volking earned 2000 total points
ID: 12630617
Actually this example is better because it shows accessing the output parameter after the command.execute.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp

The line that accesses the output is:
Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters["@OutputParm"].Value);
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question