Wrox Home  
Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL
by Wallace B. McClure, Gregory A. Beamer, John J. Croft, IV, J. Ambrose Little, Bill Ryan, Phil Winstanley, David Yack, Jeremy Zongker
December 2005, Paperback

Excerpt from Professional ADO.NET 2: Programming with SQL Server 2005, Oracle, and MySQL

Not Another ADO Release!

Autumn 2005 marked the arrival of the .NET Framework 2.0. But never fear — this release of ADO.NET won't force you to uproot existing code and rewrite it in the new format. Your old code should work under the new Framework (unless you're still using any of the bugs they've fixed — but you wouldn't do that . . . would you?).

No Revolutions

The new ADO.NET API is the same as before; it hasn't been turned upside down. In fact, the existing API has been carefully extended so that the code and applications you've written in 1.0 or 1.1 should continue to work without any change.

All of the features introduced in ADO.NET 2.0 can be used incrementally. In other words, if you want to use one of the new features, all you need to do is add on to your existing code; you don't have to switch to a whole new API model. In general, you can preserve your existing code base, only adding the things you need for one particular feature in one part of your application.

We're telling the truth — the API still works!

Obsolete APIs

With every release of .NET, Microsoft has a bit of a spring clean, and this release is no different. They've killed off a few of the methods, types, and properties. What we said earlier still applies, however: Any old code you have with methods or types that have been deprecated or made obsolete will continue to run under ADO.NET 2.0, even if it has references to APIs that have been removed in .NET Framework 2.0. There is one catch: You can't recompile those applications with obsolete APIs under .NET 2.0 with the references remaining.

For example, take something simple, such as this Console application, which was originally built in .NET 1.0 under Visual Studio 2002. It instantiates the System.Data.OleDb.OleDbPermission class with a blank constructor, which was made obsolete in .NET 1.1 and remains obsolete in .NET 2.0:

namespace DotNetHosting
Sub UseObcoleteClass

    Dim ODP As New System.Data.OleDb.OleDbPermission

End Sub

The preceding code compiled (and still compiles) in .NET 1.0 without any problems. If, however, you attempt to compile the same code under .NET 1.1 or 2.0, the compiler will not be particularly nice to you, presenting you with a rather colorful compilation error:

'Public Sub New()' is obsolete: 'OleDbPermission() 
	has been deprecated.  
Use the OleDbPermission(PermissionState.None) constructor.

When you run into that kind of exception and you want to compile your application under that version of the Framework, you must change your code to bypass the compiler errors. In the case of the preceding example, you can see that the compiler error that was thrown also describes the fix you should perform.

This issue only exists, however, if you wish to recompile your applications. You don't need to recompile them, of course, just to have them run under a new version of the Framework. In fact, if you've already installed .NET 2.0, it's likely that many of your .NET applications are already running under it. (You can confirm this by checking the value of System.Environment.Version.ToString(). It will tell you the version of the Framework under which your applications are running.)

As long as you don't recompile your applications, they will continue to work fine under any version of the Framework. You can force an application to run under a specific version of the Framework very easily with the addition of an entry to the application's configuration file (app.config/web.config) that defines the version of the Framework the application is to run under:

   <supportedRuntime version="v1.1.4322" />

In short, you don't need to recompile your existing applications to take advantage of the 2.0 release of the .NET Framework. In fact, you're probably already running existing applications that were developed in .NET 1.0 and 1.1 under .NET 2.0. Moreover, if you need to recompile your existing applications in .NET 2.0, you'll have to clean up anything that has been removed from the Framework.

Try your applications under .NET 2.0. You might find they work flawlessly and that you can take complete advantage of the performance increases in both ADO.NET 2.0 and the Framework in general at no cost.

We can't guarantee your code will work. Microsoft says it should, but of course, we all know their track record on that point — it means they may be writing some future notes of their own.

APIs in Their Twilight Months

As well as dealing with types and methods that have been removed in .NET 2.0 or previous incarnations of the Framework, .NET 2.0 introduces changes of its own, marking many types and methods as obsolete — in other words, they won't work in future versions of the Framework.

In the past, Microsoft has dealt harshly with the deprecation of members and types. In the transition between .NET 1.0 and .NET 1.1, types and members marked as obsolete would not compile under 1.1. With .NET 2.0, types and methods that have the mark of death placed on them by the Microsoft Grim Reaper are not being blocked outright. Rather, the compiler will provide warnings, informing developers of the API's impending death.

What this means for you, the developer, is that you can continue to use the APIs that have been placed on death row. However, you already know that the code won't compile in .NET 2.0, so be forewarned.

A full list of all the changes between all versions of the .NET Framework can be found at www.gotdotnet.com/team/changeinfo/default.aspx.

As an example of this deprecation and warning system, take a look at the following code, which uses the SqlParameterCollection.Add(string,string) method signature that has been marked as obsolete in .NET 2.0:

Sub SqlSqlCommandAddParameter()
    Dim SqlComm As New System.Data.SqlClient.SqlCommand
    SqlComm.Parameters.Add("@Socks", "Smelly");
End Sub

By default, the code will compile and run without any issues under .NET 2.0, but the compiler will output a warning that indicates the method signature has been marked as obsolete. The warning looks like this:

'Public Function Add(parameterName As String, value As Object) 
  As System.Data.SqlClient.SqlParameter' is obsolete: 
  'Add(String parameterName, Object value) has been deprecated.  
   Use AddWithValue(String parameterName, Object value). 

Think of the warning as a death knell ringing on the APIs that have been marked as obsolete.

To be completely accurate, the preceding code may or may not compile, depending on the settings of your build environment and whether warnings are treated as errors. If it doesn't compile cleanly, you'll need to change the code to use new or alternative methods suggested by the compiler in the error message.

If you find yourself receiving compiler warnings, change your code. It's not worth the hassle down the line after you've forgotten all about the code and then find yourself needing to change its functionality or fix a bug (not that our code ever has any. . .), or discovering that it won't even compile on future versions of .NET.

The Generic Factory Model

If you don't know what the Generic Factory Model is and you develop against different database servers, then you're in for a real treat. Microsoft has outdone themselves with ADO.NET 2.0 and come to the rescue of all multiplatform database developers.

One day you might be developing against a SQL server; the next you might be developing against an Oracle server. It's possible down the line you'll be asked to develop against a fridge freezer. Whatever your data source, ADO.NET 2.0 gives you a provider-agnostic platform on which to build your applications, meaning you can write your code once and have it work on any data source you wish.

The Generic Factory Model is an architecture that enables access to any database, from one set of code. ADO.NET 2.0 has that architecture plumbed right into the Framework, so you can use it too.

Inside the System.Data.Common namespace are some lovely new classes that enable us to make platform-independent code very easily, but before we get our hands dirty, we'll quickly run through the Generic Factory Model.


During the Dark Ages (when our only Framework was .NET 1.0), there were three providers in the form of the following namespaces:

  • System.Data.SqlClient
  • System.Data.Odbc
  • System.Data.OleDb

In those days, we programmers were encouraged by samples all across the Internet, in books, and by our peers to directly use the most appropriate set of classes from the correct namespace. Doing this was problematic, however, because after a specific provider such as SqlClient was hard-coded into the application, the code could no longer be used to look at an Oracle database server using the OracleClient provider. In other words, we were locked into a single provider, and when our bed was made — as the saying goes — we had to lie in it.

If you wanted to write platform-agnostic code in the olden days (nearly three long years ago), you'd have to use a bit of black magic, interfaces, and a switch statement:

Public ReadOnly Property Connection() 
	As System.Data.IDbConnection
    Select Case OldGenericFactoryHelper.Provider
     Case "SqlClient"
         Return New System.Data.SqlClient.SqlConnection
     Case "Odbc"
         Return New System.Data.Odbc.OdbcConnection
     Case "SqlClient"
         Return New System.Data.OleDb.OleDbConnection
     Case Else
         Return Nothing
    End Select
End Get
End Property

As you can see, the method returns an interface of type IDbConnection, which is a generic implementation of the Connection class that all provider-specific classes implement (SqlConnection, OdbcConnection, and so on). This approach enabled you to code against the interfaces, rather than the specific providers, but it always felt a little dirty.

Any application employing this approach had a design that was completely platform-independent. The data access architecture is shown in Figure 1.

Figure 1
Figure 1

One of the main problems with this model was that each time a provider was added to the system, the switch statement had to be altered. The fun didn't stop there, though. You also needed switch statements for all of the other provider-specific classes, such as those that implement IDbCommand, so that your applications could retrieve the right Command class (SqlCommand, OleDbCommand, OdbcCommand, and so on).

Although this wasn't a massive problem, and the approach generally worked well, the ADO.NET 2.0 team at Microsoft came up with a much better solution, called the Generic Factory Model.

ADO.NET to the Rescue

ADO.NET 2.0 solves the aforementioned problem by introducing Factories into the Framework. Just like a real factory, a Factory takes in raw materials and produces fully working products. In this case, the raw materials are the providers we want to use, and the products are the provider-independent classes we need.

The provider-independent classes include DbConnection, DbCommand, and DbParameter, as well as a whole host of other classes. The way they are used is very similar to the way they were used in the old model, but they come from a Factory built into the Framework — you don't have to write the code yourself.

The new architecture is shown in Figure 2.

Figure 2
Figure 2

In other words, you no longer have to code any switch statements. Better yet, the .NET Framework will do all the hard work for you. For example, if you want a SqlClient connection object, all you need is the following:

Public Shared Function 
	GetConnection(ByVal providerName As String)
End Function

If you want a command object, then it's as simple as calling CreateCommand() on the Factory returned by GetFactory(). This code is much cleaner and self-maintaining. You never have to modify it, even to use new providers — they just appear to your code automatically.

See how all of this makes life easier? We're speaking one language only, that of the provider-agnostic class implementations. Now let's go into a little more detail regarding the real-world implementation of ADO.NET 2.0 Provider Factories.


The Factory is the key to the Generic Factory Model; without it, there would be no model. It's the creator of all of your classes, and it's the place where all of the real work happens.

The .NET Framework is shipped with a bundle of Factory implementations in the box. They're defined in the machine.config file inside the Framework folders. You can access the machine.config file and take a look for yourself:


There are five providers out of the box, but as with everything else in the .NET Framework, this is customizable — you can easily add your own providers to extend the list on any machine with the Framework installed. The five built-in providers are listed in the following table.

Name Invariant Type
Odbc Data Provider System.Data.Odbc System.Data.Odbc.OdbcFactory
OleDb Data Provider System.Data.OleDb System.Data.OleDb.OleDbFactory
OracleClient Data Provider System.Data.OracleClient System.Data.OracleClient.OracleClientFactory
SqlClient Data Provider System.Data.SqlClient System.Data.SqlClient.SqlClientFactory
SQL Server CE Data Provider Microsoft.SqlServerCe.Client Microsoft.SqlServerCe.Client.SqlCeClientFactory

As mentioned previously, it's very easy to add your own providers. There are various places where you can define a provider so it can be used, including the machine.config, app.config, and web.config files. Just add a small section to one of the configuration files (and have the provider installed on the machines in question!):

    <add name="Odbc Data Provider" 
 	description=".Net Framework Data Provider for Odbc" 
	type="System.Data.Odbc.OdbcFactory, System.Data, 
	Culture=neutral, PublicKeyToken=b77a5c561934e089" />

Extensibility is the operative word here. The Generic Factory Model is easy to use, easy to implement, and very easy to justify.

Generic Factory versus Specific Providers

Decisions, decisions. The new features thrown at the feet of .NET developers in ADO.NET 2.0 are extensive and formidable, so in order to help you decide if the shift is worth it, we'll briefly run through the pros and cons of the Generic Factory Model (in comparison to directly accessing a provider such as SqlClient).


Here are some reasons to use the Generic Factory Model:

  • Even if you just use one provider today, your code can be moved to another provider without any effort later, saving you and your company both time and money.
  • The potential market for your applications is massively increased. Some shops will only use Oracle or DB2, for example. By giving your client the option of any provider, you widen your scope to customers that are currently outside your reach.
  • You and your development team only need to know one API; the specifics of each provider can be abstracted away, enabling you and your colleagues to concentrate on the applications. Plus, using the Generic Factory Model will leave loads of room in your head to watch Internet Flash animations relating to woodland creatures and fungi.

Of course, there are always some disadvantages too:

  • There's a good chance you already know your data provider inside out. If you work with SQL Server, you'll know the SqlClient namespace. If you decide to move to the provider model, you'll have to learn an entirely new data access API.
  • If your current applications contain data access code that is not provider-agnostic (generic) and you decide to go the generic provider route, you'll have to either rewrite all your existing data access code or maintain both the old provider-specific code and any new provider-generic code.
  • While the majority of your data Access code in ADO.NET 2.0 can be provider-agnostic, huge swathes of your applications are still not generic. For example, any exception thrown from a database server will still be specific to the provider from which it's been thrown, meaning you have to deal with all the "special cases" for every provider manually.

Applications can be weighed against one another in terms of maintainability, security, and performance. For some people, maintainable code is more important than secure code, whereas for others, security is the primary concern. For the majority of developers, however, performance is the only thing they need to worry about — in other words, the only thing the boss will notice.