时间:2019-03-17 标签:c#genericsqlcontextparameter

Question

我有一个 mssql-context-class，可以轻松访问数据库。它包含一个用于插入数据行的函数，如下所示:

public int? Insert(string tableName, Action<SqlParameterCollection> actionSqlParameterCollection)
{
    using (var sqlConnection = new SqlConnection(ConnectionString))
    {
        sqlConnection.Open();

        using (var sqlCommand = sqlConnection.CreateCommand())
        {
            var commandText = $"insert into {tableName} (@columns) output inserted.id values (@values)";

            var valueBuilder = new StringBuilder();
            var columnBuilder = new StringBuilder();

            actionSqlParameterCollection?.Invoke(sqlCommand.Parameters); //Fill the parameters from outside with some values

            foreach (SqlParameter parameter in sqlCommand.Parameters)
            {
                valueBuilder.Append($",@{parameter.ParameterName}");
                columnBuilder.Append($",{parameter.ParameterName}");
            }

            commandText = commandText.Replace("@values", valueBuilder.ToString().Substring(1));
            commandText = commandText.Replace("@columns", columnBuilder.ToString().Substring(1));


            sqlCommand.CommandText = commandText;

            object result = sqlCommand.ExecuteScalar();

            return (int?)result;
        }
    }
}

调用它看起来像这样:

var context = MsSqlContext.CreateFrom("some_connectionstring");

context.Insert("myTable", parameters => {
    parameters.AddWithValue("foo_1", "bar_1");
    parameters.AddWithValue("foo_2", "bar_2");
});

现在我想构建一个通用的 sql-context-class，它也可以处理 mysql 数据库。到目前为止，插入函数看起来像这样:

public int? Insert(string tableName, Action<IDataParameterCollection> actionParameterCollection)
{
    using (var connection = this.CreateConnection())
    {
        using (var command = connection.CreateCommand())
        {
            var commandText = $"insert into {tableName} (@field) values (@values)";

            var valueBuilder = new StringBuilder();
            var columnBuilder = new StringBuilder();

            actionParameterCollection?.Invoke(command.Parameters); 

            foreach (IDbDataParameter parameter in command.Parameters)
            {
                valueBuilder.Append($",@{parameter.ParameterName}");
                columnBuilder.Append($",{parameter.ParameterName}");
            }

            commandText = commandText.Replace("@values", valueBuilder.ToString().Substring(1));
            commandText = commandText.Replace("@columns", columnBuilder.ToString().Substring(1));


            command.CommandText = commandText;

            object result = command.ExecuteScalar();

            return (int?)result;

        }
    }
}

当我尝试调用该函数时，它看起来像这样:

var context = SqlContext.CreateFrom(SqlProvider.MySql, "Server=localhost;Database=4713_demo;Uid=root;Pwd=;");

context.Insert("my_table", parameters =>
{
    parameters.Add(?); //It expects an object
});

我的问题是，不想做类似的事情

context.Insert("my_table", parameters =>
{
    parameters.Add(context.CreateParameter("foo","bar")); 
});

我只想传递参数名称和参数值。上下文类本身知道其提供者并应该创建参数。如何负担得起？

Answer 1

我想出的解决方案是这个 SqlParameterizer 类。

public class SqlParameterizer
{
    private SqlProvider Provider { get; set; }
    private List<IDbDataParameter> ParameterList { get; set; }

    public SqlParameterizer(SqlProvider sqlProvider)
    {
        this.Provider = sqlProvider;
        this.ParameterList = new List<IDbDataParameter>();
    }


    public void Add(string parameterName, object parameterValue)
    {
        switch(this.Provider)
        {
            case SqlProvider.MsSql:
                this.ParameterList.Add(new SqlParameter(parameterName, parameterValue));
                break;
            case SqlProvider.MySql:
                this.ParameterList.Add(new MySqlParameter(parameterName, parameterValue));
                break;
            case SqlProvider.OracleSql:
                throw new Exception($"SqlProvider '{this.Provider}' not supported yet...");
            default:
                throw new Exception($"Unknown SqlProvider '{this.Provider}'");
        }
    }
    public IDbDataParameter[] GetParameters()
    {
        return ParameterList.ToArray();
    }


}

使用此类将如下所示:

var commandText = $"insert into {tableName} (@columns) values (@values)";

var valueBuilder = new StringBuilder();
var columnBuilder = new StringBuilder();
var parameterizer = new SqlParameterizer(this.Provider);

actionValueParameterizer?.Invoke(parameterizer);

foreach(IDbDataParameter parameter in parameterizer.GetParameters())
{
    command.Parameters.Add(parameter);
    valueBuilder.Append($",@{parameter.ParameterName}");
    columnBuilder.Append($",{parameter.ParameterName}");
}

commandText = commandText.Replace("@values", valueBuilder.ToString().Substring(1));
commandText = commandText.Replace("@columns", columnBuilder.ToString().Substring(1));

command.CommandText = commandText;

command.ExecuteNonQuery();

调用我的插入函数:

context.Insert("some_table", parameterizer =>
{
    parameterizer.Add("some_column", "some_value");
});