The DataSet class allows multiple tables of related data to be manipulated simultaneously It does this by providing two internal collections, Tables and Relations, which are used to hold individual tables of data and relationships between them Microsoft describes the DataSet class as a class for managing an in-memory cache of data Working with a DataSet usually involves three steps 1 a DataSet is created, lled with tables from DataAdapters that are connected to the database, and has Relations de ned to indicate how the tables are linked It then disconnects from the database 2 The user views, adds to, deletes from and edits the data in the DataSet During this stage, the DataSet can be persisted to XML and reloaded without reference to the actual database so that there can be breaks in working on it 3 DataAdapters connected to the database are updated, using the altered tables in the DataSet, and this passes any updates back to the database For example, using the Biblio database, we could create a DataSet object and populate it with each of the four tables (Publishers, Authors, Titles and [Title Author] ) and four relation objects to describe the links between them (Publishers to Titles, Publishers to Authors, Titles to [Title Author] and Authors to [Title Author] the last two of these form the many-to-many link between Authors and Titles) Having done so of course, we would have a DataSet object that contained all of the data for an entire database, which is not a very desirable thing (remember we are using a database to save having to store an entire object model in memory at one time) Much more useful would be to load smaller subsets of data from the database for off-line working; a table containing a single Publisher row and another containing all of the Titles published by that publisher, or a table containing all of the Titles published in 2002 and another containing only the Publishers of these titles SQL is easily capable of extracting subsets of database tables like these and so we can create a DataAdapter to bring these into our DataSet

data from the database and then disconnect from the database For example, assume we needed to display all of the Publishers listed in Biblio, and for each publisher, a list of Titles they have published The GetDataSet() function in Listing 127 builds and returns a Publishers/Titles DataSet

Private Private Private Private Private Private CONNSTR = "" 'Code omitted see Appendix 2 dbConn As SqlClientSqlConnection ds As DataSet daTitles As SqlDataAdapter daPublishers As SqlDataAdapter pubMover As CurrencyManager

Private Function GetDataSet() As DataSet Dim sqlPub As String = _ "Select Name, PubID From Publishers" Dim sqlTitles As String = _ "Select Title, ISBN, PubID From Titles" 'Create the connection and adapters dbConn = New SqlClientSqlConnection(CONNSTR) daPublishers = New SqlDataAdapter(sqlPub, dbConn) daTitles = New SqlDataAdapter(sqlTitles, dbConn) 'Create a new datset Dim ds As DataSet = New DataSet() 'Fill the dataset with tables daPublishersFill(ds, "Publishers") daTitlesFill(ds, "Titles") 'Now configure the dataset's relationships Dim dcPublishers, dcTitles As DataColumn Dim dr As DataRelation dcPublishers = dsTables("Publishers")Columns("PubID") dcTitles = dsTables("Titles")Columns("PubID") dr = New DataDataRelation("PubsTitles", _ dcPublishers, dcTitles) dsRelationsAdd(dr) Return ds End Function Listing 127: Filling a DataSet with data, using DataAdapters

In Listing 127, we necessarily use a lot of objects to build our DataSet The rst requirement is simply to extract the necessary data from the database, for which we make use of a connection and two DataAdapter objects A DataAdapter object is used whenever we wish to get data from a database into a DataSet, or send updates from a DataSet back to the database Each DataAdapter is constructed from a SQL query string that describes the data we want and the connection, which says where it comes from Note that instead of grabbing the entire table, the SQL queries in this example specify only the