ASP.NET Data Access features Page - 2

Asynchronous Commands:

Data base operations are normally synchronous. However, asynchronous commands used in ADO.NET 1.x showed that performance advantage could be gained. ADO.NET 2.0 provides true asynchronous mode as other commands are performed while this command is completed. Moreover, the user interface is not blocked while this command is performed. The support for this has been built into the SQLCommand class. This class uses three methods to build commands that work asynchronously—non blocking, polling and callback. Nonblocking is used to start the operation and then do something unrelated and then to return and get the results. The client can also check for the status of the asynchronous operation and poll for completion. Lastly he can start the database operation and continue with the application without waiting and can receive the callback when the operation is complete.

The asynchronous command begins with the BeginExecuteXXX and this returns a IAsyncResult object which is later used to complete the call. The EndExecuteXXX is called to wind up the operation. This command syncs up the results with the rest of the application. But this blocks up the code whenever the results are not ready. This can be avoided by polling for completion. The callback function can also be passed to the BeginExecuteXXX method along with the state of the particular call. To ensure that the code stops automatically until another thread terminates and invokes a callback, a synchronizer can be placed. The specified callback function is invoked at the end of the operation. The context of the call specified in the second argument to BeginExecuteXXX is packaged into the AsyncState property of the IAsyncResult object.

SQL Bulk Copy:

Bulk Copy funcationality provides a way of transferring large amounts of data into the SQL Server table. The SqlBulkCopy class is more powerful than the ADO.NET 2.0 SqlBulkCopy class and the Bulk INSERT statement. Data can be copied to the SQL Server from a ADO.NET reader or a DataTable object. The SqlBulkCopy class consists of a number of properties. The BatchSize as the name suggests specifies the number of rows in each batch. If the size is 0 then the copy occurs in a single step. The BulkCopyTimeOut as the name suggests specifies the amount of time in seconds before the bulk copy operation times out. The default time is 30 seconds. ColumnMappings is a collection object that defines the mapping between the data source and the destination table. The collection is empty by default and must be filled only if mapping between source and destination is required. It has two methods Close and WriteToServer. The WriteToServer method has a number of overloads. The IDataReader copies all the rows from a specified data reader. The DataRow[] copies all the rows from the specified array of DataRow objects. DataTable copies all the rows form the specified Data Table. DataTable, DataRowState copies all the rows form the specified DataTable that are in a specified state.

DestinationTableName specifies the name of the destination table. NotifyAfter specifies the number of rows to process before the SqlRowsCopied notification event is generated. By default only one event is raised. The NotifyAfter property can be used to track the progress of a bulk operation if the property is set to greater than one. The application receives the SqlRowsCopied event which notifies that a block of rows have been processed.

If an error occurs during bulk copy the operation is aborted and a SqlException is raised. A destination table and a source table must exist and primary key constraints are ignored during this operation. Unique key constraints results in an exception being thrown. If a bulk operation is closed using a Close method an exception will be thrown. If an ongoing operation has to be aborted the Abort of the SqlRowsCopiedEventArgs property has to be set. An instance of the class is received as an argument of the SqlRowsCopied event. Abort is a Boolean property. It has to be set to True to abort bulk copy. It must be noted that nothing happens to the rows that have already been committed or rolled back automatically in a bulk copy when the batch terminates. The uncommitted rows of a bulk copy transaction initiated by the user will have to be specifically committed or rolled back within the user transaction.

Enhancements to SQL Server 2005.

The .NET data provider for SQL Server has been enhanced to match the features that have been built into the SQL Server 2005 and its MADC 9.0 libraries.

1. The enhancements that have been made to SQL Server 2005 include data type support, query dependency and notification and multiple active result sets.

2. SQL Server 2005 allows for a unified programming model for regular and large values. Values can be stored as varchar, nvarchar and varbinary columns.

3. SQL Server 2005 supports all CLR types. All valid .NET objects can be retrieved from the SQL server tables. For example a Point or a user defined class.

4. SQL server supports XML as a native type. XML data can be stored in columns by declaring the type of a given column as native XML.

5. SQL Notifications are made to the client whenever there is a change in the data at the SQL server level and the result set generated by a given query is modified. The SQL Server provider in ADO.NET uses the SqlNotificationRequest and SqlDependency classes to expose this functionality.

6. Multiple active resultsets of the SQL server 2005 is a natively implemented feature that allows the application to have more than one SqlDataReader open on a connection. Each SqlDataReader could have been started on a single connection using a different command. This gives a performance boost as multiple readers are less expensive than multiple connections. This feature is enabled by default when SQL server 2005 is the database server

ADO.NET Class Enhancements

The System.Data namespace consists of classes such as DataSet, DataView and DataTable. The changes to these are minimal but all the classes have been refined and enhanced. The compact serialization format used for sending the DataView and DataTable over the .NET Remoting channel is a feature that worth mentioning.

The DataTableReader class which has been introduced retrieves the content of the DataTable or a DataSet object in the form of one or more read only, forward only result sets. The contents can be read quickly in a cursor fashion and by scrolling through the contents of the in memory objects. This class can be invoked by using the GetDataReader method of the DataTable or a DataSet object.

The Load method of the DataTable and the DataSet can be invoked to fill the DataTable or the DataSet with the contents of the DataReader object.

Serializing a DataTable to XML has been made possible in the ADO.NET 2.0 using the ReadXML and WriteXML methods. The WriteXML method has been given a long list of overloads and the contents of the DataTable can be written to a stream, file or XML writer or text writer. Both data and schema can be written together or separately. The output format can be controlled by setting the XmlWriteMode parameter. Unlike the DataSet object markup, the root node defaults to DocumentElement. The ReadXML method reads the XML into a DataTable object by using the specified stream, file or reader. The method takes one parameter and returns a value drawn from the XMLReadMode enumeration.

The XMLSerializer converts the objects public properties and fields to an XML format for storage and transport. This is a generic class that works with other classes that have no circular references and implement the XMlSerializable interface. The serialization process assumes importance when ASP.NET uses the XMLSerializer class to encode XML web service messages.

The .NET Framework 2.0 has provided DataSet and DataTable classes with a new property called RemotingFormat. The purpose of this property is to accept values from the SerializationFormat enumerated type—Binary or XML and influence the serialization of the content. It prevents large chunks of data from being moved when an instance of these classes is stored in a out of process session or moved through the .NET Remoting.

Managing Views of Data

A DataView class is a databound view of data stored in a DataTable. This can be used for sorting, filtering and searching data rows. If configured a DataView can be used for editing data also. A number of DataViews can be attached to a DataTable object. In ADO.NET 2.0 the ToTable method can be used to create a new DataTable from the current DataView. The new DataTable can then be subject to XML serialization.

ADO.NET 2.0 has really changed the way data is handled. Most of these features are associated with the changes that have been made in SQL Server .NET Provider and the new functionalities of SQL Server 2005. The changes in MADC 9.0 libraries have also induced changes in Batch processing and bulk copy objects. The DataSet and DataTable classes can be read using the readers and they can be filled using the reader object. XML serialization has also become possible for DataTable object.

However, the biggest change in ASP.NET is the introduction of the DataSource object. We will be studying this in detail in the next lesson of this series.