ADO.Net Connectivity Using Stored Procedure and Output Parameters

Ankit Bansal
May 10, 2015
17.6k
0
1
Article

StoredProcedureWithOutputParameterDemo.zip

Objective

To insert data into a SQL table and then retrieve the Autogenerated ID on the ASP.NET webform using Output Parameters.

SQL Code

Create Database OutputParameterDemo
Use OutputParameterDemo
Create Table Employee(Emp_ID int identity primary key, Name nvarchar(40), Designation nvarchar(100), Salary int)
Select * from Employee
Insert into Employee values ('Ankit','Software Engineer',45000)
Insert into Employee values ('Max','Software Engineer',55000)
Insert into Employee values ('Mac','Software Engineer',9000)
Insert into Employee values ('Jane','Software Engineer',21000)
Insert into Employee values ('Joseph','Software Engineer',10000)
Insert into Employee values ('Jason','Software Engineer',32000)
Insert into Employee values ('Sumit','Software Engineer',85000)
Create Procedure spOutputParameter
@Name nvarchar(40), @Designation nvarchar(100), @Salary int, @Emp_ID int out
as
Begin
Insert into Employee Values (@Name, @Designation, @Salary)
Select @Emp_ID = SCOPE_IDENTITY()
End

Web Application in Visual Studio

WebForm1.aspx

<!DOCTYPE html>
<html>
<head runat="server">
<title>Output parameters Demo</title>
<link href="Content/bootstrap.min.css" rel="stylesheet" />
<meta name="description" content="Output parameters Demo" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
</head>
<body>
<form id="form1" runat="server">
<div class="jumbotron">
<h1 class="text-capitalize text-center">SP Output parameter Demo</h1>
</div>
<div class="container">
<table class="table table-hover">
<tr>
<td>
<asp:Label ID="lblName" runat="server" Text="Name" CssClass="label label-default" Font-Size="Large"></asp:Label></td>
<td>
<asp:TextBox ID="txtName" runat="server" CssClass="input-sm"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblDesignation" runat="server" Text="Designation" CssClass="label label-default" Font-Size="Large"></asp:Label></td>
<td>
<asp:DropDownList ID="ddlDesignation" runat="server">
<asp:ListItem>Software Engineer</asp:ListItem>
<asp:ListItem>Senior Software Engineer</asp:ListItem>
<asp:ListItem>Contract Trainee</asp:ListItem>
<asp:ListItem>Project Lead</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblSalary" runat="server" Text="Salary" CssClass="label label-default" Font-Size="Large"></asp:Label></td>
<td>
<asp:TextBox ID="txtSalary" runat="server" CssClass="input-sm"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="btnSubmit" runat="server" Text="Save Data" CssClass="btn btn-danger btn-lg" OnClick="btnSubmit_Click" />
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblStatus" runat="server" CssClass="label label-danger text-info" Font-Size="Large"></asp:Label></td>
</tr>
</table>
</div>
</form>
</body>
</html>

Webform1.aspx.cs

string CS = ConfigurationManager.ConnectionStrings["DatabaseCS"].ConnectionString;
protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spOutputParameter", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Designation", ddlDesignation.SelectedValue);
cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);
SqlParameter outputPara = new SqlParameter();
outputPara.ParameterName = "@Emp_ID";
outputPara.Direction = System.Data.ParameterDirection.Output;
outputPara.SqlDbType = System.Data.SqlDbType.Int;
cmd.Parameters.Add(outputPara);
con.Open();
cmd.ExecuteNonQuery();
string RetrievedEmpId = outputPara.Value.ToString();
lblStatus.Text = "Your Employee Id is : " + RetrievedEmpId;
}
}

Web.config

<configuration>
<connectionStrings>
<add name="DatabaseCS" providerName="System.Data.SqlClient" connectionString="data source = .; Initial Catalog = OutputParameterDemo; Integrated Security = true"/>
</connectionStrings>
</configuration>

Explanation of SQL Query

Created a database and a table with an Emp_Id column as the primary key and Identity column, in other words it generates the Id in the form of integers and increments it by 1 each time a row is inserted.
Inserted some sample data.
Created a Stored Procedure that is expecting 4 parameters out of which one is an Output Parameter that is the @Emp_Id.
Between the Begin and End scope, we have inserted data into the table using the parameters and then using a Select statement, we get the Id of the employee inserted using the SCOPE_IDENTITY() function that returns the last inserted row in the table.

Explanation of Webform1.aspx

Simple HTML is used. It just took 4 Labels, 2 Textboxes, 1 DropDownList and a Button to insert the data.
Bootstrapping is used to make the form look nice. I will be posting an article that contains a complete walkthrough of Bootstrap3.

Explanation of Web.config

We have included our connection string in the Web.config file so that we don't need to write the connection string multiple times for each webform.

The Name attribute is used to add a meaningful name to the connection string.
providerName contains the namespace that will support that function.
connectionString is the string used to connect to database. A (.) means that we are connecting to the local database. Initial Catalog is the name of the database.

Explanation of Webform1.aspx.cs

Created the SqlConnection and used it in a using statement so that the connection closes automatically after the work is done.
SqlCommand is created and the name of the Stored Procedure is passed as a parameter.
Parameters are added to the command object with the same name as that of the parameters of the Stored Procedure we created in SQL and the control is specified from which the value should be taken against each parameter.
SqlParameter is created for the Output Parameter and the Name, Direction and DataType of the parameter is specified using the respective properties of the SqlParameter class. This object is added as a parameter to the command object.
The connection is opened and the data is inserted using ExecuteNonQuery().
An Output Parameter value is converted to a string because we want to display the Id in the form of a string.
The value of the output parameter is displayed on the label; that helps us to satisfy our objective.

I hope you find this article useful. I will be writing more of them soon.

Please comment in case of any queries.