/// <summary>
/// Gets the <see cref="List{T}"/> from a sql database
/// </summary>
/// <typeparam name="TModel">The type of data to get from the corresponding table in the <see cref="DBContext"/></typeparam>
/// <param name="predicate">An <see cref="Action"/> to set the where clause for the select from the sql database</param>
/// <returns></returns>
public async override Task <DBSet <TModel> > Fetch <TModel>(Action <TModel> predicate = null)
{
try
{
return(await Task.Run(() =>
{
// Get the name of the database table
string table = (typeof(TModel).CustomAttributes.Count() > 0) ? (typeof(TModel).GetCustomAttributes(typeof(DBName), false).First() as DBName)?.Name : typeof(TModel).Name;
// Get the properties of the given object type
string columns = "";
List <string> dbcolumns = new List <string>();
var props = typeof(TModel).GetProperties();
foreach (var prop in props)
{
dbcolumns.Add((prop.CustomAttributes.Count() > 0) ? (prop.GetCustomAttributes(typeof(DBName), false).First() as DBName)?.Name : prop.Name);
}
var count = 0;
foreach (string col in dbcolumns)
{
columns += $"{col}";
count++;
if (count < dbcolumns.Count)
{
columns += ", ";
}
}
// Get the where state from the predicate if given
string where = null;
if (predicate != null)
{
TModel item = new TModel();
predicate(item);
foreach (var prop in item.GetType().GetProperties())
{
if (prop.GetValue(item) == null)
{
continue;
}
var value = prop.GetValue(item);
// If there is already a where clause
// Add a "AND"
if (where != null)
{
where += " AND ";
}
var propname = (prop.CustomAttributes.Count() > 0) ? (prop.GetCustomAttributes(typeof(DBName), false).First() as DBName)?.Name : prop.Name;
var propEquater = "";
switch (value.GetType().ToString())
{
case "System.Guid":
case "System.String":
propEquater = $"'{value}'";
break;
case "System.Boolean":
var v = (bool)value ? 1 : 0;
propEquater = $"{v}";
break;
default:
propEquater = $"{value}";
break;
}
where += $"{propname} = {propEquater}";
}
}
// Generate the query
string query = $"SELECT {columns} FROM {table} WHERE {where ?? "1 = 1"}";
DBSet <TModel> retval = new DBSet <TModel>();
using (SqlConnection connection = new SqlConnection(_ConnectionString))
using (SqlCommand command = new SqlCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = query;
command.Connection = connection;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TModel item = new TModel();
foreach (var prop in props)
{
string propname = (prop.CustomAttributes.Count() > 0) ? (prop.GetCustomAttributes(typeof(DBName), false).First() as DBName)?.Name : prop.Name;
var value = reader[$"{propname}"];
if (value.GetType() == typeof(string))
{
value = (value as string).Trim();
}
if (value.GetType() == typeof(DBNull))
{
value = null;
}
prop.SetValue(item, value);
}
// Add the item to the remote and local lists of the DBSet
retval.Add(item);
}
}
}
// Return the requested data
return retval;
}));
}
catch (Exception ex)
{
throw new DBActionProviderException("The action provide could not fetch any data", ex);
}
}
