Understanding DataSets
Understanding DataSetsIn Chapter 9, "Introduction to ADO.NET," you learned how to use the Command and DataReader classes to modify and represent database data. In this chapter, you will examine a separate set of classes that also enables you to work with database data. Why two sets of classes? The two sets of classes are useful in different applications. If you simply need to grab some database records and quickly display them on a Web page, you should use the Command and DataReader classes. If, on the other hand, you need to work with a disconnected and memory-resident representation of database records, you need to use DataSets . In Chapter 9, for example, you learned how to use a DataReader to represent database records. However, the DataReader represents only one database record at a time. You must call the Read() method to fetch each new record from the underlying database table into memory. Each time you call Read() again, the previously fetched record is lost. The DataReader , therefore, cannot be used to work with the results of a database query as a whole. For example, you cannot sort , or filter, or retrieve a count of the number of records in a DataReader because only one record from a database query is represented by a DataReader at a time. Furthermore, for it to work, the DataReader must remain connected to a database table. A DataReader is tied down to its underlying data source. This means, for example, that you cannot cache a DataReader in your server's memory so that the same DataReader can be used on multiple pages or over multiple requests to the same page. A DataSet , on the other hand, enables you to represent the results of a database query in your server's memory. Because a DataSet provides you with a memory-resident representation of data, you can work with the results of a database query as a whole. For example, a DataSet includes methods for sorting, filtering, and returning a count of the records from a database query. ASP CLASSIC NOTE If you're familiar with previous versions of ActiveX Data Objects (ADO), it might be helpful to think of a DataReader as a forward-only recordset. A DataSet , on the other hand, is similar to a disconnected, client-side, static recordset. Unlike a DataReader , a DataSet represents a disconnected set of records. When a DataSet is populated with records, you can break the connection to its underlying data source. This means that you can easily cache a DataSet in your server's memory and achieve dramatic performance benefits. When used wisely, DataSets can dramatically improve the performance of your Web applications. If you need to represent the same set of records over and over again in multiple pages or across multiple requests to the same page, you can represent the records in a DataSet and cache it in your server's memory. Caching the database records enables you to avoid connecting to your database server, which is a resource- intensive task. When used badly , however, DataSets can hurt the performance of your Web site. DataSets can occupy a lot of memory. If you use a DataSet in an ASP.NET page to represent a table with 10,000 database records, then 10,000 records must be represented in memory. If 100 people request the page at the same time, 1 million records must be represented in your server's memory. NOTE DataSets also require more overhead to create and populate than DataReaders . This is true even though, behind the scenes, a DataSet actually uses a DataReader to fetch the records. So, my general advice is that if you need to retrieve a different set of records whenever you request a page, use a DataReader . If you need to display the same set of records (or a filtered or sorted version of the same set of records) when you request a page, you should use a DataSet . Elements of DataSetsWhen working with DataSets , you use the following ADO.NET classes:
A DataSet can contain one or more DataTables that represent database tables. Relationships between the tables (such as parent/child relationships) can be defined using DataRelation classes. Finally, a DataView represents a particular filtered or sorted view of a DataTable . The DataSet , DataTable , DataRelation , and DataView classes all live in the System.Data namespace. There's one more class that you need to use when working with DataSets : the DataAdapter . The DataAdapter class represents the bridge between a DataSet and the data source it represents. You use a DataAdapter to populate a DataSet from an existing database table. You can also use a DataAdapter to update an existing database table with changes made to a DataSet . When working with a Microsoft SQL Server (version 7.0 or higher) database, you use the SqlDataAdapter , which you can find in the System.Data.SqlClient namespace. When working with other databases, such as Oracle or Microsoft Access, you use the OleDbDataAdapter , which you can find in the System.Data.OleDb namespace. Adding DataTables to a DataSetYou add the contents of existing database tables to a DataSet by using the DataAdapter . To add a database table to a DataSet , follow these steps:
The page in Listing 12.1, for example, illustrates how you can add the Authors database table from the Pubs database to a DataSet . Listing 12.1 SqlDataAdapter.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <% Dim dstAuthors As DataSet Dim conPubs As SqlConnection Dim dadAuthors As SqlDataAdapter dstAuthors = New DataSet() conPubs = New SqlConnection( "Server=localhost;Database=Pubs;UID=sa;PWD=secret" ) dadAuthors = New SqlDataAdapter( "Select * From Authors", conPubs ) dadAuthors.Fill( dstAuthors, "Authors" ) %> Authors table added to DataSet! The C# version of this code can be found on the CD-ROM. When the Fill method is called in Listing 12.1, a new DataTable is added to the DataSet named Authors. Notice that a connection to a database is never opened. The Fill method automatically opens a database connection when it is not already opened and then automatically closes the connection that it opens. NOTE If a database connection is already opened when you call the Fill method, the Fill method does not close the connection. This allows you to avoid opening and closing a database connection multiple times (a resource-intensive task) when you need to reuse the same database connection. The page in Listing 12.1 works only with a Microsoft SQL Server (version 7.0 and higher) database. It uses the SqlConnection and SqlDataAdapter classes from the System.Data.SqlClient namespace. If you want to work with another type of database, such as Microsoft Access or Oracle, you must use the classes from the System.Data.OleDb namespace. The page in Listing 12.2 illustrates how you can add a DataTable from a Microsoft Access database to a DataSet . Listing 12.2 OleDbDataAdapter.aspx [View full width] <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <% Dim dstAuthors As DataSet Dim conAuthors As OleDbConnection Dim dadAuthors As OleDbDataAdapter dstAuthors = New DataSet() conAuthors = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Authors The C# version of this code can be found on the CD-ROM. In Listing 12.2, the classes from the System.Data.OleDb namespace are used. Instances of the OleDbConnection and OleDbAdapter classes are created. There's nothing wrong with adding multiple DataTables to the same DataSet . In fact, you often need to do so when creating relationships between DataTables , such as parent/child relationships. The page in Listing 12.3, for example, demonstrates how you add both Categories and Products tables to the same DataSet . Listing 12.3 DataSetDoubleFill.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <% Dim dstNorthwind As DataSet Dim conNorthwind As SqlConnection Dim dadNorthwind As SqlDataAdapter ' Create the DataSet dstNorthwind = New DataSet() ' Open database connection conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) conNorthwind.Open() ' Add two tables dadNorthwind = New SqlDataAdapter( "Select * From Categories", conNorthwind ) dadNorthwind.Fill( dstNorthwind, "Categories" ) dadNorthwind.SelectCommand = New SqlCommand( "Select * From Products", conNorthwind ) dadNorthwind.Fill( dstNorthwind, "Products" ) ' Close the connection conNorthwind.Close() %> Categories and Products tables added to DataSet! The C# version of this code can be found on the CD-ROM. In Listing 12.3, the same SqlDataAdapter adds both the Categories and Products tables to the same DataSet . A new SQL Select statement is assigned to the DataAdapter class's SelectCommand property to retrieve the second table, the Products table, into the DataSet . Notice that we explicitly open the connection in Listing 12.3. This prevents the Fill () method from opening and closing the connection each time the method is called. If necessary, you can even add DataTables to a DataSet from different data sources. For example, you can populate a DataSet with both a Microsoft SQL Server database table and a Microsoft Access database table. Or, as you see in the next chapter, "Working with XML," you can add a DataTable that represents an XML file. Binding Controls to a DataSetSo, you might be thinking, these DataSets are all very nice, but how do I use them in an ASP.NET page? You can use a DataSet with the Repeater , DataList , and DataGrid controls in almost exactly the same way as you would use these controls with a DataReader . The page in Listing 12.4, for example, displays the contents of the Products database table in a DataGrid control (see Figure 12.1). Listing 12.4 DataGrid.aspx<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <Script Runat="Server"> Sub Page_Load Dim dstProducts As DataSet Dim conNorthwind As SqlConnection Dim dadProducts As SqlDataAdapter ' Create the DataSet dstProducts = New DataSet() conNorthwind = New SqlConnection( "Server=localhost;UID=sa;PWD=secret;Database=Northwind" ) dadProducts = New SqlDataAdapter( "Select * From Products", conNorthwind ) dadProducts.Fill( dstProducts, "Products" ) ' Bind dataset to DataGrid dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() End Sub </Script> <html> <head><title>DataGrid.aspx</title></head> <body> <asp:DataGrid ID="dgrdProducts" Runat="Server" /> </body> </html> The C# version of this code can be found on the CD-ROM. Figure 12.1. Displaying the contents of a DataSet . The DataGrid control is bound to the Products database table with the following two lines of code: dgrdProducts.DataSource = dstProducts dgrdProducts.DataBind() The first statement assigns the first table in the DataSet to the DataGrid control's DataSource property. The second statement actually binds the data from the DataSet to the DataGrid control, populating the DataGrid with the items from the DataSet . If a DataSet contains multiple DataTables , you can indicate which of the DataTables to use with the DataGrid by using the DataMember property of the DataGrid control. For example, imagine that the DataSet contains both Categories and Products database tables. In that case, you can bind the Products database table to the DataGrid like this: dgrdProducts.DataSource = dstProducts dgrdProducts.DataMember = "Products" dgrdProducts.DataBind()  |
.mdb" ) dadAuthors = New OleDbDataAdapter( "Select * From Authors", conAuthors ) dadAuthors.Fill( dstAuthors, "Authors" ) %> Authors Table added to DataSet! 
EAN: 2147483647
Pages: 263