This article provides a demo of how to divide up data into pages. Mayank Gupta illustrates how to automatically create pages containing the number of rows you require and how to make a custom interface.
Contributed by Mayank Gupta Rating: / 57 June 16, 2004
One example of using the DataGrid control to display data is the use of “paging”. When you have a large number of rows to display, sending them all to the client in one group at once doesn’t make sense. Your client will get impatient waiting for them all to arrive and may find that they actually wanted to see something else instead. To prevent this aggravation and waste of bandwidth, we actually divide the output into pages containing 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 ALLOWPAGING property of the DataGrid control to true and specify the name of an event handler that will run when the PAGEINDEXCHANGED event occurs.
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. Here I am not using any business process or initializing DataGrid and their properties.
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 numbers of rows that should appear in each page, and you can see the results.
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 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.
To perform the above picture task, you have to use one of DataGrid property, i.e., DataKeyField in the source dataset as 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 we 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.
Because we have turned off automatic generation of the columns, we must specify the columns that we want to appear. We include a BoundColoumn that displays values from the DocTId column in our DataSource, a custom TemplateColoumn that displays the Title, followed by another BoundColoumn that displays the CheckBox for selecting individual row as well as we have inside the TemplateColoumn <HeaderTemplate> in which we have to define an HTML check box control for group selection. OnClick event of HTML checkbox, call the JavaScript function “SelectAll(CheckBoxControl)”. That function will allow you select all rows in a displayed DataGrid or deselect all rows in 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:
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.
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:
Now that we have the grid in “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 existing the EditItemIndex property back to –1.
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 working on. Because we are updating dates, we are using Calendar. So it should not 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 contain the edited values, we first access the calDateFrom calendar using the FindControl method of the item that is contained in DataGridCommandEventArgs object passes to our event handler as a parameter. Notice that we have to convert the return value to the correct type like Calendar. It goes similar for second control that is calToDate.
Now that we have the reference of two Calendar control, we can create an SQL UPDATE statement and call our Command Object and its ExecuteNonQuery.
We finish off by the grid back out of “edit mode” by setting the EditItemIndex property of the DataGrid control back to -1, and rebind the control to display the result.
<HeaderTemplate> will show a checkbox named as SelectAllCheckBox on DataGrid Header. And a JavaScript code will be execute 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 CheckBox Control or deselects all CheckBox Control.
Handling the btnApprove_Click Events (for delete particular row as well as multiple row deletion)
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 delete multiple rows from the DataGrid Control.
Above demo will start working on btnDisplay_Click Event. In that event we will define the DataGrid Control paging length and Page style mode, then subroutine will call, i.e., BindGrid. That subroutine we have discussed earlier.
/ 57 
ASP.NET Export to Excel and Word using VB.NET and C#
