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