public virtual async Task <ICollection <TEntity> > FindAll()
{
try
{
if (dbc.Connection.State != ConnectionState.Open)
{
await dbc.Open();
}
using (SqlCommand cmd = new SqlCommand(CMDText, dbc.Connection))
{
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
ICollection <TEntity> entities = new List <TEntity>();
while (await reader.ReadAsync())
{
entities.Add(MapToObject.Execute(reader));
}
logger.LogInformation($"FindAll complete for {typeof(TEntity)} entity.");
return(entities);
}
}
}
catch (SqlException ex)
{
logger.LogError(ex.Message);
return(null);
}
}
public virtual async Task <ICollection <TEntity> > FindAllPaged(int offset, int pageSize)
{
try
{
if (dbc.Connection.State != ConnectionState.Open)
{
await dbc.Open();
}
using (SqlCommand cmd = new SqlCommand(CMDText, dbc.Connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@p1", offset));
cmd.Parameters.Add(new SqlParameter("@p2", pageSize));
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
ICollection <TEntity> entities = new List <TEntity>();
while (await reader.ReadAsync())
{
entities.Add(MapToObject.Execute(reader));
}
logger.LogInformation($"FindAllPaged complete for {typeof(TEntity)} entity.");
return(entities);
}
}
}
catch (SqlException ex)
{
logger.LogError(ex.Message);
return(null);
}
}
public virtual async Task <TEntity> FindByPK(IPrimaryKey pk)
{
int idx = 1;
TEntity entity = null;
try
{
if (dbc.Connection.State != ConnectionState.Open)
{
await dbc.Open();
}
using (SqlCommand cmd = new SqlCommand(CMDText, dbc.Connection))
{
if (SqlCommandType == Constants.DBCommandType.SPROC)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (pk.IsComposite)
{
foreach (int k in ((PrimaryKey)pk).CompositeKey)
{
cmd.Parameters.Add(new SqlParameter("@pk" + idx.ToString(), k));
idx++;
}
}
else
{
cmd.Parameters.Add(new SqlParameter("@pk", pk.Key));
}
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
if (reader.Read())
{
entity = MapToObject.Execute(reader);
}
else
{
entity = null;
}
logger.LogInformation($"FindByPK complete for {typeof(TEntity)} entity.");
}
}
}
catch (SqlException ex)
{
logger.LogError(ex.Message);
return(null);
}
return(entity);
}
public virtual TEntity FindByPK(IPrimaryKey pk)
{
int idx = 1;
TEntity entity = null;
try
{
if (dbc.Connection.State != ConnectionState.Open)
{
dbc.Open();
}
using (SqliteCommand cmd = new SqliteCommand(CMDText, dbc.Connection))
{
if (pk.IsComposite)
{
foreach (int k in ((PrimaryKey)pk).CompositeKey)
{
cmd.Parameters.Add(new SqliteParameter("@pk" + idx.ToString(), k));
idx++;
}
}
else
{
cmd.Parameters.Add(new SqliteParameter("@pk", pk.Key));
}
using (SqliteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
entity = MapToObject.Execute(reader);
}
else
{
entity = null;
}
logger.LogInformation($"FindByPK complete for {typeof(TEntity)} entity.");
}
}
}
catch (SqliteException ex)
{
logger.LogError(ex.Message);
return(null);
}
return(entity);
}
public virtual async Task <ICollection <TEntity> > FindAll(IList <SqlParameter> parms)
{
ICollection <TEntity> entities;
try
{
if (dbc.Connection.State != ConnectionState.Open)
{
await dbc.Open();
}
using (SqlCommand cmd = new SqlCommand(CMDText, dbc.Connection))
{
if (SqlCommandType == Constants.DBCommandType.SPROC)
{
cmd.CommandType = CommandType.StoredProcedure;
}
foreach (SqlParameter parm in parms)
{
cmd.Parameters.Add(parm);
}
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
entities = new List <TEntity>();
while (await reader.ReadAsync())
{
entities.Add(MapToObject.Execute(reader));
}
logger.LogInformation($"FindAll(SqlParameter) complete for {typeof(TEntity)} entity.");
return(entities);
}
}
}
catch (SqlException ex)
{
logger.LogError(ex.Message);
return(null);
}
}
public virtual async Task <TEntity> FindByPK(IPrimaryKey pk)
{
TEntity entity = null;
try
{
if (dbc.Connection.State != ConnectionState.Open)
{
await dbc.Open();
}
using (SqlCommand cmd = new SqlCommand(CMDText, dbc.Connection))
{
cmd.Parameters.Add(new SqlParameter("@pk", pk.Key));
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
if (reader.Read())
{
entity = MapToObject.Execute(reader);
}
else
{
entity = null;
}
logger.LogInformation($"FindByPK complete for {typeof(TEntity)} entity.");
}
}
}
catch (SqlException ex)
{
logger.LogError(ex.Message);
return(null);
}
return(entity);
}