Disconnected Data Access
| The ADO.NET Framework supports two models of data access. In the first part of this chapter, you saw how you can use the SqlConnection, SqlCommand, and SqlDataReader objects to connect to a database and retrieve data. When you read data from a database by using a SqlDataReader object, an open connection must be maintained between your application and the database. In this section, we examine the second model of data access supported by ADO.NET: the disconnected model. When you use the objects discussed in this section, you do not need to keep a connection to the database open. This section discusses four new ADO.NET objects:
The ADO.NET objects discussed in this section are built on top of the ADO.NET objects discussed in the previous section. For example, behind the scenes, the DataAdapter uses a DataReader to retrieve data from a database. The advantage of using the objects discussed in this section is that they provide you with more functionality. For example, you can filter and sort the rows represented by a DataView. Furthermore, you can use the DataTable object to track changes made to records and accept or reject the changes. The big disadvantage of using the objects discussed in this section is that they tend to be slower and more resource intensive. Retrieving 500 records with a DataReader is much faster than retrieving 500 records with a DataAdapter. Note For detailed performance comparisons between the DataReader and DataAdapter, see Priya Dhawan's article at the Microsoft MSDN website (msdn.Microsoft.com), entitled "Performance Comparison: Data Access Techniques." Therefore, unless you need to use any of the specialized functionality supported by these objects, my recommendation is that you stick with the objects discussed in the first part of this chapter when accessing a database. In other words, DataReaders are good and DataAdapters are bad. Using the DataAdapter ObjectThe DataAdapter acts as the bridge between an in-memory database table and a physical database table. You use the DataAdapter to retrieve data from a database and populate a DataTable. You also use a DataAdapter to push changes that you have made to a DataTable back to the physical database. The component in Listing 16.25 illustrates how you can use a SqlDataAdapter to populate a DataTable. Listing 16.25. App_Code\Movie8.vb
The page in Listing 16.26 contains a GridView that is bound to an ObjectDataSource that represents the component in Listing 16.25 (see Figure 16.12). Figure 16.12. Displaying data with a DataAdapter. Listing 16.26. ShowMovie8.aspx
Notice that a SqlConnection is never explicitly created in the component in Listing 16.25. When you call the SqlDataAdapter object's Fill() method, the SqlDataAdapter automatically creates and opens a connection. After the data is fetched from the database, the Fill() method automatically closes the connection. You don't need to wrap the call to the Fill() method within a Using or try...Catch statement. Internally, the SqlDataAdapter uses a try...Catch statement to ensure that its connection gets closed. Opening and closing a database connection is a slow operation. If you know that you will need to perform another database operation after using the SqlDataAdapter, then you should explicitly create a SqlConnection and open it like this: Dim con As New SqlConnection(...connection string...) Dim dad As New SqlDataAdapter("SELECT Title,Director FROM Movies", con) Using con con.Open() dad.Fill(dtblMovies) ... Perform other database operations with connection ... End Using If a SqlConnection is already open when you call the Fill() method, the Fill() method doesn't close it. In other words, the Fill() method maintains the state of the connection. Performing Batch UpdatesYou can think of a SqlDataAdapter as a collection of four SqlCommand objects:
You can use a DataAdapter not only when retrieving data from a database. You can also use a DataAdapter when updating, inserting, and deleting data from a database. If you call a SqlDataAdapter object's Update() method, and pass the method a DataTable, then the SqlDataAdapter calls its UpdateCommand, InsertCommand, and DeleteCommand to make changes to the database. You can assign a SqlCommand object to each of the four properties of the SqlDataAdapter. Alternatively, you can use the SqlCommandBuilder object to create the UpdateCommand, InsertCommand, and DeleteCommand for you. The SqlCommandBuilder class takes a SqlDataAdapter that has a SELECT command and generates the other three commands automatically. For example, the page in Listing 16.27 displays all the records from the Movies database table in a spreadsheet created with a Repeater control (see Figure 16.13). If you make changes to the data and click the Update button, then the Movies database table is updated with the changes. Figure 16.13. Batch updating database records. Listing 16.27. ShowDataAdapterUpdate.aspx
The SqlDataAdapter in Listing 16.27 performs a batch update. When a SqlDataAdapter object's UpdateBatchSize property is set to the value 0, the SqlDataAdapter performs all its updates in a single batch. If you want to perform updates in smaller batches, then you can set the UpdateBatchSize to a particular size. Note Performing batch updates is a new feature of ADO.NET 2.0. Using the DataTable ObjectThe DataTable object represents an in-memory database table. You can add rows to a DataTable with a SqlDataAdapter, with a SqlDataReader, with an XML file, or programmatically. For example, the page in Listing 16.28 builds a new DataTable programmatically. The contents of the DataTable are then displayed in a GridView control (see Figure 16.14). Figure 16.14. Displaying a DataTable that was built programmatically. Listing 16.28. ShowDataTableProgram.aspx
In Listing 16.28, a DataTable with the following three columns is created: Id, ProductName, and ProductPrice. The data type of each column is specified with a .NET Framework type. For example, the ProductPrice column is created as a decimal column. Alternatively, you could create each column with a SqlType. For example, you could use System.Data.SqlTypes.SqlDecimal for the type of the ProductPrice column. Notice that the Id column is created as an autoincrement column. When you add new rows to the DataTable, the column increments its value automatically. Selecting DataRowsYou can retrieve particular rows from a DataTable by using the DataTable object's Select() method. The Select() method accepts a filter parameter. You can use just about anything that you would use in a SQL WHERE clause with the filter parameter. When you retrieve an array of rows with the Select() method, you can also specify a sort order for the rows. When specifying a sort order, you can use any expression that you would use with a SQL ORDER BY clause. For example, the page in Listing 16.29 caches a DataTable in memory with the ASP.NET Cache object. The page contains a TextBox control. When you enter a partial movie title into the TextBox control, a list of matching movies is displayed in a GridView control. The rows are sorted in order of the movie title (see Figure 16.15). Figure 16.15. Selecting matching rows from a cached DataTable. Listing 16.29. ShowDataTableSelect.aspx
The DataTable Select() method returns an array of DataRow objects. Notice that there is nothing wrong with binding an array of DataRow objects to a GridView control. However, you must explicitly cast each data item to a DataRow and read within a GridView TemplateField. DataRow States and DataRow VersionsWhen you modify the rows in a DataTable, the DataTable keeps track of the changes that you make. A DataTable maintains both the original and modified version of each row. Each row in a DataTable has a particular RowState that has one of the following values:
Each row in a DataTable can have more than one version. Each version is represented by one of the following values of the DataRowVersion enumeration:
You can use the DataTable.AcceptChanges() method to copy the current versions of all the rows to the original versions of all the rows. And you can use the DataTable.RejectChanges() method to copy the original versions of all the rows to the current versions of all the rows. For example, the component in Listing 16.30 includes an AcceptChanges() and RejectChanges() method. The component maintains a DataTable in Session state. If you update a row in the DataTable, the row is updated in memory. If the RejectChanges() method is called, any changes made to the DataTable are rejected. If the AcceptChanges() method is called, the database is updated and all changes are accepted. Listing 16.30. App_Code\Movie9.vb
The page in Listing 16.31 contains a GridView that is bound to the component in Listing 16.30. The GridView includes a column that indicates whether each row has been changed. The column displays the value of the corresponding DataRow object's RowState property (see Figure 16.16). Listing 16.31. ShowMovie9.aspx
Figure 16.16. Tracking data row changes. If you click the Accept Changes button, all the changes made to the rows in the GridView are sent to the database. If you click the Reject Changes button, all the rows revert to their original values. Using the DataView ObjectThe DataView object represents an in-memory database view. You can use a DataView object to create a sortable, filterable view of a DataTable. The DataView object supports three important properties:
The easiest way to create a new DataView is to use the DefaultView property exposed by the DataTable class like this: Dim dataView1 As DataView = dataTable1.DefaultView; The DefaultView property returns an unsorted, unfiltered view of the data contained in a DataTable. You also can directly instantiate a new DataView object by passing a DataTable, filter, sort order, and DataViewRowState filter to the DataView object's constructor, like this: Dim dataView1 As New DataView(dataTable1, _ "BoxOfficeTotals > 100000", _ "Title ASC", _ DataViewRowState.CurrentRows) This statement creates a new DataView from a DataTable that represents the Movies database table. The rows are filtered to include only the movies that have a box office total greater than 100,000 dollars. Also, the rows are sorted by the movie title in ascending order. Finally, all the current rows are represented from the DataTable (as opposed, for instance, to rows that have been deleted). The page in Listing 16.30 illustrates one way that you can use a DataView. In Listing 16.32, a DataView is cached in Session state. You can sort the cached DataView by clicking on the header links rendered by the GridView control (see Figure 16.17). Listing 16.32. ShowDataView.aspx
Figure 16.17. Sorting a cached DataView. Using the DataSet ObjectThe DataSet object represents an in-memory database. A single DataSet can contain one or many DataTable objects. You can define parent/child relationships between the DataTable objects contained in a DataSet. For example, the page in Listing 16.33 contains a treeView control. The TReeView displays a list of movie categories and, beneath each movie category, a list of matching movies (see Figure 16.18). Listing 16.33. ShowDataSet.aspx
Figure 16.18. Building a treeView from a DataSet. The treeView is built programmatically. In the BuildTree() method, a DataSet is created that contains two DataTable objects. The first DataTable represents the MovieCategories database table and the second DataTable represents the Movies database table. A parent/child relationship is created between the two DataTable objects with the help of a DataRelation. The DataRelation is used to get the movies that match each movie category. The DataRow.GetChildRows() method is called to retrieve the movies that match a particular movie category. |
.ConnectionString End Sub End Class 
- An Emerging Strategy for E-Business IT Governance
- A View on Knowledge Management: Utilizing a Balanced Scorecard Methodology for Analyzing Knowledge Metrics
- Measuring ROI in E-Commerce Applications: Analysis to Action
- Technical Issues Related to IT Governance Tactics: Product Metrics, Measurements and Process Control
- Governance Structures for IT in the Health Care Industry