Displaying data from database in gridview

In this tutorial we will learn how to select records from mssql server database and then display these records/data in ASP.NET Gridview Control. In .aspx page we have a .NET gridview control and it’s .cs side we are creating connection to database after creating connection, dataset, data adapter and data command objects, and then selecting records from database using select query and then binding gridview with these records/data using data adapter.

displaying_data_in_gridview.aspx

<asp:gridview id="GridView1" runat="server" width="100%">
<Columns>
<asp:TemplateField HeaderText="Email" >
<ItemTemplate>
<asp:Label ID="lbl_Email" Text='<%# Bind("User_Email")%>' runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Font-Size="15px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="First Name" >
<ItemTemplate>
<asp:Label ID="lbl_first_name" Text='<%# Bind("User_First_Name")%>' runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Font-Size="15px" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name" >
<ItemTemplate>
<asp:Label ID="lbl_last_name" CssClass="aLinkButton" Text='<%# Bind("User_Last_Name")%>' runat="server"></asp:Label>
</ItemTemplate>
<ItemStyle Font-Size="15px" />
</asp:TemplateField>
</Columns>
</asp:gridview>

displaying_data_in_gridview.aspx.cs

using System;
using System.Collections;
using System.Configuration;
using System.Data;

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;


public partial class Tutorials_displaying_data_in_gridview : System.Web.UI.Page
{
string strcon = "server=localhost;database=tutorials-db; uid=adeel;password=pakistan; Connect Timeout=200; Max Pool Size=5000";

SqlConnection con = new SqlConnection();

SqlCommand com = new SqlCommand();

DataSet ds = new DataSet();

SqlDataAdapter da;

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

private void bindgrid()
{
ds.Clear();
con.ConnectionString = strcon;

com.Connection = con;

con.Open();

string strQuery = "select * from users";

da = new SqlDataAdapter(strQuery,con);

da.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

con.Close();
}

}

Let’s understand .cs code

1) if (!IsPostBack) means bindgrid() function will be call if user refresh the webpage or posted back the webpage, this is very good for us because request will go to the database server if and only when page is posted back, not every time when page is loaded.
2) bindgrid() is a custom made function that will bind the data in gridview.
3) ds.Clear() means clean the dataset, we normally clean the dataset before using it just for precaution so that if dataset contains any old data then that old data will be deleted.
4) da = new SqlDataAdapter(strQuery,con); means we are Creating SqlDataAdapter object and then Passing SQL Query and Connection Object as Paramters to SqlDataAdapter object
5) da.Fill(ds) means filling dataset through data adapter.
6) GridView1.DataSource = ds; means assigning datasource to the gridview.

Note: - Don’t forget to include System.Data.SqlClient namespace in the list of namespaces because due to this namespace connection with mssql server will be established.

So this is the way to display the data in gridview in asp.net using C#