Introduction
One very common example of using the DataGrid control to display data demonstrates the use of “paging”. When there are a large number of rows to display, sending them all to the client in one go doesn’t make sense. Your client will get impatient waiting for them all to arrive, and may find that they actually waited to see something else instead. To prevent this aggravation and waste of bandwidth, we actually divide the output into pages congaing 10–20 rows per page.
DataGrid web control makes it easy to provide a paging feature. It contains logic that can automatically create pages containing the number of rows you require, and it can render the navigation control in a range of ways. You can also take over paging entirely and implement all the features yourself to provide a custom interface.
Description
To allow paging, we simply set ALLOWPAGING property of the DataGrid control to true. And we specify the name of an event handler that will run when the PAGEINDEXCHANGED event occurs.
<asp:DataGrid id="dgNonApproveList" runat="server"
OnPageIndexChanged="MyDataGrid_Page" Width="752px"
AllowPaging="True" AllowSorting="True" ForeColor="#000000"
Font-Names="Arial" Font-Size="Smaller" AutoGenerateColumns="false">
Our example that uses the automatic paging feature is using a DataGrid control with an EDITITEMTEMPLATE.
Let's start describing the example, a viewer to run the SQL script in SQL analyzer:
In Web.Config file, you have to define your own connection string:
<appSettings>
<add key="strConn" value="workstation id='VENIRE-102-011';
packet size=4096;integrated security=SSPI;
data source='VENIRE-102-011\MAYANK';
persist security info=True;initial catalog=vkmf">
</add>
</appSettings>CREATE TABLE [DocTemplate] (
[DocTId] [int] NOT NULL ,
[DocTitle] [varchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL CONSTRAINT
[DF_DocTemplate_PostDate] DEFAULT (getdate()),
[FuncId] [int] NULL ,
[TemplateAttachment] [varchar] (50)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MetaData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Image] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL CONSTRAINT [DF_DocTemplate_Image]
DEFAULT ('UnrecoFile.jpg')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Insert Into DocTemplate values(1, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(2, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(3, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(4, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(5, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(6, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(7, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
Insert Into DocTemplate values(8, Microsoft Stake Holder Request’,
’1/1/1900 0:00’,1,’DocTemplate\VKMFStakeholder Requests-1.doc’,
’<Long Text>’,’UnrecoFile.jpg’)
CREATE TABLE [UserDocDefault] (
[UserId] [int] NULL ,
[DocTId] [int] NULL ,
[DateTo] [datetime] NULL CONSTRAINT
[DF_UserDocDefault_DateTo] DEFAULT (getdate()),
[DateTill] [datetime] NULL CONSTRAINT
[DF_UserDocDefault_DateTill]
DEFAULT (getdate()),
[Activate] [tinyint] NULL CONSTRAINT
[DF_UserDocDefault_Activate] DEFAULT (0)
) ON [PRIMARY]
GO
Insert Into UserDocDefault values(11003,1, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,2, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,3, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,4, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,5, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,6, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,7, ‘5/22/2004’,’ 5/26/2004’, 0)
Insert Into UserDocDefault values(11003,8, ‘5/22/2004’,’ 5/26/2004’, 0)
How actually the work is done:
<HTML><HEAD>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<FORM Runat="server">
<TABLE id="Table2" >
<TR>
<TD>
<asp:DataGrid id="dgNonApproveList" runat="server"
OnPageIndexChanged="MyDataGrid_Page"
OnCancelCommand="dgNonApproveList_CancelCommand"
OnUpdateCommand="dgNonApproveList_UpdateCommand"
OnEditCommand="dgNonApproveList_EditCommand"
DataKeyField="DocTId" Width="752px" AllowPaging="True"
AllowSorting="True" ForeColor="#000000"
Font-Names="Arial" Font-Size="Smaller"
AutoGenerateColumns="false">
<HeaderStyle Font-Bold="True"></HeaderStyle>
<Columns>
<asp:TemplateColumn>
<HeaderTemplate>
<input type="CheckBox" name="SelectAllCheckBox"
onclick="SelectAll(this)">
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox id="SelectCheckBox" Runat="Server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="UserId"
HeaderText="User Id"></asp:BoundColumn>
<asp:BoundColumn DataField="DocTitle"
HeaderText="DocTitle"></asp:BoundColumn>
<asp:TemplateColumn>
<HeaderTemplate>
Date From
</HeaderTemplate>
<ItemTemplate>
<%# DataBinder.Eval(Container, "DataItem.DateTo")%>
</ItemTemplate>
<EditItemTemplate>
<asp:Calendar ID=calDateFrom
SelectedDate='<%# DataBinder.Eval(Container, "DataItem.DateTo")%>'
Runat="server" Width="186px" ForeColor="Black"
Font-Size="8pt" Height="134px" BackColor="White"
DayNameFormat="FirstLetter" Font-Names="Verdana"
BorderColor="#999999" CellPadding="4">
<TodayDayStyle
ForeColor="Black" BackColor="#CCCCCC"></TodayDayStyle>
<SelectorStyle BackColor="#CCCCCC"> </SelectorStyle>
<NextPrevStyle VerticalAlign="Bottom"></NextPrevStyle>
<DayHeaderStyle Font-Size="7pt" Font-Bold="True"
BackColor="#CCCCCC"></DayHeaderStyle>
<SelectedDayStyle Font-Bold="True" ForeColor="White"
BackColor="#666666"> </SelectedDayStyle>
<TitleStyle Font-Bold="True"
BorderColor="Black" BackColor="#999999"></TitleStyle>
<WeekendDayStyle BackColor="#FFFFCC"> </WeekendDayStyle>
<OtherMonthDayStyle ForeColor="#808080"> </OtherMonthDayStyle>
</asp:Calendar>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<HeaderTemplate>Date Till</HeaderTemplate>
<ItemTemplate>
<%# DataBinder.Eval(Container, "DataItem.DateTill")%>
</ItemTemplate>
<EditItemTemplate>
<asp:Calendar ID="calDateTo"
SelectedDate='<%# DataBinder.Eval(Container, "DataItem.DateTill")%>'
Runat="server" Width="186px" ForeColor="Black"
Font-Size="8pt" Height="134px" BackColor="White"
DayNameFormat="FirstLetter" Font-Names="Verdana"
BorderColor="#999999" CellPadding="4">
<TodayDayStyle ForeColor="Black"
BackColor="#CCCCCC"></TodayDayStyle>
<SelectorStyle BackColor="#CCCCCC"> </SelectorStyle>
<NextPrevStyle VerticalAlign="Bottom"></NextPrevStyle>
<DayHeaderStyle Font-Size="7pt" Font-Bold="True"
BackColor="#CCCCCC"></DayHeaderStyle>
<SelectedDayStyle Font-Bold="True" ForeColor="White"
BackColor="#666666"> </SelectedDayStyle>
<TitleStyle Font-Bold="True" BorderColor="Black"
BackColor="#999999"> </TitleStyle>
<WeekendDayStyle BackColor="#FFFFCC"></WeekendDayStyle>
<OtherMonthDayStyle ForeColor="#808080"> </OtherMonthDayStyle>
</asp:Calendar>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Update!" CancelText="Cancel!" EditText="Edit!">
</asp:EditCommandColumn>
</Columns>
</asp:datagrid>
</TD>
</TR>
<TR>
<TD align="left">
<asp:button id="btnApprove" onclick="btnApprove_Click"
runat="server" Text="Delete"></asp:button>
<asp:label ID="MessageLabel" ForeColor="Red"
EnableViewState="False" Runat="Server"
Font-Bold="True"></asp:label>
</TD></TR>
</TABLE>
<TABLE id="Table1"
style="Z-INDEX: 102; LEFT: 8px; WIDTH: 760px;
POSITION: absolute; TOP: 8px; HEIGHT: 77px"
cellSpacing="1" cellPadding="1" width="760" border="0">
<TR>
<TD style="WIDTH: 114px">Record Per Page</TD>
<TD style="WIDTH: 186px">
<asp:textbox id="txtPageLength" runat="server"
Width="64px"></asp:textbox></TD>
</TR>
<TR>
<TD style="WIDTH: 114px">Paging Style</TD>
<TD style="WIDTH: 186px">&
lt;asp:radiobutton id="optNum"
runat="server" Text="1 2 3 4 ...." GroupName="opt" Checked="True">
</asp:radiobutton>
<asp:radiobutton id="button" runat="server"
Text="Pre Next" GroupName="opt"></asp:radiobutton></TD>
<TD><asp:button id="btnDisplay" onclick="btnDisplay_Click"
runat="server" Text="Display Record"></asp:button></TD>
</TR>
<TR>
<TD style="WIDTH: 114px">Sort By</TD>
<TD style="WIDTH: 186px">
<asp:dropdownlist id="ddwnSortBy" runat="server">
<asp:ListItem Value="DocTemplate.DocTitle">Document Title</asp:ListItem>
<asp:ListItem Value="UserDocDefault.DateTo">Date From</asp:ListItem>
<asp:ListItem Value="UserDocDefault.DateTill">Date Till</asp:ListItem>
</asp:dropdownlist></TD>
</TR>
</TABLE>
</FORM>
</body>
</HTML>
The Page_Load Event Handler:
When the page loads each time, either when the user opens the page for the first time or in response to a click by the user on the paging controls, our Page_Load event handler is executed. But here, I am not using any business process or initializing DataGrid and its properties.
Subroutine BindGrid():
void BindGrid()
{
try
{
dgNonApproveList.DataSource=GetRecord(recordstring);
dgNonApproveList.DataBind();
}
catch(Exception e1)
{
Response.Write("Error! "+ e1.Message +
" Please contact your software development team.");
}
}
Here, we simply bind the DataGrid to its data source, dgNonApproveList.DataSource=GetRecord (recordstring):
private ICollection GetRecord(string recordstring)
{
string strConn =
System.Configuration.ConfigurationSettings.AppSettings["strConn"];
SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlCommUserList = new SqlCommand(recordstring,sqlConn);
SqlDataAdapter da = new SqlDataAdapter(sqlCommUserList);
DataSet ds = new DataSet();
da.Fill(ds,"table");
return ds.Tables["table"].DefaultView;
}
Here, in GetRecord, we are passing the querystring, and this function will return DataView as ICollection.
We have selected the text-style paging option, and entered different text for the pager lines. We have also changed the numbers of rows that should appear in each page, and you can see the results.
public void MyDataGrid_Page(Object sender, DataGridPageChangedEventArgs e)
{
dgNonApproveList.CurrentPageIndex = e.NewPageIndex;
BindGrid();
}
As you can see, all we have to do is collect the index number of the page that the user selected from the NewPageIndex property of the page's DataGridPageChangedEventArgs object that is passed to our event handler when the event occurs. We have assigned this value to the CurrentPageIndex property of the DataGrid, and then call our BindGrid routine to fetch, bind, and display the appropriate page of data rows.
Editing & Deleting Data with Data-Bound controls
How it works
To perform the above picture task, you have to use one of the DataGrid properties, i.e., DataKeyField in the source DataSet, DocTId. This useful feature of the list-controls means that we don’t have to include the primary key of the source DataSet in our columns – even if need to access it to perform data updates. For any row, we can access the primary key from this collection, as you will see later in this example.
The next three attributes in the opening DataGrid tag are used to specify the names of the event handlers that will be executed in response to the user clicking the edit, update, and cancel links that the EditCommandColoumn will generate for us. Finally, we set the AutoGenerateColoumns property to false as we want to create our own column structure for the grid.
<asp:DataGrid id="dgNonApproveList" runat="server"
OnPageIndexChanged="MyDataGrid_Page"
OnCancelCommand="dgNonApproveList_CancelCommand"
OnUpdateCommand="dgNonApproveList_UpdateCommand"
OnEditCommand="dgNonApproveList_EditCommand"
DataKeyField="DocTId"
AllowSorting="True"
AutoGenerateColumns="false">
Because we have turned off automatic generation of the columns, we must specify the columns that we want to appear on the DataGrid. We include a BoundColoumn that displays values from the DocTId column in our data source, a custom TemplateColoumn that displays the title, followed by another BoundColoumn that displays the CheckBox for selecting individual rows. Also, we have inside the TemplateColoumn, <HeaderTemplate> in which we have to define a HTML CheckBox control for group selection. OnClick event of the HTML CheckBox calls the JavaScript function “SelectAll(CheckBoxControl)”. That function will allow you select all rows in a displayed DataGrid or deselect all rows in a displayed DataGrid.
Firstly, the automatic editing feature displays two calendar controls instead of a simple text value in all the columns that are not read only. We just specify an <ItemTemplate> to be used to display the column values in normal mode, and an <EditItemTemplate> that defines the control to be used in edit mode:
<asp:EditCommandColumn ButtonType="LinkButton"
UpdateText="Update!" CancelText="Cancel!" EditText="Edit!">
</asp:EditCommandColumn>
As you can see from the code, the last column is the EditCommandColoumn we mentioned earlier. While there are plenty of attributes that we can apply to this column to control the formatting, we have just specified the text we want to use for the three commands that can appear in this column. And that completes the definition of our DataGrid control.
Handling Item Edit Events
The EditCommand event is raised when the user clicks the Edit link in any row within the grid. For this event, we specified our dgNonApproveList_EditCommand event handler routine. Then we set the EditItemIndex property of the DataGrid control to the index of the row that contained the edit link the user clicked:
void dgNonApproveList_EditCommand(Object s, DataGridCommandEventArgs e)
{
dgNonApproveList.EditItemIndex=e.Item.ItemIndex;
BindGrid();
}
Handling the Update and Cancel Events
Now that we have got the grid into “edit mode”, we just need to handle the update and cancel events. We specified that a click on the Cancel link should execute our event handler named dgNonApproveList_CancleCommand. In this event handler, all we need to do is switch the grid back out of “edit mode” by editing the EditItemIndex property back to –1.
void dgNonApproveList_CancelCommand(Object s, DataGridCommandEventArgs e)
{
dgNonApproveList.EditItemIndex=-1;
BindGrid();
}
However, if the user clicks the Update link, our dgNonApproveList_UpdateCommand will be called. Here, we have to create a suitable SQL UPDATE statement in our example. For this, we need to get the edited values from the DataGrid row that the user worked on. Because we are updating dates, we are using the Calendar control. So there is no need to go for validation test. In the case of TextBox, we need to have validation. After declaring two variables to hold references to the Calendar that contains the edited values, we first access the calDateFrom calendar using the FindControl method of the item that is contained in DataGridCommandEventArgs object passed to our event handler as a parameter. Notice that we have to convert the return value to the correct type: Calendar. It goes similar for the second control, that is, calToDate.
Now, when we have got the reference to the two Calendar controls, we can create a SQL UPDATE statement and call our Command object and its ExecuteNonQuery method.
void dgNonApproveList_UpdateCommand(Object s, DataGridCommandEventArgs e)
{
string DocTId = dgNonApproveList.DataKeys[e.Item.ItemIndex].ToString();
string strConn =
System.Configuration.ConfigurationSettings.AppSettings["strConn"];
Calendar calFrom;
Calendar calTo;
calFrom = (Calendar)e.Item.FindControl("calDateFrom");
calTo = (Calendar)e.Item.FindControl("calDateTo");
DateTime dtFrom = calFrom.SelectedDate.Date;
DateTime dtTo = calTo.SelectedDate.Date;
if (dtFrom < dtTo)
{
SqlConnection sqlConn = new SqlConnection(strConn);
sqlConn.Open();
SqlCommand objCommand = new SqlCommand();
objCommand.Connection=sqlConn;
SqlTransaction myTrans = sqlConn.BeginTransaction();
objCommand.Transaction=myTrans;
try
{
objCommand.CommandText =
"Update UserDocDefault Set DateTo='"+
dtFrom +"', DateTill='"+ dtTo +
"' Where DocTId = "+ DocTId +"";
objCommand.ExecuteNonQuery();
myTrans.Commit();
}
catch (SqlException SqlEx)
{
MessageLabel.Text = SqlEx.Message;
myTrans.Rollback();
}
catch (Exception Ex)
{
MessageLabel.Text = Ex.Message;
}
finally
{
sqlConn.Close();
}
dgNonApproveList.EditItemIndex=-1;
BindGrid();
}
else
MessageLabel.Text = "Date From must be lower than Date Till";
}
We finish off by setting the grid back out of “edit mode”, by setting the EditItemIndex property of the DataGrid control back to -1, and rebinding the control to display the result.
Handling multiple row selection for deletion by checkbox control
How to add CheckBox with DataGrid using TemplateColoumn
<asp:TemplateColumn>
<HeaderTemplate>
<input type="CheckBox" name="SelectAllCheckBox" onclick="SelectAll(this)">
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox id="SelectCheckBox" Runat="Server" />
</ItemTemplate>
</asp:TemplateColumn>
<HeaderTemplate> will show a CheckBox named as SelectAllCheckBox on the DataGrid header. And a JavaScript code will execute the OnClick event, i.e., SelectAll(this).
function SelectAll(CheckBoxControl)
{
if (CheckBoxControl.checked == true)
{
var i;
for (i=0; i < document.forms[0].elements.length; i++)
{
if ((document.forms[0].elements[i].type == 'checkbox') &&
(document.forms[0].elements[i].name.indexOf('dgNonApproveList') > -1))
{
document.forms[0].elements[i].checked = true;
}
}
}
else
{
var i;
for (i=0; i < document.forms[0].elements.length; i++)
{
if ((document.forms[0].elements[i].type == 'checkbox') &&
(document.forms[0].elements[i].name.indexOf('dgNonApproveList') > -1))
{
document.forms[0].elements[i].checked = false;
}
}
}
}
That function selects either all the CheckBox controls or deselects all of them.
Handling the btnApprove_Click Events (for deleting a particular row as well as for multiple row deletions)
string GetSelectedUsers()
{
int i;
for (i=0; i < dgNonApproveList.Items.Count; i++)
{
if (((CheckBox)
dgNonApproveList.Items[i].FindControl("SelectCheckBox")).Checked == true)
{
if (SelUsersId != "")
{
SelUsersId += ",";
}
SelUsersId += dgNonApproveList.DataKeys[i];
}
}
return SelUsersId;
}
void btnApprove_Click(object sender, System.EventArgs e)
{
string SelUsersId = GetSelectedUsers();
if (SelUsersId!=String.Empty)
{
string strConn =
System.Configuration.ConfigurationSettings.AppSettings["strConn"];
SqlConnection sqlConn = new SqlConnection(strConn);
sqlConn.Open();
SqlCommand objCommand = new SqlCommand();
objCommand.Connection=sqlConn;
SqlTransaction myTrans = sqlConn.BeginTransaction();
objCommand.Transaction=myTrans;
try
{
objCommand.CommandText="Delete From DocTemplate" +
" Where DocTId in ("+ SelUsersId +")";
objCommand.ExecuteNonQuery();
objCommand.CommandText="Delete From UserDocDefault" +
" Where DocTId in ("+ SelUsersId +")";
objCommand.ExecuteNonQuery();
myTrans.Commit();
}
catch (SqlException SqlEx)
{
MessageLabel.Text = SqlEx.Message;
myTrans.Rollback();
}
catch (Exception Ex)
{
MessageLabel.Text = Ex.Message;
}
finally
{
sqlConn.Close();
}
BindGrid();
}
else
MessageLabel.Text = "Pls Select at least one User Id.";
}
The above code you have seen will work for deleting multiple rows from the DataGrid Control.
Finishing Up
Handling btnDisplay_Click Event
The above demo will start working on btnDisplay_Click event. In that event, we will define the DataGrid control's paging length and PageStyle Mode. Then, the subroutine BindGrid will be called. We have discussed that subroutine earlier.
<asp:button id="btnDisplay" onclick="btnDisplay_Click"
runat="server" Text="Display Record"></asp:button>void btnDisplay_Click(object sender, System.EventArgs e)
{
dgNonApproveList.PageSize=Int32.Parse(txtPageLength.Text);
if (optNum.Checked)
dgNonApproveList.PagerStyle.Mode = PagerMode.NumericPages;
else
dgNonApproveList.PagerStyle.Mode = PagerMode.NextPrev;
recordstring = recordstring + " ORDER BY " + ddwnSortBy.SelectedValue;
BindGrid();
}
The namespaces
The namespaces that are used for this article:
<%@ import Namespace=" System.Web.UI.WebControls.WebControl" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.Data" %>
References
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin