public async Task <ICollection <T> > All <T>(string SqlWhere = null)
{
var list = new List <T>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
var queryString = MapTable.BuilderSelect <T>(SqlWhere);
SqlCommand command = new SqlCommand(queryString, connection)
{
CommandType = System.Data.CommandType.Text
};
command.Connection.Open();
using SqlDataReader dr = command.ExecuteReader();
while (await dr.ReadAsync())
{
var instance = Activator.CreateInstance(typeof(T));
this.fill(instance, dr);
list.Add((T)instance);
}
await dr.CloseAsync();
await dr.DisposeAsync();
}
return(list);
}
public async Task CreateTable <T>()
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.CreateTable <T>();
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
await command.ExecuteNonQueryAsync();
await connection.CloseAsync();
}
public async Task Delete <T>(T obj)
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.BuilderDelete(obj);
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
await command.ExecuteNonQueryAsync();
await connection.CloseAsync();
}
public async Task Update <T>(T obj)
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.BuilderUpdate(obj);
SqlCommand command = new SqlCommand(queryString, connection);
var parameters = MapTable.BuilderParameters(obj, true);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
command.Connection.Open();
await command.ExecuteNonQueryAsync();
await connection.CloseAsync();
}
public async Task Save <T>(T obj)
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.BuilderInsert(obj);
SqlCommand command = new SqlCommand(queryString, connection);
var parameters = MapTable.BuilderParameters(obj);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
command.Connection.Open();
MapTable.SetIdOfEntity(obj, await command.ExecuteScalarAsync());
await connection.CloseAsync();
}
public async Task <T> FindById <T>(int id)
{
using SqlConnection connection = new SqlConnection(connectionString);
var instance = MapTable.CreateInstanceAndSetId <T>(id);
var queryString = MapTable.BuildFindById <T>(id);
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
using SqlDataReader dr = command.ExecuteReader();
if (await dr.ReadAsync())
{
this.fill(instance, dr);
}
await dr.CloseAsync();
await dr.DisposeAsync();
return(instance);
}