在 ADO.NET 中获取参数前缀 [英] Get the parameter prefix in ADO.NET

问题描述

我想使用列名作为参数，基于一个列列表生成几个 SQL 语句.

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

编辑: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}
( {1}) VALUES
({2})",
    tableName,
    string.Join(",  ", columns),
    string.Join(", ", columns.Select(c => prefix + c)));

生成的 SQL:

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

这适用于 SqlClient.但是我使用 System.Data 中的抽象类(DbCommand、DbParameter 等)和基于连接的不同数据提供程序，例如 Oracle、MySQL、Postgres 等app.config 中的字符串设置.因此我需要知道我必须使用哪个前缀.对于 MS-SQL，它是 @，Oracle 使用 :，其余的我实际上不知道.

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.Instance没有这样的信息或者至少我找不到.

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?

编辑:目标平台是 .NET 2 到 .NET 4，信息应该可以通过提供者工厂获得.

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

推荐答案

我找到了答案，但我无法重现我是如何找到它的:

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

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

DbConnection 可以提供一个模式表，其中还包含用于创建命令参数名称的正确格式字符串，SqlClient 除外！！

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();
}

对于 SqlCclient parameterMarker 是 {0} 但应该是 @{0}.我将进一步调查以找出其他架构表列中包含的内容.

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.

这篇关于在 ADO.NET 中获取参数前缀的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！