How to Bind Gridview using Store Procedure, SqlDataAdapter and Datatable in Asp.net - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

How to Bind Gridview using Store Procedure, SqlDataAdapter and Datatable in Asp.net


Introduction: In this post I will explain to bind Gridview Data Control using Store Procedure, SqlDataAdapter and Datatable.
Description:
I have created a table name STUDENT_DETAIL and insert data into table.
STUDENT_ID
int
STUDENT_NAME
varchar(50)
STUDENT_ADDRESS
varchar(50)
STUDENT_CLASS
varchar(50)
STUDENT_ID is primary key.
Create a Store Procedure:
CREATE PROCEDURE BIND_GRID

AS
BEGIN
     
      SET NOCOUNT ON;

    SELECT * FROM dbo.STUDENT_DETAIL
END
GO


Now open the Visual Studio>Go to File>New>Website. Add the Connectionstring in web.config file of website.

<configuration>
       <connectionStrings>
    <add name="connection" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True"/>
       </connectionStrings>
       <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
</configuration>

After that add new web form to website, drag and drop the Gridview data control from Toolbox.
 <asp:GridView ID="grdstudentdetail" runat="server" AutoGenerateColumns="False"
            DataKeyNames="STUDENT_ID">
            <Columns>
                <asp:TemplateField HeaderText="STUDENT NAME">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="STUDENT ADDRESS">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtaddress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lbladdress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="STUDENT CLASS">
                    <EditItemTemplate>
                        <asp:TextBox ID="txtclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                           </Columns>
            <PagerSettings PageButtonCount="2" />
        </asp:GridView>
Now got to .aspx.cs page.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bindgrid();
        }
    }
    public void Bindgrid()
    {
        SqlCommand cmd = new SqlCommand("BIND_GRID", con);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
      
        cmd.CommandType = CommandType.StoredProcedure;
       
        DataTable dt = new DataTable();
        adp.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            grdstudentdetail.DataSource = dt;
            grdstudentdetail.DataBind();
        }
        else
        {
            dt.Rows.Add(dt.NewRow());
            grdstudentdetail.DataSource = dt;
            grdstudentdetail.DataBind();
            int columncount = grdstudentdetail.Rows[0].Cells.Count;
            grdstudentdetail.Rows[0].Cells.Clear();
            grdstudentdetail.Rows[0].Cells.Add(new TableCell());
            grdstudentdetail.Rows[0].Cells[0].ColumnSpan = columncount;
            grdstudentdetail.Rows[0].Cells[0].Text = "No Records Available";
        }

    }

In VB

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Bindgrid()
        End If
    End Sub
    Public Sub Bindgrid()
        Dim cmd As New SqlCommand("BIND_GRID", con)
        Dim adp As New SqlDataAdapter(cmd)

        cmd.CommandType = CommandType.StoredProcedure

        Dim dt As New DataTable()
        adp.Fill(dt)
        If dt.Rows.Count > 0 Then
            grdstudentdetail.DataSource = dt
            grdstudentdetail.DataBind()
        Else
            dt.Rows.Add(dt.NewRow())
            grdstudentdetail.DataSource = dt
            grdstudentdetail.DataBind()
            Dim columncount As Integer = grdstudentdetail.Rows(0).Cells.Count
            grdstudentdetail.Rows(0).Cells.Clear()
            grdstudentdetail.Rows(0).Cells.Add(New TableCell())
            grdstudentdetail.Rows(0).Cells(0).ColumnSpan = columncount
            grdstudentdetail.Rows(0).Cells(0).Text = "No Records Available"
        End If

    End Sub

Now run 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.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon