he list of ASP.NET rich data-bound controls is a long one, but it's not all-encompassing. You won't find any Microsoft® .NET counterpart for the Data controlâ€"a scrollable and updateable binding context that data-bound controls could rely on to refresh their user interface. The original Data control allowed for simple movementsâ€"first, next, previous, and last. It worked on top of a Recordset object and exposed the UpdateRecord method to save changes according to the capabilities of the currently selected type of cursor. While not the perfect programming tool, the Data control was especially good at building simple user interfaces for tables used internally for administrative purposes.
Although the ASP.NET set of Web controls has nothing like it, the .NET Framework provides all the tools you may need to set up a Web-based data navigator control that is superior to the old Visual Basic® Data control.
.NET DataNavigator Control
It's not a coincidence that the Visual Basic Data control works with a limited number of local data sourcesâ€"the most powerful of which is Microsoft Access 2000â€"and that there's no similar control in the .NET Framework. To be really powerful and effective, a data control must be able to connect to any data sourceâ€"or at least target a specific database server such as SQL Serverâ„¢ 2000. The key functions of a data navigator control are moving back and forth on a per-record basis and updating and deleting the current record.
In practice, these two requirements are in conflict. If you use adapters to load data into memory, then edit the database rows through a batch update, you lose the data source neutrality. Data adapters, in fact, work on a per-data-source basis. If you plan to use single SQL commands for all updates, then you must think about how to move from record to record. The SQL language has no built-in notion of record position. To jump to a particular record, you have to use either server-side cursors or other DBMS-specific features such as the SQL Server SELECT TOP clause and Oracle's ROWNUM keyword.
The preferred way to bind data to data-bound controls in .NET is through the DataSet object. But when controls have to be used over the Web, then the DataSet object loses ground to the more effective DataReader object. (See the December 2001 Cutting Edge column for more information about caching tools for .NET Web applications.) But again, the DataReader object is DBMS-specific and is a feature of the data provider module acting as the .NET proxy of a given DBMS server.
Overall, setting up a Web-based data navigator control in .NET is not particularly difficult. However, it does pose a number of issues whose solutions depend on the application requirements and the programmer's preferences. In addition to the requirements I've mentionedâ€"navigation, updating, and deletionâ€"the DataNavigator could feature more advanced functionalities such as search and filtering. Since one bitmap is worth a thousand words, look at Figure 1. It shows a .NET custom control that I've used in a couple of applications I worked on.
Figure 1A .NET Custom Control
The control I'll build here executes a query each time you move to a new recordâ€"whether you use a toolbar button to jump to a relative position or to a particular row number. The SQL statement that I'll employ exploits some T-SQL features like EXISTS and TOP that not all commercial DBMSs support in the same form. For this reason, the control would actually be a SQL Server DataNavigator control that you can bind to a particular connection string and table name. The control would also require you to indicate the name of one field with unique values. This field should not necessarily be set as the primary key of the table. In addition, you specify the field you want the rows to be sorted by. Figure 2 shows properties and methods supported by the control.
Data Source Neutrality
Before going any further with the details of the SqlDataNavigator control, let's take a look at a topic that many programmers have stumbled on. In ADO.NET, the physical data access is carried out by DBMS-specific modulesâ€"the .NET data providers. The class you use to create a command that runs against SQL Server 7.0 and higher is different from the class you would use with, say, Oracle, SQL Server 6.5, or any other OLE DB or ODBC provider. This class, SqlCommand, is expected to work over a SQL Server connection and will fail if you use it over a connection to a Microsoft Access or Oracle runtime.
Likewise, the programming interface of a SqlCommand object is different from other command classes because it is adjusted to the capabilities of SQL Server. For example, the SqlCommand class features an ExecuteXmlReader method that relies on the XML infrastructure supported in SQL Server 2000. The reader object that command classes return is different, too. The SqlDataReader class and the OleDbReader class have only a few interfaces in commonâ€"IDataRecord and IDataReaderâ€"but no actual code.
So, how can you write data access code to work over different connections at different times? Of all the data-related objects in the ADO.NET Framework, only two must be created in a strongly typed mannerâ€"using the true class name. They are connection objects and data adapters. By contrast, command objects and data readers can also be created from generic, loosely typed pointers to a base connection and command objects.
The reasons you are not allowed to create a generic (non provider-specific) connection object are straightforward. ADO.NET simply dictates that you create all your data access using a data source-specific connector moduleâ€"the .NET data provider. But what about data adapters? Well, data adapters are the only ADO.NET objects that can manage connections implicitly to serve a disconnected DataSet to users. Since the data adapter may be requested to create, open, and close a connection internally, it must know about the object:
SqlConnection oConn = new SqlConnection(strConnString);
Once you have created a connection object of a certain type, you can manage it through a generic pointer. This pointer is simply a reference to the base class from which all connection classes inherit: the IDbConnection abstract class. When you manage a connection object through IDbConnection, you cannot access any properties or methods that are specific to a particular connection class. For example, you cannot read the PacketSize or the WorkStationID properties because they have been added only to the programming interface of SqlConnection.
The following code snippet shows how to create and execute a command although you're unaware of the true characteristics of the underlying connection:
The command is represented through the IDbCommand base class, which also represents a common set of functionalities for an ADO.NET command class. The ExecuteReader method creates and returns a data reader object that can be further employed to get actual data. In doing so, you cannot use any provider-specific methods, such as GetSqlValue, but must resort to more generic methods like GetValue. This is not a big issue as long as data readers are involved. The only difference between GetSqlValue and GetValue is in the type used to box the actual value. GetSqlValue returns data using the native SQL Server types, whereas GetValue retrieves data using the .NET Framework types.
If you're going to write a custom Web control that is expected to work with more than one data provider over different connections, then design the control to obtain from the client a valid connection object prior to processing it. The connection object cannot be created and assigned to a control's property just once. Web controls are subject to ASP.NET state management rules and usually persist a portion of their state through the page's ViewState bag. When using a connection object you run into a problem since you cannot serialize a connection object. To be persisted in the ViewState bag, an object must be serializable. This status can be obtained in two ways: by applying the [serializable] attribute to the class or by implementing the methods of the ISerializable interface. Either way, it's not something you can do with an existing class that's not designed to support that feature. If you try to put a connection object in the ViewState bag, an exception is thrown to invite you to either avoid that or endow the object with a type-converter class. Writing such classes is not easy and it's strongly discouraged for connections.
So how can your control get a living instance of a connection object from one of its clients? One way is through a custom event. The following code shows how a control defines and exposes a public event called Connecting:
public event ConnectingEventHandler Connecting; public delegate void ConnectingEventHandler(object
sender, ConnectingEventArgs e); protected IDbConnection GetConnectionObject() {
ConnectingEventArgs cea; cea = new ConnectingEventArgs(); if (Connecting != null)
Connecting(this, cea); return cea.Connection; }
The event exchanges a data structure with its client. The client creates a valid connection object and returns it through an ad hoc field in the event's data structure:
public void Connecting(Object sender, ConnectingEventArgs e) { String strConn
= "..."; SqlConnection oConn = new SqlConnection(strConn); e.Connection = oConn;
}
The most natural way to define an event data structure is to inherit from the EventArgs base class and then add the extra fields:
public sealed class ConnectingEventArgs : EventArgs { public IDbConnection
Connection; }
Designing the Control
The SqlDataNavigator must be able to move from record to record and must change the layout according to the underlying table. Unless you want to design and implement such functionality from the ground up, there's just one existing control you can extendâ€"the DataGrid. The SqlDataNavigator control will embed some special settings in a DataGrid. The grid will have a page size of 1 and feature exactly one templated column. The layout of the control is shown in Figure 3.
Figure 3Control's Layout
The SqlDataNavigator control is built from WebControl and creates the constituent DataGrid control during the initialization phase. Upon creation, the DataGrid is configured as needed. In particular, the DataGrid must support custom pagination, contain exactly one row per page, and have a customized pager bar on top. Lastly, the grid must include exactly one column object. Figure 4 shows the internal method responsible for setting up the DataGrid control. The DataGrid also assigns itself the width of the parent control. Although the SqlDataNavigator control is just a special case of a DataGrid control, I chose to create it by aggregating a DataGrid control to an outer Web control. In this way, the constituent DataGrid control is not visible at the page level and none of its settings can be programmatically changed.
The DataGrid keeps only the current record in memory and retrieves it using the aforementioned T-SQL command. The binding takes place via a DataReader objectâ€"much faster than a DataTable or a DataSet for reading a few bytes out of a table:
private void BindDataToGrid() { // Get the current record IDataReader dr
= GetRecord(); // Refresh the control's UI RefreshGridUI(); // Bind and display
m_grid.DataSource = dr; m_grid.DataBind(); dr.Close(); Connection.Close(); }
The GetRecord internal method opens the connection and returns a DataReader objectâ€"a connected object. Once the DataGrid has refreshed its own user interface via the DataBind method, remember to close both the reader and the connection.
Building a VCR-like Pager Bar
The DataGrid's custom paging feature requires you to set the virtual item count; that is, the maximum number of items you plan to iterate. This piece of information allows the DataGrid's runtime to draw the pager bar properly, disabling the Next button when on the last record and the Previous button when on the first table's row:
You can make the whole pager bar much more appealing using a little trick that involves the DataGrid's PrevPageText and NextPageText properties. The code snippet below replaces the default "<" and ">" characters with "3" and "4":
Now how about transforming the pager bar into a VCR-like command bar? To add a couple of extra buttons to jump to the first or last record of the table, you must hook the ItemCreated event. If the type of the item being created is ListItemType.Pager, you must first verify whether or not the new buttons are active. The activation status of the Go to first record button should always match the status of the Previous button. In fact, if you can move one record back, then you can certainly jump directly to the first record. Likewise, if you can move one record forward, then nothing would prevent you from going to the last record. The Previous or First buttons are active if, and only if, the corresponding WebControl is a LinkButton, since active navigation controls are rendered as LinkButtons.
ControlCollection ctls = pager.Controls; bool bFirstEnabled = (ctls[0] is
LinkButton); bool bLastEnabled = (ctls[2] is LinkButton);
Figure 5 shows the portion of the control code that implements a VCR-like pager bar. In Figure 6 you can view the results. The current record of the SqlDataNavigator control matches the current page index of the constituent DataGrid control. You update the CurrentRecordIndex property in the DataGrid's PageIndexChanged event handler:
When the user clicks on the First or Last link button, the event is handled by the GoToPage method shown here:
private void GoToPage(Object sender, int nPageIndex) { // page number is
0-based DataGridPageChangedEventArgs evPage; evPage = new DataGridPageChangedEventArgs(sender,
nPageIndex); OnPageIndexChanged(sender, evPage); }
Actually, as I mentioned, jumping to the first or the last page is not very different from moving to the next or the previous page. Therefore, you can exploit the basic DataGrid pagination mechanism. To jump to the first or the last record, the SqlDataNavigator control simulates a PageIndexChanged event by preparing an ad hoc event data structure and calling into the DataGrid's handler for pagination events.
Figure 6VCR-like Pager Bar
Displaying the Contents
Figure 6 also shows the default template that SqlDataNavigator utilizes to display its contents. The only column in the SqlDataNavigator's child DataGrid is a TemplateColumn object. The item template for this column is created dynamically (see the January 2002 Cutting Edge column). You can programmatically set the ItemTemplate property of a TemplateColumn object with the living instance of any class that implements the ITemplate interface:
TemplateColumn tc = new TemplateColumn(); tc.ItemTemplate = new DataNavigatorItemTemplate();
The DataNavigatorItemTemplate class (shown in Figure 7), which is an internal class in the SqlDataNavigator project, is responsible for rendering the contents of the current record. The template is provided by a placeholder control that contains a dynamically created table. The table has two cells (field name and value) and as many rows as the field count of the table. Notice that when the data source is a DataReader, the row data item is accessible through the DbDataReader class. You use some methods of this classâ€"one of the base classes for SqlDataReaderâ€"to get the name of the field (GetName), its type (GetFieldType), and its value (GetValue). In the sample code I've applied some special formatting rules to binaries and dates. Binary types are ignored and dates are formatted to include day and month name:
In Figure 8, you can see the navigator control in action on the Employees table of the Northwind database.
Figure 8Navigator Control in Action
Setting up the Search
Now, let's see how to add search capabilities to the control. The search box must be an integral part of the navigator, so the control needs to create it during the initialization step. During the ItemCreated event, you create a few custom controls and add them to the pager bar (or anywhere else). The search box is a label control that acts as the container for the textboxes and buttons used to perform the actual search:
The search box is made of two parts, one to jump to a given record and one to find the record that matches the specified key. Both parts have the same structure and are comprised of a textbox and a push button. The following code adds a button dynamically:
Button btn = new Button(); btn.Text = "Go"; btn.ID = "btnGo"; btn.Click +=
new EventHandler(GoToSpecifiedPage); ctls.Add(btn);
When the button is clicked, the page posts back and executes the procedure bound to the OnClick event. The number of the record would be read from the corresponding textbox. Is it really that easy? Not exactly. Since the search box is created dynamically during ItemCreated, neither the DataGrid nor the page know anything about it. So you are responsible for preserving the state of the textbox across the postback:
m_recNo = new TextBox(); m_recNo.ID = "txtRecNo"; ctls.Add(m_recNo);
You can retrieve the current value of the textbox using the Request object. You should note that this service would normally be provided by the ASP.NET infrastructure, the exception being when dynamic controls are involved and created within another custom control. And another tricky issue to consider is: what's the real (and unique) ID of a child control embedded in a DataGrid? The textbox control I just discussed has an ID of txtRecNo, but its unique ID is grid:_ctl1:txtRecNo. Web controls have a UniqueID property, but this value is not available to an ItemCreated event handler.
Let's review the actual structure of a grid control's ID. The ID string is a colon-separated triplet: grid ID, grid item ID, and control ID. You know the grid and the control ID. The item ID has a default form of _ctlX where X is the zero-based index of the DataGrid item. Control _ctl0 is the outer table that renders the grid. Control _ctl1 is the topmost pager, whereas _ctl2 is the header. Data items and alternating items occupy other places, starting with _ctl3. The final data item has an index of (PageSize+3)-1. In the case of SqlDataNavigator, the grid has a page size of 1 so _ctl3 is the only data item. Control _ctl4 is the footer and _ctl5 is the bottom pager bar. Notice that the DataGrid control always instantiates such DataGridItem controls whether they are used programmatically or not. Each grid always creates two pagers even if you use one or none. In Figure 9 you can see a trace of the grid control's layout.
Figure 9Grid Control Trace
This code retrieves and restores the previous value typed in the textbox:
Once the contents of the textbox have been properly restored, it's easy to get the record number or the key value. The SearchKeyField property specifies the field to search on. To refresh the control interface, you need to know the index of the record in the current sorting. You obtain this index like so:
SELECT COUNT(*) FROM table WHERE searchField < value
When no record is found with the specified key or number, you might want to display a message. I implemented this using an internal member called m_Error. When an error is detected, a related text is stored in the member. The footer, which is normally hidden, is then made visible to show just this message. Figure 10 shows the new interface of the SqlDataNavigator control.
Figure 10The Control's Interface
The code for this column is in two folders: Step 1 and Step 2. Each contains a simple batch to compile the C# code and a test ASPX page. The navigator of Step 2 includes the search box. Next month, I'll build a more complex control with editing capabilities.
Dino Esposito is an instructor and consultant based in Rome. Author of Building Web Solutions with ASP.NET and ADO.NET (Microsoft Press), he now spends most of his time teaching classes on ASP.NET and ADO.NET for Wintellect. Reach Dino at dinoe@wintellect.com.
.gif)
.gif){kind=small}
.gif)
.gif)
.gif)
.gif)
.gif)
.gif)