Introduction
Inserting data into SQL Table and then Retrieving Autogenerated ID on the ASP.NET webform using Output Parameters.
Background
ADO.NET, SQL, HTML, C#
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 Database and a table which is having Emp_Id column as the primary key and is an Identity column i.e. It generated Id in the form of integers and increment it by 1 each time a row is inserted.
- Inserted some Sample Data.
- Created stored procedure which is expecting 4 Parameters out of which one is Output Parameter which is the @Emp_Id.
- Between Begin and End scope, we have inserted data into the table using the Parameters and then using Select statement, we get the Id of the employee inserted using the SCOPE_IDENTITY() function which returns the last inserted row in the table.
Explanation of Webform1.aspx
- Simple HTML is used. Just took 4 Labels, 2 Textboxes, 1 DropDownList and a Button to Insert 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 have to write the connection string again and again for each webform.
- Name attribute is used to add a meaningful name to connection string.
- providerName contains the namespace that is going to 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 Database.
Explanation of Webform1.aspx.cs
- Created the SqlConnection and used it in using Statement so that the connection closes automatically after the work is done.
- SqlCommand is created and name of the stored procedure is passed as 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.
- Connection is opened and data is inserted using ExecuteNonQuery().
- Output Parameter value is converted to 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 which helps us to complete our objective.
Hope you find this article useful. I will be posting more of them soon..
Please comment in case of any queries.
