public IHttpActionResult GetAutoShipResults(GetAutoShipResultsRequest request)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@iSessionKey", request.SessionKey);
parameters.Add("@AutoShipQID", request.AutoShipQID);
GetAutoShipResultsResponse response = new GetAutoShipResultsResponse();
try
{
using (DbConnection connection = ConnectionFactory.GetOpenConnection("DefaultConnection"))
{
Dapper.SqlMapper.GridReader reader = connection.QueryMultiple("spsoGetAutoShipResults_RKL", parameters, commandType: CommandType.StoredProcedure);
response.Batch = reader.Read <AutoShipBatch>().ToList();
response.Shipments = reader.Read <AutoShipShipment>().ToList();
response.ShipmentLines = reader.Read <AutoShipShipmentLine>().ToList();
response.ShipmentDist = reader.Read <AutoShipShipmentDist>().ToList();
response.ASErrors = reader.Read <AutoShipmentQueueError>().ToList();
response.QLog = reader.Read <AutoShipQueueLog>().ToList();
response.Transactions = reader.Read <AutoShipTransactions>().ToList();
}
}
catch (Exception exception)
{
Common.LogError(request.LoginName, exception.Message, exception.StackTrace, "AutoshipController.GetAutoShipResults", "E");
return(BadRequest(exception.Message));
}
return(Ok(response));
}
public static IEnumerable <TFirst> Map <TFirst, TSecond, TKey>
(
this Dapper.SqlMapper.GridReader reader,
Func <TFirst, TKey> firstKey,
Func <TSecond, TKey> secondKey,
Action <TFirst, ICollection <TSecond> > addChildren
)
{
var first = reader.Read <TFirst>().ToList();
var childMap = reader
.Read <TSecond>()
.GroupBy(s => secondKey(s))
.ToDictionary(g => g.Key, g => g.AsEnumerable());
foreach (var item in first)
{
IEnumerable <TSecond> children;
if (childMap.TryGetValue(firstKey(item), out children))
{
addChildren(item, children.ToList());
}
}
return(first);
}
public Tuple <IEnumerable <T1>, IEnumerable <T2>, IEnumerable <T3> > QueryWithMultiOutput <T1, T2, T3>(string query, Dictionary <string, SqlParam> parameters = null)
{
Dapper.SqlMapper.GridReader reader = null;
var _connection = Context.Connection;
if (parameters == null)
{
using (var result = _connection.QueryMultiple(query, null, null, null, CommandType.StoredProcedure))
{
reader = result;
var data1 = reader.Read <T1>().ToList();
var data2 = reader.Read <T2>().ToList();
var data3 = reader.Read <T3>().ToList();
_connection.Close();
return(new Tuple <IEnumerable <T1>, IEnumerable <T2>, IEnumerable <T3> >(data1, data2, data3));
}
}
else
{
var ps = new SqlDynamicParameters();
foreach (var p in parameters)
{
ps.Add(p.Key, p.Value.Value, p.Value.SqlType, p.Value.Direction);
}
using (var result = _connection.QueryMultiple(query, ps, null, 1000000, CommandType.StoredProcedure))
{
var data1 = result.Read <T1>().ToList();
var data2 = result.Read <T2>().ToList();
var data3 = result.Read <T3>().ToList();
_connection.Close();
return(new Tuple <IEnumerable <T1>, IEnumerable <T2>, IEnumerable <T3> >(data1, data2, data3));
}
}
}
/// <summary>
/// 取数据，返回多个结果集的读取方式
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="connString">链接字符串</param>
/// <param name="proc">存储过程名</param>
/// <param name="procParams">参数</param>
/// <param name="readResult">对结果集的处理函数</param>
/// <returns></returns>
public static T InnerQueryMultiple <T>(String connString, String proc, DynamicParameters procParams, Func <Dapper.SqlMapper.GridReader, T> readResult)
{
IDbConnection conn = null;
Dapper.SqlMapper.GridReader reader = null;
try
{
conn = new SqlConnection(connString);
reader = conn.QueryMultiple(proc, procParams, commandType: CommandType.StoredProcedure);
return(readResult(reader));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (conn != null)
{
conn.Dispose();
}
}
}
public void TestMultiClosedConnAsync()
{
using (var conn = Program.GetClosedConnection())
{
using (Dapper.SqlMapper.GridReader multi = conn.QueryMultipleAsync("select 1; select 2").Result)
{
multi.ReadAsync <int>().Result.Single().IsEqualTo(1);
multi.ReadAsync <int>().Result.Single().IsEqualTo(2);
}
}
}
public List <T> ExecuteQuery(string sql)
{
List <T> list = null;
using (IDbConnection cn = Connection)
{
cn.Open();
Dapper.SqlMapper.GridReader result = cn.QueryMultiple(sql);
list = (List <T>)result.Read <T>();
}
return(list);
}
public int Insert(T record)
{
Type type = typeof(T);
string tableName = type.ToString();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("insert into {0}(", tableName);
// get properties of T
List <PropertyInfo> properties = type.GetProperties().ToList();
// get non-key property
List <PropertyInfo> valueProps = properties.Where(p => !string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase)).ToList();
for (int i = 0; i < valueProps.Count; i++)
{
sb.AppendFormat("[{0}]", valueProps[i].Name);
if (i < valueProps.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(") values(");
for (int i = 0; i < valueProps.Count; i++)
{
sb.AppendFormat("@{0}", valueProps[i].Name);
if (i < valueProps.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(");select SCOPE_IDENTITY() id");
Dapper.SqlMapper.GridReader reader = db.QueryMultiple(sb.ToString(), record);
var first = reader.Read().FirstOrDefault();
int id = 0;
if (first != null && first.id != null)
{
id = (int)first.id;
}
return(id);
}
public DapperMultipleResultSet(IDatabaseContext dbContext, Dapper.SqlMapper.GridReader gridReader)
{
if (dbContext == null)
{
throw new ArgumentNullException("dbContext");
}
if (_gridReader == null)
{
throw new ArgumentNullException("_gridReader");
}
if (_gridReader.IsConsumed)
{
throw new InvalidOperationException("The underlying reader has been consumed or closed");
}
_dbContext = dbContext;
_gridReader = gridReader;
}
public void TestRand()
{
var r = new Random();
//for (int i = 0; i < 100; i++)
// Console.WriteLine(r.Next(27));
// long tick = DateTime.Now.Ticks;
// Random ran = new Random((int)(tick & 0xffffffffL) | (int)(tick >> 32));
// Stream Recy
//var manager = new RecyclableMemoryStreamManager();
//manager.g
for (int i = 0; i < 100; i++)
{
// Console.WriteLine(ran.Next());
}
DateTime t = DateTime.Now;
Console.WriteLine(t);
t = t.AddMonths(-1);
Console.WriteLine(t);
DapperContext context = new DapperContext("TestDapper");
context.Batch(s =>
{
string sql = @"select 1 as a1;select 2 as a2;select 3 as a3;select 4 as a4;
select 5 as a5;select 6 as a6;select 7 as a7;select 8 as a8;select 9 as a9;select 10 as a10;
";
Dapper.SqlMapper.GridReader gr = s.QueryMultiple(sql);
var a1 = gr.Read <b1>();
var a2 = gr.Read <b2>();
var a3 = gr.Read <b3>();
var a4 = gr.Read <b4>();
var a5 = gr.Read <b5>();
var a6 = gr.Read <b6>();
var a7 = gr.Read <b7>();
var a8 = gr.Read <b8>();
var a9 = gr.Read <b9>();
var a10 = gr.Read <b10>();
});
}
public static TParent MapForSingle <TParent, TChild, TKey>
(
this Dapper.SqlMapper.GridReader reader,
Func <TParent, TKey> firstKey,
Func <TChild, TKey> secondKey,
Action <TParent, TChild> addChildren)
{
var first = reader.Read <TParent>().FirstOrDefault();
var childMap = reader
.Read <TChild>()
.GroupBy(s => secondKey(s))
.ToDictionary(g => g.Key, g => g.FirstOrDefault <TChild>());
TChild child;
if (childMap.TryGetValue(firstKey(first), out child))
{
addChildren(first, child);
}
return(first);
}
public static List <MortalidadEntidad> DatosMortalidad(string pCountry, string pYear)
{
var p = new OracleDynamicParameters();
p.Add("P_COD_COUNTRY", pCountry); //parametro entrada codigo pais
p.Add("P_YEAR", pYear); // parametro entrada año yyyy
p.Add("P_CUR_MORT_PAIS", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var vMortalidad = new List <MortalidadEntidad>();
try
{
using (Dapper.SqlMapper.GridReader gr = DB.GetMultiGrid("HLEIVA.PKG_DATA_DEMOGRAF.SP_OBT_MORT_PAIS", p))
{
vMortalidad = gr.Read <MortalidadEntidad>().ToList <MortalidadEntidad>();
}
}
catch (InvalidCastException e)
{
throw e;
}
return(vMortalidad);
}
public static IEnumerable <TParent> Map <TParent, TChild, TKey>
(
this Dapper.SqlMapper.GridReader reader,
Func <TParent, TKey> firstKey,
Func <TChild, TKey> secondKey,
Action <TParent, TChild> addChildren)
{
var first = reader.Read <TParent>().ToList();
var childMap = reader
.Read <TChild>()
.GroupBy(s => secondKey(s))
.ToDictionary(g => g.Key, g => g.FirstOrDefault <TChild>());
foreach (var item in first)
{
TChild child;
if (childMap.TryGetValue(firstKey(item), out child))
{
addChildren(item, child);
}
}
return(first);
}
public MyReader(Dapper.SqlMapper.GridReader reader)
{
this.reader = reader;
}
//PageInfo参数必须是dynamic par = new ExpandoObject();
public void QueryByPage <T>(PageInfo <T> pageinfo)
{
if (pageinfo.ReturnFields == null)
{
pageinfo.ReturnFields = "[id],[username],[pwd],[sex]";
}
if (pageinfo.Where != null)
{
pageinfo.Where = "WHERE " + pageinfo.Where;
}
if (pageinfo.OrderBy != null)
{
pageinfo.OrderBy = "ORDER BY " + pageinfo.OrderBy;
}
else
{
pageinfo.OrderBy = "ORDER BY [id] DESC";
}
string sqlData = null;
if (pageinfo.Skip == 0)
{
sqlData = string.Format("SELECT TOP {0} {1} FROM [admins] {2} {3}", pageinfo.Take, pageinfo.ReturnFields, pageinfo.Where, pageinfo.OrderBy);
}
else
{
sqlData = string.Format("SELECT TOP {3} {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS Num,{0} FROM [admins] {2}) AS [T] WHERE [T].[Num]>@skip", pageinfo.ReturnFields, pageinfo.OrderBy, pageinfo.Where, pageinfo.Take);
}
string sql = string.Format(@"
DECLARE @total BIGINT
SET @total=(SELECT COUNT(1) FROM [admins] {0})
SELECT @total
IF(@total!=0)
BEGIN
{1}
END" , pageinfo.Where, sqlData);
using (var conn = SqlDapperHelper.CreateConnection())
{
Dapper.SqlMapper.GridReader Reader = null;
if (pageinfo.Params == null)
{
Reader = conn.QueryMultiple(sql, new { skip = pageinfo.Skip });
}
else
{
pageinfo.Params.skip = pageinfo.Skip;
Reader = conn.QueryMultiple(sql, (object)pageinfo.Params);
}
pageinfo.Total = Reader.Read <long>().FirstOrDefault();
if (pageinfo.Total != 0)
{
pageinfo.Data = Reader.Read <T>();
}
else
{
pageinfo.Data = new List <T>();
}
Reader.Dispose();
}
}
public GridReader(Dapper.SqlMapper.GridReader r, Dapper.DynamicParameters ps)
{
this.reader = r;
this.OutputParams = ps;
}
//PageInfo参数必须是dynamic par = new ExpandoObject();
public void QueryByPage <T>(PageInfo <T> pageinfo)
{
if (pageinfo.ReturnFields == null)
{
pageinfo.ReturnFields = "[id],[exam_id],[grade_class],[exam_subject],[exam_avg],[exam_stdevp],[exam_max],[exam_allpassnum],[exam_onepassnum],[exam_twopassnum],[exam_threepassnum],[exam_greatnum],[exam_passnum],[exam_allrank],[exam_onerank],[exam_tworank],[exam_threerank]";
}
if (pageinfo.Where != null)
{
pageinfo.Where = "WHERE " + pageinfo.Where;
}
if (pageinfo.OrderBy != null)
{
pageinfo.OrderBy = "ORDER BY " + pageinfo.OrderBy;
}
else
{
pageinfo.OrderBy = "ORDER BY [id] DESC";
}
string sqlData = null;
if (pageinfo.Skip == 0)
{
sqlData = string.Format("SELECT TOP {0} {1} FROM [examtotalcount_without] {2} {3}", pageinfo.Take, pageinfo.ReturnFields, pageinfo.Where, pageinfo.OrderBy);
}
else
{
sqlData = string.Format("SELECT TOP {3} {0} FROM (SELECT ROW_NUMBER() OVER({1}) AS Num,{0} FROM [examtotalcount_without] {2}) AS [T] WHERE [T].[Num]>@skip", pageinfo.ReturnFields, pageinfo.OrderBy, pageinfo.Where, pageinfo.Take);
}
string sql = string.Format(@"
DECLARE @total BIGINT
SET @total=(SELECT COUNT(1) FROM [examtotalcount_without] {0})
SELECT @total
IF(@total!=0)
BEGIN
{1}
END" , pageinfo.Where, sqlData);
using (var conn = SqlDapperHelper.CreateConnection())
{
Dapper.SqlMapper.GridReader Reader = null;
if (pageinfo.Params == null)
{
Reader = conn.QueryMultiple(sql, new { skip = pageinfo.Skip });
}
else
{
pageinfo.Params.skip = pageinfo.Skip + 1;
pageinfo.Params.take = pageinfo.Take;
Reader = conn.QueryMultiple(sql, (object)pageinfo.Params);
}
pageinfo.Total = Reader.Read <long>().FirstOrDefault();
if (pageinfo.Total != 0)
{
pageinfo.Data = Reader.Read <T>();
}
else
{
pageinfo.Data = new List <T>();
}
Reader.Dispose();
}
}
internal GridReaderWrapper(GridReader gridReader)
{
_gridReader = gridReader;
}
