Enterprise .NET Community: Build a Data Access Layer with the Visual Studio 2005 DataSet Designer
来源：百度文库 编辑：神马文学网 时间：2024/10/02 18:20:34
October 13, 2005
A good data access layer is critical for most business applications, whether that layer resides on a middle tier application server, a web server or on the client. Data access layers tend to involve a lot of error-prone, repetitive coding if you try to do it by hand. You can alleviate some of that by designing a good set of base and helper classes to encapsulate the repeating patterns, or by using a code generation tool. However, you still usually have to write all the data access methods by hand for the ad-hoc queries that service large parts of your application.
If you are using Visual Studio 2005, that does not have to be the case. Some significant improvements have been made to the Visual Studio 2005 DataSet designer and the code that it generates that make it so you will rarely have to write your data access methods by hand of you choose to work with DataSets. In this article, I’ll walk you through what those features are and how to use them. I’ll also discuss some of the pros and cons of using the new features and give some guidance on when you might want to do something different.
A Quick Intro to Typed Data Sets
Typed data sets are classes that you create declaratively through the Visual Studio designer. Typed data set classes derive from the ADO.NET DataSet, DataTable, and DataRow classes and expose a type-safe API for accessing data contained within a DataSet that has a particular schema. You create typed data sets in Visual Studio through some simple drag and drop operations and by setting properties in the Properties window of the designer. What you are actually creating when you design a typed data set is an XML Schema Definition (XSD) file that contains the schema of the data that will be contained by the DataSet. The XSD file also contains annotations that associate it with the data source it was generated from. Visual Studio then uses that XSD to generate a code file that contains the typed data set class definitions.
When you work with data in an application, the data is usually partitioned into different types of logical business entities, such as Customers, Products, Employees, and so on. To work with that data, you need to encapsulate those logical entities into objects that you can deal with in your code. You could write a custom class for each entity type. Those entity types would expose properties for each of the data values that the entity includes. You would then also want to create a custom collection type for each entity type so that you could have strongly typed collections to contain those entities.
Typed data sets represent an easy alternative to creating and maintaining all those custom types yourself. Essentially what you are doing when you create a typed data set is that you are creating a set of custom type definitions to contain logical business entities and collections of those entities, similar to writing those types by hand. The difference is that you are doing it in a declarative way through the designer that is easy to visualize, edit, and keep synchronized with the database schema that populates those business entities. The code generation of Visual Studio takes care of writing all the underlying properties and methods that give you a strongly typed API for dealing with those business entities in your consuming code. Additionally, because these types are inheriting from the ADO.NET types, you inherit the rich relational data manipulation functionality from those types. These types are also aligned well with the data binding capabilities in Windows Forms and ASP.NET, so if you will be setting up data binding using the objects, then you have less work to do on that front as well.
Finally, and perhaps most importantly, when you create typed data sets in Visual Studio 2005 from a database, you also get a table adapter type created for each table that you add to the data set. A table adapter is a full fledged data access component that lets you retrieve and update data from the database. It encapsulates a connection, a data adapter, and a set of command objects that allow you to execute queries to the database. I’ll get into more detail on table adapters in a little bit.
DataSets vs. Business Objects – an Open Debate
Almost as common as the debate over which .NET language to choose is the argument about whether to use DataSets or not. As described above, typed data sets are easy to generate through the designer, provide a type safe API for containing business entity data, and already support advanced features such as change tracking, sorting, filtering, and searching. Some of the resistance to DataSets resulted from several performance shortcomings in the .NET 1.1 implementations. These problems included poor performance when working with large DataSets and the fact that DataSets always serialized themselves as XML, which had bandwidth and performance implications when passing DataSets across remoting boundaries. These problems have been fixed in .NET 2.0 and there have been a large number of other improvements. If you dismissed DataSets in .NET 1.1, they deserve another look.
The alternative to using DataSets is to create custom object types that represent your business entities, and custom collection types to contain them. When you go this route, you end up needing to write a lot more code yourself. This has gotten a lot better in Visual Studio 2005 and .NET 2.0 with the additions of code snippets and generic collection classes. But to support the full range of features that a DataSet provides, there is still a fair amount of custom code that you will need to write by hand.
Custom objects have the advantage of giving you explicit and complete control over the way the type is designed, what its internal capabilities are, and what the API is that is exposed from the object. If you prefer a pure object-oriented design approach, then custom business entities will feel a little more comfortable to you. You can accomplish almost anything with a typed data set that you can with a custom business entity, but some things may be a little less clean with a typed data set if the things you are trying to do don’t map well to the relational nature of a typed data set. But if you are primarily getting business data for the purposes of presenting the data, allowing the user to work with the data, and then will persist the data back to the database, you will be able to get things done quicker with typed data sets if you harness the features of the DataSet designer.
When you go with typed data sets in Visual Studio 2005, you can actually support most of the same design styles that you could with custom business entity types. The data access code will always be separated into the table adapter types generated by the designer, or into data access components that you write. But you can add custom validation and other logic into your business entity types (the typed data row or data table classes) through partial class extensions. Each of the types created as part of a typed data set definition (data set, data table, data row, and table adapter) are defined in the generated code as partial classes. This feature in .NET 2.0 allows you to supplement the designer generated code with custom code that becomes part of the compiled type, but you do so through a separate code file. This prevents your code from being destroyed if you choose to regenerate the designer generated code. For more information on partial types, see the MSDN library documentation for .NET 2.0.
Another argument that comes up a lot against using DataSets is the assertion that if you are using DataSets in your presentation or business layer, then you are tightly coupling your application to the data tier. This does not have to be the case. First off, you should consider using stored procedures as a layer of decoupling between your actual data tier schema and your application. Your stored procedures can then return and work with result sets that map well to the business entities that you will be manipulating in your application. Additionally, if you need to provide additional decoupling beyond what the stored procedures provide, you can transform data that has been placed into a DataSet into either a different (decoupled) typed data set definition or a custom business entity type in your business or data access layer.
One final thing to point out has to do with returning DataSets from web services. There is a strong argument that you should never return a DataSet from a web service method. Never is a strong word in design, but if you want to support a true service-oriented paradigm with your web services, I would have to agree with this guidance. Typed data sets do introduce both a type coupling and a .NET technology coupling for any clients of that service, which is something you should avoid in a service-oriented world. If your client is a .NET client, it is fairly simple to consume the proxy generated version of the typed data set on the client side, and there is little harm if you are willing to lock yourself into only .NET clients. But in general you should think twice before exposing a DataSet as a type on a web service contract, and favor not doing so.
If you want to achieve maximum productivity in building a .NET application, and can live with the limitations described in this section, then typed data sets are a great capability that you should consider exploiting in your .NET applications where it makes sense. One of the biggest wins in using typed data sets in Visual Studio 2005 is the fact that it also generates a table adapter class, which is like a typed data adapter, for each table in the data set. The designer allows you to very quickly create customized query methods in the table adapter that makes it so you will rarely have to write any ADO.NET code yourself if you are working with typed data sets.
A Working Example
To make things more concrete, let’s step through a simple example of creating a typed data set and using table adapters. To emphasize that nothing that I am showing in this article is particular to SQL Server 2005, I will use the tried-and-true Northwind database with SQL Server 2000 as a data source. If you are working with a machine that does not have SQL Server 2000 on it, you can also get an instance of Northwind installed on SQL Server 2005, including the Express version. You will need to get a data connection configured for the Northwind database in Server Explorer. To do so, right click on the Data Connections node in Server Explorer, select Add Connection, and step through the Add Connection dialog to configure a connection to Northwind.
Unfortunately the Northwind database does not have a good stored procedure layer set up for CRUD (Create, Retrieve, Update, Delete) operations. It also does not have any columns in the tables to help enforce optimistic concurrency efficiently. To do this, you should have a datetime, rowguid, or timestamp column that gets updated on every INSERT or UPDATE, and that can be used to check for concurrency violations when updating or deleting rows. The download code for this article includes a script that you can run against the Northwind database through Query Analyzer or SQL Server Management Studio that will add a Modified datetime column to the Employees table and CRUD stored procedures to work with that table. The sample developed below works against those modifications to implement the table adapter using a good technique for real applications. The stored procedures include SelectEmployees, UpdateEmployees, InsertEmployees, and DeleteEmployees standardized CRUD procedures that I have implemented in a CodeSmith template so that I can generate them for any table I am working with that uses an optimistic concurrency checking column. The CodeSmith template is also included in the download code in case you use that tool for code generation, but for the sample you can just run the included SQL script.
After running the SQL script to modify Northwind as described above, open up Visual Studio 2005 and create a new class library project named NorthwindDataAccess (see Figure 1). You can delete the Class1.cs file that is added to the project by default. Next, right click on the project in Solution Explorer and select Add > New Item… from the context menu. Select the DataSet template type, and name it EmployeesDataSet.xsd as shown in Figure 2. When you do this, you are adding a typed data set definition to your project.
Figure 1: Create a Data Access Class Library Project
Figure 2: Add a Typed Data Set Definition to the Project
Next we will add some table definitions to the data set through the Server Explorer window. Bring up Server Explorer (View > Server Explorer) and expand the Northwind data connection down to the Stored Procedures level. Drag the SelectEmployees stored procedure onto the design surface.
When you do this, you will see a table definition added that has all the same columns as the Employees table from the database. These are defined based on the columns that the SELECT statement in the stored procedure returns. Rename the table from SelectEmployees to just Employees by single clicking on the name in the title bar of the table definition in the designer. Alternatively, you can select the table in the designer and use the Properties window to rename it.
You will also see that at the bottom of the Employees table, something else has been added called EmployeesTableAdapter (see Figure 3). This is the table adapter that I introduced earlier. When you save the data set, Visual Studio will kick in and do some code generation behind the scenes.
Figure 3: Employees Table Definition and Table Adapter
For the simple drag and drop that we just did, Visual Studio created a number of new type definitions. The EmployeesDataSet type is the top level container class for the other data container types we are working with. An EmployeesDataTable was generated that provides a strongly typed collection class for Employee entities. An EmployeesRow class was generated that is a strongly typed entity class with properties exposed for each of the columns in the table. And finally, the EmployeesTableAdapter is generated to allow you to retrieve data into the Employees table using the SelectEmployees stored procedure. In one single drag and drop operation, over 1000 lines of highly functional best-practice ADO.NET code was written for you.
All of these types are defined as partial classes in a designer code file (EmployeesDataSet.Designer.cs in this case) when you save the XSD file. This allows you to add code to any of the classes in a separate partial class definition file that will not be affected if you need to regenerate your code in the future. Regenerating the data set might be necessary if you are iteratively developing the database schema, or if you just goon something up in the designer.
Hooking up the Other Stored Procedures
When you drag a SELECT stored procedure onto the designer, the table adapter only implements the code to fill the generated table. Specifically, it adds a Fill method that takes the specific table type (EmployeesDataTable in this case) as a parameter and fills that instance with the rows returned from the SELECT stored procedure. It also generates a GetData method that will create an instance of the table type, populate it with the rows, and return it from the method as the return value so that you do not have to construct one yourself first.
To hook up the corresponding UPDATE, INSERT, and DELETE stored procedures, there is a simple wizard available through the designer. This wizard is the same one you can use to later modify the table adapter if you want to target different or modified stored procedures.
To bring up the wizard, click on the title bar of the table adapter in the designer to select it, then right click on it and select Configure… from the context menu. This will bring up the Table Adapter Configuration Wizard, which allows you to change the stored procedures that are called by the table adapter. Since the table adapter is already hooked up to the existing SelectEmployees stored procedure, it will start at the step labeled Bind Commands to Existing Stored Procedures (see Figure 4).
Figure 4: Table Adapter Configuration Wizard
To hook up the InsertEmployees, UpdateEmployees, and DeleteEmployees stored procedures, just select them from the drop down lists on the left side of the wizard. The right side of the wizard will show the mapping of stored procedure parameters to columns in the associated table. If your stored procedure parameter names match existing columns in the table, the mappings will all be set up automatically for you, which should always be the case when the table is generated from the stored procedure.
Unfortunately, at least in Visual Studio 2005 Beta 2, the wizard is not perfect and may not match all the parameters correctly. For example, in this sample, the PhotoPath parameter on the UPDATE and INSERT stored procedures gets mapped to the Photo column by mistake. To fix that, you just need to select the correct column in the column mappings as shown in Figure 5. Make sure you do this for both the insert and update stored procs. Hopefully this behavior will be fixed by release.
Figure 5: Correcting the column mappings
Once you have selected the stored procedures, you can simply press the Finish button. The other steps in the wizard are used for configuring other scenarios that I don’t have room to cover here. For more comprehensive coverage of configuring table adapters, see Chapter 2 in my book Data Binding in Windows Forms 2.0. At this point Visual Studio adds Insert, Update, and Delete methods that allow you to perform modifications at the table or row level, as well as overloads that allow you to pass the parameters to the stored procedures explicitly.
With those simple steps, we are all hooked up to retrieve employee data into instances of the EmployeesDataTable collection type and work with them in the middle tier or client layer. You can then pass modified tables, rows, or individual values back down through the table adapter to persist those changes to the database.
If you work directly against the tables in a database by dragging them out instead of dragging out a stored procedure, SELECT, UPDATE, INSERT, and DELETE SQL statements will be written and added to the table adapter for you. Those default queries use optimistic concurrency checking that compares the values of every column in the table to their original values. If you wanted to modify these queries, you could just step into the Table Adapter Configuration Wizard and edit the queries.
Adding Additional Queries to a Table Adapter
You are not stuck with the basic CRUD queries that are generated for you by default for a table adapter. Using the designer, it is easy to add additional ad-hoc queries that can be used for things like retrieving Employees by country.
To do so, right click on the table adapter in the designer and select Add Query… from the context menu. This brings up the TableAdapter Query Configuration Wizard. The first step, shown in Figure 6, allows you to specify whether you will base the new query method on a SQL statement, a new stored procedure, or an existing stored procedure. For the first two choices, you are then led through additional steps that allow you to specify the supporting SQL that will be called by the table adapter. The option to Use existing stored procedures allows you to point to another stored procedure that is already in the target database and hook it up to either return rows, return a single value, or perform update, insert, or delete forms of operations.
Figure 6: Selecting the query command type
We need to create a stored procedure to support retrieving employees by country, so we might as well do it through the wizard. Select the option to Create new stored procedure and press Next. The next step allows you to specify what the query type will be as shown in Figure 7.
Figure 7: Selecting the query type
We are designing a stored procedure that returns rows of employees for a specified country, so the first option is the one we want for this case. Press Next and you are presented with the step that allows you to specify the SQL that the stored procedure will wrap (see Figure 8). You can press the Query Builder button to get a dialog that helps you select the appropriate column names and table names based on the database schema, or you can just type in the query. Type in the SQL code shown in Figure 8.
Figure 8: Specifying the body of the stored procedure
The wizard will infer the parameters for the stored procedure based on the parameters present in the query and the underlying types of the columns. It will also automatically try to map the columns to the corresponding columns in table as was done when configuring the original stored procedure. Any problems will be reported by the wizard at the end and will prevent the creation of the query method in the table adapter. Press Next to move to the step that allows you to specify the name of the stored procedure that will be added to the database to contain the specified SQL (see Figure 9). There is a button in this step that will allow you to preview the full SQL script that is generated to create the stored procedure if you want.
Figure 9: Naming the stored procedure
Press Next to move to the next step, which allows you to specify the name(s) of the corresponding methods that will be added to the table adapter. For a procedure that will return rows, two methods will be created. One takes the table type (EmployeesDataTable in this case) as a parameter, as well as any parameters required by the stored procedure (country in this case). The second method just takes the parameters that are passed to the stored procedure, if any, and returns a populated instance of the table type. The naming convention for these methods is FillByXXX and GetDataByXXX, where XXX is something indicating the criteria that is specified in the parameters (see Figure 10).
Figure 10: Naming the table adapter query methods
Pressing Next just takes you to the final step where the code generation is executed. If you got everything correct, you will just see confirmation of what was performed by the wizard (see Figure 11). If you had a syntax error on your query or other problems occurred in the code generation process, they will be indicated here.
Figure 11: Finishing the wizard
Make sure to save the data set at this point. The wizard is actually adding code (annotations) to the XSD that is the true data set definition. The code generation of the corresponding C# or VB code is not run until you save the data set XSD file.
In addition to adding queries to an existing table adapter, you can add ad-hoc queries to a typed data set definition, not associated with any of its tables. To do so, just right click in an empty area in the designer and you can add a query as described above. Doing so will define methods on a table adapter type that gets named QueriesTableAdapter and simply resides in the data set code generated file.
Going Beyond the Designer
What if you want to use a data reader to populate a custom business entity instead of populating a data table? What if you want to execute some custom validation of a column value when it is changed by client code? Do these requirements mean you need to abandon the typed data set designer? Not necessarily. These things cannot be set up directly in the designer, but are fairly straightforward to add through partial class extensions. If you add a class to your data access layer project, and put the following code in it:
using System; using System.ComponentModel; using System.Data.SqlClient; using System.Data; namespace NorthwindDataAccess.EmployeesDataSetTableAdapters { public partial class EmployeesTableAdapter : Component { public SqlDataReader GetReader() { return Adapter.SelectCommand.ExecuteReader( CommandBehavior.CloseConnection); } } }
You will now have added a method to the table adapter that can execute the SelectCommand on the encapsulated data adapter directly, instead of going through the Fill or GetData methods. You can then do whatever you need to with the internal Connection, Adapter, or Commands properties on the table adapter to access its encapsulated objects.
Likewise, if you wanted to add custom validation of columns, you can just add that validation logic through a partial class extension of the table type itself:
namespace NorthwindDataAccess { public partial class EmployeesDataSet : DataSet { public partial class EmployeesDataTable : DataTable { public override void BeginInit() { this.ColumnChanging += ValidateColumn; } void ValidateColumn(object sender,DataColumnChangeEventArgs e) { if(e.Column.ColumnName == "BirthDate") { if((DateTime)e.ProposedValue < DateTime.Parse("1/1/1900")) { throw new ArgumentException( "Employee‘s productivity is likely to be very low"); } } } } } }
In this code we are actually extending both the typed data set class and the typed data table class, which we have to do since the table type is a nested type inside the typed data set type. Admittedly, this is not as clean as just calling a validation method in the set block of a property definition in a custom entity type, but the net result is the same.
Consuming the Table Adapter Methods
Once you have defined your data access layer through your table adapters, you can consume them with nice clean code, the same as you would if you were writing all the methods yourself:
// Get a collection of business entities EmployeesTableAdapter adapter = new EmployeesTableAdapter(); EmployeesDataSet.EmployeesDataTable employees = adapter.GetDataByCountry("USA"); // Modify one of the items employees[0].BirthDate = DateTime.Parse("1/1/1965"); // Save the changes to the database adapter.Update(employees);
To use the table adapter, you just create an instance of it and call its methods, passing the appropriate parameters, just like you would with a custom data access component. The big difference is that you did not have to write a line of ADO.NET code yourself, and new queries can be added in seconds instead of minutes.
Wrap Up
There is one downside to the way the code is generated for table adapters. The code is injected into the same file as the typed data set definition. This means that the business entity type definitions (the data set, data table, and data row types) are physically coupled to the data access component type definition (the table adapter). This prevents you from being able to factor your business entity definitions into separate class library that is referenced from any consuming layer, while keeping the data access components in their own class library that is only referenced from the business layer. You can work around this by taking the code generated code and moving it into another project, but then you lose the designer support for modifying and maintaining that code. Perhaps this limitation will be addressed in a future version of Visual Studio. But for now, that is not a significant enough limitation to offset the huge productivity boon that the data set designer represents.
DataSets, and the DataSet designer in Visual Studio in particular, include some significant new capabilities in Visual Studio 2005. Typed data sets are quick and easy to create, and provide fully functional, strongly-typed containers for collections of business entity data. In addition to the type definitions for strongly typed tables and rows within the data set, you get a table adapter per table that acts as a data access component encapsulating all the ADO.NET code to work with a particular table in a strongly typed way. You can add custom query methods to the table adapter to support multiple use cases for a particular entity type, or you can add ad-hoc query methods not associated with a particular table.
DataSets are not appropriate for every application, and custom business entities certainly have advantages in many scenarios. But for knocking out data access use cases quickly and effectively, DataSets and table adapters work great for a large number of situations as well.
Download code samples
Authors