A developer recently asked me how to set up
a TableAdapter application that’s fed by
stored procedures instead of base tables.
Stored procedures optimize application, database, and
development team performance as well as simplify
maintenance. However, using them as the rowset source
for forms-over-data applications is thinly documented
and requires some manual setup and custom coding—
especially when you’re working with stored proceduregenerated
rowsets that have an implicit hierarchy,
such as those drawn from tables with parent–child
relationships. With guidance from Microsoft’s Steve
Lasker and Beth Massi, I created a comprehensive
demonstration application that illustrates most of the
problems you can encounter when trying to build such
an application. In this article, I’ll use this sample application
to explain how to create the solution as well as
the behind-the-scenes mechanisms that make it work.
I used Visual Studio (VS) 2005 to build this application
because it automatically generates most of the
plumbing code, which you shouldn’t have to modify or
debug. In addition, VS also generates the binding components
and UI
controls for you.
Although code
generators can
generate controls
and navigation
code you
might not need,
cleaning up these extra UI elements is fairly safe and
easy. Just remember to back up your work frequently
when working with VS. I’ve had to start from scratch
several times after my changes caused unrecoverable
application errors.
What’s the Big Deal?
Simply put, a TableAdapter is a VS-generated class
that exposes a rowset as a strongly typed DataTable that you can update and handle the same as you would
a DataAdapter. You’ve probably seen forms-over-data
applications demonstrated many times using base
tables. But the typical base-table approach to creating
production applications can have a dramatic impact on
server performance and application scalability. If you
simply create a new VS data source and click a table in
the list, VS builds a SELECT * FROM MyTable query
that unconditionally returns all the rows in the table—
even though your user might need only a few rows.
Although pulling all 50,000 (or five million) rows from
the server into client memory is possible, users might
not like the wait time.
Any application-development strategy you choose
should focus the initial query on a subset of the rows.
For example, instead of fetching all the customers in
the database, get only customers who meet the usersupplied
criteria. In fact, the user might not want any
rows at all. I recommend performing queries only after
users provide a few parameter values describing what
they’re looking for. In the case of the sample application,
which contains only a few thousand customers,
I asked the user to specify a state and a last name
to narrow down the initial rowset to a manageable
number.
Although you can use custom-written Fill queries
to refocus the data, they make the applications harder
to maintain if the query logic is too sophisticated. If
the logic changes, you have to recompile, retest, and
redeploy your applications.
Stored procedures let you set up and save database
queries that optimize your application performance
and allow for easy maintenance. One note about
building an application using the approach I describe:
Although you can create the TableAdapter from a
rowset derived from a join, this method can add a lot
of complexity. If you’re simply adding columns to each
row, you can write custom stored procedures to update
the affected tables. Just make sure that ADO.NET’s Update method, which is called behind the scenes when
the TableAdapter Update method is invoked, returns
a RowsAffected value of 1. You might even have to
implement your own TableAdapter partial class, which
is beyond the scope of this article.
If the join returns more than one row for a single
primary key—such as when you join Customers with
Addresses into a single rowset and a customer has
several addresses—it can confuse the client-side databinding
mechanism. You might find it easier to keep
your queries simple and use other mechanisms to
achieve the same result, such as performing a clientside
join of the related data columns, which is what I
do with the Addresses DataTable in the Web-exclusive
sidebar “Additional Features,” InstantDoc ID 97180.
Getting Started
Before I describe how to build the sample application,
let’s lay down some ground rules. First, you can use
this approach with both Active Server Pages (ASP)
and Windows Forms applications (with a few exceptions
that I mention along the way). My example is a
Windows Forms application written in Visual Basic
(VB) and designed to access SQL Server. The application
includes little hand-written code, and some of my
custom code was even leveraged from VS-generated
code that I relocated or simply tuned. However, C#
programmers will need to convert the VB code.
In addition, I’ve set up the database tables in this
example with server-side primary key–foreign key
relationships configured to prevent duplicates and
ensure that parent–child relationships are correctly
maintained. It’s important to establish these referential
integrity constraints to make sure the application code
doesn’t destroy the data.
Let’s get started with the preparation steps required
to create the sample application. First, start VS 2005
and begin a new Windows Forms project. I used VS
Team System, but you can use VS Standard Edition or
later. However, VS Express Edition is missing some key
features that I leveraged, so I don’t recommend using it
for serious development work. In some VS configurations,
the project isn’t actually saved until you click Save
All, so save your project immediately to make sure the
directory is created and the files you save along the way
are correctly persisted.
Next, open the Data Sources window, which you
can launch by clicking Data, Show Data Sources. Click
Add New Data Source, and select Database from the
Choose a Data Source Type menu. Select or create
a Data Connection that points to the SQL Server
instance that has the target database. In this case, I
pointed to the server hosting the Customers database
created for the sample application. (This database and
the complete VS project are available online at www.sqlmag.com, InstantDoc ID 97182.) Save the Connection String to the app.config file so that the Table-
Adapter Configuration Wizard can leverage it when it
needs to build ADO.NET connection objects behind
the scenes.
The Choose Your Database Objects dialog box,
which Web Figure 1 (www.sqlmag.com, InstantDoc
ID 97182) shows, lets you select the stored procedures
that you want to use to return the rowsets for the
TableAdapter objects you’ll create. In this case, you’d
select the GetCustomerbyState, GetItemsbyCustomer-
Order, and GetOrdersbyCustomer stored procedures.
Whatever you do, never select root elements such as
Tables, Views, or Stored Procedures; doing so generates
TableAdapter objects for all the items in the list, which
isn’t good.
You want to access the data through parameterdriven
stored procedures written to return a focused
rowset. To view these stored procedures, use VS’s
Server Explorer or SQL Server Management Studio
(SSMS) to open the stored procedure definitions.
Note that you can’t use Server Explorer until the Data
Source Configuration Wizard is finished. Web Listing
1 shows the first stored procedure, which returns customers
from a selected state.
Continue to page 2
Prev. page
[1]
2
3
4
next page 
