Click here to Skip to main content
Click here to Skip to main content

ADO.NET – How to call a stored procedure with output parameters?

, 24 Mar 2014
Rate this:
Please Sign up or sign in to vote.
CodeProject In the last article on ADO.Net, we have discussed about preventing SQL injection attack. You can read that article here. In this article, we will go over calling stored procedure with output parameters. Let’s understand this with an example. Our example will be based on tblEmployee

In the last article on ADO.NET, we have discussed about preventing SQL injection attack. You can read that article here. In this article, we will go over calling stored procedure with output parameters.

Let’s understand this with an example. Our example will be based on tblEmployees table. The script to create this table is shown below. The important point to note here is that EmployeeId is marked as an identity column. When inserting a row into the table, we need not provide a value for the EmployeeId column. If you want to learn more about identity column, please read here.

CREATE TABLE tblEmployees
(
EmployeeId int identity primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)

Script to insert sample data is following. Notice that in the insert statement, we are not providing a value for EmployeeId Column.

INSERT INTO tblEmployees values(‘Mason’,‘Male’,5000)
INSERT INTO tblEmployees values(‘Priyanka’,‘Female’,3500)
INSERT INTO tblEmployees values(‘John’,‘Male’,2350)
INSERT INTO tblEmployees values(‘Louna’,‘Female’,5700)
INSERT INTO tblEmployees values(‘Jackson’,‘Male’,4890)
INSERT INTO tblEmployees values(‘Aulia’,‘Female’,4500)

Then create a stored procedure named spAddEmployee. The features of this stored procedure are following:

  1. @Name, @Gender and @Salary are input parameters.
  2. @EmployeeId is an output parameter.
  3. The stored procedure has got only 2 lines of code with in the body. The first line inserts a row into the tblEmployees table. The second line gets the auto generated identity value of the EmployeeId column.

This procedure will later be called by a .Net application.

CREATE PROCEDURE spAddEmployee
@Name nvarchar(50),
@Gender nvarchar(20),
@Salary int,
@EmployeeId int Out
AS
BEGIN
INSERT INTO tblEmployees Values(@Name, @Gender, @Salary)
SELECT @EmployeeId = SCOPE_IDENTITY()
END

At this point, we have done everything that is required, from a database perspective. Now let’s flip to visual studio. Create an asp.net web application. Copy and Paste the following HTML onto a webform.

<table style="border: 1px solid black; font-family:Arial">
<tr>
<td>
Employee Name
</td>
<td>
<asp:TextBox ID="txtEmployeeName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Gender
</td>
<td>
<asp:DropDownList ID="ddlGender" runat="server">
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Salary
</td>
<td>
<asp:TextBox ID="txtSalary" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="">
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="lblMessage" runat="server"></asp:Label>
</td>
</tr>
</table>

The design of the webform, should be as shown below.

Employee Details

Copy and paste the following code in the code behind page.

protected void btnSubmit_Click(object sender, EventArgs e)
{
//Read the connection string from Web.Config file
string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object
SqlCommand cmd = new SqlCommand(“spAddEmployee”, con);

//Specify that the SqlCommand is a stored procedure
cmd.CommandType = System.Data.CommandType.StoredProcedure;

//Add the input parameters to the command object
cmd.Parameters.AddWithValue(“@Name”, txtEmployeeName.Text);
cmd.Parameters.AddWithValue(“@Gender”, ddlGender.SelectedValue);
cmd.Parameters.AddWithValue(“@Salary”, txtSalary.Text);

//Add the output parameter to the command object
SqlParameter outPutParameter = new SqlParameter();
outPutParameter.ParameterName = “@EmployeeId”;
outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
outPutParameter.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(outPutParameter);

//Open the connection and execute the query
con.Open();
cmd.ExecuteNonQuery();

//Retrieve the value of the output parameter
string EmployeeId = outPutParameter.Value.ToString();
lblMessage.Text = “Employee Id = “ + EmployeeId;
}
}

Note: Please make sure to add the following using declarations at the top of the code behind page.

using System.Data.SqlClient;
using System.Configuration;

Now run the application. Fill in the employee details and click Submit. The Employee row gets added to the database and the generated EmployeeId is shown on the screen.

Reference: Arun Ramachandran (http://BestTEchnologyBlog.Com)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 24 Mar 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid