改进数据访问层选择方法Pattern

问题描述

最近我发现自己正在编写数据访问层选择方法，这些方法的代码全部采用这种通用形式：

Lately I find myself writing data access layer select methods where the code all takes this general form:

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或将数据记录到表示层可以使用的强类型业务对象中。

These methods would be called by business layer code that then converts the base DataTable or DataRecord into strongly typed business objects that the presentation tier can use.

由于我反复使用类似的代码，因此我想确保此代码是最好的代码。那么如何改进呢？并且，值得尝试将通用代码从此转移到它自己的方法中。如果是这样，该方法是什么样的（特别是关于传递SqlParameter集合的方法）？

Since I'm using similar code repeatedly, I want to make sure this code is the best it can be. So how can it be improved? And, is it worth trying to move the common code from this out to it's own method. If so, what would that method look like (specifically with regards to passing an SqlParameter collection in)?

推荐答案

必须添加我的方法自己：

Had to add my own:
Return DataReader from DataLayer in Using statement

新模式使我一次只能在内存中存储一​​条记录，但仍将连接包含在一个很好的使用语句中：

The new pattern enables me to only have one record in memory at a time, but still encases the connection in a nice 'using' statement:

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

这篇关于改进数据访问层选择方法Pattern的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！