JAL Computing

General Concepts

A few general concepts are presented here and represent my best understanding of how the DataSet and DataGrid works.

The SqlDataAdapter provides a bridge between the DataSet and the data source. Calling Fill, refreshes the data in the DataSet. Calling Update, attempts to commit any changes in the DataSet. The SqlDataAdapter wizard simplifies building of data driven forms by automatically generating UPDATE, INSERT and DELETE statements from your SqlDataAdapter SELECT statement. By default, the UPDATE statement implements an Optimistic Concurrency scheme that looks for an unaltered row on UPDATE.

The DataSet can encapsulate a parent child hierarchy of zero or more data tables. The DataSet also stores its state, storing both the @original values and any new uncommitted values. These original values are used to enforce "Optimistic Concurrency" by searching for unaltered rows on UPDATE. Unlike the DataGrid, the DataSet does _not_ persist itself on post back.

The DataGrid is used to display data in the HTML page. If DataGrid paging is not enabled, the DataGrid will display all of the rows in the appropriate table in the corresponding DataSet. To enable sorting and filtering, the DataGrid is bound to a View, not to the DataSet.

The View is used to sort and filter data in the DataSet before it is passed to the DataGrid using the properties:

view.RowFilter
view.Sort

The data path looks like this:

DataBase --> DataSet --> View --> DataGrid

In this project, the DataSet and a View is filled on every page load with a call to:

sqlDataAdapter1.Fill(dataSet11);
view = dataSet11.Tables[0].DefaultView;

The DataGrid is only refreshed from the View/DataSet on the first page load (or in the appropriate event handler):

if (!IsPostBack)
{								
	...
	DataGrid1.DataBind();
}

If you do not call DataBind(), the DataGrid state is refreshed from the ViewState. This is accomplished by DataGrid's automatic support for persistence on post back (enableViewState= true).

Persisting the DataSet

An alternate solution to hitting the database on ever page load, is to persist the DataSet to ViewState or to the Session object, calling Update() only when the user is ready to commit all changes. As a result of .NET's built in support for Optimistic Concurrency, the update could fail if another user has edited or deleted a row of interest since the initial page load. If you implement this solution, you would need to add a "commit" button to save changes and a "refresh" button to allow the user to reload the DataSet.

Default Paging Loads The Entire DataSet

It is important to understand that much of the simplicity and power of default paging and sorting is greatly simplified by loading the entire DataSet into memory. However, this may not be a very efficient use of server memory. One solution is to limit the amount of data returned from the database by implementing "custom paging", returning only the current page of rows to the dataset. For a very large data set (Customers-Orders-OrderDetails) it may be more efficient to return a single row at a time using "custom paging" with a page size of one! In this scenario, the user navigates through a hierarchy of virtual tables of Customers and Orders, displaying only the OrderDetails in a DataGrid.

DataGrid State Is Persisted By Default

In a similar manner, the DataGrid comes with built in support for data persistence. By default, the EnableViewState property is set to true so that the data is written to a hidden field in the HTML output. This data is then sent back to the server on post back and is used to refresh the DataGrid . Loading the entire "Customer" table into a DataGrid is inefficient. One solution is to use paging to limit the amount of data that must be persisted in ViewState.

Invalid Page Index and Unintended Editing Bugs

Two rather obscure bugs can creep into your program when you enable paging. One is trying to page to an invalid page. On a single user system, this occurs when the user deletes the only row on the last page. When you call DataBind(), the page index is persisted and an attempt is made to page the data set, throwing an exception. However, a much more subtle bug occurs when _another_ user deletes one or more rows. For this reason, you need to check for an invalid page index on every call to DataBind after the initial page load. If you persist the DataSet to ViewState or Session state, you only need to check for an invalid index when the user refreshes the DataSet or deletes a row. Here is our ResetPageIndex function:

// ResetPageIndex resets invalid page index to last page
// ASSERT grid and view NOT NULL
protected void ResetPageIndex(DataGrid grid, DataView view) 
{
	// check for invalid page index
	if ((grid.CurrentPageIndex != 0) && (((grid.CurrentPageIndex)*grid.PageSize)>= view.Count)) 
	{
		// invalid so leave at last page
		if ((view.Count % grid.PageSize)== 0)
		{ // ends on page border
			grid.CurrentPageIndex= (view.Count/grid.PageSize)-1;
		}
		else // partial page
		{
			grid.CurrentPageIndex= (view.Count/grid.PageSize);
		}
	}
}

Another subtle bug is seen when the user is in the edit mode and pages to another page. If you don't reset the edit index, the user is left unexpectedly in edit mode on the new page.

Saving Variable State

Since the web is by nature stateless, changes to a instance variable are lost on post back. To implement "state", you must manually write the value of the variable to the ViewState object and then read the saved value of the variable from the ViewState on post back. Here is some sample code from the PageLoad function that demonstrates how to save the sort order and sort column values:

// This code executes the first time only 
if (!IsPostBack) 
{ 
  view.Sort = "au_id"+ " ASC"; 
  ViewState["LastSortOrder"]="ASC"; 
  ViewState["LastSortColumn"]= "au_id"; 
  ViewState["LastFilter"]= ""; 
  DataGrid1.DataBind(); 
} 
else // This code executes only on post back 
{ 
  string lastSortColumn= (string)ViewState["LastSortColumn"]; 
  string lastSortOrder= (string)ViewState["LastSortOrder"]; 
  string lastFilter= (string)ViewState["LastFilter"]; 
  view.Sort= lastSortColumn+ " "+ lastSortOrder; 
  view.RowFilter= lastFilter; 
}

Saving the DataSet and Concurrency Control

In this sample project the dataset is refreshed on every page load, effectively negating .NET's built in support for optimistic concurrency. The UPDATE method of the SqlAdapter uses the original values of the DataSet to implement optimistic concurrency by looking for the original unaltered record in the database. The DataSet stores these original values in memory, but you must persist the dataset between page loads or the original data will be lost. It is possible to persist the dataset using the ViewState or Session object on the first page load. This will persist the original values on post back. Persisting the DataSet into ViewState or into the Session object, allows .NET's optimistic concurrency control to work properly.

Using Code Behind

This project was created with the IDE project wizard. As such, it uses "code behind". The HTML layout (WebForm1.aspx) is separated from the application logic which resides in the WebForm1.aspx.cs file. If you look at the WebForm1.aspx file in the HTML view you can see where the code behind is declared:

Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="WebApplication3.WebForm1" 
trace="false"

The project includes the following files (not inclusive):

	WebForm1.aspx
	WebForm1.aspx.cs
	DataSet1.xsd

WebForm1.aspx

It is important to understand that the WebForm1.aspx file can be viewed in both Design and HTML views. You may need to edit or at least look at the IDE code in the HTML view to understand how events are wired. For example, here is the HTML code that is used to not only create the DataGrid, but also to wire the event handlers. Note that clicking on the "delete" link will call the DataGrid1_Delete function: OnDeleteCommand="DataGrid1_Delete"

<asp:datagrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 23px; POSITION: absolute; TOP: 221px" runat="server" DataKeyField="au_id" DataSource="<%# view %>" Height="270px" Width="679px" OnUpdateCommand="DataGrid1_Update" OnCancelCommand="DataGrid1_Cancel" OnEditCommand="DataGrid1_Edit" OnDeleteCommand="DataGrid1_Delete" BorderColor="Blue" OnItemCommand="Item_Click" AllowSorting="True" OnSortCommand="DataGrid1_Sort" AllowPaging="True" OnPageIndexChanged="DataGrid1_Page" BackColor="#C0FFFF">

WebForm1.aspx.cs

The file WebForm1.aspx.cs contains the C# code behind. To create a button event handler I simply double clicked on the "Add", "Clear" and "Filter" buttons in the design view. The wizard created the empty event handler functions and registered the functions with the buttons. Note that the wizard generated code is hidden by default. You can expand the hidden code and look at the wizard's source code to see how the button event handlers were registered:

this.buttonFilter.Click += new System.EventHandler(this.buttonFilter_Click);
this.buttonAdd.Click += new System.EventHandler(this.buttonAdd_Click); 
this.buttonClear.Click += new System.EventHandler(this.buttonClear_Click);

DataSet1.xsd

The XML schema can be viewed in the DataSet1.xsd file. Note that the schema can be viewed as a "DataSet" table or as raw XML. This view can also be used to view and create more complex master-detail hierarchies.

Hosted by www.Geocities.ws