Parameter naming using System.Data.Common

Question

This is probably an oldie-but-goodie. I am using System.Data.Common for an interchangeable Oracle/SQL Server/SQLite data access library. During the constructor I take the connection string name and use that to determine the underlying provider type. The reason I do this is to handle the different IDbParameter naming conventions for each provider. For example, Oracle likes :parameter whereas SQL Server and SQLite like @parameter. The default is ? to cover Oledb.

Question: Is this all unnecessary and is there some simple thing I'm missing that should simply take care of this? If my IDbCommand.CommandText = "select id, name from my.table where id = :id" am I covered? For now I'm just adopting ? as the default and then RegEx'ing my way to the right parameter identifier before executing the command.

Thanks.

        /// <summary>
    /// Initializes a new instance of the <see cref="RelationalGateway"/> class.
    /// </summary>
    /// <remarks>You must pass in the name of the connection string from the application configuration
    /// file rather than the connection string itself so that the class can determine
    /// which data provider to use, e.g., SqlClient vs. OracleClient.</remarks>
    public RelationalGateway(string connectionStringName)
    {
        if (string.IsNullOrEmpty(connectionStringName)) throw new ArgumentNullException("connectionStringName");
        if (ConfigurationManager.ConnectionStrings[connectionStringName] == null ||
            ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString.Length == 0 ||
            ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName.Length == 0)
        {
            throw new InvalidOperationException(string.Format(
                                                    "The configuration file does not contain the {0} connection ",
                                                    connectionStringName) +
                                                "string configuration section or the section contains empty values. Please ensure the " +
                                                "configuration file has the appropriate values and try again.");
        }

        _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        _providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
        _theProvider = DbProviderFactories.GetFactory(_providerName);
        _adapter = _theProvider.CreateDataAdapter();
        //GetConnection();
        DetermineProviderSpecificParameters();
    }

The DetermineProviderSpecificParameters bit basically figures out "?" or ":" or "@" or something else.

UPDATE Here's how I'm handling the details thus far:

1. Get the right parameter string:

   private void DetermineProviderSpecificParameters() { // Check for supported providers. This is so that parametized queries to limit // by spatial extent are correctly created. string shortName = _providerName.Substring(_providerName.LastIndexOf(".") + 1);

       switch (shortName)
       {
           case "SqlClient":
               _param = "@";
               _ql = "[";
               _qr = "]";
               break;
           case "SQLite":
               _param = "@";
               _ql = string.Empty;
               _qr = string.Empty;
               break;
           case "OracleClient":
               _param = ":";
               _ql = string.Empty;
               _qr = string.Empty;
               break;
           default:
               _param = "?";
               _ql = string.Empty;
               _qr = string.Empty;
               break;
       }
   }
   

2. call a little helper before I execute each command to "cleanify" or "parameterific" it or however we call this half-assed hack:

   private void MakeProviderSpecific(IDbCommand command)
   {
       foreach (IDataParameter param in command.Parameters)
       {
           param.ParameterName = GetProviderSpecificCommandText(param.ParameterName);
       }
       command.CommandText = GetProviderSpecificCommandText(command.CommandText);
   }
   

3. And this calls a little regex to do to do:

   public string GetProviderSpecificCommandText(string rawCommandText)
   {
       return Regex.Replace(rawCommandText, @"\B\?\w+", new MatchEvaluator(SpecificParam));
   }
   

Yuck. Still searching for a relatively simple solution but the advice thus far is certainly appreciated.

Answer 1

Seems there's no convention or API for this. ORMs such as nhibernate also implements their own mapping of the placeholder prefix for each driver.

Answer 2

You could take a slight performance hit and use the System.Data.OleDb classes. That way, you could use the same code regardless of the database. Or you could use an Inversion of Control framework like Unity. You could then require that your data access class be injected with the appropriate factory for the database, parameters and such that the caller desires to use.

Answer 3

I have done something like this for Salamanca : see ParameterBuilder.cs. This code uses :

• protected methods on DbCommandBuilder, called via reflection (will only work in full trust mode, I assume) : GetParameterPlaceholder and GetParameterName.
• the information returned by DbConnection.GetSchema. In a perfect world, I could only use this, but I failed to see how so far...

The thing is that you need a valid name for your parameter ("@name" in Sql Server, "name" in Oracle), and a valid placeholder in your SQL query ("@name" in Sql Server, ":name" in Oracle).

1. With a proper connection, GetParameterName will give you a valid name for your parameter.

2. Create your placeholder :

   • Either via GetParameterPlaceholder.

   • Or query the DbMetaDataColumnNames.ParameterMarkerFormat value contained in the schema for your connection . You should be able to create your placeholder by using this string as a format string, taking the previous parameter name as input (implying that the format string is "{0}" for Sql Server and ":{0}" for Oracle) :

     // DbConnection connection;
     // string parameterName
     DataRow schema=connection.GetSchema(DbMetaDataCollectionNames.DataSourceInformation).Rows[0];
     string placeholder=string.Format(
         CultureInfo.InvariantCulture,
         (string)schema[DbMetaDataColumnNames.ParameterMarkerFormat],
         name.Substring(0, Math.Min(parameterName.Length, (int)schema[DbMetaDataColumnNames.ParameterNameMaxLength]))
     );
     

This has been tested with Sql Server, Access, Sqlite and Oracle (but note that, quite unsurprisingly, this will not work as is with ODP .NET...).