DataSet
sIN THIS CHAPTER
DataSet
DataSet
s to Live DataDataSet
DataSet
Using Partial ClassesSo far in this book you have been exposed to many different aspects of data access using ADO.NET. One of the core pieces of ADO.NET is the DataSet
, an in-memory representation of relational data that can contain multiple related tables, each with primary and foreign keys. DataSet
s also let you perform queries on the data contained within them, as well persisting that data in XML format. Using a DataAdapter
, you can synchronize data contained in a DataSet
with data stored in a back-end data source such as SQL Server or Oracle.
This chapter introduces you to a special kind of DataSet
, the typed DataSet
. You will learn the basics of working with typed DataSet
s, as well as how to use functionality new to Visual Studio 2005 and .NET 2.0 to connect those DataSet
s to live data sources using TableAdapter
classes. Finally, you’ll see how to tailor the member and method names of your typed DataSet
s to increase their readability and ease of use as well as how to extend the DataSet
functionality using the new C# 2.0 partial classes.
DataSet
A typed DataSet
is a DataSet
whose data structure is known at compile time, allowing Visual Studio to create strongly typed wrappers for the tables, rows, columns, and relationships contained within the DataSet
.
You can create a typed DataSet
in several ways. The easiest, of course, is to use the designer provided by Visual Studio. The schema (metadata including columns, tables, keys, and relationships) for the typed DataSet
is dictated using a dialect of XML called XSD (XML Schema Definition). You can also create a typed DataSet
by supplying an XSD file as input to the XSD.EXE
command-line tool.
In this section you’ll see the various ways in which you can produce a typed DataSet
and how you can use that DataSet
to work with your data.
DataSet
The format and language of XSD itself is beyond the scope of this chapter. If you want to know all about the XSD standard, you can check out the XML Schema home page at http://www.w3.org/XML/Schema. The important thing to note here is that XML Schema is used for many purposes above and beyond the typing of DataSet
s. XML Schema has numerous uses, and Microsoft has utilized various extensions to the XSD Schema to provide additional features for typed DataSet
s.
Take a look at the following sample schema, which defines a table called Books
that contains columns describing individual titles:
This schema defines the structure of a DataSet
, and you can use the XSD
command-line tool to create a C# class that inherits from System.Data.DataSet
that provides strongly typed members specific to the preceding schema, as shown in the following code:
xsd books.xsd /d /namespace:SAMS.DataSets
This creates a class named SAMS.DataSets.Books
and places it in a file named books.cs
. This class inherits from System.Data.DataSet
, and exposes strongly typed members on each of its rows such as id
, author
, title
, and price
. You don’t need to access members using the indexer notation as in the following example:
myDataSet.Tables["Books"].Rows[0]["author"]
Instead, you can use the far more friendly (and strongly typed) member accessors provided by the SAMS.DataSets.Books
class:
myDataSet.Books[0].author
If you don’t know enough about XSD to create a schema from scratch, you can also use the XSD
tool to infer a schema from an instance document. An instance document is just an XML document that contains data rather than metadata. For example, an instance document that produces the book schema is shown in the following code:
To infer an XSD schema from your source data without having to know all of the intricacies of the XSD language, you can issue the following XSD
command:
xsd books.xml
This will create the books.xsd
file, which you can then use to create a typed DataSet
.
DataSet
Now that you’ve seen how to build a typed DataSet
the “hard” way, this section will walk you through creating one visually, using the Visual Studio designer. It seems as though using raw XSD to create DataSet
s is not as common as it was with the first iterations of the .NET Framework and Visual Studio. In fact, support for viewing the XSD source for a DataSet
created with Visual Studio has been completely removed from the product, you have to open an XSD file as if it were an XML file to get to the source view.
To get started, create any project type you like, but a console project will do just fine for this exercise. Next, right-click the project and choose Add, and then New Item. Select the DataSet
template from the list of options presented to you and give it a suitable name.
You will be presented with a blank design surface onto which you can drag items from the Toolbox or even directly from the Server Explorer. This means that you can accomplish some pretty amazing things just by dragging a table from your source database onto your DataSet
’s design surface.
For now we’ll keep it simple and just drag a DataTable
onto the design surface. You can call it whatever you like. Right-click the table and choose Add, and then Column. You can choose the column name, its data type, whether it autoincrements, and much more. You can then right-click your key column and choose Set Primary Key. The interface looks and feels very much like the table creation screens in the SQL Server Management Studio application for SQL Server 2005.
Create a second table and give it a column that matches the primary key column in the first table. Drag a DataRelation
from the Toolbox onto the design surface, and you can easily and visually set up a parent-child relationship between the two tables, as illustrated in Figure 20.1.
Figure 20.1 A simple DataSet
with a parent table and a child table.
The design surface is an extremely powerful tool because it not only distances you from the low-level XSD generation, but it provides you with a user interface that lets you see the relationships between all of the tables in your DataSet
at a quick glance, as well as the columns in each table.
DataSet
Writing code that uses typed DataSet
s is the easiest part of all. As you saw earlier, with a typed DataSet
you don’t need the string- or ordinal-based member access as shown in the following example:
myDataSet.Tables["Books"].Rows[0]["author"]
Instead, you can access the members in a more user-friendly fashion because the class that derives from DataSet
has exposed strongly typed members:
myDataSet.Books[0].author
After you have added a DataSet
to your project and configured it using the designer, you can create an instance of it and begin working with it just as you would any other DataSet
. Listing 20.1 provides an illustration of how to populate a typed DataSet
using its strongly typed members.
Listing 20.1 Populating a Typed DataSet
Programmatically
For those of us who prefer business objects with meaningful and appropriately typed members over the generic nature of the traditional DataSet
, the typed DataSet
is an extremely powerful tool, providing developers with the power and flexibility of the standard DataSet
coupled with the easy-to-read and easy-to-maintain strongly typed members. Using a typed DataSet
’s strongly typed members, you can actually catch type mismatch errors at compile time that might have otherwise taken hours or days to track down using a standard DataSet
.
DataSet
s to Live DataDataSet
s on their own are a pretty powerful tool, but a DataSet
doesn’t do anyone any good without any data. This section shows you the basics of using a DataAdapter
to populate a typed DataSet
as well as how to use some of the new features of ADO.NET 2.0, such as the TableAdapter
component.
DataSet
Manually Using a DataAdapter
If you are familiar with working with standard DataSet
s and DataAdapter
s, the material in this section should be very familiar to you. A typed DataSet
is really just a class that derives from System.Data.DataSet
in order to provide a developer with a DataSet
that is specific to one schema. As a result of this, any existing code that works on standard DataSet
s will also work on a typed DataSet
, including the DataAdapter
. The code in Listing 20.2 shows that you can use a DataAdapter
to populate a typed DataSet
the same way you would have used one to populate a basic DataSet
.
Listing 20.2 Populating a Typed DataSet
using a Standard DataAdapter
DataSet
s Using TableAdapter
sA TableAdapter
can be thought of as a strongly typed DataAdapter
that specializes in synchronizing one table between a typed DataSet
and the relational back-end database. With previous versions of ADO.NET, Microsoft realized that one of the most common tasks that people performed on DataSet
s was exchanging data between the DataSet
and the database. Using a DataAdapter
such as SqlDataAdapter
can accomplish this, but the regular DataAdapter
s are not designed to work with specific types. When you create a TableAdapter
using the DataSet
designer, that TableAdapter
is designed according to your rules for how data should be loaded into the indicated table.
To create a TableAdapter
, drag one from the Toolbox onto a blank area of the design surface. Though you might initially think so, you can’t drag a TableAdapter
onto an existing table within your DataSet
designer. The reason for this is that the act of dragging a TableAdapter
onto the design surface brings up a wizard that prompts you for information on how the adapter can connect to the database, whether it should use stored procedures or SQL statements, and a host of other options. This also will read the schema information from the database and use that to generate the actual table in the DataSet
.
After being created by the wizard, each TableAdapter
starts off with two very important methods:
Fill
—This method populates only the table for which the adapter was created. When you call this method, the adapter will read the connection string information obtained by the wizard from inside the DataSet
’s XSD file, as well as the query information you indicated, and use that to select data from the database table and place it in the strongly typed DataTable
.GetData
—This method performs the same function as Fill()
, except that instead of filling a destination table, this method returns a new instance of the strongly typed table, prepopulated with the results from the default SELECT
query specified during the TableAdapter
configuration wizard.Figure 20.2 shows the design surface after creating two TableAdapter
s (and, as a result, two strongly typed DataTable
s) by indicating to the configuration wizard the location of the tables in the database and the queries that should be run by the adapter.
Figure 20.2 Connected TableAdapter
s and their corresponding tables.
TableAdapter
s, despite their direct association with a single strongly typed table, do not appear as nested classes within a typed DataSet
. Instead, they show up in their own unique namespace related to the DataSet
itself.
For example, the DataSet
in Figure 20.2 is called ConnectedOrderDataSet
. All of the table adapter classes created for that DataSet
will appear in a namespace called ConnectedOrderDataSetTableAdapters
.
The code in Listing 20.3 shows how to use the Fill()
methods on TableAdapter
s to populate a typed DataSet
with data retrieved directly from the database.
Listing 20.3 Populating a Typed DataSet
with TableAdapter
s
One thing that may seem to be missing from the preceding code is the use of a connection string or a connection at all. This is because when you configured the TableAdapter
using the wizard, you specified the connection to use and you also specified the query to use when retrieving data from the table. Other options you can choose during the wizard include giving the TableAdapter
the ability to directly send INSERT
, UPDATE
, and DELETE
statements to the database.
At the end of the wizard creation, you will see a summary of the actions taken. This typically includes the creation of INSERT
, UPDATE
, DELETE
, and SELECT
statements to be used in synchronizing data between the table and the database. This allows you to do things like change a row, call Update()
on the TableAdapter
, and have the change automatically propagated to the database:
orderData.Orders[0].ShipTo = "Modified User";
orderTa.Update(orderData.Orders);
Unfortunately, some of the queries that the wizard generates on your behalf aren’t the most efficient. You can easily remedy this by supplying your own manually written query to provide the most optimal update experience possible.
Take a look at the screenshot of the Query Builder that is integrated directly into Visual Studio in Figure 20.3. As you can see, the query used for the TableAdapter
’s UpdateCommand
property is pretty inefficient.
Figure 20.3 The QueryBuilder
editing a TableAdapter
’s UpdateCommand
.
The power at your fingertips with the combination of typed DataSet
s, the TableAdapter
, and the integrated Query Builder is pretty impressive. You can visually design and edit all of the SQL statements (or stored procedures) that will be used when updating and querying the underlying data source. You can combine all of this with the new ability of data adapters to update in batches to create some extremely powerful data access mechanisms with just a few lines of C#.
DataSet
One of the limitations of the DataAdapter
is that it only has room for four commands: INSERT
, UPDATE
, SELECT
, and DELETE
. The new TableAdapter
recognizes the fact that there may be five or six different ways in which you want to retrieve data with the same schema. For example, at one point in your application you might want only the orders that belong to a specific user, and at another point in the application you might want just the orders placed on a specific date. At the same time, you want to be able to maintain the parent/child relationship between orders and order items. You can do this by creating additional queries and placing them inside the TableAdapter
.
To add an additional query to an existing TableAdapter
, just right-click the TableAdapter
on the design surface and choose Add Query. This brings up the query configuration wizard, which first asks if you want to create a new SQL statement, a new stored procedure, or attach your query to an existing stored procedure. You don’t have to re-enter any connection information because that is already stored as part of the TableAdapter
itself.
You will see a dialog prompting you to enter a SQL statement that will be used to populate the data table. You can click the Query Builder button to get a more interactive dialog. You can create a parameterized query here, which will, in turn, create a method on the table that accepts that parameter. In this example, the following query was entered into the dialog:
SELECT OrderID, OrderDate, ShipTo, ShipAddress, ShipZip, ShipCity, ShipState
FROM Orders
WHERE (ShipCity = @City)
This query will be used in order to selectively fill the table with orders that were shipped to a specific city. When prompted for the method names for the fill
and getdata
operations, select something that describes the nature of the operation. In this case, FillByCity
and GetDataByCity
are descriptive and appropriate.
After you add the query to your TableAdapter
, you can call that method the same way you would call any other method and pass the filter parameter:
The power of being able to create additional parameterized queries exposed as methods on a TableAdapter
is something that just hasn’t been available to developers until ADO.NET 2.0. The preceding code also demonstrates that the parent-child relationships remain intact when the parent table has been filtered using a custom query.
DataSet
When you create a typed DataSet
, the XSD
tool that generates the C# class provides default names for all of the members, including properties, methods for obtaining child rows, table class names, and row class names. For example, if you were to create a table called Customers
, the default class name for a customer would be CustomersRow
, not Customer
. To access the customer’s order history, you would have to use the GetOrderHistoryRows()
method rather than something more appropriate like GetOrderHistory()
.
You can solve this problem by annotating your DataSet
. In previous versions of Visual Studio, you could access the XSD text for a typed DataSet
directly from within the IDE. For some unknown (and highly aggravating) reason, that feature is no longer present in Visual Studio 2005 by default. To get at the XML view of an XSD file, right-click it in the Solution Explorer and choose Open With and then select the XML Editor option.
When you look at the inside of the XSD file, you will notice that there are a lot of attributes that begin with msprop:Generator_
. These are all customizable annotation attributes. By changing these attributes, you can directly control the names of the classes, members, and methods created when the DataSet
class is built.
Table 20.1 shows the various annotation attributes and what effect they have on the typed DataSet
.
Table 20.1 Annotation Attributes (All Prefixed by msprop:Generator_
Unless Otherwise Specified)
With several of the preceding annotations applied to a typed DataSet
containing customer data with a Customers
table and an OrderHistory
table, you can write code that looks much cleaner and is far more readable than code generated with no annotations:
Because the contents of an XSD file can get fairly bloated, I will only show you the annotated "Customers"
element from the preceding typed DataSet
:
DataSet
Using Partial ClassesAfter you have created your typed DataSet
and configured all of your TableAdapter
s to run all of the queries that you need and annotated it so that all of the members have appropriate names, you still may find that you want to extend the functionality of your typed DataSet
. For example, you may decide that you have several operations that will be performed frequently on the data, such as summing the total price of all orders within the DataSet
. Rather than creating a separate utility class that takes a ConnectedOrderDataSet
as a parameter, you can actually extend the class itself through the use of partial
classes in C#.
To do this, you can either right-click the DataSet
in the Solution Explorer and choose View Code, or you can right-click the design surface of the DataSet
designer and choose View Code. If this is the first time you have done this, you’ll see an empty partial
class implementation like the one that follows:
To add your own custom functionality, all you have to do is add the methods you want, such as the method shown in the following code:
With this method in place, you can call this method on the DataSet
just like any other method:
Console.WriteLine("Total sum of all purchases in DataSet: ${0:00.00}",
orderData.SumAllOrders());
This chapter started with a basic introduction to the typed DataSet
including the various ways in which you can create one. In addition, this chapter covered how to connect a typed DataSet
to live data sources using conventional DataAdapter
s as well as the new TableAdapter
component. Finally, you saw how to extend the functionality already provided by a typed DataSet
by adding additional queries to a TableAdapter
and by adding code to the partial class provided by the typed DataSet
.
After reading this chapter, you should have a firm grasp of how you can use typed DataSet
s in your application to increase productivity, code reuse, and ease of maintenance.
18.216.37.123