PetaPoco-多个结果集支持 [英] PetaPoco - Multiple result set support
本文介绍了PetaPoco-多个结果集支持的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的作品最近开始使用PetaPoco，尽管很棒，但我错过了Dapper中的功能，该功能允许将要处理的单个查询中的多个结果网格转化为pocos.
My work recently starting using PetaPoco and although fantastic I missed the feature from Dapper which allowed multiple result grids from a single query to be processed into pocos.
结果，我为PetaPoco编写了自己的实现-如下所示-但有人编写了自己的实现并愿意分享吗?
As a result I wrote my own implementation for PetaPoco - shown below - but has anyone written their own and care to share it?
我认为可能还有其他人错过了此功能.
I thought there might be others out there who have missed this feature.
推荐答案
编辑(2016-06-27): 从 PetaPoco 的版本 5.1.181 (目前是在撰写本文时)，此功能现已在PetaPoco NuGet软件包中提供
Edit (2016-06-27): As of version 5.1.181 of PetaPoco (currently a beta at the time of writing) this feature is now available in the PetaPoco NuGet Package
信息
我以Dapper源码为灵感来实现此功能，因此有任何相似之处.
Info
I used the Dapper source as inspiration to implement this feature, so any similarities are due to that.
这将直接对PetaPoco.cs文件进行更改，您可能不应该在一开始就从注释中进行判断！
This makes changes directly to the PetaPoco.cs file, which you probably shouldn't do judging from the comment at the start!
此代码已经过测试，但尚未彻底测试，请谨慎行事！
This code has been tested but not thoroughly, please exercise appropriate caution!
private string _sql = @"SELECT * FROM WebOrders w INNER JOIN Address a ON a.Id = w.DeliveryAddressId WHERE OrderId = @0
SELECT * FROM WebOrderLines WHERE OrderId = @0 AND CustomerId = @1
SELECT * FROM ContactDetails WHERE CustomerId = @1";
private readonly Database _db = new Database("Shop");
var result = new WebOrder();
using (var multi = _db.QueryMultiple(_sql, 12345, 67890))
{
result = multi.Read<WebOrder, Address, WebOrder>((w, a) => { w.Address = a; return w; }).Single();
result.OrderLines = multi.Read<WebOrderLines>().ToList();
result.ContactDetails = multi.Read<ContactDetails>().ToList();
}
return result;
更改-PetaPoco(核心)v.5.0.1
在类 PetaPoco.Database 中添加:
#region operation: Multi-Result Set
/// <summary>
/// Perform a multi-results set query
/// </summary>
/// <param name="sql">An SQL builder object representing the query and it's arguments</param>
/// <returns>A GridReader to be queried</returns>
public GridReader QueryMultiple(Sql sql)
{
return QueryMultiple(sql.SQL, sql.Arguments);
}
/// <summary>
/// Perform a multi-results set query
/// </summary>
/// <param name="sql">The SQL query to be executed</param>
/// <param name="args">Arguments to any embedded parameters in the SQL</param>
/// <returns>A GridReader to be queried</returns>
public GridReader QueryMultiple(string sql, params object[] args)
{
OpenSharedConnection();
GridReader result = null;
var cmd = CreateCommand(_sharedConnection, sql, args);
try
{
var reader = cmd.ExecuteReader();
result = new GridReader(this, cmd, reader);
}
catch (Exception x)
{
if (OnException(x))
throw;
}
return result;
}
#endregion
在根级别(名称空间: PetaPoco )添加:
At the root level (namespace: PetaPoco) add:
#region Multi-Results Set GridReader
public class GridReader : IDisposable
{
private IDataReader _reader;
private IDbCommand _command;
private readonly Database _db;
/// <summary>
/// The control structure for a multi-result set query
/// </summary>
/// <param name="database"></param>
/// <param name="command"></param>
/// <param name="reader"></param>
internal GridReader(Database database, IDbCommand command, IDataReader reader)
{
_db = database;
_command = command;
_reader = reader;
}
#region public Read<T> methods
/// <summary>
/// Reads from a GridReader, returning the results as an IEnumerable collection
/// </summary>
/// <typeparam name="T">The Type representing a row in the result set</typeparam>
/// <returns>An enumerable collection of result records</returns>
public IEnumerable<T> Read<T>()
{
return SinglePocoFromIDataReader<T>(_gridIndex);
}
/// <summary>
/// Perform a multi-poco read from a GridReader
/// </summary>
/// <typeparam name="T1">The first POCO type</typeparam>
/// <typeparam name="T2">The second POCO type</typeparam>
/// <returns>A collection of POCO's as an IEnumerable</returns>
public IEnumerable<T1> Read<T1, T2>()
{
return MultiPocoFromIDataReader<T1>(_gridIndex, new Type[] {typeof (T1), typeof (T2)}, null);
}
/// <summary>
/// Perform a multi-poco read from a GridReader
/// </summary>
/// <typeparam name="T1">The first POCO type</typeparam>
/// <typeparam name="T2">The second POCO type</typeparam>
/// <typeparam name="T3">The third POCO type</typeparam>
/// <returns>A collection of POCO's as an IEnumerable</returns>
public IEnumerable<T1> Read<T1, T2, T3>()
{
return MultiPocoFromIDataReader<T1>(_gridIndex, new Type[] {typeof (T1), typeof (T2), typeof (T3)}, null);
}
/// <summary>
/// Perform a multi-poco read from a GridReader
/// </summary>
/// <typeparam name="T1">The first POCO type</typeparam>
/// <typeparam name="T2">The second POCO type</typeparam>
/// <typeparam name="T3">The third POCO type</typeparam>
/// <typeparam name="T4">The forth POCO type</typeparam>
/// <returns>A collection of POCO's as an IEnumerable</returns>
public IEnumerable<T1> Read<T1, T2, T3, T4>()
{
return MultiPocoFromIDataReader<T1>(_gridIndex,
new Type[] {typeof (T1), typeof (T2), typeof (T3), typeof (T4)}, null);
}
/// <summary>
/// Perform a multi-poco query
/// </summary>
/// <typeparam name="T1">The first POCO type</typeparam>
/// <typeparam name="T2">The second POCO type</typeparam>
/// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
/// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
/// <returns>A collection of POCO's as an IEnumerable</returns>
public IEnumerable<TRet> Read<T1, T2, TRet>(Func<T1, T2, TRet> cb)
{
return MultiPocoFromIDataReader<TRet>(_gridIndex, new Type[] {typeof (T1), typeof (T2)}, cb);
}
/// <summary>
/// Perform a multi-poco query
/// </summary>
/// <typeparam name="T1">The first POCO type</typeparam>
/// <typeparam name="T2">The second POCO type</typeparam>
/// <typeparam name="T3">The third POCO type</typeparam>
/// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
/// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
/// <returns>A collection of POCO's as an IEnumerable</returns>
public IEnumerable<TRet> Read<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb)
{
return MultiPocoFromIDataReader<TRet>(_gridIndex, new Type[] {typeof (T1), typeof (T2), typeof (T3)}, cb);
}
/// <summary>
/// Perform a multi-poco query
/// </summary>
/// <typeparam name="T1">The first POCO type</typeparam>
/// <typeparam name="T2">The second POCO type</typeparam>
/// <typeparam name="T3">The third POCO type</typeparam>
/// <typeparam name="T4">The forth POCO type</typeparam>
/// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
/// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
/// <returns>A collection of POCO's as an IEnumerable</returns>
public IEnumerable<TRet> Read<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb)
{
return MultiPocoFromIDataReader<TRet>(_gridIndex,
new Type[] {typeof (T1), typeof (T2), typeof (T3), typeof (T4)}, cb);
}
#endregion
#region PocoFromIDataReader
/// <summary>
/// Read data to a single poco
/// </summary>
/// <typeparam name="T">The type representing a row in the result set</typeparam>
/// <param name="index">Reader row to be read from the underlying IDataReader</param>
/// <returns></returns>
private IEnumerable<T> SinglePocoFromIDataReader<T>(int index)
{
if (_reader == null)
throw new ObjectDisposedException(GetType().FullName, "The data reader has been disposed");
if (_consumed)
throw new InvalidOperationException(
"Query results must be consumed in the correct order, and each result can only be consumed once");
_consumed = true;
var pd = PocoData.ForType(typeof (T));
try
{
while (index == _gridIndex)
{
var factory =
pd.GetFactory(_command.CommandText, _command.Connection.ConnectionString, 0, _reader.FieldCount,
_reader) as Func<IDataReader, T>;
while (true)
{
T poco;
try
{
if (!_reader.Read())
yield break;
poco = factory(_reader);
}
catch (Exception x)
{
if (_db.OnException(x))
throw;
yield break;
}
yield return poco;
}
}
}
finally // finally so that First etc progresses things even when multiple rows
{
if (index == _gridIndex)
{
NextResult();
}
}
}
/// <summary>
/// Read data to multiple pocos
/// </summary>
/// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
/// <param name="index">Reader row to be read from the underlying IDataReader</param>
/// <param name="types">An array of Types representing the POCO types of the returned result set.</param>
/// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
/// <returns>A collection of POCO's as an IEnumerable</returns>
private IEnumerable<TRet> MultiPocoFromIDataReader<TRet>(int index, Type[] types, object cb)
{
if (_reader == null)
throw new ObjectDisposedException(GetType().FullName, "The data reader has been disposed");
if (_consumed)
throw new InvalidOperationException(
"Query results must be consumed in the correct order, and each result can only be consumed once");
_consumed = true;
try
{
var cmd = _command;
var r = _reader;
var factory = MultiPocoFactory.GetFactory<TRet>(types, cmd.Connection.ConnectionString, cmd.CommandText,
r);
if (cb == null)
cb = MultiPocoFactory.GetAutoMapper(types.ToArray());
bool bNeedTerminator = false;
while (true)
{
TRet poco;
try
{
if (!r.Read())
break;
poco = factory(r, cb);
}
catch (Exception x)
{
if (_db.OnException(x))
throw;
yield break;
}
if (poco != null)
yield return poco;
else
bNeedTerminator = true;
}
if (bNeedTerminator)
{
var poco = (TRet) (cb as Delegate).DynamicInvoke(new object[types.Length]);
if (poco != null)
yield return poco;
else
yield break;
}
}
finally
{
if (index == _gridIndex)
{
NextResult();
}
}
}
#endregion
#region DataReader Management
private int _gridIndex;
private bool _consumed;
/// <summary>
/// Advance the IDataReader to the NextResult, if available
/// </summary>
private void NextResult()
{
if (!_reader.NextResult()) return;
_gridIndex++;
_consumed = false;
}
/// <summary>
/// Dispose the grid, closing and disposing both the underlying reader, command and shared connection
/// </summary>
public void Dispose()
{
if (_reader != null)
{
if (!_reader.IsClosed && _command != null) _command.Cancel();
_reader.Dispose();
_reader = null;
}
if (_command != null)
{
_command.Dispose();
_command = null;
}
_db.CloseSharedConnection();
}
#endregion
}
#endregion
我已将此更改提交到 PetaPoco v5分支，但认为人们可能会得到一些帮助如果我在这里张贴它会有所帮助.
I have submitted this change to the PetaPoco v5 branch but thought people might get some benefit if I posted it here.
这篇关于PetaPoco-多个结果集支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
