/// <summary>
/// Method handling deletion of a row.
/// </summary>
/// <param name="dto"></param>
public void Delete(DalHelperModel dto)
{
try
{
using (var cmd = BuildCommand(dto))
{
if (dto.Parameters != null)
{
foreach (var parameter in dto.Parameters)
{
cmd.Parameters.Add(parameter);
}
}
dto.Connection.Open();
cmd.ExecuteNonQuery();
}
}
finally
{
if (dto.Connection.State == ConnectionState.Open)
{
dto.Connection.Close();
}
}
}
/// <summary>
/// Method to handle inserts
/// </summary>
/// <param name="dto"></param>
/// <returns>Nullable Int indicating the number of affected rows</returns>
public int?Insert(DalHelperModel dto)
{
try
{
using (var cmd = BuildCommand(dto))
{
if (dto.Parameters != null)
{
foreach (var parameter in dto.Parameters)
{
cmd.Parameters.Add(parameter);
}
}
dto.Connection.Open();
var result = cmd.ExecuteScalar();
int newId;
if (result != null && int.TryParse(result.ToString(), out newId))
{
return(newId);
}
}
}
finally
{
if (dto.Connection.State == ConnectionState.Open)
{
dto.Connection.Close();
}
}
return(null);
}
/// <summary>
/// Method handling the retrieval of 0 to many records based
/// on the parameters.
/// </summary>
/// <typeparam name="T">Type of the items in the IList returned</typeparam>
/// <param name="dto">Contains information needed to make the database call and a mapper
/// used to convert the sql data records to the list
/// </param>
/// <returns></returns>
public IList <T> RetrieveList <T>(DalHelperModel <T> dto)
{
var items = new List <T>();
try
{
using (var cmd = BuildCommand(dto))
{
if (dto.Parameters != null)
{
foreach (var parameter in dto.Parameters)
{
cmd.Parameters.Add(parameter);
}
}
dto.Connection.Open();
if (dto.Mapper != null)
{
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
items.Add(dto.Mapper(reader));
}
}
}
}
else
{
throw new Exception("No usable mapper was defined for the retrieve.");
}
}
}
finally
{
if (dto.Connection.State == ConnectionState.Open)
{
dto.Connection.Close();
}
}
return(items);
}
/// <summary>
/// Method to build the SqlCommand and configure it as a procedure call or command text.
/// </summary>
/// <param name="dto"></param>
/// <returns>SqlCommand with a configured command type</returns>
private SqlCommand BuildCommand(DalHelperModel dto)
{
if (!String.IsNullOrWhiteSpace(dto.CommandText))
{
return(new SqlCommand(dto.CommandText, dto.Connection)
{
CommandType = CommandType.Text
});
}
if (!String.IsNullOrWhiteSpace(dto.ProcName))
{
return(new SqlCommand(dto.ProcName, dto.Connection)
{
CommandType = CommandType.StoredProcedure
});
}
throw new Exception("No command text or stored procedure name found in arguments");
}
