| One of the big advances in ADO.NET is the introduction of a new class called the DataSet . In a multi-tier application, data can extracted from a source database, stored in a DataSet, and passed among the tiers. A DataSet is more powerful than an ADO Recordset because it can contain multiple tables and be aware of the relationships between them. It is never connected to an underlying database, making it ideal for disconnected data applications. In a sense, the DataSet is almost like a portable database unto itself. With all this power comes added complexity, so if you are used to previous data structures (such as the ADO Recordset) you may have to learn a few new things. In this section, we will show you how to get records from a database into a DataSet object, modify them, and finally send changes from a DataSet to a database. Introducing the Data Adapter As we just mentioned, a DataSet does not have an underlying database associated with it. In order to transfer records to and from a DataSet, you use one of the Data Adapter classes. A data adapter is like a bridge or conduit between a data source and a DataSet object. (This is different from the command classes we discussed in the last section, which behaved like functions that returned records.) A DataAdapter has several properties that represent individual command objects: SelectCommand, UpdateCommand, InsertCommand, and so on. When you pass data through a data adapter, it uses these commands to transfer records to and from the source database. The primary concern of a data adapter is how data is mapped to and from a source database. As we cover the DataSet class in the sections that follow, we will also describe the SQLDataAdapter in more detail. Filling a DataSet To retrieve records from a database and populate a DataSet, you use the Fill method of a data adapter object. The following lines of code show a simple transfer of some fields from the Person table to a DataSet object: Dim cn As SqlConnection Dim cmdGetPeople As SqlCommand Dim strSQL As String Dim adPerson As SqlDataAdapter Dim dsResults As DataSet 'INITIALIZE OBJECTS cn = New SqlConnection("server=BrianPC;uid=test;pwd=xyz;database=Test") adPerson = New SqlDataAdapter() dsResults = New DataSet() 'SET UP COMMAND strSQL = "SELECT SSN, LastName, FirstName, Age FROM Person" cmdGetPeople = New SqlCommand(strSQL, cn) adPerson.SelectCommand = cmdGetPeople 'FILL DATASET cn.Open() adPerson.Fill(dsResults) cn.Close() The previous code invokes the Fill method of the data adapter, passing a DataSet object, dsResults, as a parameter. The Fill method uses the SelectCommand (which is an ordinary SQL command, discussed earlier) to retrieve records from a database. Accessing Field Values Accessing individual records in populated DataSet is different than in a DataReader, because there is no current record or associated movement method. Instead, collections are used to organize tables, rows, and field values. The following line of code would display the contents of the first field in the first record of the first table: Messagebox.Show(dsResults.Tables(0).Rows(0).Item(0).ToString) As with any collection hierarchy, you can make navigating easier by using named identifiers, loops, and the With statement: With dsResults.Tables(0) For i = 0 To .Rows.Count - 1 Debug.WriteLine(.Rows(i).Item("LastName")) Next End With The sample code prints the LastNamefield for each row to the Output window. Loading Multiple Tables Filling a DataSet object with a single table is relatively easy. (As we will see in a moment, the DataTable object can also be created independently of a DataSet if you need it.) However, to really take advantage of the power of a DataSet, you can put data from two or more tables in the same DataSet and establish relationships between the tables. To accomplish this, you could take one of the following approaches: -
Call the Fill method on multiple data adapter objects using the same DataSet as an argument. -
Execute a stored procedure with more than one SELECT statement. In this case, only a single SQLDataAdapter object is necessary.
By using either of these techniques, you can cause data from different databases or tables to be included in the same DataSet. Using Multiple Commands The code shown in Listing 22.4 puts records from both the Employee and Person tables into the dsResultDataSet. Listing 22.4 COMMANDEXAMPLES.ZIP Filling a DataSet Dim cn As SqlConnection Dim cmdGetPeople As SqlCommand Dim cmdGetEmployee As SqlCommand Dim strSQL As String Dim adPerson As SqlDataAdapter Dim adEmployee As SqlDataAdapter Dim dsResults As DataSet 'INITIALIZE OBJECTS cn = New SqlConnection("server=bsw2k;uid=sa;pwd=;database=BrianTest") adPerson = New SqlDataAdapter() adEmployee = New SqlDataAdapter() dsResults = New DataSet() 'SET UP COMMANDS strSQL = "SELECT SSN, LastName, FirstName, Age FROM Person" cmdGetPeople = New SqlCommand(strSQL, cn) strSQL = "SELECT SSN, Dept, Salary, HireDate FROM Employee" cmdGetEmployee = New SqlCommand(strSQL, cn) adPerson.SelectCommand = cmdGetPeople adPerson.TableMappings.Add("Table", "Person") adEmployee.SelectCommand = cmdGetEmployee adEmployee.TableMappings.Add("Table", "Employee") 'FILL DATASET cn.Open() adPerson.Fill(dsResults) adEmployee.Fill(dsResults) cn.Close() Listing 22.4 also introduces the concept of mappings. A mapping tells the data adapter which DataSet fields and tables to use when filling the DataSet. In other words, you can use a mapping to name a field differently in your DataSet than in the original source database. The mapping in Listing 22.4 is a very high-level mapping; it just lets the data adapter know the name of the destination tables in the DataSet. Note To learn more about detailed table and column mappings, see the help file topic "DataTableMapping Class."
Note Adding a table mapping allows you to access a table by its name within the DataSet.
The Add method of the data adapter's TableMappings collection accepts a source and destination table name. From the data adapter's point of view, every result set generated by a command is a table. By default, the source tables in a data adapter are named Table, Table1, and so on. Using a Stored Procedure Using multiple data adapters allows you to populate a DataSet with tables from completely independent database servers. However, if your tables reside on the same SQL server, you might want to consider using a stored procedure to return the tables at the same time: CREATE PROCEDURE spGetAllEmployeeInfo AS SELECT SSN, LastName, FirstName, Age FROM Person SELECT SSN, Dept, Salary, HireDate FROM Employee The stored procedure declaration for spGetAllEmployeeInfo includes two SELECT queries. The following code, slightly modified from Listing 22.4, retrieves both tables using a single SQLDataAdapter object: Dim cn As SqlConnection Dim cmdGetAll As SqlCommand Dim adAllInfo As SqlDataAdapter Dim dsResults As DataSet 'INITIALIZE OBJECTS cn = New SqlConnection("server=bsw2k;uid=sa;pwd=;database=BrianTest") adAllInfo = New SqlDataAdapter() dsResults = New DataSet() 'SET UP COMMAND cmdGetAll = New SqlCommand("spGetAllEmployeeInfo", cn) cmdGetAll.CommandType = CommandType.StoredProcedure adAllInfo.SelectCommand = cmdGetAll 'SET UP MAPPINGS adAllInfo.TableMappings.Add("Table", "Person") adAllInfo.TableMappings.Add("Table1", "Employee") 'FILL DATASET cn.Open() adAllInfo.Fill(dsResults) cn.Close() The two sets of records returned by the stored procedure are assigned the generic names Table and Table1 in the data adapter, so mappings were added to give them more meaningful names in the DataSet. When thinking of the tables in a data adapter keep in mind the number of "tables" is based on the number of sets of records returned, not the number of tables in the SQL statement itself. In other words, the following SQL statement would look like a single table (called Table) to the data adapter: SELECT * FROM Person P, Employee E WHERE P.SSN = E.SSN If multiple fields with the same name are returned in the same source table (SSN in the preceding example), the data adapter follows the same naming convention as it does with tables (SSN, SSN1, and so on). Adding Constraints and Relationships In addition to storing tables, a DataSet object has the capability to store relationships and constraints, just like a regular database. Because a DataSet can be modified while disconnected from the database, the database management system is not present to enforce constraints such as unique fields and primary keys. Recall our last example, in which we created a DataSet containing the Person and Employee tables we created in Chapter 20. When these tables were created in the DataSet, key and constraint information was not automatically transferred. Fortunately, this information can be added with a few lines of code, as shown in Listing 22.5. Listing 22.5 DATAGRIDEXAMPLE.ZIP Adding Key Constraints 'SET UP COLUMN OBJECTS FOR EASY ACCESS Dim colSSNP, colSSNE As DataColumn colSSNP = m_dsMain.Tables("Person").Columns("SSN") colSSNE = m_dsMain.Tables("Employee").Columns("SSN") 'CREATE PRIMARY KEYS Dim arPersKey(1) As DataColumn Dim arEmplKey(2) As DataColumn arPersKey(0) = colSSNP arEmplKey(0) = colSSNE arEmplKey(1) = m_dsMain.Tables("Employee").Columns("Dept") m_dsMain.Tables("Person").PrimaryKey = arPersKey m_dsMain.Tables("Employee").PrimaryKey = arEmplKey 'CREATE FOREIGN KEY FOR CASCADING DELETE Dim fkSSN As ForeignKeyConstraint fkSSN = New ForeignKeyConstraint("SSNForKey", colSSNP, colSSNE) fkSSN.DeleteRule = Rule.Cascade m_dsMain.Tables("Employee").Constraints.Add(fkSSN) The code in Listing 22.5 introduces a new object, the DataColumn, which represents a field in a data table. The PrimaryKey property of each table in a DataSet can be set to an array of these columns to define the primary key. The SSN column is the primary key for the Person table. The SSN and Dept columns together are the primary key for the Employee table. SSN is also a foreign key in the Employee table, so in order to maintain database integrity, we should delete a record from the Employee table if the corresponding person was deleted from the Person table. By creating a ForeignKeyConstraint object, we can ensure that this happens automatically. Displaying a DataSet's Contents Sometimes visual aids can help a presentation. As we continue to discuss the many aspects of the DataSet class, you may want to display the actual records to get a feel for what is happening behind the scenes. In our discussion this far, we have only mentioned using a message box or a Debug.Writeline statement to display field values. However, with only a few lines of code you can display this data on the screen in a tabular format. This is accomplished through the magic of data binding. Understanding Data Binding The concept of data binding means associating a control on a form with a field or data table. The control handles navigation, updates, and deletes so you do not have to write code to update the records in the DataSet bound to it. In previous versions of Visual Basic, data binding meant foregoing a lot of control over the communication between your application and the database. As a result, data binding received a bad reputation in the VB community. Many developers think that data binding is for novice programmers only. However, in a disconnected environment, data-bound controls do not talk to the database directly, but rather to a local data store. In this case, data binding may once again become acceptable in the user interface tier. Using a DataGrid Control If you have created the sample database from Chapter 20 and are following along with the code samples, the following exercise will allow you to display contents of a DataSet containing the Person and Employee tables. -
Start a new Windows Application project. -
Add a Button control to the form. Set its Name property to btnLoad and its Text property to Load Data. -
Add two DataGrid controls to the form. Set their Name properties to grdPerson and grdEmployee. -
Add the code from Listing 22.6 to the form class.
Listing 22.6 DATAGRIDEXAMPLE.ZIP Displaying Data in a DataGrid Control Imports System.Data.SqlClient Public Class frmMain Inherits System.Windows.Forms.Form Private m_dsWork As DataSet Private Sub btnLoad_Click(ByVal sender As System.Object,_ ByVal e As System.EventArgs)_ Handles btnLoad.Click Dim cn As SqlConnection Dim cmdGetAll As SqlCommand Dim adAllInfo As SqlDataAdapter 'INITIALIZE OBJECTS cn = New SqlConnection("server=localhost;uid=sa;pwd=;database=BrianTest") adAllInfo = New SqlDataAdapter() m_dsWork = New DataSet() 'SET UP COMMAND cmdGetAll = New SqlCommand("spGetAllEmployeeInfo", cn) cmdGetAll.CommandType = CommandType.StoredProcedure adAllInfo.SelectCommand = cmdGetAll 'SET UP MAPPINGS adAllInfo.TableMappings.Add("Table", "Person") adAllInfo.TableMappings.Add("Table1", "Employee") 'FILL DATASET cn.Open() adAllInfo.Fill(m_dsWork) cn.Close() 'SET UP COLUMN OBJECTS FOR EASY ACCESS Dim colSSNP, colSSNE As DataColumn colSSNP = m_dsWork.Tables("Person").Columns("SSN") colSSNE = m_dsWork.Tables("Employee").Columns("SSN") 'CREATE PRIMARY KEYS Dim arPersKey(1) As DataColumn Dim arEmplKey(2) As DataColumn arPersKey(0) = colSSNP arEmplKey(0) = colSSNE arEmplKey(1) = m_dsWork.Tables("Employee").Columns("Dept") m_dsWork.Tables("Person").PrimaryKey = arPersKey m_dsWork.Tables("Employee").PrimaryKey = arEmplKey 'CREATE FOREIGN KEY FOR CASCADING DELETE Dim fkSSN As ForeignKeyConstraint fkSSN = New ForeignKeyConstraint("SSNForKey", colSSNP, colSSNE) fkSSN.DeleteRule = Rule.Cascade m_dsWork.Tables("Employee").Constraints.Add(fkSSN) 'BIND TABLES TO GRIDS grdPerson.DataSource = m_dsWork grdPerson.DataMember = "Person" grdPerson.CaptionText = "Person" grdEmployee.DataSource = m_dsWork grdEmployee.DataMember = "Employee" grdEmployee.CaptionText = "Employee" End Sub End Class The code in Listing 22.6 is a combination of the code from previous sections, with two important differences. First, the DataSet variable has been moved to the class level, so it will not fall out of scope as long as the form is loaded. Second, four lines of code were added to bind the DataSet to the two DataGrid controls. Each grid has a DataSource property, which is assigned to the DataSetm_dsWork. The tables are specified using the DataMember property. Run the sample application and your form should look similar to Figure 22.3. Figure 22.3. Changes made to the data grids will be reflected in the DataSet.  You should be able to enter new records, edit values, or even delete a record (by using record selector column and pressing the Delete key). Note that if you delete a record from the Person table, the corresponding record or records in the Employee table will disappear due to our foreign key constraint. Using a Filter to Determine Display Rows In Listing 22.6, we assigned the DataTable object to the DataMember property of the grid, and it dutifully displayed every field in the table with full editing capabilities. However, there may be times when you only want to display certain rows, or prohibit users from editing their contents. You determine the records within a table that are visible using a filter. A filter is like a WHERE clause in SQL it limits the records returned based on criteria. Continuing with the example from the last section, perform the following steps to create a filtered view of the Person table: -
Add another Button control to your form. Set its Name property to btnFilter and its Text property to Filter. -
Add a text box to the form. Set its Name property to txtFilter and its Text property to a blank string. -
Add the following code to the Click event for btnFilter: 'CLEAR CURRENT BINDING grdPerson.DataMember = "" grdPerson.DataSource = Nothing 'MODIFY AND BIND DEFAULT TABLE VIEW With m_dsWork.Tables("Person") .DefaultView.RowFilter = "LastName Like '" & txtFilter.Text & "%'" .DefaultView.AllowDelete = False .DefaultView.AllowEdit = False .DefaultView.AllowNew = False grdPerson.DataSource = .DefaultView End With grdPerson.CaptionText = "Filtered on Last Name" -
Start the sample program and click the Load Data button. -
Enter the first few characters of a last name into the text box and click the Filter button. You should see the data grid change to reflect the filter.
The code in Step 3 introduces the concept of a view, which is very similar to a table but can be filtered and sorted as desired. A view does not contain its own data, but rather points to the data in the underlying data table. In addition to modifying the default view, you can create your own DataView objects and bind them to various controls. Note DataView objects can be used to control table access and filter records, but not columns like a SQL view.
Verifying Modified Records A DataTable object contains multiple DataRow objects, each of which represents a record in the table. You can modify the contents of your DataSet either by changing the values of a particular DataRow object, or using a bound control like the data grid as described in the previous section. One interesting feature of the DataSet class is the ability to tell which rows have been modified. This is important because your DataSet is disconnected from a database; you need some way to know if rows were added, deleted, or updated so changes can be sent to a database. For example, suppose your table contains 1,000 rows of data and the end user modifies only a single row. Depending on the design of your application, it may be more efficient to pass only the changed row back to the business layer. The following properties and methods can be used to manage changes in a DataSet: RowState Determines what change has been made to a row, if any. GetChanges Returns a new DataSet object containing only the changed rows. AcceptChanges Commits all outstanding changes to the current DataSet, resetting the RowState property. RejectChanges Rolls back DataSet changes to the original version, or the version since the last call to AcceptChanges.
To test these methods, again return to the sample project. Perform the following steps: -
Add three new button controls, btnAccept, btnReject, and btnView. Set their Text properties to Accept Changes, Reject Changes, and View Changes. -
Add the code from Listing 22.7 to your form: Listing 22.7 DATAGRIDEXAMPLE.ZIP Viewing Changed Records Private Sub btnAccept_Click(ByVal sender As System.Object,_ ByVal e As System.EventArgs) Handles btnAccept.Click m_dsWork.AcceptChanges() grdPerson.DataSource = m_dsWork grdEmployee.DataSource = m_dsWork End Sub Private Sub btnReject_Click(ByVal sender As System.Object,_ ByVal e As System.EventArgs) Handles btnReject.Click m_dsWork.RejectChanges() grdPerson.DataSource = m_dsWork grdEmployee.DataSource = m_dsWork End Sub Private Sub btnView_Click(ByVal sender As System.Object,_ ByVal e As System.EventArgs) Handles btnView.Click Dim dsTemp As DataSet dsTemp = m_dsWork.GetChanges() dsTemp.Tables("Person").DefaultView.RowStateFilter = _ Dataviewrowstate.ModifiedCurrent Or dataviewrowstate.New dsTemp.Tables("Employee").DefaultView.RowStateFilter = _ Dataviewrowstate.ModifiedCurrent Or dataviewrowstate.New grdPerson().DataSource = dsTemp grdEmployee().DataSource = dsTemp End Sub -
Run the sample program and click Load Data. Pick one of the rows of data and modify the person's name. Delete another row. Finally, move to the bottom of the grid and enter a new row for the Person table. -
Click the View Changes button. The grids should show the records you added and modified. (The grid does not display deleted rows, although they can be accessed via code.) -
Click the Reject Changes button and the grids should return to their original state, erasing any changes you made. -
Repeat Step 3 and then click the Accept Changes button. -
Click View Changes. This time the grids should be blank, because you accepted outstanding changes.
The code in Listing 22.7 again makes use of a view, although instead of filtering records we filter the row state. You can use an Or expression to filter multiple row states, although deleted records will not be displayed in a bound data grid. Updating the Source Database You already have enough knowledge to update a database from a DataSet. For example, you could loop through the changed rows in a DataSet and call the appropriate stored procedure depending on the value of the RowState property. However, Microsoft has attempted to do some of the work for you by providing the Update method of the data adapter. You can set up additional command objects in the data adapter and then call the Update method to process changes in the DataSet. The process is similar to filling a DataSet, in that you set up a command and provide mappings. Setting Up the Commands In the example DataSet used throughout this chapter, we selected a few columns from the Person table. The following stored procedure processes an update to the Person table for all those fields: CREATE PROCEDURE spPersonUpdate @strOriginalSSN char(11), @strNewSSN char(11), @strLastName char(20), @strFirstName char(20), @intAge int AS UPDATE Person SET SSN=@strNewSSN, LastName=@strLastName, FirstName=@strFirstName, Age=@intAge WHERE SSN = @strOriginalSSN As you may recall from previous sections, a SQLCommand object for the previous stored procedure will require several Parameter objects. However, instead of setting the Value property to define the value passed to the stored procedure, we will use two new properties: SourceColumn and SourceVersion. The following lines of code show how these properties are used to map the stored procedure parameters to DataSet columns: Dim cmdUpdate As SqlCommand Dim parmTemp As SqlParameter cmdUpdate = New SqlCommand() cmdUpdate.CommandText = "spPersonUpdate" cmdUpdate.CommandType = CommandType.StoredProcedure cmdUpdate.Connection = cn parmTemp = cmdUpdate.Parameters.Add("@strOriginalSSN", SqlDbType.Char) parmTemp.SourceColumn = "SSN" parmTemp.SourceVersion = DataRowVersion.Original parmTemp = cmdUpdate.Parameters.Add("@strNewSSN", SqlDbType.Char) parmTemp.SourceColumn = "SSN" parmTemp.SourceVersion = DataRowVersion.Current parmTemp = cmdUpdate.Parameters.Add("@strLastName", SqlDbType.Char) parmTemp.SourceColumn = "LastName" parmTemp.SourceVersion = DataRowVersion.Current parmTemp = cmdUpdate.Parameters.Add("@strFirstName", SqlDbType.Char) parmTemp.SourceColumn = "FirstName" parmTemp.SourceVersion = DataRowVersion.Current parmTemp = cmdUpdate.Parameters.Add("@intAge", SqlDbType.Int) parmTemp.SourceColumn = "Age" parmTemp.SourceVersion = DataRowVersion.Current In the preceding sample code, the SourceColumn property is the name of the data table column that will be sent for each row to be updated. The SourceVersion property is usually set to Current to provide the most current version of the column value. However, when updating a primary key field, you need both the current and original version to update the correct record. Therefore, we pass two versions of the SSN column to the stored procedure. Calling the Update Method After creating the commands for each type of database change, you need to assign the SQLCommand objects to the InsertCommand, UpdateCommand, and DeleteCommand properties of a SQLDataAdapter object: Dim adPerson As SqlDataAdapter adPerson = New SqlDataAdapter() adPerson.UpdateCommand = cmdUpdate adPerson.DeleteCommand = cmdDelete adPerson.InsertCommand = cmdInsert Finally, execute the Update method, specifying the name of the DataSet and data table to be updated: cn.Open() adPerson.Update(m_dsWork, "Person") cn.Close() The Update method will execute the appropriate SQLCommand object, depending on how a given row in the data table has been modified. After executing the previous lines of code, any changes made in the DataSet should be reflected in the Person table. Note If you call the Update method using a DataSet in which records have been deleted, inserted, or updated, you must have valid command objects associated with these actions or an exception will occur. (If you only want to process a particular type of update, use the GetChanges function described earlier to create a new DataSet with the desired row states.)
Limiting Network Traffic In our previous examples, we populated the DataSet object from a database. This is perfectly acceptable for most applications, especially if the user needs to edit existing information. However, if your application just needs to add new records, you may want to create a blank DataSet with code to avoid an unnecessary database query. This way, your application does not incur the overhead of talking to the database until it is actually ready to insert new records. To create a DataSet object from code, create DataTable objects and then append them to a DataSet object, as in the following example: Dim tblStates As DataTable tblStates = New DataTable() tblStates.Columns.Add(New DataColumn("State", GetType(String))) tblStates.Columns.Add(New DataColumn("Population", GetType(Long))) Dim dsStateInfo As DataSet dsStateInfo = New DataSet() dsStateInfo.Tables.Add(tblStates) The data table in the previous example only contains two columns and no constraints, so the code to create a new data set is minimal. However, if your DataSet requires a lot of tables with very complex relations, writing code to create an empty DataSet could be a daunting task. The performance gain is not always worth the effort of duplicating a table definition that already exists in a database. If you are on a fast network and have to connect for other reasons, you may want to consider using the Fill Schema method, shown here: adAllInfo.FillSchema(m_dsWork, SchemaType.Mapped) The previous code sample creates a new DataSet containing the schema of any tables. A table schema is the definition of a table's column names and data types. If the previous line of code were used in place of the Fill method from Listing 22.6, the DataSetm_dsWork would contain blank Person and Employee tables. Although it required a connection to the database, no actual rows were transferred back to the client. You can, of course, still add new rows on the client with code: Dim rowTemp As DataRow Dim tblPerson As DataTable tblPerson = m_dsWork.Tables("Person") 'CREATE THE NEW ROW rowTemp = tblPerson.NewRow rowTemp.Item("SSN") = "123-45-6789" rowTemp.Item("LastName") = "Simpson" rowTemp.Item("FirstName") = "Homer" 'ADD THE NEW ROW tblPerson.Rows.Add(rowTemp) These lines of code add a new row to the Person table. Notice that we used the NewRow method to initialize rowTemp with the correct schema. Note New rows added to a DataSet will have a RowState of New until you call the AcceptChanges method as described earlier.
It should also be mentioned that the DataTable can be created and used independently of the DataSet, and is a valid parameter to both the Fill and FillSchema methods of a DataAdapter: tblTemp = New DataTable() adPerson.Fill(tblTemp) grdEmployee.DataSource = tblTemp If your data requirements are simple enough, you may want to use the DataTable object instead of the more complex DataSet. Using Typed DataSets Another interesting feature of the .NET framework is the ability to represent DataSet objects as typed entities within your program. Consider the following two lines of code: LastName = dsResults.Tables(0).Rows(5).Item("LastName") FirstName = dsResultsTyped.Person(5).FirstName The first line of code accesses the LastName field of the Person table using the traditional, collection-based approach. The second line of code uses typed data access, identifying the table and field names directly in Visual Basic code. Typed DataSets allow you to access their members using full Intellisense capability, just as you would any other object in your program. Because data generally comes from a database and is generated dynamically at runtime, you must define the custom DataSet class at design time in order to access it in a typed manner. The .NET framework uses XML to represent DataSets, and Visual Studio includes an XML designer that lets you easily create a custom DataSet class. You can set up the tables and field definitions manually, or connect to a database and import them. In an upcoming chapter, you will see how to create a typed DataSet definition from an existing DataSet object.  To learn about creating a schema from a DataSet, p.637
To add a new DataSet to your program, right-click the project name in the Solution Explorer and choose to Add a New Item, then DataSet from the menu. A new file with an xsd extension will be added to your product. (XSD stands for XML Schema Definition. The schema stores information about the DataSet, not data itself. ) The easiest way to build your DataSet is to use the Server Explorer. As you learned in Chapter 20, the Server Explorer allows you to browse the contents of a database visually. To add table definitions to your DataSet, drag the table name from the Server Explorer to the XML designer. Figure 22.4 shows the XML designer after dragging adding Employee and Person tables to the designer. Figure 22.4. A custom data class can be built using Visual Studio .NET's XML designer.  To find out how to connect to a database with the Server Explorer, p.535 Once you have completed designing the DataSet, you can create an instance of it in your project just as you would any other object: Dim dsInfo as New MyDataSet() Because theMyDataSet class is inherited from the DataSet class, you can fill the DataSet from a database using the same methods described earlier. Note To change the type name for your custom DataSet, set the dataSetName property in the XML designer.
| 
