Retrieving the Parameter Marker Format When Using Generic Database Factories

With .NET 2, Microsoft introduced something called DbProviderFactories that allows you to work with databases in a generic fashion. That is, you don't create strongly typed objects like a SqlConnection or an OleDbConnection at design-time, but defer the decision what object to create till run-time. This way, you can write code that works against a number of different databases, like SQL Server, Oracle and OleDb databases like Microsoft Access.

One of the big differences between how these databases operate is the way you must name the parameters you send to a stored procedure or query. For example, SQL Server uses the @ symbol (@userName) and even allows you to leave out a prefix altogether. Microsoft Access in turn uses a question mark without a specific name while Oracle uses a colon as the prefix. Now, how do you deal with these differences when you don't know what database you're working with till run-time?

Introduction

Let's briefly look at how these generic database factories work. Take a look at the following example:

ConnectionStringSettings mySettings = 
        ConfigurationManager.ConnectionStrings[0];

string myProviderName = mySettings.ProviderName;

DbProviderFactory myFactory = 
        DbProviderFactories.GetFactory(myProviderName);

DbConnection myConnection = myFactory.CreateConnection();
myConnection.ConnectionString = mySettings.ConnectionString;

DbCommand myCommand = myConnection.CreateCommand();

myCommand.CommandText = "SELECT Id, Description FROM MyTable";
myCommand.CommandType = CommandType.Text;

myConnection.Open();

using (DbDataReader myReader = 
          myCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
  while (myReader.Read())
  {
    MessageBox.Show(myReader[0].ToString());
  }
  myReader.Close();
}

myConnection.Close();
myConnection.Dispose();

This code first retrieves a ConnectionStringSettings object based on the first available connection string in the config file. I am using the first connectionstring for demo purposes only. In your own code, you could access the connectionstring by its name instead. From this object, it uses the ProviderName property to determine the type of the provider. Possible values for this property are System.Data.SqlClient, System.Data.OleDb and so on. It then creates a DbProviderFactory object that, as a real-world factory, is used to create instances of other objects, like DbConnections and DbCommands. The code then builds up a DbCommand object, executes a SQL statement and loops through the results from the query.

In my latest book, ASP.NET 2.0 Instant Results I devoted an entire chapter to working with database factories. Chapter 6 - The Wrox Blog has a generic data access layer that works with SQL Server and an Access database. You'll find a lot more details and explanation about database factories in that chapter.

One problem that this chapter didn't solve very well was the parameter problem. The Blog uses a number of stored procedures / queries that are parameter driven. So, I needed a way to determine the right parameter name and constructed a simple Select Case statement that returned the proper format, like this:

Public Shared Function ReturnCommandParamName( _
        ByVal paramName As String) As String

  Dim returnValue As String = String.Empty

  Select Case AppConfiguration.ConnectionStringSettings.ProviderName.ToLower()
    Case "system.data.sqlclient"
      returnValue = "@" & paramName
    Case "system.data.oledb"
      returnValue = "?"
    Case Else
      Throw New NotSupportedException("The provider " & _
           AppConfiguration.ConnectionStringSettings.ProviderName & _
           " is not supported")
   End Select

  Return returnValue

End Function

While this code certainly works, it has one big drawback: you'll need to change it whenever you need to support another data provider, like Oracle.

Fortunately, all the information you need to retrieve the parameter info at run-time is already available for you on your system. Information like the parameter format marker can be queried by executing GetSchema against an open DbConnection object. The following code snippet shows how you can do this:

ConnectionStringSettings mySettings = 
       ConfigurationManager.ConnectionStrings[0];

string myProviderName = mySettings.ProviderName;

DbProviderFactory myFactory = 
       DbProviderFactories.GetFactory(myProviderName);

DbConnection myConnection = myFactory.CreateConnection();
myConnection.ConnectionString = mySettings.ConnectionString;

myConnection.Open();

string parameterMarker = myConnection.GetSchema("DataSourceInformation")
       .Rows[0]["ParameterMarkerFormat"].ToString();

myConnection.Close();
myConnection.Dispose();

This code is similar to the previous snippet: it uses a DbProviderFactory to create and open a generic DbConnection object. The GetSchema method is then used to retrieve the parameter marker format for the current connection type. For SQL Server, this returns {0}, while for a Microsoft Access database this returns ?. You can use this format in combination with the String.Format method to get the right parameter name. For example:

string paramName = String.Format(parameterMarker, "firstName");

For SQL Server, this will return firstName. For an Access database, you'll get ? and for an Oracle database you get :firstName.

You shouldn't call this code every time you need a parameter name because it takes quite some resources. Instead, you should cache the value somewhere, or store it in a static variable accessible by your DAL code.

Download Files

Download the Source Code for this Article
The download contains a simple Windows Forms application with the code shown above to retrieve the parameter marker format. When the application is loaded, a drop down list is filled with all the available connection strings from the web.config file. If you want to add another connection string, don't forget to add the providerName attribute that should point to a valid data provider type.


Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Tuesday, May 15, 2007 12:33:51 PM Veerabhadra said:
Hi,
I read your article and its very good. But my question is even though i haven't placed "ParameterMarker" in my code, the code is still able to connect to my both "MS-Access" and "SQL Server" database.
Can you please tell why it is doing like this. Is the parameter marker not required or not?
Thanks,
Veerabhadra
On Tuesday, May 15, 2007 1:08:43 PM Imar Spaanjaars said:
Hi there,

Hard to tell without seeing your actual code.

Can you post it in a relevant category at p2p.wrox.com and then post a link here?

Imar
On Thursday, September 06, 2007 6:28:29 AM Gian MAria said:
For Sql server the ParameterMarkerFormat returns {0} so the parameter name did not get prefixed with @ and the query does not work

Gian Maria
On Thursday, September 06, 2007 8:09:09 PM Imar Spaanjaars said:
Hi Gian,

I believe that even with an empty marker, it still works in SQL Server....

Imar
On Thursday, December 11, 2008 4:13:21 PM Juan Antonio Bosca said:
Hello,

Gian is right. For SqlClient data provider, ParameterMarkerFormat returns {0} instead of @{0}, so the query does not work ("Invalid column name" error) if you do not include @ into the parameter name (goodbye to generic code...).

Is it a bug of this .NET data provider?
On Monday, April 27, 2009 4:45:45 AM Marcelo said:
Well, for parameters it will work, but i was thinking about anothers problems, like "top 10" in SqlServer and "Limit" on MySQL. ?

On Monday, April 27, 2009 5:00:16 PM Imar Spaanjaars said:
Hi Marcelo,

I am not 100% sure, but I don't think you can retrieve these. Provider specific features are, well, provider specific so there's much sense in using them in provider independent code.... ;-)

Cheers,

Imar

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.