DbCommand 和参数化 SQL，ORACLE 与 SQL Server

DbCommand and parameterized SQL, ORACLE vs SQL Server

提问by Nils Magne Lunde

I have an application that will, among other things, store various data into a database. The database might be ORACLE or SQL Server. The SQL is created dynamically based on configuration and values picked up during execution.

我有一个应用程序，可以将各种数据存储到数据库中。数据库可能是 ORACLE 或 SQL Server。SQL 是根据配置和执行期间获取的值动态创建的。

By using DbProviderFactory my db methods are able to work with either ORACLE or SQL Server without writing custom code for any of the databases, except from one thing; parameters/bind variables. For ORACLE I need to use ":ParameterName"whereas for SQL Server I need to use "@ParameterName". Is there any way to make this generic?

通过使用 DbProviderFactory，我的 db 方法能够与 ORACLE 或 SQL Server 一起使用，而无需为任何数据库编写自定义代码，除了一件事；参数/绑定变量。对于 ORACLE，我需要使用，":ParameterName"而对于 SQL Server，我需要使用"@ParameterName". 有什么办法可以使这个通用吗？

Sample code:

示例代码：

public class DbOperations
{
    private DbProviderFactory m_factory;
    private DbConnection m_CN;

    ...

    private void InsertToDb(ValueType[] values, ColumnType[] columns)
    {     
        DbCommand Cmd = m_factory.CreateCommand();
        Cmd.Connection = m_CN;

        StringBuilder sql = new StringBuilder();
        sql.Append("INSERT INTO ");
        sql.Append(DestinationTable);
        sql.Append(" (");

        for (int i = 0; i < columns.Length; i++)
        {
            sql.Append(columns[i].ColumnName);
            if (i < columns.Length - 1) 
            sql.Append(", ");
        }
        sql.Append(") VALUES (");

        for (int i = 0; i < values.Length; i++)
        {        
            //sql.Append(String.Format(":{0}", columns[i].ColumnName));  //ORACLE
            sql.Append(String.Format("@{0}", columns[i].ColumnName)); // SQL Server
        }       

        DbParameter param = m_factory.CreateParameter();
        param.Direction = ParameterDirection.Input;
        param.ParameterName = columns[i].ColumnName;
        param.Value = values[i];
        Cmd.Parameters.Add(param);

        if (i < columns.Length - 1)           
            sql.Append(", ");
      }
      sql.Append(")");
      Cmd.CommandText = sql.ToString();
      Cmd.ExecuteNonQuery();
}

采纳答案by Nils Magne Lunde

I accepted an answer for this question long ago, but for some reason that answer is no longer here... So I guess I need to answer my own question.

我很久以前就接受了这个问题的答案，但由于某种原因，这个答案不再存在......所以我想我需要回答我自己的问题。

What I did was to create a parambuilder class:

我所做的是创建一个 parambuilder 类：

class ParamBuilder
{
    private DbProviderFactory m_factory;
    private DbCommandBuilder m_builder;
    private string m_parameterMarkerFormat;
    public ParamBuilder(DbProviderFactory factory) : this(factory, null)
    {
    }

    public ParamBuilder(DbProviderFactory factory, DbConnection source)
    {
        m_factory = factory;
        m_builder = m_factory.CreateCommandBuilder();
        if (source != null)
        {
            using (DataTable tbl =
                source.GetSchema(DbMetaDataCollectionNames.DataSourceInformation))
            {
                m_parameterMarkerFormat =  
                    tbl.Rows[0][DbMetaDataColumnNames.ParameterMarkerFormat] as string;
            }
        }
        if (String.IsNullOrEmpty(m_parameterMarkerFormat))
            m_parameterMarkerFormat = "{0}";
    }

    public DbParameter CreateParameter(string parameterName, 
        out string parameterMarker)
    {
        DbParameter param = m_factory.CreateParameter();
        param.ParameterName =  
            (string)typeof(DbCommandBuilder).InvokeMember("GetParameterName",
                System.Reflection.BindingFlags.Instance |
                System.Reflection.BindingFlags.InvokeMethod |
                System.Reflection.BindingFlags.NonPublic, null, m_builder, 
                new object[] { parameterName });

        parameterMarker = 
            String.Format(System.Globalization.CultureInfo.InvariantCulture, 
            m_parameterMarkerFormat, param.ParameterName);

        return param;
    }

}

I create a member variable of the ParamBuilder type:

我创建了一个 ParamBuilder 类型的成员变量：

private readonly ParamBuilder m_ParamBuilder;

Then in the method where I use parameters, I use it as follows:

然后在我使用参数的方法中，我是这样使用的：

...
string paramMarker;
DbParameter param = m_ParamBuilder.CreateParameter(destination[i].ColumnName, 
    out paramMarker);
sql.Append(paramMarker);

param.Direction = ParameterDirection.Input;
param.Value = source[i];
Cmd.Parameters.Add(param);
...

回答by GaryB96

Make an abstract property to get the format string used in the "values" loop.

创建一个抽象属性以获取“值”循环中使用的格式字符串。

class DBOperations
 public abstract string ParameterStringFormat;
 ...
for (int i = 0; i < values.Length; i++)
        {        
            sql.Append(String.Format(ParamterStringFormat, columns[i].ColumnName)); // SQL Server
        }  


class SqlDbOperations : DBOperations
 public override string ParameterStringFormat { get { return "@{0}"; }}


class OracleDBOperations : DBOperations
 public override string ParameterStringFormat { get { return ":{0}"; }}

回答by Snekse

Just throwing in another option if you want quick and dirty.

如果你想要快速和肮脏，只需投入另一个选择。

string sql = select * from foo there foo.id = @id;
if (isOracle) {
 sql = replaceAll(sql,"@",":");
}