ASP.NET VERSIONS: ALL
Employ Data Designers Effectively
Visual Studio.NET Data Designers Can Save You Time, but Can also Lead to Poor Data Access Design If Used Improperly
By Brian Noyes
I'm constantly amazed at how easily certain kinds of development are compared to the "good old days." One of those areas is data access design. I cringe when I think back to the days of writing all my data access code by hand using obscure APIs. Between the clean object model of ADO.NET and the power Visual Studio.NET brings for writing data access code for you, data access is easier than ever. With Visual Studio 2005, these features are getting even better. However, using a tool is never an excuse for bad data access design; unfortunately, common practices with tools sometimes lead to a poor design.
In this article I want to cover a few fundamental data access design concepts and show how to put them into practice, harnessing the power of the designer to the extent possible, but not sacrificing good design in the process.
Layers Are an Important Part of Good Design
We've all seen the impressive demos (and probably repeated them ourselves) of using the VS.NET designers to drag and drop controls on a form, drag and drop data sources onto the same form, wire them up through the properties grid, write a couple lines of code, and -voil ! - a fully functioning data access application.
So what's the problem? If you're building toy apps or ones that don't need to be maintained, evolved, or support a large number of users, there is no problem. But for real-world apps, you always need to do these things, and directly coupling your user interface to the data source is the fast lane to being incapable of doing any of those things.
Layered architectures have evolved to provide a separation of business logic from presentation, and data access from business logic. Individual layers in an architecture should only have direct knowledge of the layer directly beneath them, and layers lower in the stack should have no knowledge of the layers above them (see Figure 1). When you follow this practice, it makes it far easier to maintain the code in any individual layer, either to add functionality or modify existing functionality. You don't have to go searching through your entire code base to figure out what might break due to changes, because there are minimal points of coupling between any given component and the other components in the system. The layer boundaries also give you the opportunity to move those layers out to other physical tiers to achieve better scalability for your application to support larger numbers of users or larger transaction volumes.
Figure 1: A typical layered application architecture.
When you do the drag-and-drop magic directly onto a form, you have just short-circuited the whole layered stack. So does that mean you're back to writing all your own code by hand to comply with a good layered architecture? Absolutely not. You can still separate your data access into its own layer by putting all your data access classes into their own class library projects, yet still get the RAD data access design experience that you see in the demos.
Components Have Design Surfaces, Too
The key realization toward the path to data access righteousness is that any class that derives from System.ComponentModel.Component will have a design surface in Visual Studio. This means that you can perform drag-and-drop actions on any class that you derive from Component in the same way that you can with forms. This also works if you are developing your data access .NET components as part of an Enterprise Services (COM+) application and deriving them from ServicedComponent, which itself derives from Component.
So the right way to start your layered architecture is to create a class library project to contain your data access classes. Delete the Class1.cs file that's added by the project wizard, and add a new item to your project. In the Add New Item dialog box, select the Local Project Items category, and the Component Class template. Give it a name that describes the data that it will access, and press the Open button (see Figure 2).
Figure 2: Adding a component to your class library project.
Once you've done this, you are presented with a blank designer surface on which you can drag and drop components or items from the Server Explorer window. Just like with forms, you can drag a SQL Server table from Server Explorer onto the surface, a SqlConnection and SqlDataAdapter member will be added to the class, and all the necessary code will be generated to retrieve and update data in that table through a data adapter. I recommend naming the data adapter and the connection object using your normal member variable naming convention, although you may want to extract the connection from the class as described later in the section on connection management.
You can generate a typed data set from the data adapter by selecting it and selecting the Generate DataSet option from the bottom of the properties grid, or by right-clicking on the data adapter component on the design surface and selecting the option from the context menu. You can also achieve this same step in any project type by adding a DataSet item from the Add New Item dialog box, and then dragging the table onto the XML Schema design surface for the data set. Typed data sets are generated from an XML schema by Visual Studio or the xsd.exe command-line tool.
Once you have your data access component class, its members for the connection and adapter, and a typed data set definition, you're ready to start coding the public interface to your data access component. Following good component-oriented design, I recommend you do this through an interface definition as shown in Figure 3. Of course, for more real-world scenarios, you may have a number of other related methods, such as a Get method that takes a CustomerID and returns a data set with the corresponding row. If you can handle a little more abstraction, you might want to avoid using relational data specific terms like data access, get, and update, and instead use more abstract state management terms like persistence, load, and store. This way you can use a consistent approach and naming convention to access state for your application, whether that state is coming from a database, a file, a network stream, or some other form of state storage.
public interface ICustomersDataAccess
void UpdateCustomers(CustomersDataSet customers);
Figure 3: Customers data access interface definition.
When you have the interface definition laid down that forms the contract between the consumers of your data (which should be primarily business logic components in an enterprise-scale layered architecture) and the provider of that data (your data access component), you're ready to code up the actual implementation of those methods. Because of the work Visual Studio does for you, there is very little code you need to write by hand.
First you need to implement the interface on the data access component class you created earlier. This means providing method implementations for each of the methods defined on the interface. Again, the Visual Studio editor can help you out here by creating stubs for the methods after you type in the interface inheritance (the mechanisms here depend on whether you are using C# or VB.NET). The implementation of these two methods is shown in Figure 4.
public CustomersDataSet GetCustomers()
CustomersDataSet customers = new CustomersDataSet();
public void UpdateCustomers(CustomersDataSet customers)
Figure 4: Data access interface method implementation in the component class.
Don't Settle for Inefficient Queries
Have you ever looked at the queries generated in the designer-generated code when you drag a table onto a form or component? If so, you should have been a little intimidated by what you saw. The designer generates a query and constructs the commands for inserts, updates, and deletes (collectively referred to as updates) using optimistic concurrency based on the inherent change-tracking mechanisms of the DataSet.
The way it does this is to construct a query that uses the original values of each row that is being updated to compare to the current values of the corresponding row in the database at the time the update will be performed. If the value of any field has changed since the time you filled the data set, it means someone other than you has modified the database since then. With the optimistic concurrency model, this means you should not write your changes to the database, because you are working with stale information and could overwrite the changes made by someone else.
The queries constructed by the designer compare each and every field in the table being updated to the original field values in the dataset. This can be horrendously inefficient depending on the contents of those fields and the types of the columns.
A better way to implement optimistic concurrency, if you can control your schema, is to include a time stamp or row version number field that is a single field indication of whether the row has been modified since you retrieved it. Then you can modify the designer-generated queries to check only that field against the original version to make a concurrency determination. You can also more easily add this field as a parameter to a stored procedure that encapsulates the updating of your database.
I recommend going through stored procedures, and not directly against the table, for all of your data access whenever possible. You can still use the RAD designer experience with stored procedures, as well. Instead of dragging a table onto the designer surface, drag a SqlDataAdapter from the Toolbox. You will be presented with a wizard that allows you to base the adapter off SQL statements, or stored procedures, which you can have the wizard generate on the fly or from existing ones. Usually it's a good idea to design and test your stored procedures first, but you can generate them as part of the wizard if desired. The default options for the generated stored procedures use the same approach for optimistic concurrency described above for direct table SQL queries, but you can override that through the wizard's Advanced options if desired, and you can edit the generated stored procedure code before it is used to configure the data adapter.
Centralize Connection Management
You probably don't want to have to maintain a separate connection object on every data access component you write, especially when they are all working against the same database, and especially if the target of the connection can change. You also probably don't want clients of your data access components worrying about connection management if the connections aren't going to be changing at run time. It makes sense to centralize the management of connections in a way that makes it easy to use a consistent approach within each data access component that shields the consumer from worrying about connection management.
One approach to doing so for generic ADO.NET data access components is shown in Figure 5. The connection management class simply encapsulates the initialization of the connection string from either a default value or from a config file appSettings entry. It also exposes a factory method to return an initialized SqlConnection object that can be used by query methods in the data access components in the class library.
public class NorthwindConnectionManager
private static readonly string m_ConnectionString;
// Private constructor to prevent instance creation.
// Static constructor to initialize connection string.
// Try to get it out of config file
string connStr = ConfigurationSettings.AppSettings[
if (connStr != null)
m_ConnectionString = connStr;
// If not set, use default
m_ConnectionString = "server=localhost;" +
// Factory method to get connection objects
// that are ready to go.
public static SqlConnection GetConnection()
return new SqlConnection(m_ConnectionString);
Figure 5: A simple connection management class for Northwind.
Using the connection management class makes it very easy to internalize the initialization of the connections for the adapter in a data access component into the constructor. You can do this with a helper method, as shown in Figure 6. Depending on your needs to change this at run time, you might also choose to expose this as a public property that can be set by the client if desired. If so, make it part of the component's public interfaces.
private void SetConnection(SqlConnection conn)
m_Adapter.SelectCommand.Connection = conn;
m_Adapter.InsertCommand.Connection = conn;
m_Adapter.UpdateCommand.Connection = conn;
m_Adapter.DeleteCommand.Connection = conn;
Figure 6: Internalizing connection initialization in the data access component.
Finally, once you've done that, it makes the client's data access code extremely simple and easy to read (see Figure 7).
private void Form1_Load(object sender, System.EventArgs e)
CustomersDataAccess dac = new CustomersDataAccess();
Figure 7: Simple data access component usage from the client.
There are many different ways to encapsulate your data access code as a layer within your application. This article has discussed one simple way that lets you harness the power of the designer to write code for you, but still allows you to encapsulate your data access logic separately from your UI and business logic in a clean, easy-to-use API. The download code for this article includes the entire class library implementation of the code snippets presented with a simple client app that uses it.
The files referenced in this article are available for download.
Brian Noyes is a software architect with IDesign, Inc. (http://www.idesign.net), a .NET-focused architecture and design consulting firm. Brian is a Microsoft MVP in ASP.NET who specializes in designing and building data-driven distributed Windows and Web applications. Brian writes for a variety of publications and is working on a book for Addison-Wesley on building Windows Forms Data Applications with .NET 2.0. Contact him at mailto:[email protected].