Introduction: Hello Guys, “Welcome back” Today, I am here with another one new great article. In this article I explained the implementation about how to use Store Procedure with Dapper in ASP.Net using C#.
Join our Channel on Whtsapp and Telagram for All Updates
Installing Dapper package using Nuget
Firstly you need to install Dapper library using Nuget. To detailed process about the installation of the Dapper Please refer article.
Database Details
I created the following table named userdetail with the all details as follows.
Column name data Type
userid int
username varchar(100)
useremail varchar(50)
useraddress varchar(250)
I have already inserted few records in the table.
userid username useremail useraddress
1 Sanjeev sanjeev@gmail.com #250 Sector 127, Chandigarh
2 Sanjay sanjay@gmail.com #123 Sector 255, Chandigarh
3 Ruhika ruhika@gmail.com #256 Sector 855, Chandigarh
4 Rajan rajan@gmail.com #55 Sector 00, Chandigarh
Here I am also giving SQL Server Database Script, you can execute this SQL Database Script by using copy and paste to create table with column and data.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[userdetails](
[userid] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](100) NOT NULL,
[useremail] [varchar](50) NOT NULL,
[useraddress] [varchar](250) NOT NULL
CONSTRAINT [PK_userdetails] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Stored Procedure
Here I am going to create Store Procedure named userdetail_getdetails to fetch the records from the table in SQL Server database.
CREATE PROCEDURE [userdetail_getdetails]
AS
BEGIN
SET NOCOUNT ON;
SELECT [userid]
,[username]
,[useremail]
,[useraddress]
FROM [userdetail]
END
Code for HTML Page
Create a page .cs named showuser_deatils and place the gridview control with the id named gridview_userdetails on this page to display the fetched records from the table in SQL Server database
There will be four BoundFIeld columns in the gridview.
<asp:GridView ID="gridview_userdetails" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="userid" HeaderText="User ID" />
<asp:BoundField DataField="username" HeaderText="User Name" />
<asp:BoundField DataField="useremail" HeaderText="User Email" />
<asp:BoundField DataField="usereaddress" HeaderText="User Address" />
</Columns>
</asp:GridView>
Namespaces
Here we need to import below mentioned namespaces for the showuser_deatils.cs page.
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
Binding GridView using Stored Procedure and Dapper in ASP.Net
On the Page_Load event handler, the records will be fetched from the table named userdetail in SQLServer database using ExecuteReader method of Dapper library with Store procedure and it will be copied to DataTable object using Load method.
Finally, the DataTable is assigned to the DataSource property of GridView and DataBind method is called.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string constr = ConfigurationManager.ConnectionStrings["Connection_string"].ConnectionString;
string spName = "userdetail_getdetails";
using (SqlConnection con = new SqlConnection(constr))
{
using (IDataReader sdr = con.ExecuteReader(spName, commandType: CommandType.StoredProcedure))
{
using (DataTable dtuserdetail = new DataTable())
{
dtuserdetail.Load(sdr);
gridview_usrdetails.DataSource = dtuserdetail;
gridview_usrdetails.DataBind();
}
}
}
}
}
Here I am giving complete code for HTML page, You need to copy and paste the code in your HTML page for enjoying the code.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
body {
font-family: Arial;
font-size: 10pt;
}
table {
border: 1px solid #ccc;
border-collapse: collapse;
}
table th {
background-color: #F7F7F7;
color: #333;
font-weight: bold;
}
table th, table td {
padding: 5px;
border: 1px solid #ccc;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="gridview_userdetails" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="userid" HeaderText="User ID" />
<asp:BoundField DataField="username" HeaderText="User Name" />
<asp:BoundField DataField="useremail" HeaderText="User Email" />
<asp:BoundField DataField="usereaddress" HeaderText="User Address" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
Here I am giving complete code for C# page You need to copy and paste the code in your HTML page for enjoying the code.
using System;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class CS : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
string constr = ConfigurationManager.ConnectionStrings["Connection_string "].ConnectionString;
string spName = "userdetail_getdetails";
using (SqlConnection con = new SqlConnection(constr))
{
using (IDataReader sdr = con.ExecuteReader(spName, commandType:
CommandType.StoredProcedure))
{
using (DataTable dtuserdetail = new DataTable())
{
dtuserdetail.Load(sdr);
gridview_usrdetails.DataSource = dtuserdetail;
gridview_usrdetails.DataBind();
}
}
}
}
}
Here I am giving complete code for Web cofig, You need to copy and paste the code in your web config file and enjoy the code.
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name=" Connection_string" connectionString="Data Source=.\SQL2022;DataBase=;UID=;PWD=pass@123" providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.8" />
<httpRuntime targetFramework="4.8" />
</system.web>
</configuration>
Conclusion: In above code, I have been explained that how to use Store Procedure with Dapper in ASP.Net using C#. Bye and take care of yourself Developers. We will come back shortly with the new article.
Regards
Programming Hub
No comments:
Post a Comment