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

Q:
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.

A:
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:Label>
</div>

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

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

</EmptyDataTemplate>
</asp:GridView>
</div>

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)
{
PopulateStudentsList();
}

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");
gv_StudentsList.DataBind();
}

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);
sqlConnect.Open();
SqlCommand cmd = new
SqlCommand("select * from Student WHERE division ='" + pm_strDivision + "'",
sqlConnect);
SqlDataReader sdr = cmd.ExecuteReader();

if
(sdr.HasRows)
{
//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));
dt.Columns.Add("Last
name", typeof(string));
dt.Columns.Add("Date of birth", typeof(DateTime));
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("Religion",
typeof(string));

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

/*
This line populates value in the data table */
dt.Rows.Add(strfirstname,
strlastname, strdob, strcity, strreligion);
}

}

//Add Table
to dataset
dt.TableName = "Students list";
dsListStudent.Tables.Add(dt);

sdr.Close();
sqlConnect.Close();

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
) ON [PRIMARY]