/// <summary> /// 分页获取数据 /// </summary> /// <param name="innerQuery">分页前的内部子查询</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">页大小</param> /// <param name="conditions">条件子句(不用包含WHERE,如:"AND status = 1 ...")</param> /// <param name="projection">外层SELECT子句</param> /// <param name="param">查询参数对象</param> /// <typeparam name="T">结果集类型</typeparam> /// <returns></returns> public PagedList <T> GetPaging <T>(string innerQuery = null, int pageIndex = 1, int pageSize = int.MaxValue, string conditions = "", string projection = "*", object param = null) where T : class { if (string.IsNullOrEmpty(innerQuery)) { var tableName = SqlMapperExtensions.GetTableName(typeof(TEntity)); innerQuery = $"SELECT * FROM {tableName}"; } StringBuilder builder = new StringBuilder(); builder.Append($"SELECT COUNT(1) as totalCount FROM ({innerQuery}) as temp WHERE 1 = 1 {conditions};"); int start = (pageIndex - 1) * pageSize; builder.Append($"SELECT {projection} FROM ({innerQuery}) as temp WHERE 1 = 1 {conditions} LIMIT {start},{pageSize};"); return(Run(conn => { string sql = builder.ToString(); using (var multi = conn.QueryMultiple(sql, param)) { int totalCount = multi.Read <int>().Single(); var list = multi.Read <T>().ToList(); return new PagedList <T>() { PageIndex = pageIndex, PageSize = pageSize, TotalCount = totalCount, List = list }; } })); }
/// <summary> /// 删除| /// Delete data in table "Ts". /// </summary> /// <param name="tableNameFormat">Table Name Format placeholder</param> /// <param name="whereString">parameterized sql of "where",(example:whereString:name like @name)</param> /// <param name="param">whereString's param,(example:new { name = "google%" })</param> /// <returns>受影响的行数|The number of rows affected.</returns> public async Task <bool> DeleteAsync(string tableNameFormat, string whereString, object param) { SqlBuilder sb = new SqlBuilder(); sb.Append("DELETE FROM " + SqlMapperExtensions.GetTableName(TableName, tableNameFormat)); sb.Where(whereString, param); return(await ExecuteAsync(sb.ESQL, sb.Arguments) > 0); }
/// <summary> /// 异步查询总数| /// Returns the number of rows /// </summary> /// <param name="tableNameFormat">Table Name Format placeholder</param>/// /// <param name="whereString">parameterized sql of "where",(example:whereString:name like @name)</param> /// <param name="param">whereString's param,(example:new { name = "google%" })</param> /// <returns>总数|Returns the number of rows</returns> public async Task <int> QueryCountAsync(string tableNameFormat, string whereString = null, object param = null) { SqlBuilder sb = new SqlBuilder(); sb.Select(args: "Count(*)"); sb.From(SqlMapperExtensions.GetTableName(TableName, tableNameFormat)); if (!string.IsNullOrEmpty(whereString)) { sb.Where(whereString, param); } return(await DBConnection.QueryFirstAsync <int>(sb.ESQL, sb.Arguments, transaction : _dbTransaction)); }
public async Task BulkInsertAsync <T>(IDbConnection connection, IEnumerable <T> data, IDbTransaction transaction = null, int batchSize = 0, int bulkCopyTimeout = 30) { var type = typeof(T); var tableName = SqlMapperExtensions.GetTableName(type); DataTable dataTables = data.ToDataTable(); using (var bulkCopy = new SqlBulkCopy((SqlConnection)connection, SqlBulkCopyOptions.Default, (SqlTransaction)transaction)) { bulkCopy.BulkCopyTimeout = bulkCopyTimeout; bulkCopy.BatchSize = batchSize; bulkCopy.DestinationTableName = tableName; bulkCopy.ToColumnMapping <T>(); await bulkCopy.WriteToServerAsync(dataTables); } }
protected ReportRepository() { ConnectionString = ConfigSetting.ConnectionLongzhuSportsEntities; SlaveConnectionString = ConfigSetting.ConnectionLongzhuSportsEntitiesReadOnly; CurrentTableName = SqlMapperExtensions.GetTableName(Type); }
public ISqlBuilder GetPageList <T>(string tableNameFormat, IRepository <T> repository, int pageNum = 0, int pageSize = 0, string whereString = null, object param = null, object order = null, bool asc = false) where T : class, IEntity { SqlBuilder sqlBuilder = new SqlBuilder(); var sbColumnList = new StringBuilder(null); var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T)); var tableName = SqlMapperExtensions.GetTableName(repository.TableName, tableNameFormat); for (var i = 0; i < allProperties.Count; i++) { var property = allProperties[i]; AppendColumnName(sbColumnList, SqlMapperExtensions.GetColumnName(property), property.Name); if (i < allProperties.Count - 1) { sbColumnList.Append(", "); } } sqlBuilder.Select(args: sbColumnList.ToString()); if (pageSize > 0) { SqlBuilder sqlBuilderRows = new SqlBuilder(); string ascSql = " asc"; if (!asc) { ascSql = " desc"; } string orderSql = ""; if (order == null) { var type = typeof(T); var keys = SqlMapperExtensions.KeyPropertiesCache(type); orderSql = keys.Count > 0 ? SqlMapperExtensions.GetColumnName(keys[0]) : string.Empty; if (string.IsNullOrEmpty(orderSql)) { throw new Exception($"Need set table poco key [{tableName}]"); } } else { orderSql = SqlBuilder.GetArgsString("ORDER BY", args: order); } sqlBuilderRows.Select(args: $"SELECT ROW_NUMBER() OVER(ORDER BY {orderSql} {ascSql} ) AS row_id,it.*"); sqlBuilderRows.From($"{tableName} it"); if (!string.IsNullOrEmpty(whereString)) { sqlBuilderRows.Where(whereString, param); } sqlBuilder.Append($"From ({sqlBuilderRows.SQL}) TT", sqlBuilderRows.Arguments); if (pageNum <= 0) { pageNum = 1; } int numMin = (pageNum - 1) * pageSize + 1, numMax = pageNum * pageSize; sqlBuilder.Where("TT.row_id between :numMin and :numMax", new { numMin, numMax }); } else { sqlBuilder.From(tableName); if (!string.IsNullOrEmpty(whereString)) { sqlBuilder.Where(whereString, param); } if (order != null) { sqlBuilder.OrderBy(order); sqlBuilder.IsAse(asc); } } return(sqlBuilder); }