/// <summary>
/// Gets a collection of entitiews by Entity Key value.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Key">The key.</param>
/// <param name="ValueOfKey">The value of key.</param>
/// <returns></returns>
/// <exception cref="Exception">There is not any property marked as EntityKey</exception>
public async Task <EntityCollection <T> > GetByEntityKey_Async <T>(string Key, string ValueOfKey) where T : class, IDataEntity
{
var Filters = new Base.RequestFilters
{
Filters = new List <Base.DataFilter> {
new Base.DataFilter {
Comparer = "=",
Field = Key,
Value = ValueOfKey
}
}
};
return(await GetRecords_Async <T>(Filters));
}
private static void BuildWhereStaTement(Base.RequestFilters filters, StringBuilder Query, SqlCommand com)
{
if (filters != null)
{
if (filters.Filters != null)
{
for (int i = 0; i < filters.Filters.Count; i++)
{
if (filters.Filters[i].Field.ToLower().Contains("date"))
{
Query.Append($" and convert(date,{filters.Filters[i].Field}){filters.Filters[i].Comparer}@{filters.Filters[i].Field}_{i}");
com.Parameters.Add(new System.Data.SqlClient.SqlParameter($"@{filters.Filters[i].Field}_{i}", DateTime.Parse(filters.Filters[i].Value, System.Globalization.CultureInfo.InvariantCulture)));
}
else
{
Query.Append($" and {filters.Filters[i].Field}{filters.Filters[i].Comparer}@{filters.Filters[i].Field}_{i}");
com.Parameters.Add(new System.Data.SqlClient.SqlParameter($"@{filters.Filters[i].Field}_{i}", filters.Filters[i].Value));
}
}
}
if (filters.OrFilters != null)
{
for (int i = 0; i < filters.OrFilters.Count; i++)
{
var currentFilters = filters.OrFilters[i].Filters;
Query.Append($" and ( 1=1 ");
for (int x = 0; x < currentFilters.Count; x++)
{
if (currentFilters[x].Field.ToLower().Contains("date"))
{
Query.Append($" or convert(date, {currentFilters[x].Field}){currentFilters[x].Comparer}@{currentFilters[x].Field}{x}_Or{x}");
com.Parameters.Add(new System.Data.SqlClient.SqlParameter($"@{currentFilters[x].Field}{x}_Or{x}", DateTime.Parse(currentFilters[x].Value, System.Globalization.CultureInfo.InvariantCulture)));
}
else
{
Query.Append($" or {currentFilters[x].Field}{currentFilters[x].Comparer}@{currentFilters[x].Field}{x}_Or{x}");
com.Parameters.Add(new System.Data.SqlClient.SqlParameter($"@{currentFilters[x].Field}{x}_Or{x}", currentFilters[x].Value));
}
}
Query.Append($" ) ");
}
}
}
}
private static void BuildCountPagingQuery <T>(Base.RequestFilters filters, int PageSize, Type currentType, EntityCollection <T> records, DataInterface.Attribbutes.Table TableAttribute, SqlConnection connection) where T : class, IDataEntity
{
//count the records also
var CountQuery = new StringBuilder();
if (TableAttribute == null)
{
CountQuery.Append($"Select count(*) from {currentType.Name } where 1=1 ");
}
else
{
CountQuery.Append($"Select count(*) from {TableAttribute.Name} where 1=1 ");
}
SqlCommand countcom = new SqlCommand();
BuildWhereStaTement(filters, CountQuery, countcom);
countcom.Connection = connection;
countcom.CommandText = CountQuery.ToString();
var Totalrecords = Convert.ToInt32(countcom.ExecuteScalar());
records.Pager.TotalRecords = Totalrecords;
records.Pager.TotalPages = (Totalrecords / PageSize) + 1;
}
/// <summary>
/// Gets the records asynchronous.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filters">The filters.</param>
/// <param name="Order">The order.</param>
/// <param name="PageSize"></param>
/// <param name="Page"></param>
/// <returns></returns>
public async Task <EntityCollection <T> > GetRecords_Async <T>(Base.RequestFilters filters = null, Base.DataOrder Order = null, int PageSize = 0, int Page = 0) where T : class, IDataEntity
{
var currentType = typeof(T);
var records = new EntityCollection <T>();
//first set the source where we get the data
//if there is not any Table attribute we set the source as the type name
DataInterface.Attribbutes.Table TableAttribute = (DataInterface.Attribbutes.Table)(from attr in currentType.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Table) select attr).FirstOrDefault();
using (SqlConnection connection = new SqlConnection(Source))
{
await connection.OpenAsync();
try
{
StringBuilder Query = new StringBuilder();
if (TableAttribute == null)
{
Query.Append($"Select * from {currentType.Name } where 1=1 ");
}
else
{
Query.Append($"Select * from {TableAttribute.Name} where 1=1 ");
}
SqlCommand com = new SqlCommand();
BuildWhereStaTement(filters, Query, com);
if (Order != null)
{
Query.Append($" order by {Order.Field} {Order.Order}");
}
if (Page != 0)
{
Query.Append($" OFFSET (({Page} - 1) * {PageSize }) ROWS FETCH NEXT {PageSize} ROWS ONLY; ");
BuildCountPagingQuery(filters, PageSize, currentType, records, TableAttribute, connection);
}
com.Connection = connection;
com.CommandText = Query.ToString();
var result = await com.ExecuteReaderAsync(System.Data.CommandBehavior.CloseConnection);
if (result.HasRows)
{
FillresultsNew(currentType, records, result);
}
result.Close();
}
catch (Exception ex)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
throw;
}
}
if (Page != 0)
{
records.Pager.CurrentPage = Page;
}
else
{
records.Pager.TotalRecords = records.Count;
}
records.Pager.PageRecords = records.Count;
return(records);
}
