By Greg DeMichillie [bio]
Posted: Sep. 12, 2005

• Sidebar: Why Build Database Code with .NET?
• Illustration: ADO.NET Provider Factories

The following is the full text of an article published by Directions on Microsoft, an independent research firm focused exclusively on Microsoft strategy & technology. Each month we make one or more key articles available to non-subscribers.

.NET Comes to SQL Server

VS 2005 includes new features to support SQL Server 2005's integration of the .NET Framework Common Language Runtime (CLR), the software component which loads and runs applications written in languages such as C# and VB.NET, as well as portions of the .NET Framework APIs. Integrating the CLR into SQL Server 2005 allows server-side database application code, such as stored procedures, to be written using those languages rather than Microsoft's Transact SQL (T-SQL) dialect of the SQL database query language. This feature gives developers a simpler, more reliable way to write server-side database code, which in turn can yield important performance and maintenance advantages. (For a discussion of why a developer might want to use a .NET language rather than T-SQL, see the sidebar "Why Build Database Code with .NET?".)

.NET-SQL 2005 integration in VS 2005 takes the form of a new SQL Server project type that supports T-SQL as well as C# and VB.NET. A SQL Server project is connected to a specific instance of SQL Server 2005 and supports using .NET for a variety of database objects, including the following:

• Stored procedures—application code typically used for complex data validation or other processing tasks or to encapsulate commonly used business rules that must be run regardless of how the data is accessed
• Triggers—special kinds of stored procedures that are executed when specified database events occur, such as when a new record is inserted or an existing record is modified
• Aggregates—code used to summarize the contents of a database table, such as producing a sum or an average.

Once a project is created, VS 2005 is able to compile the code into a .NET assembly (similar to a Windows DLL) and automatically deploy the assembly to the specified SQL Server. In some cases, however, developers must manually deploy their assembly to the SQL Server. The most common case is when the assembly being built relies on other assemblies that are not part of the version of the .NET Framework built in to SQL Server. Once deployed, the assembly can be debugged from within VS.

Code running within SQL Server accesses the data stored in the database through a new set of APIs known as System.Data.Sql. These APIs are designed to resemble client-side APIs used to access SQL Server. However, they are not identical to the client APIs, so code that accesses a SQL Server from the client must be modified to run within the database itself. Even if the APIs were identical, copying and pasting data access code from the client to the server would likely result in poor performance and scalability because the techniques developers use to optimize client applications are quite different from the techniques used to optimize code running in the server.

ADO.NET Improvements

VS 2005 also includes a new version of ADO.NET—Microsoft's database APIs for the .NET Framework. Some improvements offered by ADO.NET 2.0 help developers build better client applications regardless of the back-end database they are using, while others are designed specifically to take advantage of capabilities in SQL Server 2005. In addition, ADO.NET's extensibility architecture has been improved to better support third parties seeking to connect ADO.NET to other database engines.

General API Enhancements

ADO.NET 2.0 offers a number of improvements that help developers regardless of the database engine they are using.

Asynchronous processing in ADO.NET 2.0 performs database operations such as queries or updates in the background, while the client application continues with other tasks, eventually receiving a notification when the query or update is complete. Asynchronous processing allows applications to appear more responsive to end users by not pausing every time data is sent or received. To take advantage of asynchronous processing, however, developers must modify their applications.

Batch operations allow developers to group sets of operations, such as insertions, updates, or deletions, and send those operations to the server en masse rather than sending each update individually. Batch operations perform more quickly, particularly when network bandwidth is limited. Previous versions of ADO.NET allowed clients to work with offline copies of data and update the database with a single function call, but ADO.NET still sent the updates to the database one row at a time. Batch operations aren't supported on all database engines, but are supported on both SQL Server and Oracle.

Enhancements for SQL Server

In addition, ADO.NET 2.0 offers several new capabilities that require SQL Server.

Bulk copy quickly transfers large amounts of data from a client to a SQL Server. Unlike normal database insertions, bulk operations do not, by default, cause triggers to be executed. Bulk copy operations are most often used to transfer data from one database to another.

Client support for database mirroring allows an alternate SQL Server to be used in the event of a server failure. Database mirroring is an alternative to clustering that improves database reliability but without the specialized, and more expensive, hardware. Unlike clustering, however, which is transparent to client applications, database mirroring requires client applications to specify the alternate server to be used. ADO.NET 2.0 allows developers to specify an alternate server when establishing a database connection.

Password changing lets users of ADO.NET 2.0 applications change the password associated with a database.

While none of these features are revolutionary, they do give developers, particularly those already using SQL Server, the ability to create faster and more reliable applications.

Better Support for Third Parties

ADO.NET 2.0 better isolates an application from the specifics of the database engine.

In particular, ADO.NET offers more provider independence, letting developers write applications that work across a variety of data sources. In ADO.NET, a provider is the piece of code that connects ADO.NET to a specific class of data source (e.g., Oracle databases). The .NET Framework includes providers for many commonly used database engines, including Oracle and SQL Server, and can use existing ODBC and OLE DB database drivers as providers. Microsoft also encourages third parties, such as DataDirect (formerly known as InterSolv and Merant), to create ADO.NET providers for database engines not included in the .NET Framework.

However, in the initial versions of ADO.NET, developers were often forced to write code specific to one provider for tasks such as enumerating all of the tables in a database. ADO.NET 2.0 improves provider independence by expanding the set of APIs that can be used across all providers (known as System.Data.Common) and by including a Provider Factory, which isolates the application code from the specifics of the provider being used. (For an illustration of this API, see "ADO.NET Provider Factories".) Provider independence is helpful not only to system integrators who want to reuse code from one application to another but it also helps Microsoft ensure that its database development tools work across a variety of database systems.

Bringing Data Access Simplicity Back to VB.NET

Although the 2002 introduction of the .NET Framework, ADO.NET, and VB.NET brought new capabilities for database application developers using VB, it made creating database front ends more complex. VS 2005 seeks to restore some of the ease of use that was a hallmark of early versions of VB. Specifically, it allows developers to drag a database table or query onto a Windows Form to automatically generate the appropriate controls to display the data. (If the developer doesn't want to display the entire contents of each record, he can also drag and drop specific columns of the database to build a form.)

In addition to automatically generating a form, VS 2005 brings back the "VCR-style" controls of previous versions. These controls appear in an application's toolbar and allow the user to scroll forward and back through the data similar to the way that a VCR's fast-forward and rewind buttons work.

Although these changes may seem trivial, to thousands of VB developers the lack of drag-and-drop data application support and VCR-style controls, along with the need to learn a new language and APIs, constituted enough of a roadblock to cause them to hold off on moving to .NET. By reintroducing these features, Microsoft hopes to entice the remaining VB6 holdouts to make the jump to .NET.

Resources

More information on SQL Server 2005 can be found in "SQL Server Gets .NET, XML" on page 5 of the Jan. 2004 Update.

Technical details on SQL Server's CLR integration can be found at msdn.microsoft.com/library/en-us/dnsql90/html/sqlclrguidance.asp/.

The SQL Server Developer Center is at msdn.microsoft.com/sql/.

The Visual Studio 2005 Developer Center is at msdn.microsoft.com/vs2005/.