C# 在 Using 语句中从 DataLayer 返回 DataReader
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/850065/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return DataReader from DataLayer in Using statement
提问by Joel Coehoorn
We have a lot of data layer code that follows this very general pattern:
我们有很多数据层代码都遵循这个非常通用的模式：
public DataTable GetSomeData(string filter)
{
string sql = "SELECT * FROM [SomeTable] WHERE SomeColumn= @Filter";
DataTable result = new DataTable();
using (SqlConnection cn = new SqlConnection(GetConnectionString()))
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
cmd.Parameters.Add("@Filter", SqlDbType.NVarChar, 255).Value = filter;
result.Load(cmd.ExecuteReader());
}
return result;
}
I think we can do a little better. My main complaint right now is that it forces all the records to be loaded into memory, even for large sets. I'd like to be able to take advantage of a DataReader's ability to only keep one record in ram at a time, but if I return the DataReader directly the connection is cut off when leaving the using block.
我认为我们可以做得更好一点。我现在的主要抱怨是它强制将所有记录加载到内存中，即使是大集也是如此。我希望能够利用 DataReader 一次仅在 ram 中保留一条记录的能力，但是如果我直接返回 DataReader，则在离开 using 块时连接将被切断。
How can I improve this to allow returning one row at a time?
我该如何改进以允许一次返回一行？
采纳答案by Joel Coehoorn
Once again, the act of composing my thoughts for the question reveals the answer. Specifically, the last sentence where I wrote "one row at a time". I realized I don't really care that it's a datareader, as long as I can enumerate it row by row. That lead me to this:
再一次，为这个问题整理我的想法的行为揭示了答案。具体来说，我写了“一次一行”的最后一句话。我意识到我并不真正关心它是一个数据读取器，只要我可以逐行枚举它。这导致我：
public IEnumerable<IDataRecord> GetSomeData(string filter)
{
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 (IDataRecord)rdr;
}
}
}
}
This will work even better once we move to 3.5 and can start using other linq operators on the results, and I like it because it sets us up to start thinking in terms of a "pipeline" between each layer for queries that return a lot of results.
一旦我们移动到 3.5 并且可以开始在结果上使用其他 linq 运算符，这将工作得更好，我喜欢它，因为它让我们开始考虑每层之间的“管道”，用于返回大量查询结果。
The down-side is that it will be awkward for readers holding more than one result set, but that is exceedingly rare.
不利的一面是，持有多个结果集的读者会很尴尬，但这种情况极为罕见。
Update
Since I first started playing with this pattern in 2009, I have learned that it's best if I also make it a generic IEnumerable<T>return type and add a Func<IDataRecord, T>parameter to convert the DataReader state to business objects in the loop. Otherwise, there can be issues with the lazy iteration, such that you see the last object in the query every time.
更新
自从我在 2009 年第一次开始使用这种模式以来，我了解到最好也将它设为通用IEnumerable<T>返回类型并添加一个Func<IDataRecord, T>参数以将 DataReader 状态转换为循环中的业务对象。否则，延迟迭代可能会出现问题，这样您每次都会看到查询中的最后一个对象。
回答by Henk Holterman
What you want is a supported pattern, you'll have to use
你想要的是一个受支持的模式，你必须使用
cmd.ExecuteReader(CommandBehavior.CloseConnection);
and remove both using()'s form your GetSomeData() method. Exception safety has to be supplied by the caller, in guaranteeing a Close on the reader.
并从using()您的 GetSomeData() 方法中删除两者。异常安全必须由调用者提供，以保证读取器上的关闭。
回答by John
I was never a big fan of having the data layer return a generic data object, since that pretty much dissolves the whole point of having the code seperated into its own layer (how can you switch out data layers if the interface isn't defined?).
我从来不是让数据层返回一个通用数据对象的忠实拥护者，因为这几乎消除了将代码分离到它自己的层中的全部意义（如果接口没有定义，你如何切换数据层？ ）。
I think your best bet is for all functions like this to return a list of custom objects you create yourself, and in your data later, you call your procedure/query into a datareader, and iterate through that creating the list.
我认为你最好的选择是让所有像这样的函数返回你自己创建的自定义对象列表，然后在你的数据中，你将你的过程/查询调用到数据读取器中，并通过创建列表进行迭代。
This will make it easier to deal with in general (despite the initial time to create the custom classes), makes it easier to handle your connection (since you won't be returning any objects associated with it), and should be quicker. The only downside is everything will be loaded into memory like you mentioned, but I wouldn't think this would be a cause of concern (if it was, I would think the query would need to be adjusted).
这将使一般处理更容易（尽管创建自定义类的初始时间），更容易处理您的连接（因为您不会返回任何与其关联的对象），并且应该更快。唯一的缺点是所有内容都将像您提到的那样加载到内存中，但我认为这不会引起关注（如果是，我认为需要调整查询）。
回答by Peter Lillevold
In times like these I find that lambdas can be of great use. Consider this, instead of the data layer giving us the data, let us give the data layer our data processing method:
在这种情况下，我发现 lambda 表达式很有用。考虑一下，不是数据层给我们数据，让我们给数据层我们的数据处理方法：
public void GetSomeData(string filter, Action<IDataReader> processor)
{
...
using (IDataReader reader = cmd.ExecuteReader())
{
processor(reader);
}
}
Then the business layer would call it:
然后业务层会调用它：
GetSomeData("my filter", (IDataReader reader) =>
{
while (reader.Read())
{
...
}
});
回答by nawfal
The key is yieldkeyword.
关键是yield关键字。
Similar to Joel's original answer, little more fleshed out:
与乔尔的原始答案类似，还有一点充实：
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);
}
}
}
And I have this extension method:
我有这个扩展方法：
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);
}
So I call:
所以我打电话：
foreach(var user in Get(query, cmd => cmd.Parameterize("saved", 1), userSelector))
{
}
This is fully generic, fits any model that comply to ado.net interfaces. The connection and reader objects are disposed after the collection is enumerated.Anyway filling a DataTableusing IDataAdapter's Fillmethod can be faster than DataTable.Load
这是完全通用的，适合任何符合 ado.net 接口的模型。在枚举集合之后处理连接和读取器对象。无论如何填充DataTableusingIDataAdapter的Fill方法可以比DataTable.Load
