获取参数preFIX在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}\n( {1}) VALUES\n({2})",
    tableName,
    string.Join(",  ", columns),
    string.Join(", ", columns.Select(c => prefix + c)));

生成的SQL：

Generated SQL:

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

该工作原理与使用SqlClient。但是，我使用的是抽象类System.Data这（的DbCommand ， DbParameter 等）和不同的数据提供者如Oracle，MySQL和Postgres的等基础上，在app.config连接字符串设置。因此，我需要知道哪些preFIX我一定要使用。对于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.

有没有办法来从供应商的工厂这个preFIX角色？

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.$c$cwrecks.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.

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