Extending a Table Adapter
When you design a business and data access layer, you basically map entities and relationships to database tables and columns. The expected behavior of each entity is typically represented as a sequence of statements to execute against the database. How would you represent the behavior of an entity? Data access design patterns provide some guidance on this; rest assured that whatever patterns suggested are to be implemented with concrete code. The CommandCollection
property of a table adapter is the internal data structure that lists the statements used to hard-code an expected behavior on a table.
|Figure 4: Tasks to extend and customize a table adapter.|
So if you want to add a behavior to the table that represents the Customers table, you need to add a new T-SQL statement to the CommandCollection
property and then make it public via a new method on the CustomersTableAdapter class. Visual Studio 2005 provides a simple wizard to do all of this. You trigger the wizard by choosing one of the tasks defined on the table adapter designer, as in Figure 4
When you select a table adapter component in Visual Studio 2005, it displays a smart tag. After clicking it, Visual Studio displays a menu as in the figure. If you select to edit queries, you get a graphical representation of all the tables in the current data set component (see Figure 5
|Figure 5: Table relationships and table adapters in the Visual Studio 2005 Data Designer.|
To edit an existing query, you select the task in the table adapter task list and edit the properties in the Properties window. To add a new task, you select Add Query and follow the instructions of the subsequent wizard. The wizard ultimately guides you through the steps required to define a new command, be it a query or an update statement. When you're done, you see an additional entry in the task list and some changes to the underlying code.
Suppose that you define a new query statement to load customers by country. The final statement is similar to the following:
SELECT ... FROM customers WHERE country=@country
The wizard asks you to name the methods that will use the statement to retrieve data (GetDataXXX
) and fill the passed data table (FillXXX
). You can also choose to generate only one of two methods.
Let's name the methods GetDataByCountry
(see Figure 6
method is modified to make room for another command string. The implementation of the two new methods sets up the SelectCommand
statement of the adapter with the command extracted from the command collection and executes it. Listing 4
shows the source code of the FillByCountry
Remarks on the Generated Code
|Figure 6: New tasks added to the table adapter.|
It is essential to note that the code generated around the DataSet and BindingSource components use the disconnected model and plain T-SQL statements. It goes without saying that using stored procedures and transactions is definitely possible, whether you edit the code through Visual Studio 2005 wizards or manually.
The disconnected model entails that all the data is assumed to be in memory and loaded when the form starts up. Also in this case, though, you can freely edit the auto-generated code, for example to make the application load only the data it needs at that time (lazy loading). By default, the form's Load
event of a data-bound form using the code generated by the Visual Studio 2005 data designer looks like this:
Sub NorthwindForm_Load(ByVal sender As Object, _
ByVal e As EventArgs) Handles MyBase.Load
Visual Studio 2005 places a call to Fill
for each table adapter found in the bound DataSet component.
Be aware that Visual Studio 2005 generates code that will be compiled with the rest of the project. In no way, should you accept this code as-is if it doesn't completely fit your needs and requirements. You can modify it at will; before you do so, though, it is highly recommended that you deeply understand its logic and structure. Do not think that what a wizard does for you is a take-it-or-leave-it choice. You can take it, and then adapt it to your needs. Most of the time this is just what you have to do and the most sensible option.
Common Patterns and Approaches for a DAL
Any reasonably complex system requires a couple of distinct layers to access and manipulate data. You use the Business Logic Layer (BLL) to communicate with the user interface and provide security checks, data validation, as well as additional services such as pre- and post- processing of data. You use the Data Access Layer (DAL) to access and retrieval data. The DAL is the only component that incorporates the API to access the database. CRUD tasks are the goal of a DAL, exposing data to the business layer for data exchange. Layers pile up; the DAL is consumed by the BLL and should not be consumed by the user interface to avoid breaking separation between layers.
When it comes to creating a library to incorporate data access functionality, how should you proceed? Should you use a customized table-based approach like that propounded by the Visual Studio 2005 data designer? Or are you better off generating most of the code using a commercial O/R mapper tool? Should you craft your DAL yourself? Nicely enough, these are not mutually exclusive options and can be mixed together in any doses and combination. So the question arises, what should you do? Let's understand common patterns. In doing so, you should also be able to spot the method and logic behind the Visual Studio 2005 approach.
In general, there are two main models for designing the backend of a system that deals with datathe Domain model and the Table model.
The Domain model prefigures an object model where each entity is described through ad hoc classes that incorporate both behavior and data. Any instance of a class corresponds to a particular entity in the model; relationships are expressed through properties that cross over involved classes.
In the Table model, you define one class for each table existing in the target database. Any code required to process data is defined on a single class instance. In summary, if your data model includes an Order entity, you end up with one OrderEntity object for each order with the Domain model and one OrderManager object to handle all orders with the Table model. It's easy to see that the "order-manager" object here is a close relative of the table adapter object in the Visual Studio 2005 designer.
Inside the domain and table abstract models you find various concrete design patterns. The most popular of which are Data Mapper and Table Data Gateway (TDG) respectively.