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.
.gif)
Figure 1 A .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:
IDataReader ExecuteCommand(IDbConnection conn, String strCmd) { IDbCommand
cmd = conn.CreateCommand(); cmd.CommandText = strCmd; conn.Open(); IDataReader dr
= cmd.ExecuteReader(); return dr; }
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.
.gif)
Figure 3 Control'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:
private void SetVirtualItemCount() { Connection = GetConnectionObject();
IDbCommand cmd = Connection.CreateCommand(); cmd.CommandText = "SELECT COUNT(*)
FROM " + TableName; Connection.Open(); int nRecCount = (int) cmd.ExecuteScalar();
Connection.Close(); m_grid.VirtualItemCount = nRecCount; }
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":
m_grid.PagerStyle.PrevPageText = "3"; m_grid.PagerStyle.NextPageText = "4";
m_grid.PagerStyle.Font.Name = "webdings";
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:
void OnPageIndexChanged(Object sender, DataGridPageChangedEventArgs e) {
m_grid.CurrentPageIndex = e.NewPageIndex; CurrentRecordIndex = e.NewPageIndex; DataBind();
}
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.
.gif)
Figure 6 VCR-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:
cell.Text = ((DateTime)dbdr[i]).ToString("dddd, MMMM dd yyyy");
In Figure 8, you can see the navigator
control in action on the Employees table of the Northwind database.
.gif)
Figure 8 Navigator 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:
Label funcBox = new Label(); funcBox.ID = "SearchBox"; funcBox.BackColor
= Color.SkyBlue; funcBox.Width = Unit.Percentage(100); ctls.Add(funcBox);
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.
.gif)
Figure 9 Grid Control Trace
This code retrieves and restores the previous value typed in the textbox:
String strUniqueID = m_grid.ID + ":_ctl1:" + m_recNo.ID; if (Page.Request.Form[strUniqueID]
!= null) m_recNo.Text = Page.Request.Form[strUniqueID].ToString();
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.
.gif)
Figure 10 The 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.
Send questions and comments for Dino to
cutting@microsoft.com. |