ADO.NET is the managed code library for the .NET Framework. Its ancestors include ODBC, DAO, RDO, ADO, and OleDB. Some of these technologies still exist in the framework, but most developers rely on the resources provided in the ADO.NET namespaces. There are evolutionary changes in ADO.NET 2.0 that will be released next year with Visual Studio 2005, SQL Server 2005, and the .NET Framework 2.0.
This version has several important design goals. All changes are evolutionary, so existing 1.1 and 1.0 code should work without modification. Several key classes were optimized for performance, most notably dataset serialization and indexing. There are new classes specific to core functionality in SQL Server 2005. And finally, a new provider-independent API option allows database agnostic code patterns.
We will explore some of the new features of this release by examining namespace and class changes. Use the Object Browser on two machines to see this for yourself. Open one to Visual Studio 2003 with .NET 1.1 and the other to Visual Studio 2005 (Whidbey TechEd release) with the .NET Framework 2.0 beta.
At first look, there are subtle changes to the properties window for a Visual Studio project. Figure 1 shows the default configuration for a new Windows forms project including the System.Data reference.
The imported namespaces section in Table 1 lists other namespaces that can be added to the project imports. There are several familiar entries and two new sections. This is a list of the ADO.NET 2.0 namespaces. All of these live in System.Data.Dll except for the Oracle client.
Several obvious trends pervade the new classes. More exception classes are provided to isolate specific data problems. New events like TableClear provide handlers to act on them. Numerous factory classes simplify the creation of instance members. Schema mappings provide runtime metadata that can be used for query and administrative tools. And most notably, there are many classes for unique SQL Server 2005 features.
System.Data
System.Data is the core ADO.NET namespace that includes common classes like Dataset, DataTable, DataRow, DataColumn, DataAdapter, and DataReader. There are minor optimizations to these classes, but no new data structures.
Dataset performance is a key talk point in many community forums. The versatility of this class is unmatched, but XML serialization is bulky and slow across processes and HTTP transports. This is the basis for popular Web services and service-oriented architectures (SOAs). You can solve this performance problem using remoting with binary formatting. However, the Dataset still serializes XML in the 1.1 framework causing high CPU, memory, and bandwidth requirements. There is a new 2.0 Dataset property called RemotingFormat that can be set to binary to improve wire speed tremendously. Keep in mind that this only works in remoting, not with Web services.
Another advance was made with indexed Dataset updates. This is a factor with larger datasets. The update time is now constant as the dataset grows in size. Additionally, datasets can support more datatypes, including SQL Server 2005 user-defined types (UDT), which also contain strongly typed schema inferences.
DataTables are the real workhorse in ADO.NET 1.1. Datasets are often created to host one and only one table. Because a dataset can be serialized and can read and write from XML streams, you often need to carry the excess baggage of a dataset to hold one simple table. This situation is handled in ADO.NET 2.0 by adding ReadXml, WriteXml, ReadXmlSchema, and serialization to the DataTable object. They can be used without a dataset in Web services, remoting, and XML programming tasks.
Another popular design pattern is the use of faster DataReaders in stateless environments like ASP.NET Web pages. There is a new related class called DataTableReader that returns a DataTable from an existing DataReader, DataTable.Load(DataReader). This also works with views using DataView.ToTable.
DataRows live inside DataTables. There are useful row-and value-versioning properties that come into play with DataAdapter updates. The Rowstate can now be changed programmatically using SetAdded and SetModified. This will force updates on the database server even if the underlying data did not change.
The new classes in the System.Data namespace are summarized in Table 2.
System.Data.Common and System.Data.ProviderBase
System.Data.Common stores the base classes used by data providers. Most of these classes are marked "must inherit". Many of them are used to support the new provider agnostic factory model. ADO.NET 1.x is based on interface design patterns that are difficult to implement and evolve. The abstract base classes in the 2.0 version of the namespace and System.Data.ProviderBase streamline the provider development process and allow configurable runtime provisioning of connections and commands. This is handled by passing an invariant name to the DbProviderFactory class.
Dim factory as DbProviderFactory
factory = DbProviderFactories.GetFactory("System.Data.SqlClient")
dim cn as IDBConnection = factory.CreateConnection()
There are still provider-specific classes like System.Data.SqlClient that offer optimized flexibility and performance. Consider using the provider factory model only when the target database environment might change. This will be extremely valuable for ISVs and VARs that sell pre-packaged data-driven applications.
You also can enumerate the available providers, which should enable some interesting development and configuration tools.
Another interesting enhancement involves the ability to manufacture connection strings from properties. The DBConnectionStringBuilder class provides this feature.
There are base classes for schema discovery that each provider can implement. Server enumeration and metadata are available at design and runtime without resorting to older OleDB technology.
The new classes in the System.Data.Common namespace are summarized in Table 3.
The new classes in the System.Data.ProviderBase namespace are illustrated in Table 4.
System.Data.ODBC and System.Data.OleDB
Microsoft helped pioneer modern database connectivity libraries with the release of ODBC in the early '90s. This is still the only viable solution for some older back-end databases. A set of managed code wrappers expose the C-oriented ODBC API set in a consistent fashion. ODBC was not included in Visual Studio 2002 and the .NET Framework 1.0. However, it was later released as a separate download and included in .NET 1.1 as well as 2.0.
COM was the predominant interface technology in the late '90s. The vision of Universal Data Access prompted the release of OleDB drivers and the ADO provider/consumer object model. These surfaced in Visual Studio 6 for the Visual Basic RAD developers.
Both ODBC and OleDB are supported in .NET through managed code wrappers. The COM interop layer and data/type marshalling diminish performance compared to the newer and simpler managed code drivers. You would only use these for legacy data sources (like SQL Server 6.5) and special features (like server cursors and ADOX schema manipulation). Note that other classes mentioned in this article address cursor and schema handling so this approach is no longer relevant.
The new connection string builder, metadata, and provider factory patterns are now exposed for ODBC and OleDB.
The new classes in the System. Data.ODBC namespace are listed in Table 5.
The new classes in the System. Data.OleDB namespace are summarized in Table 6.
System.Data.Sql and System.Data.SqlServer
System.Data.Sql is a new namespace that accommodates SQL Server 2005-specific functionality. User-defined types, notifications, dependencies, server enumeration, aggregates, and other new features are accessed here.
The new classes in the System. Data.Sql namespace are summarized in Table 7.
The System.Data.SqlServer namespace is only installed with SQL Server 2005 server and tool environments. It supports .NET CLR functionality inside of SQL Server procedures, triggers, functions, and user-defined types. There is also a System Management Objects (SMO) library that replaces DMO as the maintenance and configuration code library. SMO is the primary tool for developing database management applications like the new SQL Server Workbench hosted in Visual Studio. SMO uses a cached object model with partial instantiation to improve performance and scalability. It also addresses WMI monitoring and server configuration. These features are described in other articles in this issue.
There are several noteworthy advances in SQL Server 2005 that touch on ADO.NET and the CLR. Managed code excels at number crunching and complicated execution logic, tasks in which T-SQL is traditionally weak. The extensive string handling and regular expression support, math and date operations, access to system resources (including file access), XML query and data manipulation, security and encryption algorithms, and type safety through Code Access Security make CLR a compelling choice for server-side processing.
There is direct HTTP access to SQL Server 2005 procedures and functions through .NET-compatible Web services. This no longer requires an IIS listener. Coupled with the deep Visual Studio tool integration, SQL Server becomes a great platform for business-tier applications. Remember that this code runs on the same hardware as your database queries. Given the wealth of computing power on the modern desktop, it might make more sense to handle some of these tasks client-side. Be sure to give some serious consideration to these features in your architecture and to do lots of testing and server monitoring.
System.Data.SqlClient
System.Data.SqlClient is familiar to many .NET developers. The optimized SqlConnection, SqlCommand, and SqlDataAdapter are the meat of most complex data-driven applications. Several enhancements are included to improve SQL Server connectivity for all versions (7, 2000, 2005, MSDE, etc.). Others are specific to SQL Server 2005.
DataAdapters now support asynchronous execution for connections and commands. SqlConnection.BeginOpen will start the process and call a delegate when the connection is ready. Client-side code can continue processing while the data server does its work. The SqlCommand.BeginExecuteNonQuery/BeginExecuteReader/BeginExecuteXmlReader series can start a lengthy query and call a delegate to handle the results. In a Windows form environment, the user can continue to interact with the interface or watch accurate progress meters during a lengthy process. On the Web side, static pages and user controls can render while waiting for the data needed for bound controls. Both scenarios will appear to the user as faster applications with little change in programming patterns.
Another interesting DataAdapter improvement works for all data providers. ADO.NET 1.x processes batch updates one row at a time, which is slow and creates excessive chatter between a client application and the database server. The DataAdapter. BatchUpdateSize defaults to 1 for backward compatibility but can be set to any reasonable integer value. Test various batch sizes to determine the optimal performer for your network environment. In most cases, batching will improve large updates by several factors. This can also execute inside a transaction.
DataAdapter.Update commands are not well suited for large batch update processes. SQL Server has a bulk copy API that is now supported with the SqlBulkCopy class. This is measurably faster than discrete insert commands. SQL Server 2005 also has a new Data Transformation Service (DTS) library that can automate more complex loads.
The SqlResultSet class offers a server-side cursor with row-level positioning. Block retrieval and positional updates are supported. There is a new Snapshot Isolation level with row versioning that offers increased availability for read-only applications.
Multiple Active Result Sets (MARS) allows several commands to run concurrently on a single connection.
Previously, DataReader and similar objects could block other attempts to access a data server. Now you can execute one command while maintaining position on an open reader. This feature is limited to SQL Server 2005.
The SqlDependency class utilizes SQL Server 2005 Query Notifications to bind a dependency object to a command object. After the command executes for the first time, subsequent changes to the underlying data fire events that can be handled in managed code. This is a great tool for client-side caching. Cache objects can refresh as soon as the data changes without the need for occasional polling.
The new SQL Service Broker queue handles the notifications. This is a distributed application framework that provides reliable asynchronous messaging at the database level. Think BizTalk for databases.
Reporting Services is a recent technology available as an add-on for SQL Server 2000. It comes in the box with SQL Server 2005, including numerous management classes and Visual Studio design tools. Developers can create and integrate custom and standard business reports. SRS is hosted as a Windows service and interacts with IIS to create a reporting portal. It consumes ADO.NET datasets in the report creation process.
The System.SqlXml namespace will exist in your ADO.NET 1.1 environment if you installed the SQLXML 3.0 add-on for SQL Server 2000. This is an XML-based API that lets users send and receive XML streams. The select statement has the FOR XML option that treats relational data as XML. This can now be used on the server side for complex XML manipulation. Updategrams are supported for batch updates. OpenXML turns XML into relational data against which you can set up SQL joins or execute queries. Web service extensions publish stored procedures for remote XML access. All of these functions rely on the OleDB SQL Server driver with the COM interop baggage.
SqlXml functionality is now built into the new server and SQLClient. There is an SqlXml datatype that can store XML documents directly in a database column. This is stored as a BLOB with an optional XSD schema definition. W3C standard XQuery language is used to query all types of XML variants. This supports XPath 2.0 syntax for powerful search predicates. The SQL DML extensions allow in-place XML updates. These features will deprecate the older SqlXml libraries, but keep them around for interaction with SQL Server 2000 systems.
The new classes in the System.Data.SqlClient namespace are summarized in Table 8.
System.Data.OracleClient
The Oracle client namespace is contained within a separate DLL called System.Data.OracleClient. This must be added to your project references when working with Oracle back-end databases. The only additions to this namespace are the ConnectionStringBuilder and the ClientFactory. The new OracleDataAdapter. UpdateBatchSize property facilitates speedier batch updates.
The new classes in the System.Data.OracleClient namespace are detailed in Table 9.
System.Data.SqlTypes
SQL datatypes can be used directly in ADO.NET 2.0 applications. Some text can be set to a type of SqlString that automatically handles anomalies like DBNull. There are also several types for handling large text and binary columns in SQL Server 2005. Read and write chunks are no longer needed. Instead, use the SqlBytes and SqlChars types for full data access up to 2 GB. SqlXml accommodates the new XML column datatype in SQL Server 2005.
The new classes in the System.Data.SqlTypes namespace are illustrated in table 10.
Designers, Wizards, and Visual Studio Tools
There is only so much one can say about namespace and framework enhancements. We will now focus on the numerous Visual Studio designers and tools for visually coding ADO.NET applications.
Snippets are your friends! Code fragments are catalogued in a smart XML structure that allows linked parameters with help from Intellisense. The current beta bits include 27 snippets in the database folder. Simply right-click on your code, select Insert Snippet, and scroll down to the database section (see Figure 2).
The code inserted into your application has highlighted parameters with suggested names. Type over one of the names and watch all of the related instances change at the same time (See Figure 3). There will be hundreds, if not thousands, of snippet samples when the product ships next year. You can also write your own and exchange them with friends!
There are new connectivity tools and options. You can make a DataConnection to an existing database using any of the providers mentioned earlier. There is a new feature that allows direct connection to a local database file. This would be an Access MDB or SQL Server MDF. You will not need MSDE or SQL Server installed to do this. You can also connect to a Web service or any business object and map the results to your databound controls (See Figure 4).
The providers included in the beta release are listed in Figure 5.
The Data Source Configuration Wizard essentially replaces the DataAdapter configuration and dataset generation tools from VS 2003. A selection of objects in the SQL Server Northwind database can be seen in Figure 6.
The Windows forms and ASP.NET designers are not in sync yet. Both offer some interesting tools and technologies. ASP.NET relies on the familiar Server Explorer model to present data connectivity. However, the drag-and-drop capabilities are great. The page in Figure 7 was created by dragging one table to a blank ASPX form.
This action creates a grid and bound DataConnection. The grid has a Task Pane with options for code-free paging, sorting, and editing. See Figure 8 for the HTML markup.
Notice the new SqlDataSource server control. It has functionality similar to a DataAdapter combined with a Dataset. This binds directly to other server controls like grids. And all of this happens with no code, but you are welcome to add your own!
Really, no code! Note the use of a partial class. This is a new compiler feature that further separates your business code from the machine-generated stuff. This manages to hide the designer-generated code that is kept in a separate file and partial class.
Conclusion
On the code side, ADO.NET 2.0 shows evolutionary refinement that increases performance while adding flexibility and scalability. This flexibility is evident in the Visual Studio 2005 designers and wizards that provide a richer development experience with less lines of hand-tuned code. These technologies will make Web site, smart client, and mobile device development faster and more efficient.
Subscribe to .NET Developer's Journal Email News Alerts
Subscribe via RSS
