public static void OrCriteriaSample()
{
ICriteria cr = new OrCriteria(new[] { ICriteria.Empty, ICriteria.Empty });
FormatedCriteria fCr = cr.Format(0);
Console.WriteLine(fCr.Query);
Console.WriteLine(fCr.Params.Length);
}
public static void EmptyCriteriaSample()
{
ICriteria cr = new EmptyCriteria();
// or ICriteria cr = ICriteria.Empty;
FormatedCriteria fCr = cr.Format(0);
Console.WriteLine(fCr.Query);
Console.WriteLine(fCr.Params.Length);
}
public static void GtCriteriaSample()
{
ICriteria cr = new GtCriteria <float>("sale", 0.5f);
FormatedCriteria fCr = cr.Format(0);
Console.WriteLine(fCr.Query); // "sale > @p0"
Console.WriteLine(fCr.Params.Length); // "1"
Console.WriteLine(fCr.Params[0].ToFormatedString());
// Parameter [ Name: "p0", Type: Single, Value: 0,5 ]
}
public static void EqCriteriaSample()
{
ICriteria cr = new EqCriteria <string>("customer", "Jhon Smith");
FormatedCriteria fCr = cr.Format(0);
Console.WriteLine(fCr.Query); // "customer = @p0"
Console.WriteLine(fCr.Params.Length); // "1"
Console.WriteLine(fCr.Params[0].ToFormatedString());
// Parameter [ Name: "p0", Type: String, Value: "Jhon Smith" ]
}
public async Task <FilteredData <dynamic> > GetFilteredDataAsync(string tableName,
ICriteria baseCriteria,
IDictionary <string, SortDirection> orderByExpression = null,
ICriteria filterCriteria = null,
int pageSize = -1,
int pageNumber = 0)
{
IDictionary <string, SortDirection> orderBy = CheckOrderByExpressionOrDefault(orderByExpression);
ColumnInfo[] columns = await GetTableInfoAsync(tableName);
using DbConnection connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
DbCommand command = connection.CreateCommand();
command.Transaction = await connection.BeginTransactionAsync(IsolationLevel.ReadUncommitted);
int paramNumber = 0;
FormatedCriteria baseFormatedCriteria = baseCriteria.Format(0);
paramNumber = baseFormatedCriteria.Params.Length;
command.CommandText = $@"
select count(*)
from {TableSchema}.{tableName}
where {baseFormatedCriteria.Query}";
command.Parameters.AddRange(baseFormatedCriteria.Params);
FilteredData <dynamic> data = new FilteredData <dynamic>
{
TotalCount = int.Parse(command.ExecuteScalar().ToString())
};
List <dynamic> records = new List <dynamic>();
FormatedCriteria filterFormatedCriteria = (filterCriteria ?? ICriteria.Empty).Format(paramNumber);
command.CommandText = $@"
select {columns.Select(c => c.ColumnName).Aggregate((cur, next) => cur + ", " + next)}
from {TableSchema}.{tableName}
where {baseFormatedCriteria.Query} and {filterFormatedCriteria.Query}
order by {orderBy.Select(ex => $"{ex.Key} {ex.Value}").Aggregate((cur, next) => cur + ", " + next)}";
if (pageSize > 0)
{
command.CommandText += $@"
offset @offset ROWS
fetch next @fetch rows only";
command.Parameters.AddRange(new[]
{
new SqlParameter("offset", SqlDbType.Int)
{
Value = pageSize * pageNumber
},
new SqlParameter("fetch", SqlDbType.Int)
{
Value = pageSize
}
});
}
command.Parameters.AddRange(filterFormatedCriteria.Params);
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
Dictionary <string, object> record = new Dictionary <string, object>();
for (int i = 0; i < columns.Length; i++)
{
ColumnInfo column = columns[i];
record.Add(column.PropertyName, column.CanNullable && dr.IsDBNull(i) ? null : GetTypedValue(dr, 0, column.Type));
}
records.Add(new RepositoryDynamicObject(record));
}
}
data.Data = records;
data.FilteredCount = records.Count;
command.Transaction.Commit();
command.Dispose();
connection.Close();
return(data);
}
