Retrieving the Parameter Marker Format When Using Generic Database Factories

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.