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.

Can you provide the code you used to generate the SQL? – mellamokb Aug 01 '11 at 21:23 — mellamokb, Aug 01 '11 at 21:23

score 5 · Answer 1 · edited May 23 '17 at 11:51

5

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)

edited May 23 '17 at 11:51

Community

1
1

answered Aug 01 '11 at 21:24

Max Toro

28,282
11
76
114

score 2 · Accepted Answer · answered Aug 01 '11 at 22:18

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.

`ParameterMarkerPattern` is `{0}` because `@` is already included in `ParameterNamePattern`, so you should check both. — Anton Shepelev, Jan 30 '18 at 12:37

Get the parameter prefix in ADO.NET

2 Answers2

Linked