Monday, September 21, 2009

ASP.NET 3.5 - How to populate grid view dynamically using dataset

I have a req. where I want to display the list of students. The records to be displayed are based on the filtered criteria; also the columns to be displayed in the grid are decided at run time.

You have a grid view defined in your page as below:

/* grid definition - spaces are added to render html tags as text
between tags */
<div style="margin-top:10px;" >
<asp:Label ID="lbl_StudentsList" runat="server" Text="Student's List:" >

<asp:GridView ID="gv_StudentsList" runat="server" >
< emptydatarowstyle backcolor="LightBlue" forecolor="Red"/>

<asp:Label runat="server">No student found. <


You have to call the method in an event on which you want to populate the records in grid view. I will call this method (Method definition is given below) on load event of the page.
/* Call method on particular event */
protected void Page_Load(object sender, EventArgs e)

public void PopulateStudentsList()
/* This defines data source for the grid as dataset returned from the
method and then bind it to grid to see the records in the grid in action */
gv_StudentsList.DataSource = getStudentList4Division("ISTYEAR");

Now, we will write a method which will populate grid view with data and the columns to be added - defined dynamically inside the method.
/* This method constructs data set using data table */
public DataSet getStudentList4Division(string pm_strDivision)
DataSet dsListStudent = new DataSet();
DataTable dt = new DataTable();

/* This example works but is not the standard way to populate data from
DB – this is just for demonstration purpose
You need a database named
Quickstart in your backend with Student table having schema as columns specified
as per req. */

String connString = "Data Source=.;Initial
Catalog=QuickStart;Integrated security=true";

SqlConnection sqlConnect =
new SqlConnection(connString);
SqlCommand cmd = new
SqlCommand("select * from Student WHERE division ='" + pm_strDivision + "'",
SqlDataReader sdr = cmd.ExecuteReader();

//Prepare data table columns
string strfirstname =
string strlastname = "";
DateTime strdob;
string strcity = "";
string strreligion = "";

/* These are the column headings */
dt.Columns.Add("First name", typeof(string));
name", typeof(string));
dt.Columns.Add("Date of birth", typeof(DateTime));
dt.Columns.Add("City", typeof(string));

while (sdr.Read())
strfirstname =
strlastname = sdr["LastName"].ToString();
strdob = Convert.ToDateTime(sdr["DateOfBirth"]);
strcity =
strreligion = sdr["Religion"].ToString();

This line populates value in the data table */
strlastname, strdob, strcity, strreligion);


//Add Table
to dataset
dt.TableName = "Students list";


return dsListStudent;
The result of the data source in the grid view will look as below:

In next post, we will see how to populate grid based on the value selected in the dropdown list using the above scenario.

*Please note that the code explored above is working but has not been validated for different unit tests like positive test or negative test.

*Schema for the table student is as below:
CREATE TABLE [dbo].[Student](
[ID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateOfBirth] [datetime] NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PanNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Religion] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cast] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Division] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

1 comment: