The DataSet ADO.NET revolves around the DataSet. This object is a completely new concept that replaces the traditional Recordset in ADO. A Recordset provided methods that allowed you to retrieve and display database rows, or records. A Recordset was very helpful when you needed to return data, but suffered from some limitations. Specifically, its representation of the data was fairly simple: It couldn't contain more than one set of data, and didn't contain information on the relationships between data. The DataSet is a simple, memory-resident data store that provides a consistent programming model for accessing data, no matter what type of data it contains. Unlike a Recordset, the DataSet contains complete sets of data, including constraints, relationships, and even multiple tables at once. Figure 9.2 shows a high-level view of the DataSet object model. Figure 9.2. The DataSet object model.
Imagine a box with several compartments. In each compartment, you can put any object you like as long as it fits in the box. You can see and manipulate each object in the box take it out, add more, just look at it, and so on. That's what the DataSet is, essentially. When you establish a connection to the database, you hand it a box and tell the data store to fill it with some stuff. You can fill it with tables of data, your own data from elsewhere, other objects any data you like. No matter what objects you put inside, the box will allow you to do the same things with each object, such as view, add, delete, and so on. Oh, and your box is dynamic, so it will expand or shrink depending on how many objects are in it! Figure 9.2 includes a DataTable object, which represents a single database table. (The DataSet maintains a collection of these tables in the TablesCollection object.) The DataTable completely represents the corresponding table, including its relationships and key constraints. It contains two other collections, Rows and Columns, which represent the data and schema of the tables, respectively. Now imagine your box again. Each compartment is a DataTable. The box now has an LCD panel on the outside that automatically lists the objects inside it. (Wouldn't that be great for a refrigerator?) Your box is becoming pretty functional! Figure 9.3 illustrates this box object. Figure 9.3. Your DataSet box model.
The RelationsCollection object allows you to navigate between tables by the relationships defined on them. It's no longer necessary to use complex joins and unions (the old way of relating tables) in your SQL queries because ADO.NET makes it much easier. The actual relationships are represented by DataRelation objects, which contain information on the two tables being joined, the primary and foreign key relationships, and the name of the relationships. You can also add relationships using the DataRelation object. ADO.NET automatically enforces key constraints as well it won't allow you to change one table in a way that would violate the relationship to the other table. Once again, your box is enhanced. You can now tell it which objects are related and how they're related. For example, let's say you have an apple and an orange, and you tell the box that the two are related because they're both fruit. The next time you see the box, you can say, "Show me fruit," and it will list the apple and the orange. Then suppose you try to add a carrot, and you tell the box that the carrot is also related to apples and oranges. The box will reject this because a carrot is a vegetable it violates the rules of the relationship. The ExtendedProperties object contains any additional information, such as user names and passwords. The example of fruit in a box is a bit simplistic, but it explains the concepts of the DataSet. It cares nothing about where the data came from or where it's going it's completely separate from any data store. Therefore, you can use the DataSet as a standalone entity. The next section will explain in more detail how your DataSet actually functions. Using the DataSet There are two ways to use a DataSet: fill it with data from an existing data store, or create your own empty DataSet from scratch. First, let's take a look at creating your own DataSet. Since the DataSet is a data store that resides in memory, you can create your own programmatically and add tables to it. This can be a very helpful feature if you just want to use dynamic data in your ASP.NET applications without having to deal with databases directly. Listing 9.1 shows an example. Listing 9.1 Creating a DataSet Programmatically 1: 'create an empty dataset 2: dim ds as new DataSet("MyDataSet") 3: 4: 'create a new table and columns 5: dim dTable as New DataTable("Users") 6: dTable.Columns.Add("FirstName", System.Type. _ 7: GetType("System.String")) 8: dTable.Columns.Add("LastName", System.Type. _ 9: GetType("System.String")) 10: dTable.Columns.Add("UserID", System.Type. _ 11: GetType("System.Int32")) 12: dTable.Columns("UserID").AutoIncrement = true 13: 14: 'add table 15: ds.Tables.Add(dTable) 16: 17: 'define primary key 18: dim keys() as DataColumn = {ds.Tables("Users"). _ 19: Columns("UserID")} 20: ds.Tables("Users").PrimaryKey = keys 21: 22: 'add a sample row 23: dim dr as DataRow = dTable.NewRow() 24: dr(0) = "Chris" 25: dr(1) = "Payne" 26: dTable.Rows.Add(dr)  | Listing 9.1 creates a DataSet, adds to it a single table, and fills it with some data. You don't even need an actual database you can use this DataSet anywhere in your ASP.NET pages now. (Note that the System.Data namespace is required for the objects used in this listing.) |
Line 2 instantiates a new DataSet named "MyDataSet". You then create a new table, "Users", on line 5, and add three columns: "FirstName", "LastName", and "UserID". The Add method takes two parameters the name of the new column and the type of the column. Use the GetType method to return a type representing the object in question, as shown on lines 7, 9, and 11. Line 12 sets the AutoIncrement property of the "UserID" field to true so that this column will automatically set values when a new column is added. This guarantees you a unique identifier for each row. Line 15 adds the new table to the DataSet's tables collection. The PrimaryKey property on line 20 takes an array of DataColumn objects so that you can define keys over multiple columns to specify a unique record. For instance, in the user database example, a first name might not be unique but a first name/last name combination might be. You can define an array of these two DataColumn objects and set the primary key. Luckily, you already have a unique identity field?TT>"UserID" so you set up an array consisting of only this column on line 18 and set the primary key to this array. You can now use this DataSet to store data. You can even write this data to an actual file or insert it into a database if you want. Line 23 creates a new row to hold values, while lines 24?5 assign the value "Chris" to the "FirstName" field and the value "Payne" to the "LastName" field. You don't need to set a value for the "UserID" column because the DataSet will do that automatically. Finally, you simply add this row to your table on line 26. The method for adding each item in a DataSet is generally one of the two shown in Table 9.1. Table 9.1. Processes for Adding Objects to a DataSet | Method 1: | Method 2: | Instantiate a new item. Set the item's properties. | Add a new item to the DataSet. Access the item through the DataSet object model to set its properties. Add the item to the DataSet. | Here's an example of method 1: dim dTable as New DataTable("Users") 'set properties for dTable ' or add columns to dTable dTable.MinimumCapacity = 25 ... ds.Tables.Add(dTable) Here's an example of method 2: ds.Tables.Add(new DataTable("Users") 'set properties for dTable ' or add columns to dTable ds.Tables("Users").MinimumCapacity = 25 Neither method results in a distinct performance benefit. The first method has better readability, while the second is more compact. You can then access each of the DataSet's properties and values by using the familiar "dot" syntax. To refer to a specific column or row, you can use the following: ds.Tables(tablename).Columns(column index or name) ds.Tables(tablename).Rows(row index) This provides a very powerful mechanism for interacting with the DataSet. Similarly, you can access or change a particular field value with the following: strValue = ds.Tables(tablename).Rows(row index)(field name). ToString: ds.Tables(tablename).Rows(row index)(field name) = strValue Relationships Now let's assume you've created two tables in this manner and you want to relate them. The first table is your user table, and the second table, "Books", holds lists of books that each person has read. These tables are related by the "UserID" of each user. Assuming both tables have a "UserID" field, Listing 9.2 shows some more code. Listing 9.2 Creating Relationships with a DataSet 1: 'create a new relation 2: dim dr as DataRelation = New DataRelation("UserBooks", _ 3: ds.Tables("Users").Columns("UserID"), _ 4: ds.Tables("Books").Columns("UserID")) 5: 6: 'add the relation 7: ds.Relations.Add(dr) | You create a new relation by specifying the relation name?TT>"UserBooks" in this case and the column from each table that should make up the relation. This relation allows you to navigate between the two tables via the linked columns. |
Tip You can also create relations over more than one column in each table. Simply pass arrays of DataColumn objects for the second two parameters. For example, the following code creates relations between the UserID and FirstName fields in both Users and Books tables: dim dcUsers(2) as DataColumn dcUsers(0) = ds.Tables("Users").Columns("UserID") dcUsers(1) = ds.Tables("Users").Columns("FirstName") dim dcBooks(2) as DataColumn dcBooks(0) = ds.Tables("Books").Columns("UserID") dcBooks(1) = ds.Tables("Books").Columns("FirstName") dim dr as DataRelation = New DataRelation("UserBooks", _ dcUsers, dcBooks) ds.Relations.Add(dr) Note, however, that when you specify arrays of DataColumns, both of the second parameters must be arrays of the same size.
Filling DataSets Now that you know what a DataSet is and what you can do with it, let's fill it with some data from the database you created yesterday. There are a lot of new objects introduced here that I won't get to until tomorrow, so don't worry if you don't understand all the code. Listing 9.3 shows an example of filling a DataSet in an ASP.NET page with the results returned from an SQL query. Listing 9.3 Retrieving Database Rows with a DataSet 1: <%@ Page Language="VB" %> 2: <%@ Import Namespace="System.Data" %> 3: <%@ Import Namespace="System.Data.OleDB" %> 4: 5: <script runat="server"> 6: sub Page_Load(Sender as Object, e as EventArgs) 7: 'set up connection 8: dim myConnection as new OleDbConnection _ 9: ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ 10: "Data Source=c:\ASPNET\data\banking.mdb") 11: 12: 'open connection 13: dim myCommand as new OleDbDataAdapter _ 14: ("select * from tblUsers", myConnection) 15: 16: 'fill dataset 17: dim ds as DataSet = new DataSet() 18: myCommand.Fill(ds, "tblUsers") 19: end sub 20: </script> 21: 22: <html><body> 23: </body></html> | On lines 2 and 3, you see the two new namespaces that you have to import when filling DataSets this way. (Actually, only System.Data is required for the DataSet, but System.Data.OleDb provides some additional helper objects.) All the action happens in the Page_Load event here. Again, don't worry if you don't recognize the objects or commands I'll discuss them in detail tomorrow. |
On lines 8?0, you set up a connection to the database with an OleDbConnection object. This object tells ASP.NET where to go to get the data it needs; specifically, the banking database you created yesterday. On line 13, you create a new OleDbDataAdapter object, which is used to execute SQL statements in ASP.NET. This object takes as parameters a SQL statement, and the OleDbConnection object you created on line 8. The SQL statement here simply retrieves all the data stored in the tblUsers table. On line 17, you create a new DataSet, which should look familiar. On line 18, you use the Fill method of the OleDbDataAdapter object to fill the DataSet with the data retrieved by the SQL query. The second parameter specifies in which table in the DataSet the data adapter should place the results. Note that you didn't explicitly create this table the Fill method does it for you automatically! You can now manipulate the data in this DataSet, as well as all of the other properties we learned about earlier today. In addition, you can now bind the data in the DataSet to controls in your ASP.NET page. Data binding is discussed in the next section. Filling a DataSet with data from a database is even easier than creating the DataSet yourself. You needed only four lines of code here to retrieve the data, and only a couple more to display it, as you'll see in the next section. | 
