how to get (retrieve) record from sql server and show in asp.net gridview in c#


Article Author : Tauseef Ahmad, Article Posted on : 9/18/2017, Article Category : Asp.net


Download full source code here: how to get (retrieve) record from sql server and show in asp.net gridview in c#
please share this article to help others :

Here i have explained with an example and attached code that how to retrieve (get) Record from sql server database and show in asp.net gridview with paging in c# and in previous article i explained how to save (insert) data in database using asp.net c#

i am explaining with very easy step by step process with screenshots and code provided.

There are few steps to follow.

 

DataBase Portion:

Step 1. Open SQL Server Go to database and  open Table (Student) All this step has been done in previous Article

 Student Table with records in screenshot

 

 

 

Visual Studio Portion:

Step 2. Now open visual studio ( i used vs 2013 ) and create new empty website just see the screenshot.

 

now give name to website and click OK.

 

 

Step 3.  now copy and add the following code in web.config file.

 

<connectionStrings>
    <add name="conString" connectionString="Data Source=tauseef;database=TestDemoDB; Integrated Security=true"/>
  </connectionStrings >

 

 

in connectionstring give your datasource name as i gave my own and you give your datasource name and also in database give your database name.

in case your sql server need sql authentication (userid and password ) than just use the following code other wise in window authentication use above code.

<connectionStrings>
    <add name="SqlConn" connectionString="Data Source=Tauseef;Initial Catalog=TestDemoDB;User Id=sa;Password=password" providerName="System.Data.SqlClient" />
  </connectionStrings>

 

Now add new file (aspx) 

 

give name to a page.

 

 

HTML Portion:

Add the following code to aspx page this code is used for show Record in gridview with paging to end user. GridView is one of the most common tools for displaying data in a grid format in ASP.NET.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true"
    OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="3">
    <Columns>
        <asp:BoundField ItemStyle-Width="150px" DataField="ID" HeaderText="Student ID" />
        <asp:BoundField ItemStyle-Width="150px" DataField="Name" HeaderText="Student Name" />
        <asp:BoundField ItemStyle-Width="150px" DataField="Degree" HeaderText="Degree" />
   
    </Columns>
</asp:GridView>

 

in  the above code asp.net gridview has some properties AllowPaging. Paging is very important When the data becomes large,paging helps the users to view chunks of data and also increases Page load time.

now if you want to give design to grid Go to page design mode and click on the small arrow shows in screenshot and than select auto format a popup window will open than select any design. some extra code will be automatically added to grid.

 

 

Code Behind Portion:

Namespace:

import the following namespaces.

using System.Data;
using System.Data.SqlClient;

 

Now create a method to get record from sql server and bind with gridview datasource.

 

 private void BindGrid()
    {
        string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT ID, Name, Degree  FROM student"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                }
            }
        }
    }

 

Implementing paging in gridview. when a page is changed inside gridview the following event handler will executed.

 

 protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        this.BindGrid();
    }

 

Finally bind the grid method in pageload event whenever page loaded Record will be shown.

 

protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.BindGrid();
        }
    }

 

 

Now Run the Page and enjoy the gridview control power.



Related Article


Add your valuable Comments


Success! Comment added successfully.

Name is required

Email is a required field Invalid Email
Message is required

Comments


{{cmt.Item1}}


{{cmt.Item2}}

{{cmt.Item3.slice(0,9)}}


ADS

Tags:

get record, retrieve record, asp.net gridview, paging