重构 DAL 代码以支持存储过程

Question

    private static readonly string dataProvider = ConfigurationManager.AppSettings.Get("Provider");
    private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);
    private static readonly string connectionString = ConfigurationManager.ConnectionStrings[dataProvider].ConnectionString;
    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql)
    {
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;

            using (DbCommand command = factory.CreateCommand())
            {
                command.Connection = connection;
                command.CommandText = sql;

                connection.Open();
                return command.ExecuteNonQuery();
            }
        }
    }

我需要帮助来重写它，以便它可以与存储过程一起使用。 （传入存储过程名称和参数） 有谁知道我应该如何做这个？ 编辑：我遇到问题的领域是试图找出填充参数的方法。

谢谢

Answer 1

无论您是否正在实现存储过程，您都已经需要参数。

现在，您的代码可以使用诸如 SELECT * FROM Table WHERE ID = @ID 之类的查询来调用，在这种情况下，您已经需要传递一个 Dictionary; 参数。 让您的代码填写已有命令的参数集合，并进行测试，然后再担心存储过程。

一旦工作正常，您应该简单地创建一个重载，它接受一个 bool 值，表示这是一个存储过程，然后使用它来设置命令的 CommandType 属性。

---

编辑：这是我将如何重构它

第 1 步：概括更新

除了名称之外，Update 方法没有什么特别之处可以阻止它用于其他非查询操作。 那么：

    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql)
    {
        return NonQuery(sql);
    }

    public static int NonQuery(string sql)
    {
        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;

            using (DbCommand command = factory.CreateCommand())
            {
                command.Connection = connection;
                command.CommandText = sql;

                connection.Open();
                return command.ExecuteNonQuery();
            }
        }
    }

第 2 步：参数怎么样？

您当前的代码甚至无法处理使用参数的 UPDATE 查询，所以让我们开始解决这个问题。 首先，确保在没有指定参数的情况下它仍然有效：

    public static int NonQuery(string sql)
    {
        Dictionary<string, object> parameters = null;

        if (parameters == null)
        {
            parameters = new Dictionary<string, object>();
        }

        using (DbConnection connection = factory.CreateConnection())
        {
            connection.ConnectionString = connectionString;

            using (DbCommand command = factory.CreateCommand())
            {
                command.Connection = connection;
                command.CommandText = sql;

                foreach (KeyValuePair<string, object> p in parameters)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = p.Key;
                    parameter.Value = p.Value;

                    command.Parameters.Add(parameter);
                }

                connection.Open();
                return command.ExecuteNonQuery();
            }
        }
    }

一旦有效，将参数提升为参数。 这不会影响 Update 的任何现有调用者：

    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql)
    {
        return NonQuery(sql, null);
    }

    public static int NonQuery(string sql, Dictionary<string, object> parameters)

此时，使用参数化查询测试 NonQuery。 工作完成后，创建一个接受参数的 Update 重载：

    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql)
    {
        return NonQuery(sql, null);
    }

    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <param name="parameters">Name/value dictionary of parameters</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql, Dictionary<string, object> parameters)
    {
        return NonQuery(sql, parameters);
    }

第 3 步：考虑存储过程

在如何处理存储过程方面几乎没有什么区别。 您已经得到的内容隐式如下：

            using (DbCommand command = factory.CreateCommand())
            {
                command.Connection = connection;
                command.CommandText = sql;
                command.CommandType = CommandType.Text;

因此，采用 CommandType.Text 并将其提升为重载中的参数：

    public static int NonQuery(string sql, Dictionary<string, object> parameters)
    {
        return NonQuery(sql, CommandType.Text, parameters);
    }

    public static int NonQuery(string sql, CommandType commandType, Dictionary<string, object> parameters)

最后，如果您愿意，请更新 Update：

    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <param name="parameters">Name/value dictionary of parameters</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql, Dictionary<string, object> parameters)
    {
        return Update(sql, CommandType.Text, parameters);
    }

    /// <summary>
    /// Executes Update statements in the database.
    /// </summary>
    /// <param name="sql">Sql statement.</param>
    /// <param name="commandType">CommandType.Text or CommandType.StoredProcedure</param>
    /// <param name="parameters">Name/value dictionary of parameters</param>
    /// <returns>Number of rows affected.</returns>
    public static int Update(string sql, CommandType commandType, Dictionary<string, object> parameters)
    {
        return NonQuery(sql, parameters);
    }

当然，作为最终为了供读者练习，您可以将所有 Update 调用替换为对 NonQuery 的调用，并完全摆脱 Update。

---

当然，这个简单的技术不处理输出参数，或者需要指定参数的 DbType 的情况。 为此，您需要接受某种类型的 ParameterCollection。

Answer 2

使用重载，例如

public static int Update(string storedProc, Dictionary<string,string> params)
{
    ...
}

Answer 3

您可能会遇到困难，具体取决于您计划放置它的环境。

如果我理解正确，您希望保留函数签名（单个字符串参数），但如果字符串中的第一个单词不是“UPDATE”，则以不同的方式处理它。 例如，这个字符串应该执行一个名为 UpdateTable1 的存储过程，并带有四个参数：

UpdateTable1 NULL, A5KMI, "New description", #5/10/2009#

或类似的东西。

问题是字符串其余部分的参数之间需要一个分隔符。 为此，您必须将字符串分解为标记，这可能会很棘手。

因此，另一种方法是在整个字符串前面添加命令 EXEC，将整个字符串设置到 CommandText 属性中，然后调用其中一个 Execute 方法。

我不喜欢这一点的是它确实让你面临 SQL 注入漏洞。 如果可能的话，请使用像其他人回答的那样的重载方法，并使调用者将参数拆分为某种类型的集合。

Answer 4

我使用接口和泛型做了类似的事情。

示例

    using System.Data.SqlClient;

public interface IParameter<T> where T : IEntity<T>
{
    void Populate(SqlParameterCollection parameters, T entity);
}

实现接口的类
使用系统数据；
使用 System.Data.SqlClient；

public class UserParameter : IParameter<User>
{
    public void Populate(SqlParameterCollection parameters, User entity)
    {
        parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = entity.Id;
        parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = entity.Name;
        parameters.Add("@EmailAddress", SqlDbType.NVarChar, 255).Value = entity.EmailAddress;
    }
}

然后我们有执行更新的方法，

            public void Update<T>(string prefix, IParameter<T> parameters, T entity)
        where T : class, IEntity<T>
    {
        using (var connection = this.Connection())
        {
            using (var command = new SqlCommand(string.Format("dbo.{0}_Update", prefix), connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                parameters.Populate(command.Parameters, entity);

                connection.Open();

                command.ExecuteNonQuery();

                connection.Close();
            }
        }
    }

之类的操作来调用它

然后我通过执行诸如Update("Users", new UserParameter(), value);

我也对从读者值填充实体执行相同的操作。

Answer 5

您可以添加 3 个参数：

1. string SchemaName -- 存储数据库模式的名称
2. string StoredProcName -- 存储存储过程的名称
3. List< /code> -- 存储过程参数列表