public async Task <List <T> > GetAll()
{
var list = new List <T>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
var queryString = MapTable.BuilderSelect <T>();
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 Delete(T entity)
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.BuilderDelete(entity);
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
await command.ExecuteNonQueryAsync();
await connection.CloseAsync();
}
public async Task Add(T entity)
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.BuilderInsert(entity);
SqlCommand command = new SqlCommand(queryString, connection);
var parameters = MapTable.BuilderParameters(entity);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
command.Connection.Open();
MapTable.SetIdOfEntity(entity, await command.ExecuteScalarAsync());
await connection.CloseAsync();
}
public async Task Update(T entity)
{
using SqlConnection connection = new SqlConnection(connectionString);
var queryString = MapTable.BuilderUpdate(entity);
SqlCommand command = new SqlCommand(queryString, connection);
var parameters = MapTable.BuilderParameters(entity, true);
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
command.Connection.Open();
await command.ExecuteNonQueryAsync();
await connection.CloseAsync();
}
public async Task <T> FindById(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);
}
