// TypeA Junction table does not have updates. to update
// delete and re add
public List <TypeAWriting> TypeAWritingGetBooksRelatedToAuthorID(int authorID, int Skip, int Take)
{
List <TypeAWriting> actualrv = new List <TypeAWriting>();
List <Book> rv = new List <Book>();
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TypeAbooksGetRelatedToAuthorID";
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 = "@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
BookMapper mapper = new BookMapper(reader);
while (reader.Read())
{
Book a = mapper.ToBook(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);
}
}
}
}
AuthorDAL authordal = new AuthorDAL(_connection);
Author author = authordal.AuthorFindByID(authorID);
if (null == author)
{
throw new Exception($"cant find author with id of {authorID}");
}
foreach (Book b in rv)
{
TypeAWriting writing = new TypeAWriting();
writing.AuthorID = author.AuthorID;
writing.theAuthor = author;
writing.BookID = b.BookID;
writing.theBook = b;
actualrv.Add(writing);
}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
}
return(actualrv);
}
// BookGetSortedAndFiltered is used by advanced ajax portion of the
// datatables example. It is used to get the 'filtered' data for displaying
// on the datatables form
public List <Book> BookGetSortedAndFiltered(string sortColumn, string sortColumnDir, string searchValue, int skip, int take)
{
List <Book> rv = new List <Book>();
try
{
string direction;
string orderby;
string where;
bool wherethere = false;
// check the sort column to verify it is a valid column
// this is to avoid SQL injection
switch (sortColumn.ToLower())
{
case ("bookid"): orderby = "bookid"; break;
case ("isbn"): orderby = "ISBN"; break;
case ("bookname"): orderby = "bookname"; break;
case ("pages"): orderby = "pages"; break;
case ("daysoverdue"): orderby = "daysoverdue"; break;
case ("genrename"): orderby = "genrename"; break;
default: orderby = "bookname"; break;
}
if (string.IsNullOrWhiteSpace(searchValue))
{
where = "";
wherethere = false;
}
else
{
// where clause is not subject to SQL injection because the
// columns are compared to an @variable
// which will be loaded if wherethere is true
// wheredata is loaded from searchvalue
where = $" where ISBN like @wheredata or Bookname like @wheredata or GenreName like @wheredata ";
wherethere = true;
}
// check direction to make sure it is valid
// this is to avoid sql injection
switch (sortColumnDir.ToLower())
{
case ("asc"): direction = " ASC"; break;
default: direction = " DESC"; break;
}
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// use text to create dynamic query
command.CommandType = CommandType.Text;
command.CommandText = "select " + "bookid,isbn,bookname,pages,price,books.genreid," +
// from Books
"genrename," +
// from genres
"checkouts, daysoverdue, avgrating from books " +
//from inner query
//first left outer join bringing over the genrename or NULL if no match
"left outer join genres " +
"on books.genreid = genres.genreid " +
//-- second left outer join bringing over the checkout data etc.
" left outer join " +
//-- inner query follows
"(select bookid as bkid, " +
" count(*) as checkouts," +
" sum(DATEDIFF(day, DueDate, GetDate())) as daysoverdue, " +
" avg(Rating) AvgRating " +
" from TypeDRatedBorrowings " +
" where ReturnedDate is null " +
" group by bookid) as x " +
//--end of inner query(still need the on clause for the previous left outer join)
" on bkid = bookid " +
// -- the first two joins are now joined to the third one also on the bookid
// this is the where clause, it may not be present
// and then the parameter will not be added either
where +
" order by " + orderby + direction +
" offset @Skip rows fetch next @Take rows only";
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);
if (wherethere)
{
p = command.CreateParameter();
p.ParameterName = "@wheredata";
p.DbType = DbType.String;
p.Direction = ParameterDirection.Input;
p.Value = searchValue;
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
BookMapper mapper = new BookMapper(reader);
while (reader.Read())
{
Book a = mapper.ToBook(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"))
{
}
return(rv);
}
/// <summary>
/// locates an Book record by its primary id
/// </summary>
/// <param name="BookID">the primary key to the record to return</param>
/// <returns>an Book record</returns>
public Book BookFindByID(int BookID)
{
Book rv = null;
// a default return value
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "BookFindByID";
IDbDataParameter 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
BookMapper mapper = new BookMapper(reader);
int count = 0;
while (reader.Read())
{
rv = mapper.ToBook(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: {BookID}");
}
}
}
}
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);
}
/// <summary>
/// returns a list of Books, 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 <Book> BooksGetAll(int Skip = 0, int Take = 0)
{
List <Book> rv = new List <Book>();
// 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 = "BooksGetAll";
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
BookMapper mapper = new BookMapper(reader);
while (reader.Read())
{
Book a = mapper.ToBook(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);
}
