| The DataSet class is a memory-resident, lightweight relational database class. It has properties that reflect the tables ( Tables ) and relationships between tables ( Relations ) within the dataset. You can control whether corresponding constraints are enforced with the EnforceConstraints property. You can name the dataset with the DataSetName property. You can also set the name of the dataset in the DataSet constructor. The SqlDataAdapter class is used to get data from the database into the DataSet . The constructor of the HotelBroker class shows how to use a data adapter class to populate a dataset. The code is found in the Hotel subdirectory of the CaseStudy directory for this chapter.  conn = New SqlConnection(connString) citiesAdapter = New SqlDataAdapter() citiesAdapter.SelectCommand = New SqlCommand(_ "select distinct City from Hotels", conn) citiesDataset = New DataSet() citiesAdapter.Fill(citiesDataset, "Cities") The SqlDataAdapter class has properties associated with it for selecting, inserting, updating, and deleting data from a data source. Here the SqlCommand instance is associated with the SelectCommand property of the SqlDataAdapter instead of being executed independently through one of its own execute methods . The Fill method of the SqlDataAdapter is then used to execute the select command and fill the DataSet with information to be put in a table, whose name is supplied as an argument. If the database connection was closed when the Fill method was executed, it will be opened. When finished, the Fill method will leave the connection in the same state as it was when it was first called. At this point the connection to the database could be closed. You now can work with the DataSet and its contained data independently of the connection to the database. SqlDataAdapter is implemented with the SqlDataReader class, so you can expect better performance with the latter. The SqlDataReader might also be more memory efficient, depending on how your application is structured. If you do not need the features of the DataSet , there is no point incurring the overhead. If you are doing expensive processing, you can free up the database connection by using a DataSet . You may get better scalability by loading the data into the DataSet , freeing the associated database resources, and doing the processing against the DataSet . Disconnected Mode This scenario of working with a database is referred to as disconnected. Connected mode represents a tightly coupled , connected environment where state and connections can be maintained. Client/server environments are examples where this is true. ADO and OLE DB were designed for this world. In a connected-mode environment data readers can be used. If necessary, ADO can be used through the COM interop facility. In fact, ADO was purposely not rewritten for .NET so that absolute backward compatibility could be maintained , bugs and all. Connections, however, are expensive to maintain in environments where you want to be able to scale to a large number of users. In this environment there is often no need to hold locks on database tables. This aids scalability because it reduces contention on database tables. The DataTable objects in the DataSet's Tables collection with their associated constraints can mimic the tables and relationships in the original database. For applications that are implemented completely with .NET, DataSet instances can be passed around or remoted to the various parts of an application. For applications that can make optimistic assumptions about concurrency, this can produce large gains in scalability and performance. This is true of many types of Internet- or intranet-based applications. In the disconnected mode, a connection is made in the same way as with the connected mode of operation. Data is retrieved using the data provider's data adapter class. The SelectCommand property specifies the SQL statement used to place data into the dataset. Unlike the data reader, which is related to a particular database connection, the dataset has no relationship to any database, including the one from which the data originally came. DataSet Collections When data is placed into a DataSet , the related tables and columns are also retrieved. Each dataset has collections that represent all the tables, columns , and data rows associated with it. The TestHotels folder has a special version of the HotelBroker class that has a method called PrintHotels that illustrates how to retrieve this information and write it to a Console. A console test program that uses Hotel.dll is provided. The hotelsDataset is a dataset that has already been filled with the data from the HotelBroker database. Public Sub PrintHotels() Console.WriteLine("Hotels") Dim t As DataTable = hotelsDataset.Tables("Hotels") If t Is Nothing Then Return End If Dim c As DataColumn For Each c In t.Columns Console.Write("{0, -16}", c.ColumnName) Next Console.WriteLine("") Dim r As DataRow For Each r In t.Rows Dim i As Integer For i = 0 To t.Columns.Count - 1 Dim typ As Type = r(i).GetType() If typ.FullName = "System.Int32" Then Console.Write("{0, -16}", r(i)) Else Dim s As String = r(i).ToString() s = s.Trim() Console.Write("{0, -16}", s) End If Next i Console.WriteLine("") Next r Console.WriteLine("") End Sub The Tables collection includes all the DataTable instances in the DataSet . In this particular case there is only one, so there is no need to iterate through that collection. The program then iterates through all the columns in the table and sets them up as headers for the data that will be printed out. After the headers have been set up, all the rows in the table are iterated through. For each column in the row, we ascertain its type and print out the value appropriately. The program checks only for the types that are in the Hotels database table. Checking for types instead of printing out the row values as Object enables us to format the data appropriately. As we will show later, you can populate the dataset through these collections without having to obtain it from a data source. You can just add tables, columns, and rows to the appropriate collections. | 
