Post
Ask Question

ADO.Net Connectivity Using Stored Procedure and Output Parameters

Objective
 
To insert data into a SQL table and then retrieve the Autogenerated ID on the ASP.NET webform using Output Parameters.
 
SQL Code
  1. Create Database OutputParameterDemo  
  2.   
  3. Use OutputParameterDemo  
  4.   
  5. Create Table Employee(Emp_ID int identity primary key, Name nvarchar(40), Designation nvarchar(100), Salary int)  
  6.   
  7. Select * from Employee  
  8.   
  9. Insert into Employee values ('Ankit','Software Engineer',45000)  
  10. Insert into Employee values ('Max','Software Engineer',55000)  
  11. Insert into Employee values ('Mac','Software Engineer',9000)  
  12. Insert into Employee values ('Jane','Software Engineer',21000)  
  13. Insert into Employee values ('Joseph','Software Engineer',10000)  
  14. Insert into Employee values ('Jason','Software Engineer',32000)  
  15. Insert into Employee values ('Sumit','Software Engineer',85000)  
  16.   
  17. Create Procedure spOutputParameter  
  18. @Name nvarchar(40), @Designation nvarchar(100), @Salary int, @Emp_ID int out  
  19. as  
  20. Begin  
  21.     Insert into Employee Values (@Name, @Designation, @Salary)  
  22.     Select @Emp_ID = SCOPE_IDENTITY()  
  23. End  

Web Application in Visual Studio

 
WebForm1.aspx 
  1. <!DOCTYPE html>  
  2.   
  3. <html>  
  4. <head runat="server">  
  5.     <title>Output parameters Demo</title>  
  6.     <link href="Content/bootstrap.min.css" rel="stylesheet" />  
  7.     <meta name="description" content="Output parameters Demo" />  
  8.     <meta name="viewport" content="width=device-width, initial-scale=1.0" />  
  9. </head>  
  10. <body>  
  11.     <form id="form1" runat="server">  
  12.         <div class="jumbotron">  
  13.             <h1 class="text-capitalize text-center">SP Output parameter Demo</h1>  
  14.         </div>  
  15.         <div class="container">  
  16.             <table class="table table-hover">  
  17.                 <tr>  
  18.                     <td>  
  19.                         <asp:Label ID="lblName" runat="server" Text="Name" CssClass="label label-default" Font-Size="Large"></asp:Label></td>  
  20.                     <td>  
  21.                         <asp:TextBox ID="txtName" runat="server" CssClass="input-sm"></asp:TextBox>  
  22.                     </td>  
  23.                 </tr>  
  24.                 <tr>  
  25.                     <td>  
  26.                         <asp:Label ID="lblDesignation" runat="server" Text="Designation" CssClass="label label-default" Font-Size="Large"></asp:Label></td>  
  27.                     <td>  
  28.                         <asp:DropDownList ID="ddlDesignation" runat="server">  
  29.                             <asp:ListItem>Software Engineer</asp:ListItem>  
  30.                             <asp:ListItem>Senior Software Engineer</asp:ListItem>  
  31.                             <asp:ListItem>Contract Trainee</asp:ListItem>  
  32.                             <asp:ListItem>Project Lead</asp:ListItem>  
  33.                         </asp:DropDownList>  
  34.   
  35.                     </td>  
  36.                 </tr>  
  37.                 <tr>  
  38.                     <td>  
  39.                         <asp:Label ID="lblSalary" runat="server" Text="Salary" CssClass="label label-default" Font-Size="Large"></asp:Label></td>  
  40.                     <td>  
  41.                         <asp:TextBox ID="txtSalary" runat="server" CssClass="input-sm"></asp:TextBox>  
  42.                     </td>  
  43.                 </tr>  
  44.                 <tr>  
  45.                     <td colspan="2">  
  46.                         <asp:Button ID="btnSubmit" runat="server" Text="Save Data" CssClass="btn btn-danger btn-lg" OnClick="btnSubmit_Click" />  
  47.                     </td>  
  48.                 </tr>  
  49.                 <tr>  
  50.                     <td>  
  51.                         <asp:Label ID="lblStatus" runat="server" CssClass="label label-danger text-info" Font-Size="Large"></asp:Label></td>  
  52.   
  53.                 </tr>  
  54.             </table>  
  55.         </div>  
  56.     </form>  
  57. </body>  
  58. </html>  
Webform1.aspx.cs
  1. string CS = ConfigurationManager.ConnectionStrings["DatabaseCS"].ConnectionString;    
  2. protected void btnSubmit_Click(object sender, EventArgs e)    
  3. {    
  4.     using (SqlConnection con = new SqlConnection(CS))    
  5.     {    
  6.         SqlCommand cmd = new SqlCommand("spOutputParameter", con);    
  7.         cmd.CommandType = System.Data.CommandType.StoredProcedure;    
  8.   
  9.         cmd.Parameters.AddWithValue("@Name", txtName.Text);    
  10.         cmd.Parameters.AddWithValue("@Designation", ddlDesignation.SelectedValue);    
  11.         cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);    
  12.   
  13.         SqlParameter outputPara = new SqlParameter();    
  14.         outputPara.ParameterName = "@Emp_ID";    
  15.         outputPara.Direction = System.Data.ParameterDirection.Output;    
  16.         outputPara.SqlDbType = System.Data.SqlDbType.Int;    
  17.         cmd.Parameters.Add(outputPara);    
  18.   
  19.         con.Open();    
  20.         cmd.ExecuteNonQuery();    
  21.   
  22.         string RetrievedEmpId = outputPara.Value.ToString();    
  23.         lblStatus.Text = "Your Employee Id is : " + RetrievedEmpId;    
  24.     }    
  25. }   
Web.config
  1. <configuration>  
  2. <connectionStrings>  
  3.   <add name="DatabaseCS" providerName="System.Data.SqlClient" connectionString="data source = .; Initial Catalog = OutputParameterDemo; Integrated Security = true"/>  
  4. </connectionStrings>  
  5. </configuration>  
Explanation of SQL Query 
  1. 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.
  2. Inserted some sample data.
  3. Created a Stored Procedure that is expecting 4 parameters out of which one is an Output Parameter that is the @Emp_Id.
  4. 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
  1. Simple HTML is used. It just took 4 Labels, 2 Textboxes, 1 DropDownList, and a Button to insert the data.
  2. 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.
  1. The Name attribute is used to add a meaningful name to the connection string.
  2. providerName contains the namespace that will support that function.
  3. connectionString is the string used to connect to the database. A (.) means that we are connecting to the local database. Initial Catalog is the name of the database.
Explanation of Webform1.aspx.cs
  1. Created the SqlConnection and used it in a using statement so that the connection closes automatically after the work is done.
  2. SqlCommand is created and the name of the Stored Procedure is passed as a parameter.
  3. 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.
  4. 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.
  5. The connection is opened and the data is inserted using ExecuteNonQuery().
  6. An Output Parameter value is converted to a string because we want to display the Id in the form of a string.
  7. The value of the output parameter is displayed on the label; which 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.


Similar Articles