/// <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);
}
