Site icon ParallelCodes

ASP.NET Gridview from Database – MS SQL server Database

ASH

To fill asp.net grid view from database, follow this simple steps. This example is to fill gridview from MS SQL server database.

ASP.NET Gridview from Database

Step 1 . Database Script :

  1. Open your MS SQL database server management studio and create a database with name SimpleAsp.
  2. 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

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>
</head>
<body>
 <form id="form1" runat="server">
 <div>
 <asp:GridView runat="server" ID="datagrid" CellPadding="4" EnableModelValidation="True" ForeColor="#333333" GridLines="None" >
 <AlternatingRowStyle BackColor="White" />
 <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
 <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
 <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
 <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
 </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\\sqlexpress;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\\sqlexpress;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;
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 frmCountries : System.Web.UI.Page
{
 protected void Page_Load(object sender, EventArgs e)
 {
 FillGrid();
 }

public void FillGrid()
 {
 SqlConnection con = new SqlConnection("Data Source=vibes\\sqlexpress;Initial Catalog=simpleAsp;User ID=hitesh;Password=789;");
 con.Open();
 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();
 adapter.Dispose();
 ds = null;
 cmd.Dispose();
 con.Close();


 }
}

Step 4. Test the page.

Now run the page in browser.

ASP.NET Gridview from Database – Browser

 


Exit mobile version