/// <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);
}
/// <summary>
/// Updates the record.
/// If there is a property marked as EntityKey the update will consider that field as Unique Key
/// If there is not any property marked as EntityKey the function will throw an exception
/// </summary>
/// <param name="record">The record.</param>
/// <returns></returns>
/// <exception cref="System.Exception">
/// There is not any property marked as EntityKey
/// or
/// A Class marked with attribute NotInsert cannot perform write opperations
/// </exception>
public bool UpdateRecord(IDataEntity record)
{
List <PropertyInfo> properties = record.GetType().GetProperties().ToList();
Dictionary <string, string> FieldsNames = new Dictionary <string, string>();
var customAttributes = record.GetType().GetCustomAttributes();
OnBeforeInsert(null);
//find if there is an EntityKey property. If there is not we abort the operation
Boolean KeyExists = false;
foreach (PropertyInfo property in properties)
{
DataInterface.Attribbutes.EntityKey KeyProperty = (DataInterface.Attribbutes.EntityKey)(from attr in property.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.EntityKey) select attr).FirstOrDefault();
if (KeyProperty != null)
{
KeyExists = true;
break;
}
}
if (KeyExists == false)
{
throw new Exception("There is not any property marked as EntityKey");
}
DataInterface.Attribbutes.NotInsert NotInsertAttribute = (DataInterface.Attribbutes.NotInsert)(from attr in record.GetType().GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.NotInsert) select attr).FirstOrDefault();
if (NotInsertAttribute != null)
{
throw new Exception("A Class marked with attribute NotInsert cannot perform write opperations");
}
//first find the table attribute to obtain the maped db table
DataInterface.Attribbutes.Table TableAttribute = (DataInterface.Attribbutes.Table)(from attr in record.GetType().GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Table) select attr).FirstOrDefault();
using (SqlConnection connection = new SqlConnection(this.Source))
{
connection.Open();
try
{
StringBuilder Query = new StringBuilder();
if (TableAttribute != null)
{
Query.Append($"Update {TableAttribute.Name} set ");
}
else
{
Query.Append($"Update {record.GetType().Name} set");
}
//get the properties of the entity
// create the insert query
CreateUpdateQuery(record, Query, properties, FieldsNames);
var com = new SqlCommand(Query.ToString(), connection);
//Create the sql parameters
if (_Encryptor != null)
{
if (record is IEncryptableClass)
{
_Encryptor.EncryptProperties((IEncryptableClass)record);
}
}
SetInsetParameters(record, properties, FieldsNames, com);
foreach (PropertyInfo property in properties)
{
DataInterface.Attribbutes.EntityKey KeyProperty = (DataInterface.Attribbutes.EntityKey)(from attr in property.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.EntityKey) select attr).FirstOrDefault();
if (KeyProperty != null)
{
DataInterface.Attribbutes.Field FieldProperty = (DataInterface.Attribbutes.Field)(from attr in property.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Field) select attr).FirstOrDefault();
if (FieldProperty != null)
{
com.Parameters.AddWithValue($"@{FieldProperty.Name}", property.GetValue(record));
}
else
{
com.Parameters.AddWithValue($"@{property.Name}", property.GetValue(record));
}
}
}
com.ExecuteNonQuery();
//find the Key property of the entity
if (_Encryptor != null)
{
if (record is IEncryptableClass)
{
_Encryptor.Decryptproperties((IEncryptableClass)record);
}
}
connection.Close();
}
catch (Exception ex)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
throw;
}
}
return(true);
}
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>
/// Adds the record asynchronous.
/// If there is not a "Field" attribute on a property then the property is maped to the datasource
/// with it's name.
/// If a property is marked as EntityKey then the property will take the Identity value after the insert
/// If there is a Table attribute on the Entity then the entity is mapped to the specified Datasource table otherwise is mapped with it's name.
/// If an encryptor instance is available the properties marked as MuStEncrypt are encrypted before insert and decrypted after the insert
/// </summary>
/// <param name="Record">The record.</param>
/// <returns></returns>
public async Task <bool> AddRecord_Async(IDataEntity Record)
{
OnBeforeInsert(new OperatorEventArguments()
{
Data = Record
});
DataInterface.Attribbutes.NotInsert NotInsertAttribute = (DataInterface.Attribbutes.NotInsert)(from attr in Record.GetType().GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.NotInsert) select attr).FirstOrDefault();
if (NotInsertAttribute != null)
{
throw new Exception("A Class marked with attribute NotInsert cannot perform write opperations");
}
//first find the table attribute to obtain the maped db table
DataInterface.Attribbutes.Table TableAttribute = (DataInterface.Attribbutes.Table)(from attr in Record.GetType().GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Table) select attr).FirstOrDefault();
using (SqlConnection connection = new SqlConnection(this.Source))
{
connection.Open();
try
{
StringBuilder Query = new StringBuilder();
if (TableAttribute != null)
{
Query.Append($"Insert INTO {TableAttribute.Name} (");
}
else
{
Query.Append($"Insert INTO {Record.GetType ().Name} (");
}
//get the properties of the entity
List <PropertyInfo> properties = Record.GetType().GetProperties().ToList();
Dictionary <string, string> FieldsNames = new Dictionary <string, string>();
List <string> FieldsHolders = new List <string>();
// create the insert query
CreateInsertQuery(Record, Query, properties, FieldsNames);
var com = new SqlCommand(Query.ToString(), connection);
//Create the sql parameters
if (_Encryptor != null)
{
if (Record is IEncryptableClass)
{
_Encryptor.EncryptProperties((IEncryptableClass)Record);
}
}
SetInsetParameters(Record, properties, FieldsNames, com);
int result = Convert.ToInt32(await com.ExecuteScalarAsync());
//find the Key property of the entity
UpdateKeyPropery(Record, properties, result);
if (_Encryptor != null)
{
if (Record is IEncryptableClass)
{
_Encryptor.Decryptproperties((IEncryptableClass)Record);
}
}
connection.Close();
}
catch (Exception ex)
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
OnRecordInsertFailed(new OperatorEventArguments()
{
Data = Record
});
return(false);
}
}
OnRecordInserted(new OperatorEventArguments()
{
Data = Record
});
return(true);
}
