/// <summary>
/// locates an author record by its primary id
/// </summary>
/// <param name="AuthorID">the primary key to the record to return</param>
/// <returns>an author record</returns>
public Author AuthorFindByID(int AuthorID)
{
Author rv = null;
// a default return value
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "AuthorFindByID";
IDbDataParameter p = command.CreateParameter();
p.ParameterName = "@AuthorID";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.Value = AuthorID;
command.Parameters.Add(p);
// load the data from the database
using (IDataReader reader = command.ExecuteReader())
{
// the mapper is constructed, and this is where the shape
// is validated to insure it is correct
// if the database structure changes,
// an exception will be thrown
// the less efficient Get Ordinal methods are only
// invoked one time per command
// this less efficient (but required) code
// is outside the loop
AuthorMapper mapper = new AuthorMapper(reader);
int count = 0;
while (reader.Read())
{
rv = mapper.ToAuthor(reader);
// the mapper uses the much more efficient getXXX
// methods internally to avoid boxing and
// string manipulation. this more efficient code is
// inside the loop
count++;
}
// this method is expecting a single record to be returned
// check to see if more than one record was returned
// this probably means that a where clause is incorrect in the SQL layer
if (count > 1)
{
throw new Exception($"Multiple reccords found with id: {AuthorID}");
}
}
}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
// ALL exceptions are logged by the when clause
// and then the exception is tossed to the next layer up.
// the catch block is NEVER invoked because the
// when clause never evaluates to true since the
// Log method returns false
}
return(rv);
}
public List <TypeAWriting> TypeAWritingGetAuthorsRelatedToBookID(int bookID, int Skip, int Take)
{
List <TypeAWriting> actualrv = new List <TypeAWriting>();
List <Author> rv = new List <Author>();
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TypeAauthorsGetRelatedToBookID";
IDbDataParameter p = command.CreateParameter();
p.ParameterName = "@Skip";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.Value = Skip;
command.Parameters.Add(p);
// since p has been added to the parameters collection
// it is safe to reuse the same variable
// for a new parameter
p = command.CreateParameter();
p.ParameterName = "@Take";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.Value = Take;
command.Parameters.Add(p);
// since p has been added to the parameters collection
// it is safe to reuse the same variable
// for a new parameter
p = command.CreateParameter();
p.ParameterName = "@BookID";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.Value = bookID;
command.Parameters.Add(p);
// load the data from the database
using (IDataReader reader = command.ExecuteReader())
{
// the mapper is constructed, and this is where the shape
// is validated to insure it is correct
// if the database structure changes,
// an exception will be thrown
// the less efficient Get Ordinal methods are only
// invoked one time per command
// this less efficient (but required) code
// is outside the loop
AuthorMapper mapper = new AuthorMapper(reader);
while (reader.Read())
{
Author a = mapper.ToAuthor(reader);
// the mapper uses the much more efficient getXXX
// methods internally to avoid boxing and
// string manipulation. this more efficient code is
// inside the loop
if (a != null)
// if the mapper returns null for some reason it will
// be ignored
{
rv.Add(a);
}
}
}
}
BookDAL bookdal = new BookDAL(_connection);
Book book = bookdal.BookFindByID(bookID);
if (null == book)
{
throw new Exception($"cant find book with id of {bookID}");
}
foreach (Author a in rv)
{
TypeAWriting writing = new TypeAWriting();
writing.BookID = book.BookID;
writing.theBook = book;
writing.AuthorID = a.AuthorID;
writing.theAuthor = a;
actualrv.Add(writing);
}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
}
return(actualrv);
}
/// <summary>
/// returns a list of authors, optionally skipping several for paging
/// purposes and only returning the amount in the page
/// </summary>
/// <param name="Skip">the number of records to ignore</param>
/// <param name="Take">the number of records to return</param>
/// <returns></returns>
public List <Author> AuthorsGetAll(int Skip = 0, int Take = 0)
{
List <Author> rv = new List <Author>();
// a default return value is an empty list
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "AuthorsGetAll";
IDbDataParameter p = command.CreateParameter();
p.ParameterName = "@Skip";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.Value = Skip;
command.Parameters.Add(p);
// since p has been added to the parameters collection
// it is safe to reuse the same variable
// for a new parameter
p = command.CreateParameter();
p.ParameterName = "@Take";
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.Value = Take;
command.Parameters.Add(p);
// load the data from the database
using (IDataReader reader = command.ExecuteReader())
{
// the mapper is constructed, and this is where the shape
// is validated to insure it is correct
// if the database structure changes,
// an exception will be thrown
// the less efficient Get Ordinal methods are only
// invoked one time per command
// this less efficient (but required) code
// is outside the loop
AuthorMapper mapper = new AuthorMapper(reader);
while (reader.Read())
{
Author a = mapper.ToAuthor(reader);
// the mapper uses the much more efficient getXXX
// methods internally to avoid boxing and
// string manipulation. this more efficient code is
// inside the loop
if (a != null)
// if the mapper returns null for some reason it will
// be ignored
{
rv.Add(a);
}
}
// when the flow gets here, all the records have been processed
// this time there is not any post processing that needs to happen
}
}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
// ALL exceptions are logged by the when clause
// and then the exception is tossed to the next layer up.
// the catch block is NEVER invoked because the
// when clause never evaluates to true since the
// Log method returns false
}
return(rv);
}