You don’t need to make any changes to the defaults
in Figure 4. This is the screen that subtly tells you what
to look for in your code. Note that the table adapter
is providing a custom class with five methods of interest
to you: Fill, GetData, Insert, Update, and Delete.
When you move to writing the code associated with
your application, you’ll need all five of these methods.
Clicking Finish, you can return to the application,
and double-click on the main form to go to the Load
event handler for your form. In Web Listing 2, you’ll
see the full custom code listing, which is required to
use the stored procedures with your data set. It starts
with the form load and button-click event handlers,
both of which consist of the following line of code:
Me.DataGridView1.DataSource = Usp_
Orders_SelectCustOrdersTableAdapter.
GetData(TextBox1.Text)
This code sets the data source for the grid in your display
to the text in the form’s textbox—similar to what
you defined in the ASP.NET wizard, but you need to
use a line of code in Windows Forms. The other item
of note is that the GetData method returns a new dataset
object, which is the underlying data source that
the data grid actually uses. This will become important
shortly.
At this point, without handling any of the other
three events (i.e., Insert, Update, Delete), you could
run the form, and the form would let you add, edit, and delete entries. However, when you closed the
form and restarted the application, you would find
that none of your changes persisted in the database.
Even though you’ve defined the stored procedures for
these actions, .NET wouldn’t automatically call them
to update the database.
To resolve this problem, you need to add custom
code; the good news is that the dataset provides builtin
calls to the stored procedures you defined. This feature
is one area of contrast between Windows Forms
and ASP.NET.
The easiest item to implement is the Delete logic.
As you see in the code, the DataGridView provides a
UserDeleting event that you can handle. This event is
fired before the item is removed from the grid, and you
can update the database; if a failure occurs, you can
prevent the item from being removed from the grid.
The next easiest item to resolve is the Update
event. To handle updates, you need to check each time
the user leaves a given row and see if he or she has
made any changes. If changes have been made, the
IsCurrentRowDirty property will be true for the row
being exited, and you can call the Update method.
Note that, similar to the Delete method, the Update
method on the table adapter was generated knowing
the parameters for your Update statement. After you
call this method, you’ll be able to commit or roll back
the user’s changes in the database.
Finally, there’s the Insert event, which is the most
painful of the three stored procedure calls to manage.
The DataGridView generates a new row in the
display and underlying dataset only when the user
enters data in the New Row at the bottom of the display.
The challenge is that you can’t really work with
this row until it’s in your data source, and that won’t
happen until it’s in the database.
Now, in general, I don’t typically
have users adding entries in a custom
grid like this one; however, if
you’re working with, say, Microsoft
Excel, there are similar items
you’ll need to consider.
When the UserRowAdded
event is fired, the Data-GridView
control has already added a new
row as far as the UI is concerned,
so you’ll note that the code passes
key default values to the Insert
method to create a matching entry
in the database. However, and
this is important, if you don’t explicitly
make a call to update the
underlying source data for this
row, that data won’t get persisted
to the database. Therefore, you
need to repopulate the underlying
data or refill the data set; this is where the Fill method
provided as part of your TableAdapter comes in.
After you execute your Update statement, you’ll
find that you can refill the dataset and everything will
work as expected. Keep in mind, however, that when
you refill the data, the data grid loses all its rows,
and as such, the user’s current row and cell are lost.
So, if you want the user in the exact same spot, be
sure to capture the current location before reloading
the data.
In general, although it’s a little challenging to work
with, this data-binding model works. As implemented,
it lets the application submit updates as they occur—
as opposed to batching them and running risks
related to having a successful transaction or old data edited by another user. It requires a bit more work
than ASP.NET, but unlike ASP.NET, it supports the
Insert capability.
ASP.NET Data Binding
Once you’ve created your data source object, you’re
ready to go—except you probably have a bug in your
generated code. Unlike Windows Forms, ASP.NET
doesn’t require that you write any code, but as noted,
you also don’t have a built-in capability to insert new
rows into the database. However, as you see in Figure
5, the look of the sample application and the context
menu for the GridView control are similar to those of
the Windows Forms sample.
The ASP.NET sample is available from the SQLMagDataBindingASP.
zip download (InstantDoc ID
97104)—a simple file-based Web project that you can
review. As with the Windows Forms project, I’ve gone
into the column properties for the grid view and made
certain columns Read Only, hidden the CustomerID
column, and changed the headers. I’ve also selected
certain options, such as data paging, that are available
only to ASP.NET applications. Unlike the Windows
Forms solution, the ASP.NET solution’s Update (aka
Edit) and Delete actions don’t require any custom
code. Unfortunately, however, if you’re building this
yourself, they also don’t work initially.
I’ve already repaired the problem in the sample
code, so you won’t see the errors. The error occurs
because of what I did in the stored procedures. Note
that the Select statement for these examples joins data
from the customer table. When Microsoft Visual Studio
mapped the stored procedure’s results, it recognized
that the CustomerID column is a primary key
to the customer table. (The stored procedures don’t
actually update this table, but that didn’t matter.)
The GridView control has a property called
DataKeyNames, which
maps columns that are
part of your grid to these
primary keys. ASP.NET
assumes you’ll need every
such column in each
of your database calls.
As a result, when you try
to run Edit or Delete out
of the gate with this project,
you’ll receive an error
message stating that
you’re sending the wrong
number of parameters to
your stored procedure.
To debug, you need
to look at the Deleting
and Updating events
for the SQLDataSource
control. You’ll see that I’ve left some sample code
commented out in the sample solution that’s part of
this article download. The solution is easy: Remove
the unneeded CustomerID column from the list of
DataKeyNames, and ASP.NET will stop appending
it to every command and will instead rely on the list
of parameters it created based on your stored procedure
definition.
The Power of Data Binding
This article represents just the first, tentative steps
toward understanding data binding. The release of
.NET 2.0 brought new support for object binding instead
of just using data sets, and as with the baseline
data binding, the rules and capabilities vary between
Windows Forms and ASP.NET. Additionally, you
have to consider binding to such things as XML data
sources and Web services interfaces. And this discussion
doesn’t touch on the possibility of data-binding
individual fields as opposed to just lists or grids. On
top of that, there are the new features of data-binding
for the Windows Presentation Foundation and—perhaps
just as important—using LINQ as the interface
for retrieving data. Finally, there are other interfaces,
such as Visual Studio Tools for Office and Excel’s
much more complex and capable grid, to consider in
your application.
Data binding is pretty powerful, but it also encapsulates
and hides many of the details of what’s actually
being done against the database. As development
languages become more declarative in nature, the use
of data binding will probably increase as more and
more of your data operations are encapsulated by objects.
Now that you know some basic elements and
best practices, you can embark on a more thorough
understanding of data binding with ADO.NET. (see .zip file)