DataSets in Microsoft .NET

Introduction

ADO.NET was designed to meet the needs of this new programming model: disconnected data architecture, tight integration with XML, common data representation with the ability to combine data from multiple and varied data sources, and optimized facilities for interacting with a database, all native to the .NET Framework.

DataSets

The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the source of the data it contains. A DataSet represents a complete set of data including the tables that contain, order, and constrain the data, as well as the relationships between the tables.

The DataSet class and the classes contained in DataSet objects-Data­Table, DataColumn, DataRow, Constraint, and DataRelation-reside in the System.Data namespace.

Features of DataSets

1. Working with disconnected Data: The data in DataSet is disconnected from the database. Once you fetch the results of a query into a DataSet using a DataAdapter object, there is no longer a connection between DataSet and the database. Changes you make to the contents of the DataSet will not affect the database. If other users modify data in a database that corresponds to the data in DataSet, you will not see those changes in your DataSet. Working with disconnected data structures definitely has its benefits. The first major benefit of working with disconnected data is that it does not require a live connection to your database. Once you've fetched the results of your query into a DataSet object, you can close the connection to your database and continue to work with the data in your DataSet. Disconnected data structures such as DataSets are also helpful when you build multi-tiered applications. If your application uses business objects running on a middle-tier server to access the database, the business object needs to pass disconnected data structures to the client application. The DataSet object is designed for use in such situations. You can pass the contents of a DataSet from one component to another. The component that receives the data can work with the information as a DataSet (if the component is built using the Microsoft .NET Framework) or as an XML document.
2. Scrolling, Sorting, Searching, and Filtering: The DataSet object lets you examine the contents of any row in your DataSet at any time. You can loop back and forth through the results of your query as often as you like. This makes the DataSet object ideal for scenarios in which your code needs to loop through data, such as in reporting routines. You can also easily build an application that allows a user to scroll back and forth through the results of a query. DataSet objects also let you change the way you view the results of queries. You can sort the data in a DataSet based on a column or a series of columns. You can search for a row of data based on simple search criteria. You can also apply a filter to the data in your DataSet so that only rows that satisfy the desired criteria are visible.
3. Working with Hierarchical Data: DataSet objects are designed to work with hierarchical data. In Chapter 2, we used the Data Form Wizard to build a simple Microsoft Windows application that lets us retrieve information from two tables-customers and orders. When you ran the application, the form that the wizard built lets you scroll through the customer data. As you moved from one customer to the next, the form displayed only the orders for the current customer. The DataSet object lets you define relationships between the tables of data stored in the DataSet. The Data Form Wizard used the input you provided to build such a relationship. The wizard then bound a DataGrid to the relationship to show only the orders for the current customer. (We'll take a closer look at the DataRelation object in the next chapter.)
4. Caching Changes: Working with read-only data is easy. One of the biggest challenges in building a database application is to transform the user's input into changes to the contents of your database. Building such logic into a multi-tiered application can present an even greater challenge if your application needs to cache changes and submit them to your database all at once. The DataSet object lets you cache changes to a row of data so that you can submit the changes to your database using a DataAdapter. You can also examine modified rows in your DataSet to determine how the row has changed (inserted, modified, or deleted) as well as to compare both the original and current values for each row.
5. XML Integration: The ADO.NET DataSet was built from the ground up to work with XML. You can save and load the contents of a DataSet to and from files as XML documents. The DataSet also lets you separate the schema information (table, column, and constraint information) into an XML schema file. In ADO.NET, DataSet objects and XML documents are almost interchangeable. It's easy to move from one data structure to the other. This duality allows developers to use the interfaces they're most comfortable with. XML programmers can work with DataSet objects as XML documents, and database programmers can work with XML documents as DataSet objects.
6. Uniform Functionality: Developers who have worked with ADO might be aware that the Recordset object has features similar to those of the DataSet. The ADO Recordset object supports features such as filtering, searching, sorting, and caching updates. However, the manner in which you open a Recordset plays a large part in determining what functionality is available in the Recordset.

For example, if you use just the default settings for the ADO Recordset and Connection objects, you cannot get an accurate count of the number of rows in the Recordset. The Recordset object has a Supports method that developers often use to determine the functionality available: Can I modify the contents of the Recordset? If I update a row, will the Recordset send the change to the database immediately or will it be cached? Can I bind my Recordset to a grid? Can I move to the previous row?

The reason that not all Recordset objects support the same functionality is that the Recordset object tries to be everything to everyone. Whether you're working with a firehose cursor, a server-side cursor, or disconnected data in ADO, you're using a Recordset object.

Creating a DataSet

Using VB.Net

Dim ds As New DataSet("DataSetName")
Console.WriteLine(ds.DataSetName)

Using C#.Net

DataSet ds = new DataSet("DataSetName");
Console.WriteLine(ds.DataSetName);

Filling the DataSet Object with data from the database Filling the data from the database into a dataset object is a very easy process. Here we can use either an SQL query or a stored procedure. Below is an example of how to fill data using a query.

Using VB.Net

Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
          "Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " & _
         "FROM Customers"
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim ds As New DataSet
da.Fill(ds, "Customers")

Using C#.Net

string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT CustomerID, CompanyName, ContactName, Phone " +
"FROM Customers";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");

Validating data in DataSet Databases offers different mechanisms that you can use to ensure that the data in your database is valid. The sample Northwind database has many rules and constraints defined. The CustomerID column in the Customers table must be populated with a string of up to five characters, and that value must be unique within the table. The Orders table generates a new OrderID value for each row and requires that the CustomerID value for each row refer to an existing entry in the Customers table.

Sometimes you'll want to apply similar rules to validate data in your application before submitting changes to your database. For example, let's say you're shopping online and reach the page where you purchase the items in your basket. Most Websites will make sure you've entered information into each of the required fields before they submit your order information to the appropriate database.

This type of logic might seem redundant because the database probably has similar validation rules defined. However, adding validation rules to your application can improve its performance. If a user fails to enter a credit card number, either by accident or in the hope that the system programmers were extremely lazy, the code for the Web page can easily determine that it can't successfully submit the order without having to contact the database. The other benefits of this approach are a slight reduction of network traffic and a lighter load on your database.

The ADO.NET DataSet offers many of the same data validation mechanisms available in database systems. You can separate these validation mechanisms, also called constraints, into two categories—column-level restrictions and table-level restrictions.

Validation properties of the DataColumn

The DataColumn object exposes a number of properties that you can use to validate your data.

1. ReadOnly: The simplest way to ensure that your data is valid is to not let users modify it. If you want to make the data in a DataColumn read-only, set the Read­Only property of the DataColumn to True.
2. AllowDBNull: Some database columns require values, while others accept empty, or null, values. The DataColumn object exposes an AllowDBNull property that you can set to control whether the column in your DataSet accepts null values.
3. MaxLength: Many databases place restrictions on the size of a string in a column. In the Customers table, for example, the CustomerID column accepts a string of up to 5 characters and the CompanyName column accepts up to 40 characters. You can place similar restrictions on a DataColumn using the MaxLength property.
4. Unique: The DataColumn lets you specify which values in a column are unique using the Unique property. When you set this property to True on a DataColumn, ADO.NET will examine the value stored in this column of each row in your DataTable. If you add or modify a row in your DataTable to create a duplicate value in a unique column, ADO.NET will throw a ConstraintException.

The DataTable object's constraints collection

You can also validate data in your DataSet by setting the properties of the Data­Table object. The ADO.NET object model includes two classes that you can use to define constraints in a DataTable. These classes, UniqueConstraint and Foreign­KeyConstraint, are derived from the Constraint class. The DataTable exposes a Constraints property that you can use to add to, modify, or examine the constraints on the DataTable.

1. UniqueConstraints: If you set the Unique property of a DataColumn to True, you've defined a unique constraint in the DataTable that contains that column. At the same time, you've also added a UniqueConstraint object to the Data­Table object's Constraints collection. Setting the Unique property of a Data­Column is simpler than creating a new UniqueConstraint in a DataTable object's Constraints collection. However, there are times when you'll want to explicitly create a UniqueConstraint, such as when you need to make sure that the combinations of values from multiple columns are unique.
2. PrimaryKey: A primary key is a special type of unique constraint. The ADO.NET DataRowCollection object has a Find method that you can use to locate a row in your DataTable by the value or values in its primary key column, as shown here.

   row = MyTable.loc["RAGS"]
   

   A DataTable can have multiple unique constraints but can contain at most one primary key. You can set or examine a DataTable object's primary key using its PrimaryKey property.
3. ForeignKeyConstraint: You can also add foreign constraints to a DataTable. I described an example of a foreign key constraint just a couple of pages back. Each order in the Northwind database's Orders table must have a value for its CustomerID column that is used in the Customers table. You can place similar restrictions on the data in your DataSet by creating a ForeignKeyConstraint and adding it to the table whose rows you want to validate.
   You generally won't need to explicitly create a ForeignKeyConstraint. Creating a DataRelation between two DataTable objects within your DataSet creates a ForeignKeyConstraint in the process. In the next chapter, I'll discuss the DataRelation object and how you can use it to work with relational data.

Creating Dataset at design time

Right-click on your project and add a new DataSet .xsd file to your project using the Add New Item dialog box.

Be sure to select the DataSet template in the dialog box rather than XML Schema. Selecting both items will add a new .xsd file to your project and launch the XML schema designer. However, DataSet schema files have an additional attribute set in their schema that Visual Studio .NET looks for to determine whether to treat the file as a standard XML schema or as a DataSet.

Once you've added a new DataSet to your project, you'll see the same designer that you saw when we generated a DataSet based on DataAdapter objects, except that your new DataSet will be empty. Let's add a new DataTable to the Dataset. The next figure shows the shortcut (context) menu that appears when you right-click on the designer. Choose Add and then New Element from the shortcut menu to add a new DataTable to the DataSet. You can also choose the same command from the Schema menu.

Once you have a new DataTable, you'll want to add some DataColumn objects. Click on the leftmost cell in the first row in the box that represents your new DataTable in the XML schema designer. This should make an arrow appear just to the right of that cell. Click on the arrow and you'll see a list, similar to the one shown in the next figure, of available items you can add to the DataTable. To add a new DataColumn, select Element from the list and then specify the name for your new DataColumn. The text you enter will be stored in the DataColumn object's ColumnName property.

To set other properties on a DataColumn, select the desired column in the XML schema designer. The next figure shows part of the list of available properties that appears in the Properties window when you select a column in the XML schema designer. You'll notice that some of the properties are specific to the DataColumn and others are more applicable to XML elements. Some properties that were added to the ADO.NET object model late in the development cycle, such as the DataColumn object's MaxLength property, are not available through this interface.

Once you've created your new DataSet with the appropriate structure, save your changes and close the XML schema designer. You've now created a DataSet schema file in your project. To add an instance of your DataSet to a designer, select DataSet on the Data tab of the Visual Studio .NET Toolbox just as you would if you wanted to add another component, such as a button, to a designer. Double-click on the Toolbox item, or drag-and-drop the item onto the designer or its components tray. Either of these actions will launch the Add Dataset dialog box, shown in the Next Figure.

Now you are ready to use your dataset. In detail, operations that can be performed using Dataset and Typed Dataset will be covered in the next part of this article.