public async Task <List <People> > GetPeoples() { string sql = "Select * From people"; var peoples = ReadConnection.Query <People>(sql).ToList(); return(peoples); }
public async Task <Tuple <List <MaintenanceInfoResponse>, int> > GetPageListAsync(MaintenanceInfoPageRequest request) { StringBuilder sbSql = new StringBuilder(); StringBuilder sbCount = new StringBuilder(); DynamicParameters parameters = new DynamicParameters(); sbSql.Append(@" SELECT * FROM maintenanceinfo m LEFT JOIN servicebusiness s ON m.RepairId = s.Id LEFT JOIN attachment a ON m.Id = a.DataId AND a.IsDeleted = 0 AND a.DataType = 1 WHERE 1=1 "); sbCount.Append(" SELECT COUNT(*) FROM maintenanceinfo m WHERE 1=1 "); var where = this.GetWhere(request, parameters); if (where?.Item1?.Length > 0) { sbSql.Append(where.Item1); sbCount.Append(where.Item1); } //添加排序 sbSql.Append(" Order By m.CreationTime DESC "); if (request.Count.HasValue && request.Count.Value > 0) { sbSql.Append(" LIMIT @Skip,@Count "); parameters.Add("@Skip", request.Skip.Value); parameters.Add("@Count", request.Count.Value); } using (ReadConnection) { var lookup = new Dictionary <Guid, MaintenanceInfoResponse>(); var query = ReadConnection.Query <MaintenanceInfoResponse, ServiceBusiness, Attachment, List <MaintenanceInfoResponse> >(sbSql.ToString(), (a, c, b) => { if (!lookup.Keys.Contains(a.Id)) { if (a != null && c != null) { a.ServiceBusiness = c; } lookup.Add(a.Id, a); if (lookup[a.Id].Attachments == null) { lookup[a.Id].Attachments = new List <Attachment>(); } lookup[a.Id].Attachments.Add(b); } else { lookup[a.Id].Attachments.Add(b); } return(lookup.Values.ToList()); }, parameters); int r2 = 0; if (request.Count.HasValue && request.Count.Value > 0) { r2 = await ReadConnection.QueryFirstAsync <int>(sbCount.ToString(), parameters); } return(new Tuple <List <MaintenanceInfoResponse>, int>(lookup.Values.ToList(), r2)); } }
public async Task <int> GetCodeTotalCount(string tableName, string paramName) { int totalCount = 0; StringBuilder codeSql = new StringBuilder(); codeSql.AppendFormat("SELECT {0} FROM {1} WHERE CreationTime >= @TodayStart AND CreationTime < @TodayEnd ORDER BY {0} DESC LIMIT 0,1;", paramName, tableName); DateTime TodayStart = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); DateTime TodayEnd = TodayStart.AddDays(1); var list = ReadConnection.Query <string>(codeSql.ToString(), new { TodayStart = TodayStart, TodayEnd = TodayEnd }).ToList(); if (list?.Count > 0) { totalCount = int.Parse(list.FirstOrDefault().Substring(7, 4)); } return(totalCount); }