ASP.NET Bind Gridview from Database

Post Views: 855

ASP.NET Bind Gridview from Database : To bind asp.net grid view from database, follow this simple steps. This example is to fill gridview from MS SQL server database.

Step 1 . Database Script :

Open your MS SQL database server management studio and create a database with name SimpleAsp.
Now create a table in your database with name tblCountry with columns name : Id int, Continent nvarchar(50) Country nvarchar(50)

This is the script code of my database for your reference :

create database SimpleAsp
use SimpleAsp
create table tblCountry 
(
Id int identity (1,1) not null,
Continent nvarchar(50),
Country nvarchar(50)
)

insert into tblCountry values ('Asia','India');
insert into tblCountry values ('Asia','Singapore');
insert into tblCountry values ('Asia','Sri Lanka');
insert into tblCountry values ('Asia','Malaysia');
insert into tblCountry values ('Asia','China');
insert into tblCountry values ('Asia','Bhutan');
insert into tblCountry values ('Asia','Nepal');
insert into tblCountry values ('Asia','Pakistan');
insert into tblCountry values ('Africa','South Africa');

select * from tblCountry

ASP.NET Bind Gridview from Database – Database

Step 2. Designing the page

Now create a ASP.NET webpage in your project with name frmCountries.aspx. Edit the frmCountries.aspx page as below :

frmCountries.aspx :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="frmCountries.aspx.cs" Inherits="frmCountries" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title></title>
 <style>
 body
 {
 font-family:Arial;
 }
 td,th
 {
 padding:5px;
 text-align:center;
 }
 th
 {
 background-color:#b8fefb;
 }

 </style>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:GridView runat="server" ID="datagrid" PageSize="10" AutoGenerateColumns="false">
 
 <Columns>
 <asp:BoundField DataField="Id" HeaderText="Country Id" ItemStyle-BackColor="#95ffa8"/>
 <asp:BoundField DataField="Continent" HeaderText="Continent name" ItemStyle-BackColor="#ffcb6a"/>
 <asp:BoundField DataField="Country" HeaderText="Country name" ItemStyle-BackColor="#ffffb7" />
 </Columns>
 </asp:GridView>
 </div>
 </form>
</body>
</html>

This a simple page design, containing only one grid view to view data from database.

Step 3. Coding the webpage

To connect the asp.net webpage with your database, you will first require a connection string. Using connection string you will be able to connect and fill the grid view.

Here is my connection string. Your connection string will be different as the server name of the MS SQL database might differ.

Data Source=vibes;Initial Catalog=SimpleAsp;User Id=hitesh;password=789;

Now, you have to give SqlConnection object this string object in order to connect :

SqlConnection con = new SqlConnection("Data Source=vibes;Initial Catalog=SimpleAsp;User Id=hitesh;password=789;"); 
con.Open();

After this, make a SqlCommand object and give following command string to it.

SqlCommand cmd = new SqlCommand("Select * from tblCountry", con);

Fill the ASP.NET Grid view from database :

To fill ASP.NET grid view from database, you will require a sql data adapter, .net Data set and bind it to our grid view.

SqlCommand cmd = new SqlCommand("Select * from tblCountry", con);
 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 adapter.Fill(ds, "tblCountry");
 datagrid.DataSource = ds.Tables[0];
 datagrid.DataBind();

This is the final code of frmCountries.aspx.cs.

frmCountries.aspx.cs:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class frmCountries : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 BindGridview();
 }
 protected void BindGridview()
 {
 SqlConnection con = new SqlConnection("Data Source=vibes;Initial Catalog=SimpleAsp;User Id=hitesh;password=789;");
 con.Open();
 SqlCommand cmd = new SqlCommand("select * from tblCountry", con);
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 da.Fill(ds);
 con.Close();
 datagrid.DataSource = ds;
 datagrid.DataBind();

 }
}

Step 4. Test the page.

Now run the page in browser.

ASP.NET Bind Gridview from Database – Browser

ASP.NET Bind Gridview from Database – MS SQL Database