public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
string countExpression,
string whereExpression,
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
object parameters = null,
bool?runCount = null)
{
ModelDefinition modelDef = ModelDefinition <T> .Definition;
string tableName = cnn.DialectProvider.GetQuotedTableName(modelDef);
string countQuery = countExpression;
if (string.IsNullOrEmpty(countQuery))
{
countQuery = "SELECT COUNT(*) FROM " + tableName;
}
var data = (await cnn.SelectAsync <T>());
long countValue = 0;
countValue = await cnn.QueryFirstAsync <long>(countQuery);
return(new PaginationQuery <T>()
{
Data = data,
TotalRecords = countValue
});
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
SqlExpressionVisitor <T> expressionVisitor,
string selectExpression = "",
string whereExpression = "",
string countExpression = "",
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
object parameters = null,
bool runCount = false)
{
return(await QueryPagination <T>(cnn,
expressionVisitor,
selectExpression,
whereExpression,
countExpression,
"",
"",
"",
startPageNo,
endPageNo,
pageSize,
parameters,
runCount));
}
protected OrmConnection OpenDbConnection()
{
if (_conn?.DbConnection == null)
{
_conn = _dialectProvider.CreateConnection(ConnectionString);
_conn.Open();
}
return(_conn);
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
object parameters = null,
bool runCount = false)
{
return(await QueryPagination <T>(cnn, null, startPageNo, endPageNo, pageSize, parameters, runCount));
}
protected OrmConnection OpenDbConnection()
{
if (_conn?.DbConnection == null)
{
_conn = _connectionFactory.OpenConnection();
}
return(_conn);
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
string whereExpression,
int?startPageNo,
int?endPageNo,
int?pageSize,
object parameters,
bool runCount = false)
{
var expressionVisitor = cnn.DialectProvider.ExpressionVisitor <T>();
return(await QueryPagination <T>(cnn, expressionVisitor, null, whereExpression, startPageNo, endPageNo, pageSize, parameters, runCount));
}
private static void AddMembers(OrmConnection db)
{
db.Insert(new Member {
Val1 = "Salut"
});
db.Insert(new Member {
Val1 = "Hello"
});
db.Insert(new Member {
Val1 = "Hola"
});
}
public virtual void Setup()
{
if (_conn != null)
{
_conn.Dispose();
_conn = null;
}
SqlMapper.ResetTypeHandlers();
SqlMapper.AddTypeHandler(typeof(TestEnum), new EnumAsIntTypeHandler <TestEnum>());
OpenDbConnection().CreateTable <TestType>(true);
OpenDbConnection().CreateTable <Person>(true);
OpenDbConnection().CreateTable <TestType2>(true);
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
Expression <Func <T, bool> > selectPredicate,
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
bool runCount = false)
{
var expressionvisitor = cnn.DialectProvider.ExpressionVisitor <T>();
var whereExpression = expressionvisitor.Where(selectPredicate).WhereExpression;
var selectExpression = expressionvisitor.SelectExpression;
return(await QueryPagination <T>(cnn,
expressionvisitor,
selectExpression,
whereExpression,
startPageNo,
endPageNo,
pageSize,
expressionvisitor.Parameters,
runCount));
}
public static async Task <PaginationQuery <T> > QueryPagination <T>(this OrmConnection cnn,
SqlExpressionVisitor <T> expressionVisitor,
string selectExpression = "",
string whereExpression = "",
string countExpression = "",
string groupByExpression = "",
string havingExpression = "",
string orderByExpression = "",
int?startPageNo = null,
int?endPageNo = null,
int?pageSize = null,
object parameters = null,
bool runCount = false)
{
if (expressionVisitor == null)
{
expressionVisitor = cnn.DialectProvider.ExpressionVisitor <T>();
}
if (string.IsNullOrEmpty(selectExpression))
{
selectExpression = expressionVisitor.SelectExpression;
}
if (string.IsNullOrEmpty(whereExpression))
{
whereExpression = expressionVisitor.WhereExpression;
}
if (string.IsNullOrEmpty(havingExpression))
{
havingExpression = expressionVisitor.HavingExpression;
}
if (string.IsNullOrEmpty(groupByExpression))
{
groupByExpression = expressionVisitor.GroupByExpression;
}
if (string.IsNullOrEmpty(orderByExpression))
{
orderByExpression = expressionVisitor.OrderByExpression;
}
ModelDefinition modelDef = ModelDefinition <T> .Definition;
string tableName = cnn.DialectProvider.GetQuotedTableName(modelDef);
string countQuery = countExpression;
if (string.IsNullOrEmpty(countQuery))
{
countQuery = "SELECT COUNT(*) FROM " + tableName;
}
var sql = selectExpression + " " + whereExpression + " " + " order by id desc";
var data = await cnn.QueryAsync <T>(sql, parameters);
// var data = (await cnn.SelectAsync<T>());
long countValue = 0;
countValue = await cnn.QueryFirstAsync <long>(countQuery);
return(new PaginationQuery <T>()
{
Data = data
,
TotalRecords = countValue
});
}
/// <summary>Count by column.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="db"> The database.</param>
/// <param name="predicate">The predicate.</param>
/// <returns>The total number of by column.</returns>
private int CountByColumn <T>(OrmConnection db, Expression <Func <T, bool> > predicate)
where T : IHasCountColumn, new()
{
return(db.GetScalar(e => Sql.Count(e.CountColumn), predicate).Value);
}
/// <summary>Counts.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="db"> The database.</param>
/// <param name="predicate">The predicate.</param>
/// <returns>An int.</returns>
private int Count <T>(OrmConnection db, Expression <Func <T, bool> > predicate) where T : IHasId <int>, new()
{
return(db.GetScalar(e => Sql.Count(e.Id), predicate));
}
/// <summary>Count by column.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="db">The database.</param>
/// <returns>The total number of by column.</returns>
private long CountByColumn <T>(OrmConnection db) where T : IHasCountColumn, new()
{
var request = new T();
return(db.GetScalar <T, long?>(e => Sql.Count(request.CountColumn)) ?? -1);
}
/// <summary>Counts.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="db">The database.</param>
/// <returns>An int.</returns>
private long Count <T>(OrmConnection db) where T : IHasId <int>, new()
{
var request = new T();
return(db.GetScalar <T, long>(e => Sql.Count(request.Id)));
}
public void Dispose()
{
this.Connection.Close();
this._connection = null;
}
