DbCommand 和参数化 SQL、ORACLE 与 SQL Server

DbCommand and parameterized SQL, ORACLE vs SQL Server(DbCommand 和参数化 SQL、ORACLE 与 SQL Server)

问题描述

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

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.

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

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?

示例代码:

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

推荐答案

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

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.

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

What I did was to create a parambuilder class:

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

}

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

I create a member variable of the ParamBuilder type:

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);
...

这篇关于DbCommand 和参数化 SQL、ORACLE 与 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！