Let’s start with a quick definition of data binding. Data binding is probably best described as the process of creating an association between a GUI object and one or more data elements. In this simple definition, the GUI object might be a data grid in a Windows form, or a list that’s part of an ASP.NET Web page, or a text box, or anything else that might display application data. The data element could be a table from a database, a set of XML data, or a custom object created to hold an array of data.
Data binding is a broad topic, and yet there are several commonalities between its different forms. This article focuses on .NET 2.0—not 3.0 or 3.5—so you won’t find any Windows Presentation Format (WPF)– or Language-Integrated Query (LINQ)– specific information—and further, I’m going to focus on simple Windows Forms–based and ASP.NETbased examples. Several other ancillary topics, such as data-binding to XML, don’t quite fit within the confines of a single introductory article. If you have specific areas you’d like to see covered, feel free to contact us or visit our forums.
In addition to introducing the concept of data binding through simple examples, this article runs through some data-binding best practices. The first such best practice is the use of stored procedures. A key feature that data binding supports is the use of stored procedures to Get, Update, Insert, and Delete data. Although it’s possible to bind directly to data tables defined in your database, it isn’t a best practice. With that in mind, how about a quick review of the advantages of stored procedures versus other data-access methods?
Data-Access Security
There’s been a debate about the best way to access data. Generally, you’ll find that most published examples use Dynamic SQL, in which the demo author takes a raw Select statement and embeds it as a string within the application code. However, this type of data access has been repeatedly shown to be completely open to hacking through SQL injection attacks and is usually considered amongst the worst practices. For more information about this vulnerability, see "Preventing SQL Injection Attack."
A somewhat more secure method of working with your database is to use parameterized queries, which have the advantage of preventing SQL injection attacks but fail in defending against a different attack vector.
Parameterized queries limit the available security options that you can apply to objects in your database. If you’re using parameterized queries, the user needs permission to access the table that the query references. However, with stored procedures, the most secure method of accessing data, SQL Server lets you grant users permission to access only the stored procedure— not the underlying table.
Setting Up Stored Procedures
For this article, I’ve created four new stored procedures against the sample Northwind database, which is available for both SQL Server 2005 and 2000. Web Listing 1 provides the T-SQL code for building the stored procedures in your own copy of Northwind. The four new stored procedures carry out the Select, Insert, Update, and Delete actions. (Note that I don’t try to retrieve every column from the table—simply some of the date columns and key relationship columns.) The Select statement retrieves all the fields of interest, as opposed to all columns. The Insert statement lets you create a new order for a customer with any of the three dates. The Update statement, however, allows for editing of only the requested and shipped dates. (see WebListing 1)
These statements are simple—not what I’d consider production quality—but they do serve to illustrate custom stored procedures that require specific parameters. As such, they’ll provide a solid basis for designing a standard typed data provider for both ASP. NET and Windows Forms to illustrate data binding and the differences in each environment. For example, the Delete stored procedure actually deletes a row. In reality, I’d mark a row as inactive or deleted and track who made that update. (see WebListing 2)
Windows Forms and ASP.NET
For the purpose of this article, I’ll go through a Windows Forms example that uses a label and text box in the upper left corner of the window, a refresh button on the upper right corner, and a DataGridView control to fill in the display. Similarly, the ASP.NET control will have a label and text box in the upper left corner, a refresh button on the upper right corner, and a GridView control to fill in the display. After creating the data objects for both solutions, I’ll look at incorporating the data objects and dive into some of the more project-specific requirements.
You might expect that Windows Forms and ASP .NET would have similar functionality, but there are quite a few differences. Yes, each solution is wizarddriven and lets you create a data adapter based on your stored procedures, but the end results are distinctly different. The good news is that the processes are similar enough to be compared and contrasted in context.
Creating a Custom Dataset or SQLDataSource
In both Windows Forms and ASP.NET, once you’ve laid out your form with the previously mentioned controls, you’ll see the context menu for the grid in the grid’s upper right corner. On this menu, you’ll see a Choose Data Source dropdown list. For the Windows Forms application, you’ll find that when you open this list for a new project, an Add Project Data Source link will appear near the bottom. In ASP.NET, opening the same Choose Data Source option reveals a simple dropdown list with the option to create a new data source. In either case, you’ll enter a wizard screen that lets you specify the underlying source of your data—a database— then lets you to build a connection string for that data source. These are fairly common steps, so I won’t go into detail. In both cases, the final step in this process of defining your data objects is to automatically add the connection information to your associated .config file.
Having created your data source, you now need to define your data objects. Figure 1 shows the next Windows Forms screen. I’ve expanded the Stored Procedures section. The wizard can’t associate all the stored procedures into a single table adapter, so after the wizard finishes, I’ll need to complete this step for my Windows Forms solution. At this point, I can finish the wizard, which will create the XML Schema Definition (XSD) describing my data set, add this element to my project, and let me edit the definition of my table adapter.
The ASP.NET wizard is a bit more involved. Its first screen lets you either select one or more tables or choose the Specify a custom SQL statement or stored procedure option. Selecting the custom option, you’ll then press Next, at which point the wizard lets you specify either a custom SQL statement or a stored procedure for SELECT, UPDATE, INSERT, and DELETE.
After you fill in all four stored procedures and press Next, you’ll map the parameter for your Select statement to a field outside your data grid. On the assumption that this parameter needs to come from somewhere, the wizard lets you map either a query string value or, in this case, a form value. As I described earlier, there will be a text box on the form, so this control is mapped as the source of this parameter. (If the text box is empty, the system provides a default value of ALFKI—the customer ID for the first customer in the Northwind sample database.)
Once you’re satisfied with your settings on this screen, the final screen lets you execute a test run of your query statement. Unlike the Windows Forms solution, the ASP.NET solution updates your default. aspx page. The default.aspx page will use the data definition embedded in the page to make the calls to the database. Now that we’ve finished setting the data source for ASP.NET, let’s return to Windows Forms and set up a basic data-bound grid.
Windows Forms Data Binding
As I mentioned earlier, the Windows Forms wizard doesn’t really understand how to map additional stored procedures to your data adapter. As you’ll see, this shortcoming has implications beyond the wizard. First, a quick review: By now, you’ve created a new Windows Application project, similar to the WinForm DataBinding.zip sample available for download from the "Download the Code" link at the top of the page. Your application would have automatically generated a Form 1, and you’ll have used the design to lay out your form similar to what Figure 2 shows.
Notice that in addition to showing the form, the figure also shows the grid control’s context menu, from which you defined your data source. Also, note that the associated options for Adding, Editing, Deleting, and Sorting are selected. This control’s context menu is quite different from that of the ASP.NET GridView control (discussed later).
Finally, by clicking on the Edit Columns option, you can change the default width of the various columns, map in different display headers, make key columns Read Only, and even make certain columns such as Company ID invisible so that they won’t appear in your UI.
After you finish customizing the DataGridView control from the context menu, it’s time to review the capabilities of your custom data set. To do so, review your solution in the solution explorer and locate the .xsd file, which was added as part of the wizard process that created your data object. The sample code has one called dataset1.xsd, and double-clicking this file opens a graphical representation of the newly created typed dataset and its associated table adapter. Working with a table adapter is important because it’s the adapter that lets you map in your database actions as stored procedures. Right-click the table adapter, and select Configure from the context menu.
The Configure option opens the screen that Figure 3 shows. This wizard is designed to let you group related stored procedures onto a given table adapter.
After you fill in your stored procedures in the wizard, you’ll proceed to Figure 4, which shows the default methods to be created for your table adapter.
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)
