Review of DataSets
September 9, 2008 .Net — Tags: .Net Dataset, asp.net dataset, DataSet, review dataset — 52coding
The DataSet type is a complex in-memory container for data. The DataSet class contains a collection of DataTable instances that contain the relational data stored in a data set. Each DataTable instance contains a collection of DataColumn instances that define the schema of the data within the table, and a collection of DataRow instances that provide access to the contained data as rows.
To work with an instance of a data set, you typically create a data adapter for the data provider you are working with and use the data adapter to fill the data set based on a database query. Filling the data set creates the tables, columns, and rows within the data set to contain the data returned by the query, as shown in the following example:
string connString = "server=localhost;database=Northwind;trusted_connection=true"; // SQL Server 2005 Express connection string: // string connString = // @"server=.\SQLEXPRESS;AttachDbFileName= // C:\temp\Northwind.mdf;trusted_connection=true"; SqlConnection conn = new SqlConnection(connString); string query = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers"; SqlDataAdapter adapter = new SqlDataAdapter(query, conn); DataSet data = new DataSet(); adapter.Fill(data,"Customers");
When working with untyped data sets (instances of the DataSet class), the pattern illustrated in this example is common. You first create an instance of a connection and pass the connection string to the constructor for the SqlConnection object. If you are working with SQL Server 2005 Express, the connection string is a little different: you use the AttachDbFilename property instead of the database or Initial Catalog property. Once you have a connection object, you create an instance of a SqlDataAdapter and pass in the SELECT query and the connection object. The query that you pass to the constructor of the data adapter sets that object’s SelectCommand property, which will be used whenever the Fill method is called.
When the Fill method is called, a number of things happen:
-
The connection is opened if it isn’t already open.
-
The SqlCommand object referenced by the SelectCommand property on the adapter is executed.
-
The data set is inspected to see if it already contains a table with the appropriate name and schema to place the returned rows into. The name depends on whether a table name was specified in the call to the Fill method, as was “Customers” in the code sample.
-
If there is an existing table, the results are added to that table or overwrite existing rows in the table, depending on the constraints on that table and the current value of the FillCommandBehavior property.
-
If there isn’t an existing table, a new table is added to the data set and the results are placed in that table.
-
The connection is closed if it was opened by the Fill method.
Data sets also let you update the database using optimistic concurrency. To support this, each data table keeps track of a RowState flag for each row that says whether it is unmodified, modified, added, or deleted. The table also maintains an additional copy of each modified row so that it can preserve the values originally retrieved from the database as well as the current values. It uses these to detect whether the row in the database has been modified by someone else between when the data set was filled and when you go back to perform an update to the database using that data.
To perform updates to the database using a DataSet, you use the Update method on a data adapter. The Update method functions similarly to the Fill method described earlier in this chapter, except that it executes a separate command for each row in each table in the data set that it finds with a row state of modified, added, or deleted. The data adapter uses the SqlCommand objects referenced by the UpdateCommand, InsertCommand, and DeleteCommand properties to execute these commands. ADO.NET 2.0 has an option to batch the queries to avoid unnecessary round-trips to the database; it uses the UpdateBatchSize property on the data adapter.
In addition to these capabilities, the DataSet type also supports defining constraints on the tables contained within it to enforce primary key constraints, unique key constraints, and foreign key constraints. You can also define data relations so you can navigate easily from a parent row in one table to related child rows in another table, or vice versa, and you can define DataView instances to wrap a DataTable. Those views can be used to sort or filter the data contained within the table without modifying the actual contents in the table, much like a view in a database works against the tables in the database.
The bottom line is that the DataSet class is a highly capable data container designed to work seamlessly with the data-binding features of Windows Forms. If you need to learn more about the basics of the DataSet class and how to work with it, as well as other data access topics in general.
Given all that capability in the DataSet class itself, why would you need anything else? Well, using the DataSet class directly and data adapters have two significant downsides in their basic form: type safety and the coding practices they generate. The problem with type safety is that the columns within a DataTable need to be able to contain any type of column contents to satisfy the schema of whatever data source they are mapped to. This is implemented by making the column simply hold an Object reference. Because all types derive from Object in .NET, the column can therefore hold a reference to any type; hence, the data set can be used with any underlying storage mechanism as long as the types of the store can be mapped into a .NET type. This approach makes the data set very flexible in containing data. But you usually have to give something up for flexibility, and in this case type safety is the first thing you give up.
