How to bind data to Gridview in Three-Tier Architecture in asp.net using SqlHelper

Introduction: In this article I have explained how we can bind Data to Gridview in Three-Tier Architecture in asp.net SqlHelper class.

Description:

In the last article i have explained How to Get Title, Description, Keywords Meta Tags and Image from URL in Asp.net and How to insert Data into Database using Three Tier Architecture in Asp.net
I have  a Table USER_REGISTRATION:

ID	int
USERNAME	varchar(50)
PASSWORD	varchar(50)
FIRST_NAME	varchar(50)
LAST_NAME	varchar(50)
SEX	varchar(50)
EMAIL_ID	varchar(50)

Here ID is autoincrement and primary key. After that create a store procedure to Display records from table as shown below:

CREATE PROCEDURE DISPLAY_USERS

AS

BEGIN

     

      SET NOCOUNT ON;

   SELECT * FROM dbo.USER_REGISTRATION

END

GO

Add a new Class  to clsuserRegistartion.cs to Bussiness Layer (DataEntities):

public class clsuserRegistartion

    {

       public int ID {get;set;}

       public string USERNAME { get; set; }

       public string PASSWORD { get; set; }

       public string FIRST_NAME { get; set; }

       public string LAST_NAME { get; set; }

       public string SEX { get; set; }

       public string EMAIL_ID { get; set; }

    }

To download the SqlHelper.cs CLICK HERE. After download the file put the class in Data Access Layer.

Note : Do not forget to add ConnectionString in web.config file:

<connectionStrings>

    <add name="Con" connectionString="Data Source=VIJAY-PC;Initial Catalog=TEST_APPLICATION;Integrated Security=True"/>

  </connectionStrings>

To call ConectionString in project I cretae class Connection.cs in Data Access layer and write the code as mention below:

using System.Configuration;

public class Connection

    {

       static string str;

       public string Con()

       {

           str = ConfigurationManager.ConnectionStrings["Con"].ToString();

           return str;

       }

    }

Bulid the project. Now add a new class clsRegistartionDal.cs to Data Access layer. Now add the project reference of DataEntites (Bussiness layer) to project , use the namespace of SqlHelper and write the below given code:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using Microsoft.ApplicationBlocks.Data;

using New.DataEn

public DataSet Display_Records()

       {

           SqlParameter[] param = new SqlParameter[0];

           {

               ds = SqlHelper.ExecuteDataset(clscon.Con(), "DISPLAY_USERS", param);

           }

           return ds;

       }

Here New.DataEn, New is the name of project and DataEn is Bussiness layer (DataEntities).

Add the reference of Data Access Layer and Bussiness Access Layer (DataEntities). After add a new webform to project. Drag and drop the Gridview Data Control from Toolbox and desgin page as shown below:

  <table align="center"><tr><td>

           <asp:GridView ID="grduse" runat="server" AutoGenerateColumns="False">

            <Columns>

                <asp:TemplateField HeaderText="USERNAME">

                    <ItemTemplate>

                        <asp:Label ID="lblname" runat="server" Text='<%# Eval("USERNAME") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="FIRST NAME">

                    <ItemTemplate>

                        <asp:Label ID="lblfirst" runat="server" Text='<%# Eval("FIRST_NAME") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="LAST NAME">

                    <ItemTemplate>

                        <asp:Label ID="lbllast" runat="server" Text='<%# Eval("LAST_NAME") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                 <asp:TemplateField HeaderText="SEX">

                    <ItemTemplate>

                        <asp:Label ID="lblsex" runat="server" Text='<%# Eval("SEX") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

                   <asp:TemplateField HeaderText="Email">

                    <ItemTemplate>

                        <asp:Label ID="lblemail" runat="server" Text='<%# Eval("EMAIL_ID") %>'></asp:Label>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView></td></tr></table>

Now on .aspx.cs page write the below given code:

using New.DAL;

using New.DataEn;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

clsRegistartionDal clsdal = new clsRegistartionDal();

        clsuserRegistartion clsdata = new clsuserRegistartion();

        DataSet ds = new DataSet();

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                BindGrid();

            }

        }

        private void BindGrid()

        {

            ds = clsdal.Display_Records();

            if (ds.Tables[0].Rows.Count > 0)

            {

                grduse.DataSource = ds;

                grduse.DataBind();

            }

        }

Now build, run the project and check the result.

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.