In this article I will explain with an example, how to use
Output Parameter in
Stored Procedure with
Dapper library in ASP.Net using C# and VB.Net.
Installing Dapper package using Nuget
Database
I have made use of the following table Customers with the schema as follows.
Note: You can download the database table SQL by clicking the download link below.
Stored Procedure
This
Stored Procedure accepts following parameters which are used to
insert the records:
Name – This is an INPUT Parameter to pass the Name of the Customer.
Country – This is an INPUT Parameter to pass the Country of the Customer.
CustomerId – This is an OUTPUT Parameter to get the last inserted CustomerId.
Note: Output Parameter is identified by the keyword OUTPUT.
CREATE PROCEDURE [Customers_InsertCustomer_Output]
@Name VARCHAR(100),
@Country VARCHAR(50),
@CustomerId INT OUTPUT
AS
BEGIN
INSERT INTO [Customers]
([Name]
,[Country])
VALUES (@Name
,@Country)
SELECT @CustomerId = SCOPE_IDENTITY()
END
HTML Markup
The following HTML Markup consists of:
TextBox – For capturing Name to be inserted.
DropDownList – For selecting Country name to be inserted.
Button – For inserting the record.
The Button has been assigned with an OnClick event handler.
<table>
<tr>
<td>Name:</td>
<td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Country:</td>
<td><asp:DropDownList ID="ddlCountries" runat="server">
<asp:ListItem Text="Please select" Value=" "></asp:ListItem>
<asp:ListItem Text="United States" Value="United States"></asp:ListItem>
<asp:ListItem Text="India" Value="India"></asp:ListItem>
<asp:ListItem Text="France" Value="France"></asp:ListItem>
<asp:ListItem Text="Russia" Value="Russia"></asp:ListItem>
</asp:DropDownList></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="OnInsert" /></td>
</tr>
</table>
Namespaces
You will need to import the following namespaces.
C#
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports Dapper
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Using Output Parameter in Stored Procedure with Dapper in ASP.Net
When Insert button is clicked, first the connection is read from Web.Config file.
An object of DynamicParameters is created and CustomerId is added as parameter with its Data Type specified and its Direction is set to Output since by default the Direction of all parameters are Input.
Then, the Name and Country values are also added as parameter.
After that, the
Execute method of
Dapper library is used to fetch
CustomerId of the inserted record from the
Customers Table.
The CustomerId of the inserted record i.e. the output parameter value is fetched using Get method of DynamicParameters class.
Finally, the TextBox and DropDownList values are set to empty and
CustomerId of the inserted record is displayed in
JavaScript Alert Message Box using
RegisterStartupScript method.
C#
protected void OnInsert(object sender, EventArgs e)
{
int customerId;
string spName = "Customers_InsertCustomer_Output";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
DynamicParameters dynamicParameters = new DynamicParameters();
// Adding Input parameters.
dynamicParameters.Add("@Name", txtName.Text);
dynamicParameters.Add("@Country", ddlCountries.SelectedItem.Text);
// Adding Output parameter.
dynamicParameters.Add("@CustomerId", DbType.Int32, direction: ParameterDirection.Output);
con.Execute(spName, dynamicParameters, commandType: CommandType.StoredProcedure);
customerId = dynamicParameters.Get<int>("@CustomerId");
}
txtName.Text = string.Empty;
ddlCountries.SelectedValue = string.Empty;
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Inserted Customer ID: " + customerId + "');", true);
}
VB.Net
Protected Sub OnInsert(sender As Object, e As EventArgs)
Dim customerId As Integer
Dim spName As String = "Customers_InsertCustomer_Output"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Dim dynamicParameters As DynamicParameters = New DynamicParameters()
' Adding Input parameters.
dynamicParameters.Add("@Name", txtName.Text)
dynamicParameters.Add("@Country", ddlCountries.SelectedItem.Text)
' Adding Input parameters.
dynamicParameters.Add("@CustomerId", DbType.Int32, direction:=ParameterDirection.Output)
con.Execute(spName, dynamicParameters, commandType:=CommandType.StoredProcedure)
customerId = dynamicParameters.Get(Of Integer)("@CustomerId")
End Using
txtName.Text = String.Empty
ddlCountries.SelectedValue = String.Empty
ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Inserted Customer ID: " & customerId & "');", True)
End Sub
Screenshots
The Form
Record after Insert in database
Downloads