Sunday, September 12, 2010

Database, Storing the Results of a Query in Memory

Problem

While a data reader is fast and convenient, you would like to keep the retrieved data around for a while, even when you are disconnected from the database or other data source.

Solution

Use the data reader to bring the results into a data set. The DataSet object represents one or more in-memory database tables, each with its records stored in a separate DataTable object.

Discussion

The following code loads all records from the Table1 table into a DataSet object, creating a DataTable object named Table1 within that data set:
' ----- Connect to the database.
 Dim  
connectionString As String = _
    "Data Source=MySystem\SQLEXPRESS;" & _
    "Initial Catalog=MyDatabase;Integrated Security=true"
 Dim theDatabase As New SqlClient.SqlConnection(connectionString)
 theDatabase.Open()

 ' ----- Prepare the SQL statement for use by the data set.
 Dim sqlStatement As New SqlClient.SqlCommand( _
    "SELECT * FROM Table1", theDatabase)

 ' ----- Create the adapter that links the SQL Server-
 '       specific connection and  
command objects with
 '       the database-neutral data set.
 Dim theAdapter As New SqlClient.SqlDataAdapter(sqlStatement)

 ' ----- Create the data set and fill it with the results
 '       of the  
query.
 Dim disconnectedSet As New Data.DataSet
 theAdapter.Fill(disconnectedSet, "Table1")

 ' ----- Clean up.
 theAdapter = Nothing
 sqlStatement = Nothing
 theDatabase.Close()
 theDatabase.Dispose()




Data sets are like miniature in-memory relational databases, complete with tables, relationships, sorting, filters, field data types, and so on. They are a lot more complex than DataReader objects, but their disconnected nature and broader feature set make them useful for the longer-term caching and manipulation of data.
Each data set includes one or more DataTable objects. When you move data from an external data source to a DataSet, there are several objects that make up the connection between the source and the target:

  • A Connection object establishes a communication transport between the application and the data source.

  • Command objects encapsulate individual SQL statements used to retrieve or update data in the database. Each command may include zero or more Parameter objects that facilitate stored procedure and generic command processing. All Connection processing occurs via Command objects.

  • A DataReader provides basic and fast data retrieval from a database via a Command object query.

  • A DataAdapter builds the individual SQL commands used to retrieve and update data in the database. Working in conjunction with a CommandBuilder object, it crafts SELECT, INSERT, UPDATE, and DELETE commands that allow a DataSet to interact with a provider database, even though it doesn't know that provider's syntax rules. The DataAdapter works with a DataReader when retrieving results with a SELECT statement.

  • The DataSet object contains the records of table and query data retrieved from the data source. These results are stored in one or more DataTable objects.

  • The DataTable object contains the actual data for a single source table, whether it's a true table or a pseudotable based on a query.

  • Data tables are built from distinct DataRow and DataColumn objects that, when used together, provide access to individual records and fields.
These represent just the most basic objects used in database/DataSet interactions. You can add even more objects and complexity through DataRelation and Constraint objects.
Once data has been copied from a database into a DataSet, you can manipulate it just as though it was still part of an organized data source. For the data retrieved from the Table1 table in our imaginary database, you can access the first record's Column1 value using code similar to the following. There are a few different ways to do this, including this statement:
disconnectedSet.Tables!Table1.Rows(0)!Column1

or the more verbose:
disconnectedSet.Tables("Table1").Rows(0).Item("Column1")

The Rows member of each table is a standard collection, so you can scan it using ordinary collection features in Visual Basic:
For Each oneRow As DataRow In disconnectedSet.Tables!Table1.Rows
     MsgBox(oneRow!Column1)
 Next oneRow

Data sets are great for keeping cached data in memory for ongoing use. But you can also update the fields stored in a data set, and later send those changes back to the database in a batch. You must use a data adapter to help process the individual INSERT, UPDATE, and DELETE statements required to modify the source database. While you can write each command yourself, you can also have a CommandBuilder object generate them for you based on the initial SELECT statement. The following code modifies this recipe's solution to include updates to the database (we've highlighted the new statements, and to save some space, we left off the database connection and cleanup code):
' ----- Prepare the SQL statement for use by the data set.
 Dim sqlStatement As New SqlClient.SqlCommand( _
    "SELECT * FROM Table1", theDatabase)

 ' ----- Create the adapter that links the SQL Server-
 '       specific connection and command objects with
 '       the database-neutral data set.
 Dim theAdapter As New SqlClient.SqlDataAdapter(sqlStatement)

 ' ----- Create a command builder that will auto-generate
 '       the various UPDATE statements.
 Dim theBuilder As New SqlClient.SqlCommandBuilder(theAdapter)

 ' ----- Create the data set and fill it with the results
 '       of the  
query.
 Dim disconnectedSet As New Data.DataSet
 theAdapter.Fill(disconnectedSet, "Table1")

 ' ---- Modify some data.
 disconnectedSet.Tables!Table1.Rows(0)!Column1 = 50

 ' ----- Return the updates to the database.
 theAdapter.Update(disconnectedSet, "Table1") 

If you don't want the "convenience" of the full DataSet object, you can retrieve your results into an individual DataTable object, which has a little less overhead. DataSet objects use DataTable objects anyway to store the records, so there's no reason you can't use them yourself. The following code modifies this recipe's original solution to use a DataTable object instead of a full DataSet object (we've highlighted the lines that are different from the DataSet-specific code):
' ----- Connect to the database.
 Dim connectionString As String = _
    "Data Source=MySystem\SQLEXPRESS;" & _
    "Initial Catalog=MyDatabase;Integrated Security=true"
 Dim theDatabase As New SqlClient.SqlConnection(connectionString)
 theDatabase.Open()

 ' ----- Prepare the SQL statement for use by the data set.
 Dim sqlStatement As New SqlClient.SqlCommand( _
    "SELECT * FROM Table1", theDatabase)

 ' ----- Create the adapter that links the SQL Server-
 '       specific connection and command objects with
 '       the database-neutral data set.
 Dim theAdapter As New SqlClient.SqlDataAdapter(sqlStatement)

 ' ----- Create and fill the data table with the results
 '       of the query.
 Dim singleTable As New Data.DataTable
 theAdapter.Fill(singleTable)

 ' ----- Clean up.
 theAdapter = Nothing
 sqlStatement = Nothing
 theDatabase.Close()
 theDatabase.Dispose()

No comments: