Getting Output Parameter from SQL Store Procedure

Introduction:
In this article you can learn how to use store procedure and to get the Identity column value as an out parameter in SQL server using c#. Some beginners feel very hard to get the out parameter in SQL store procedure using VB or C# as a back end and this article will overcome this scenario. In this article, I have used very simple coding for understanding the usages of store procedure.

The store procedure is the best method of getting, inserting, updating and deleting the records in the database table in SQL server. It will be fast in manipulating the data when compare to writing a SQL Query in VB or C# code.

Before going to the article, first we can create a table in Pubs database and a store procedure in the same. Execute the following SQL query in the query analyzer at Pubs Database.
For Creating Table:
CREATE TABLE [dbo].[DummyTable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[User_FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[User_LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

after executing this query, three column will be created User_FirstName, User_LastName
and Id column as Identity column.
For Creating Store Procedure:
CREATE PROCEDURE [InsertDummy]
@User_FirstName varchar(50),
@User_LastName varchar(50),
@OutParamID int output
AS
insert into DummyTable (User_FirstName, User_LastName) values(@User_FirstName, @User_LastName)
set @OutParamID = @@IDENTITY

On executing the above query, table and store procedure will be created and we can access that using code.
In aspx page add two textbox and a button. these TextBoxs will get the first name and last name of a user and if you Submit it (click it), the values in the TextBox will insert to the table.

In this article I will explain accessing the store procedure in C# & VB. Following code will help to insert a record in to the table and get the Identity column value of the inserted row.

C# Code:

public int InsertDummyTable()
{
SqlConnection cn = new SqlConnection(constring);
SqlCommand cm = new SqlCommand("InsertDummy",cn);
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new SqlParameter("@User_FirstName",SqlDbType.VarChar)).Value = txtFirstName.Text;
cm.Parameters.Add(new SqlParameter("@User_LastName",SqlDbType.VarChar)).Value = txtFirstName.Text;
SqlParameter outPar = new SqlParameter("@OutParamID",SqlDbType.Int);
int ourPhar =0;
outPar.Value = ourPhar;
outPar.Direction = ParameterDirection.Output;
cm.Parameters.Add(outPar);
cn.Open();
cm.ExecuteNonQuery();
int returnId;
returnId = (int)cm.Parameters["@OutParamID"].Value;
cn.Close();
return returnId;
}

private void cmdSubmit_Click(object sender, System.EventArgs e)
{
Response.Write(InsertDummyTable().ToString());
}

VB Code:
Private Function InsertDummyTable() As Integer
Dim connstring As String
connstring = "" ' Declare connection string.
Dim cn As New SqlConnection(connstring)
Dim cm As New SqlCommand("InsertDummy", cn)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.Add(New SqlParameter("@User_FirstName", SqlDbType.VarChar)).Value = txtFirstName.Text
cm.Parameters.Add(New SqlParameter("@User_LastName", SqlDbType.VarChar)).Value = txtFirstName.Text
Dim outPar As New SqlParameter("@OutParamID", SqlDbType.Int)
Dim ourPhar As Integer = 0
outPar.Value = ourPhar
outPar.Direction = ParameterDirection.Output
cm.Parameters.Add(outPar)
cn.Open()
cm.ExecuteNonQuery()
Dim returnId As Integer
returnId = Convert.ToInt32(cm.Parameters["@OutParamID"].Value)
cn.Close()
Return returnId
End Function

Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Click
Response.Write(InsertDummyTable().ToString())
End Sub
The above code will insert and print the Identity column of the record.
There is there type of parameter direct. Input, InputOutput, Output, ReturnValue.
For getting the output parameter we can use only the output as the parameter direct as in the code below.

outPar.Direction = ParameterDirection.Output;

Conclusion:
Hope this article will help you to understand how to get the output Parameter from SQL Store Procedure using C# and VB.NET. In next Article I will come up with new basic stuffs.