改进数据访问层的选择方法模式

Question

最近我发现自己在编写数据访问层的选择方法时，代码都采用这种一般形式：

public static DataTable GetSomeData( ... arguments)
{
    string sql = " ... sql string here:  often it's just a stored procedure name ... ";

    DataTable result = new DataTable();

    // GetOpenConnection() is a private method in the class: 
    // it manages the connection string and returns an open and ready connection
    using (SqlConnection cn = GetOpenConnection())
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        // could be any number of parameters, each with a different type
        cmd.Parameters.Add("@Param1", SqlDbType.VarChar, 50).Value = param1; //argument passed to function

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            result.Load(rdr);
        }
    }

    return result;
}

或者像这样：

public static DataRow GetSomeSingleRecord( ... arguments)
{
    string sql = " ... sql string here:  often it's just a stored procedure name ... ";

    DataTable dt = new DataTable();

    // GetOpenConnection() is a private method in the class: 
    // it manages the connection string and returns an open and ready connection
    using (SqlConnection cn = GetOpenConnection())
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        // could be any number of parameters, each with a different type
        cmd.Parameters.Add("@Param1", SqlDbType.VarChar, 50).Value = param1; //argument passed to function

        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
        {
            dt.Load(rdr);
        }
    }

    if (dt.Rows.Count > 0)
         return dt.Rows[0];
    return null;
}

这些方法将由业务层代码调用，然后将基础的DataTable或DataRecord转换为强类型的业务对象，供演示层使用。

由于我反复使用类似的代码，我希望确保这段代码是最好的。那么，如何改进它呢？如果要将公共代码从中移出来，是否值得尝试？如果是，那么这个方法会是什么样子（特别是关于传递SqlParameter集合方面）？

- Joel Coehoorn

---

你做得和我一样 - 虽然我真的很喜欢你如何使用双重using语句来避免深层嵌套。干得好！我感兴趣的一件事是你如何逻辑/物理地设置你的DAL vs. BIZ - 你会把它放在自己的项目中吗？还是作为一个命名空间？或者其他什么方式？ - dfasdljkhfaskldjhfasklhf

如果没有参数，您也可以堆叠数据阅读器。DAL 在它自己的程序集中，DAL + BL 将共享一个公共父命名空间。 - Joel Coehoorn

是的，这就是我现在的做法。你的业务对象和DAL对象之间的映射是什么？即，您是1：1进行映射吗？我看到有趣的一点是，有人将DAL引入业务对象，并使用CodeSmith生成所有内容。非常有趣。 - dfasdljkhfaskldjhfasklhf

提供您的业务对象如何使用这些方法的示例也会有所帮助。您如何从那些 DataTable 对象中提取数据？ - Dan C.

@BPAndrew：它可能大部分是1:1，但软件的一个特点就是总有例外。 ;) 我认为如果它总是1:1，那么拥有单独的层级就没有太多意义了；分层的好处将被分离相关代码的惩罚所抵消。 - Joel Coehoorn

Answer 1

我不得不自己添加：
在Using语句中从DataLayer返回DataReader

这种新模式使我每次只有一个记录在内存中，但仍然将连接封装在一个漂亮的'using'语句中：

public IEnumerable<T> GetSomeData(string filter, Func<IDataRecord, T> factory)
{
    string sql = "SELECT * FROM [SomeTable] WHERE SomeColumn= @Filter";

    using (SqlConnection cn = new SqlConnection(GetConnectionString()))
    using (SqlCommand cmd = new SqlCommand(sql, cn))
    {
        cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
        cn.Open();

        using (IDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return factory(rdr);
            }
            rdr.Close();
        }
    }
}

- Joel Coehoorn

---

你的异常处理在哪里？ - msfanboy

@msfanboy - 把它放在程序的更高层级。 - Joel Coehoorn

无论如何，这已经过时了。自那以后，我已经进一步改进了该模式：https://dev59.com/v3E85IYBdhLWcg3wQxPG#2862490 - Joel Coehoorn

@JoelCoehoorn，您能否更新此问题并提供您的新方法？或者在问题本身中提供链接？ - nawfal

@nawfal 这将违反既定的 Stack Overflow 协议。 - Joel Coehoorn

你能举一个工厂的例子吗？ - Fred Smith

Answer 2

我喜欢的一种模式在客户端代码方面如下所示：

        DataTable data = null;
        using (StoredProcedure proc = new StoredProcedure("MyProcName","[Connection]"))
        {
            proc.AddParameter("@LoginName", loginName);
            data = proc.ExecuteDataTable();
        }

我通常会将连接设置为可选项，并编写代码从ConnectionStrings配置部分获取它或将其视为实际的连接字符串。这使我可以在单个场景中重用dal，这在某种程度上是COM+时代的习惯，当时我使用对象构造属性存储连接字符串。

我喜欢这种方式，因为它易于阅读，隐藏了所有的ADO代码。

- Josh

---

我对此有一些问题，但还是点赞了，因为它给了我一个想法：我将尝试将常见代码隐藏为一个函子，而不是一个方法。 - Joel Coehoorn

有趣的话请分享你的想法。我对这种模式并不完全认同，只是在2.0版本中达到了这个程度。它可以减少混乱。 - JoshBerke

请注意，我说的是“玩”，我不认为我最终会走这条路。如果我想出了什么有趣的东西，我会回来发布它。 - Joel Coehoorn

Answer 3

与我在这里发布的内容类似。

public IEnumerable<S> Get<S>(string query, Action<IDbCommand> parameterizer, 
                             Func<IDataRecord, S> selector)
{
    using (var conn = new T()) //your connection object
    {
        using (var cmd = conn.CreateCommand())
        {
            if (parameterizer != null)
                parameterizer(cmd);
            cmd.CommandText = query;
            cmd.Connection.ConnectionString = _connectionString;
            cmd.Connection.Open();
            using (var r = cmd.ExecuteReader())
                while (r.Read())
                    yield return selector(r);
        }
    }
}

我有这些简单的扩展方法来帮助调用的便捷性：

public static void Parameterize(this IDbCommand command, string name, object value)
{
    var parameter = command.CreateParameter();
    parameter.ParameterName = name;
    parameter.Value = value;
    command.Parameters.Add(parameter);
}

public static T To<T>(this IDataRecord dr, int index, T defaultValue = default(T),
                      Func<object, T> converter = null)
{
    return dr[index].To<T>(defaultValue, converter);
}

static T To<T>(this object obj, T defaultValue, Func<object, T> converter)
{
    if (obj.IsNull())
        return defaultValue;

    return converter == null ? (T)obj : converter(obj);
}

public static bool IsNull<T>(this T obj) where T : class
{
    return (object)obj == null || obj == DBNull.Value;
}

所以现在我可以打电话：

var query = Get(sql, cmd =>
{
    cmd.Parameterize("saved", 1);
    cmd.Parameterize("name", "abel");
}, r => new User(r.To<int>(0), r.To<string>(1), r.To<DateTime?>(2), r.To<bool>(3)));
foreach (var user in query)
{

}

这是完全通用的，适用于符合ado.net接口的任何模型。只有在集合枚举一次后，连接对象和读取器才会被释放。

- nawfal

Answer 4

我唯一做的不同之处是，我从自己的内部数据库辅助方法切换到了实际的数据访问应用程序块http://msdn.microsoft.com/en-us/library/cc309504.aspx

这使得其他了解企业库的开发人员更容易对代码进行适应和标准化。

- BPAndrew

Answer 5

实现DBAL有很多方法，我认为你走在了正确的道路上。在你的实现中需要考虑以下几点：

• 你使用了类似工厂的方法来创建SqlConnection，这只是一个小问题，但你也可以对SqlCommand采用同样的方法。
• 参数长度是可选的，所以你可以在Parameter.Add调用中省略它。
• 创建添加参数的方法，下面是代码示例。

使用DbUtil.AddParameter(cmd, "@Id", SqlDbType.UniqueIdentifier, Id);添加参数。

internal class DbUtil {

internal static SqlParameter CreateSqlParameter(
    string parameterName,
    SqlDbType dbType,
    ParameterDirection direction,
    object value
) {
    SqlParameter parameter = new SqlParameter(parameterName, dbType);

    if (value == null) {
        value = DBNull.Value;
    }

    parameter.Value = value;

    parameter.Direction = direction;
    return parameter;
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType
) {
    return AddParameter(sqlCommand, parameterName, dbType, null);
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType,
    object value
) {
    return AddParameter(sqlCommand, parameterName, dbType, ParameterDirection.Input, value);
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType,
    ParameterDirection direction,
    object value
) {
    SqlParameter parameter = CreateSqlParameter(parameterName, dbType, direction, value);
    sqlCommand.Parameters.Add(parameter);
    return parameter;
    }
}

- David Thomas Garcia

Answer 6

首先，我认为您已经考虑过使用ORM与自己编写的区别。我不会深入讨论这个问题。

关于自己编写数据访问代码的想法：

随着时间的推移，我发现将DAL/BL对象合并为单个对象更容易（在得出这个结论后一段时间，我发现这是一个相当著名的模式，即ActiveRecord）。拥有单独的DAL程序集看起来很好，但维护成本会增加。每次添加新功能时，您都必须创建更多代码/修改更多类。根据我的经验，维护应用程序的团队通常比构建它的原始开发人员少得多，他们会讨厌需要额外的工作量。

对于大型团队，将DAL分离出来可能是有意义的（并让一个小组来处理），但这会导致代码膨胀。

针对您的特定示例：您如何使用生成的DataTable？遍历行，创建类型化对象并从行中获取数据吗？如果答案是肯定的，请考虑为什么要为在DAL和BL之间移动数据而创建额外的DataTable？为什么不直接从DataReader中获取？

还有关于示例：如果您返回一个未经类型化的DataTable，那么我想您必须在调用代码中使用列名称（SP调用返回的结果集）的方式。这意味着如果我必须更改数据库中的某些内容，它可能会影响两个层。

我的建议（我尝试了两种方法 - 建议是我想出的最新工作方法 - 它随着时间的推移而逐渐演变）。

• 为您的类型化业务对象创建一个基类。
• 在基类中保留对象状态（新建、修改等）
• 将主要数据访问方法放在此类中，作为静态方法。通过一些努力（提示：通用方法+Activator.CreateInstance），您可以为每个返回的行创建一个业务对象。
• 在业务对象中创建一个抽象方法，用于解析行数据（直接从DataReader！）并填充对象。
• 在派生的业务对象中创建静态方法，准备存储过程参数（根据各种过滤条件）并从基类调用通用数据访问方法。

目标是最终使用如下：

List<MyObject> objects = MyObject.FindMyObject(string someParam);

我的好处是只需要更改一个文件就可以应对数据库列名称、类型等的更改（通常是小的更改）。通过一些深思熟虑的区域，您可以组织代码，使它们成为同一对象中的分离“层” :）。另一个好处是基类真正可重用，从一个项目到另一个项目。而且代码膨胀很小（与好处相比）。您还可以填充数据集并将其绑定到UI控件:D

局限性-您最终会得到每个域对象的一个类（通常是每个主数据库表）。您无法在现有事务中加载对象（尽管如果您有一个事务，您可以考虑传递事务）。

如果您对更多细节感兴趣，请告诉我-我可以稍微扩展答案。

- Dan C.

Answer 7

最简单的解决方案：

var dt=new DataTable();
dt.Load(myDataReader);
list<DataRow> dr=dt.AsEnumerable().ToList();

- Mohsen

---

这完全错了。而且很可能是最慢的答案，因为它不仅一次性加载整个结果集到RAM中，而是两次。 - Joel Coehoorn