This article presents a simple data display with DataGrid, shows how to add paging to the DataGrid, and how to make the DataGrid editable. Learn how to reduce the data by providing filters so users do not get more data than they need. Also covered is how to edit the DataGrid by adding DataBound columns or using template columns.
Feel free to download the support files for this article here.
When publishing a list of records in a web page, we need to do a lot of work in the Classic ASP. But with ASP.NET a developer need not worry much except for setting some properties and adding a little code. In classic ASP we have no option of grids, but we have to write code for creating Table and creating Page Numbers, etc. Working with DataGrid is quite simple when compared to other listing web controls. We can place a grid (Drag and Drop) and bind to a DataSource object to simply presents all records. The DataGrid in Web controls accepts wide range of DataSource objects. As long as the object implements System.Collections.IEnumerabale interface, it can be assigned as a DataSource for the DataGrid. The objects, for example, DataView, ArrayList, HashTable can be used as DataSource in web pages.
We can use XML or a database as a source for the data in the above said objects. For keeping common tasks in the view, I am going to present Database related code in this article. And one more point in the coding is, I am presenting the code which I wrote in the plain text editor so that users who use editors like UltraEdit and TextPad or NotePad may feel easy about understanding the coding. Those who use Visual Studio may not find much difficulty to use “code behind”.
I would like to present a simple data display with DataGrid in the beginning, and later on show how to add paging to the DataGrid, and finally, how to make our DataGrid editable. This may look complicated, but it's nothing but setting properties of the DataGrid most of the time and adding a little bit of code.
Public Sub Page_Load() If (not isPostBack) then LoadData() end if End Sub
Private Sub LoadData() Dim SqlCon as New SqlConnection("Data Source=LocalHost;User Id=Sa;Password=Password;Initial Catalog=NorthWind") Dim SqlAdp as New SqlDataAdapter("Select * from Customers",SqlCon) Dim MyDataSet as new DataSet() try SqlCon.Open() SqlAdp.Fill(MyDataSet,"Customers") Status.Text = "Presenting Data..." MyGrid.DataSource= New DataView(MyDataSet.Tables("Customers")) MyGrid.DataBind () catch Ex as Exception Status.Text = "Unable to Present Data " & Ex.Message Finally If SqlCon.State=ConnectionState.Open then SqlCon.Close end if end Try End Sub </Script> </Form></Body></Html>
In the above code, we are adding one DataGrid and Label controls to a web page. In Pageload we are checking whether the page is loading for the first time or not. If it is first time, we are adding data to the Grid by calling LoadData method.
In the LoadData method, we are opening a connection to the database (In my example SqlServer on Local Machine) and populating the DataSet with DataAdapter. After populating data into dataset, we are creating a DataView which can be assigned to DataGrid as a DataSource. (Unlike Windows forms, we cannot assign DataTable to DataGrid in WebForms).
In this code no cosmetics were added to the page; it's just plain. We are not worried about how the page looks but how it works. Here DataGrid is displaying all the records available in the database. Sometimes data may be too big which takes time to fetch from the database, and sometimes a user may not be interested to work with all the data. And, when presented all the records, the "ViewState" of the page is also too big. Obviously this increases network traffic for unnecessary records. In such scenarios, we can reduce the data in two ways:
The first one is providing filters so we can fetch the data the user is interested in.
The second one is the user can be provided with data in pages so that the page they are interested in can be populated with "desired number of records".
To make our above DataGrid display paging, we have to adjust simple properties -- simply add attributes [ AllowPaging="true" onPageIndexChanged="NavigatePage" ] at Declaration so that after these properties, your DataGrid declaration looks like:
Here NavigatePage is the Method which responds when a user clicks on the page number. I will explain it in a moment. Before that in page load, we have to adjust DataGrid properties like this:
Public Sub Page_Load() If (not isPostBack) then MyGrid.PageSize=10 MyGrid.PagerStyle.Mode=PagerMode.NumericPages MyGrid.CurrentPageIndex=0 LoadData() end if End Sub
Assign the page size (I:e How many rows per page), page style and page index.
Page indexing will be in numerical mode for most of the applications, but the time comes when page indexing also exceeds more than one row; it won't look nice in that case. To overcome this we have "PagerStyle" Property.
Here page mode implies whether you want numerical page index or just "Next" "Previous" style. PagerStyle.Mode=PagerMode.NumericPages gives the numerical index at the bottom of the Grid.
PagerStyle.Mode= PagerMode.NextPrev provides "Next" and "Previous" hyperlinks at the bottom of the Grid. By default, the indexing will be aligned to left; to make right aligned, just add the attribute PagerStyle-HorizontalAlign=”Right” at Declaration so that the page indexing will be right aligned.
In case of PrevNext mode we can change the text also with properties like:
Now we have to define our function "NavigatePage".
This function takes two parameters -- first is the sender as usual, and the second is “DataGridPageChangedEventArgs”, just sounds like the event raised for Grid change. The event handler goes like this:
Public Sub NavigatePage(Sender as Object , Args as DataGridPageChangedEventArgs) Status.Text=Args.NewPageIndex MyGrid.CurrentPageIndex= Args.NewPageIndex LoadData() End sub
Don’t forgot to reload the data to reflect our selection.
Public Sub Page_Load() If (not isPostBack) then MyGrid.PageSize=10 MyGrid.PagerStyle.Mode=PagerMode.NumericPages ' Another Option is PagerMode.NextPrev MyGrid.CurrentPageIndex=0 LoadData() end if End Sub
Private Sub LoadData() Dim SqlCon as New SqlConnection("Data Source=LocalHost;User Id=Sa;Password=Password;Initial Catalog=NorthWind") Dim SqlAdp as New SqlDataAdapter("Select * from Customers",SqlCon) Dim MyDataSet as new DataSet() try SqlCon.Open() SqlAdp.Fill(MyDataSet,"Customers") MyGrid.DataSource= New DataView(MyDataSet.Tables("Customers")) MyGrid.DataBind () catch Ex as Exception Status.Text = "Unable to Present Data " & Ex.Message Finally If SqlCon.State=ConnectionState.Open then SqlCon.Close end if end Try End Sub Public Sub NavigatePage(Sender as Object , Args as DataGridPageChangedEventArgs) Status.Text=Args.NewPageIndex MyGrid.CurrentPageIndex= Args.NewPageIndex LoadData() end sub </Script> </Form></Body></Html>
As long as our application is related to a simple listing of records, the above code is sufficient (except cosmetics). In an intranet application, sometimes a user will be given a choice to update the data. DataGrid can be edited by adding DataBound columns or using template columns. Since DataBound columns works well for most of the scenarios, we will concentrate on this area. The DataGrid provides most automated support for editing data in the Grid. We need to just assign some more properties of the DataGrid.
At Grid Declaration, specify the events to be invoked when a particular action is performed by the user. Change the Grid declaration like this:
Pay attention to set AutogenerateColumns=”False”. Since we are going to change the default behavior of the DataGrid, we must assign the column values with our code. Here we specify the Identifier column to Unique Column I:e CustomerId.
All the editing methods have the same arguments -- they are Sender and DataGridCommandEventArgs. The latter one provides information about the column being edited or updated and in case of updation values of the proposed column etc.
First we look at the editing. The Event Name we specified at Grid Declaration with "OnEditCommand" property.
Private Sub MyEditAction(Sender as Object, Args as DataGridCommandEventArgs) Status.Text="Editing .." MyGrid.EditItemIndex= Args.Item.ItemIndex LoadData() End Sub
First we display the status of editing, and then set the grid into edit mode by setting its EditItemIndex. At the end we must reload the data. Once grid is changed to Edit Mode Framework takes care of adding text boxes for the columns. Here note that we marked the CustomerId BoundColumn as "readOnly" to restrict the user from editing primary key data. And all other columns just used bound columns.
Other than TextBox, or when a user wants ClientSide validations or a different look and feel, user must go for the ItemTemplates where a user can specify which one style to use at edit. With Asp:BoundColumns and ItemTemplates, we can specify different header text instead of the one we get from the database.
This will change the default field name into “Customer Code”. Of course if we dig more, we can assign accessing keys also.
In addition to the columns we can specify to the Grid. For example to change the editing row style in the Grid, add properties like “EditItemStyle-BackColor=”Yellow” EditItemStyle-ForeColor=”black” at Declaration.
Then we move to the cancel command.
Private Sub MyCancelAction(Sender as Object, Args as DataGridCommandEventArgs) MyGrid.EditItemIndex=-1 MyGrid.CurrentPageIndex=0 LoadData() End Sub
Here we are setting the edit item index to –1 to cancel our edit mode, and setting page index is up to the user and must reload the data to fetch the original values from database.
And finally we move to the Update command:
Private Sub MyUpdateAction(Sender as Object, Args as DataGridCommandEventArgs) Status.Text="Updated" Dim strID, strCompany,strContact,strTitle,strAddress,strCity as String strID= MyGrid.DataKeys(Args.Item.ItemIndex) strCompany= Ctype(Args.Item.Cells(1).Controls(0),TextBox).Text strContact= CType(Args.Item.Cells(2).Controls(0),TextBox).Text strTitle= CType(Args.Item.Cells(3).Controls(0),TextBox).Text strAddress = CType(Args.Item.Cells(4).Controls(0),TextBox).Text strCity = Ctype(Args.Item.Cells(5).Controls(0),TextBox).Text
Dim StrSql as String StrSql="Update Customers Set CompanyName='" & strCompany &"',ContactName='" & strContact &"',ContactTitle='" & strTitle &"',Address='" & strAddress &"',City='" & strCity &"' where CustomerID='" & strId &"'" 'open Connection and through SqlCommand Update to the Database Status.Text= strSql 'Finally disable edit mode MyGrid.EditItemIndex= -1 LoadData() End Sub
Here we extract the values from the controls. In case of Item Template, we can specify the Id for text box used in the edit mode. In case of bound column, we must rely on the column position in the Grid Control. (Remember column index starts with ‘0’.) And since the Grid controls list returns control type, we must type cast into text box to get the value from the edit control. After extracting all the values from respected text fields, we must get the unique column value with the help of DataKey (which we specified at design time with DataKey attribute). Preparing SqlStatement and Executing is up to the user. If DataSet is the source, we must flush all the updates with SqlAdapter at the end.
ASP.NET Export to Excel and Word using VB.NET and C#
