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)); } } }
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 void TestMultiClosedConnAsync() { using (var conn = Program.GetClosedConnection()) { using (Dapper.SqlMapper.GridReader multi = conn.QueryMultipleAsync("select 1; select 2").Result) { multi.Read <int>().Single().IsEqualTo(1); multi.Read <int>().Single().IsEqualTo(2); } } }
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 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 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 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 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 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 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); }
//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(); } }
/// <summary> /// 应立即读取数据 如: Read().ToList() 否则导致读取异常; 对多个resultset,多次调用此方法,但每个resultset仅可读取一次 /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public IEnumerable <T> Read <T>() { return(reader.Read <T>()); }
/// <inheritdoc /> public IEnumerable <TResult> Get <TResult>() { return(_gridReader.Read <TResult>()); }
//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(); } }
/// <summary> /// Read the next grid of results /// </summary> public IEnumerable <T> Read <T>(bool buffered = true) => _gridReader.Read <T>(buffered);
//读取列表返回dynamic public IEnumerable <dynamic> Read(bool buffered = true) { return(reader.Read(buffered)); }