Get the parameter prefix in ADO.NET

Question

I want to generate several SQL statements based on a column list using the column names as parameters.

Edit: C#

var columns = new string[] { "COL1", "COL2" };
var tableName = "TABLE_1";
var prefix = "@"; // TODO get this from the provider factory

string sqlInsert = string.Format(
    "INSERT INTO {0}\n( {1}) VALUES\n({2})",
    tableName,
    string.Join(",  ", columns),
    string.Join(", ", columns.Select(c => prefix + c)));

Generated SQL:

INSERT INTO TABLE_1
( COL1,  COL2) VALUES
(@COL1, @COL2)

This works with the SqlClient. But I'm using the abstract classes in System.Data (DbCommand, DbParameter, etc.) and different data providers such as Oracle, MySQL, Postgres etc based on the connection string settings in the app.config. Thus I need to know which prefix I have to use. For MS-SQL it is the @, Oracle uses the :, the rest I actually don't know.

Is there a way to get this prefix character from the provider factory?

System.Data.SqlClient.SqlClientFactory.Instancedoesn't have such an information or at least I couldn't find it.

Otherwise, can you give me a list for the common databases?

Edit: The target platforms are .NET 2 to .NET 4 and the information should be available through the provider factory.

Answer 1

There are 2 DbCommandBuilder methods that will help you, GetParameterName and GetParameterPlaceholder. These are protected, so you'll need a little reflection to use them.

Check out my answer to the following question for an implementation (also included in the DbExtensions library):

What are your favorite extension methods for C#? (codeplex.com/extensionoverflow)

Answer 2

I found the answer, but I cannot reproduce how I found it:

http://www.codewrecks.com/blog/index.php/2007/09/06/about-parametermarkerformat/

The DbConnection can provide a schema table that also contains the correct format strings for creating command parameter names, except for SqlClient!!

DbProviderFactory myFactory = DbProviderFactories.GetFactory(myProviderName);

using (DbConnection myConnection = myFactory.CreateConnection())
{
    myConnection.ConnectionString = mySettings.ConnectionString;
    myConnection.Open();

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

    myConnection.Close();
}

For SqlCclient parameterMarker is {0} but should be @{0}. I'll investigate a bit more to find out what is contained in the other schema table columns.