Here is a quick tutorial on how the dataset generator works in Visual Studio 2005.
To get the most from the system, use the Data Sources wizard--in the top line Data menu,
click on the "Add New Data Source" option. The wizard begins by selecting or
creating a connection. Select the server and the NorthWind database. The next screen
shows a list of all the objects in the database:
Expand the tree and select the objects you wish to represent in the dataset. Usually,
tables will be selected, but views and stored procedures may be selected as well. Be
sure to give the dataset a meaningful name--usually the business object name.
Completing this wizard will create a typed dataset in your project. In this article,
a multi-tiered application is the goal so the dataset must be created in (or moved to)
a separate Class Library type of "Business Layer" project. Open the new
dataset from the Solution Explorer (has an ".xsd" extension). The visual
editor will show a representation of the two entities and their relationship.
More tables or objects can be added by dragging and dropping from the Server explorer.
Entity names and field names may be edited, added, or deleted as necessary using the
properties and the contextual menus.
On the bottom of each entity is the table adapter used to associate the entity with
the persisted table in the data store. By default, the Fill (an existing table in
the dataset), GetData (returns a data table object), and if possible, the Update
methods are added automatically. (Update handles insert, update, and delete functions.)
The queries that link the table data to the entity data can be edited by right
clicking on the adapter and selecting "Configure.." to launch a wizard.
The first screen shows the SQL statement to select the data. The SQL can be edited
directly here or click the "Query Builder…" button to visually edit the SQL.
The four part editor shows the table(s) in the top pane, a list of fields with
sorting and filtering in the second pane, the generated (also editable) SQL in
the third pane, and after clicking the "Execute Query" button, the
results of the query in the bottom pane.
Back on the Wizard, the "Advanced Options" button allows the update
functions to be edited. Checkboxes allow the update function to be deselected
(for lookup tables) saving generated lines of code, using optimistic concurrency
(deselecting this will save a few lines of generated code), and automatic table
refresh after update.
The next screen of the wizard allows the user to select and edit the names of
the three different types of methods created to accomplish the CRUD.
The last screen shows a summary of all the classes and methods that are generated
by the dataset.
From the dataset editor, you may also right click the table adapter and choose
to add a new query for the entity to select data in a different manner by using
parameters. The results of the query should return exactly the same fields, but
can join several tables and require parameters to filter data. This is a good
way to provide a fill by relation functionality to only select child records to
match the record(s) selected in the parent table.
What Was Generated?
Here is a look into the Object Browser to see what Visual Studio generated for us.
In the dataset namespace is the dataset itself, a typed data table to contain
the collection of rows for each table, a typed row object for each table to
expose a single instance of each row, and the event handlers. In the same file,
but in a separate namespace is the table adapter for each table that is persisted
in the database.
The type dataset inherits from System.Data.DataSet for a good deal of its
functionality, but also adds a lot of functionality from generated code.
Other than initialization and serialization methods, it has collection of table,
a collection of relations, and properties to return the typed version of each data
table.
The typed data table contains methods and events for changing and deleting a row,
and data columns for each field in the data table.
Of note are the AddCustomerRow (two different overloads), and FindByCustomerID
(the primary key of the table) methods which are very useful.
The typed data row has another set of properties and methods.
There is a typed property for each column that allows access to the field data.
.NET has a problem handling null data, so several methods are added for each
nullable field to check for null and to set to null.
The table adapters do not have a base type to inherit from except for component,
so all the functionality must be generated into methods.
There are methods to Fill an existing data table, return a data table with
GetData, and several overloads for the Update method. Several methods like
Insert and Delete take each of the fields as parameters to add a new record
and delete a record with concurrence.
The command collection contains the SQL to select, insert, update, and
delete records, and any special queries.
What Is Missing?
In a tiered architecture, each tier can only access the tier either above or
below it. The data access is handled by the table adapters and should not be
called by the presentation layer. The business layer should expose methods to
retrieve and update data. Specifically, each entity in the business object
should have the methods necessary for persisting its data. These methods
need to be added to the data table objects that contain the entity. Basically
it is just a wrapper to instantiate the table adapter and call the appropriate
method on it. Each data table needs a method to Fill itself and Update any edits
and if needed, a method for wrapping the GetData function to return a data table.
The second missing feature is the ability to modify the SQL at runtime to filter
the records returned. The best way to do this is to use the visual editor to
create select queries as necessary to filter the data before it is loaded. But
there are times when you need to change the SQL on the fly for complex relational
data retrieval.
Part 2 of this article will cover how to implement these missing features to
complete a great ORM tool.
