// this class wraps a (Type-D) many to many junction table
// connecting books and borrowers
// a book can be borrowed by many users
// a user can borrow many books
// Type-d : duplication
// a single book can be borrowed multiple times by the same user
// Type-d : extra data
// The Due Date, Returned Date, and Rating is different for
// each borrowing. This extra data is not stored with the user
// or the book, it is stored in the junction record.
// in addition, this is the ViewModel class, so here is the info about that
// This class is a simple shim around an embedded common library class
// This class may have PopulateXXXItems and XXXItems classes which are
// helper functions to make it easy to work with drop down list box controls
// containing friendly descriptions for Foreign Key IDs
// This class does contain the following additional helper functions:
// MakeNew -- acts as a special constructor for the viewModel layer. The
// Lower Layers may return NULL if the do not find an expected record
// the MakeNew method will pass this null on instead of createing an instance
// of the ViewModel Object. A typical constructor would create a real VM
// object that was wrapping null, it would not return null as the MakeNew
// method does.
// ToList -- helps translate a List<XXX> to a List<VMXXX>. Most of the
// lowerlevel methods return a List<XXX> instead of a List<ViewModelXXX>
// implicit operator XXX(VMXXX) -- this helps convert VMXXX to XXX whenever and
// whereever needed. The conversion is simple, return the embedded object!
// Two Constructors -- to allow the embedded items to be inserted as needed
//
//
// This ViewModel class is intended to be used at the MVC layer without requiring
// a reimplementation of the base XXX. This class allows the properties to be
// decorated with data annotations to be recognized at the MVC layer, and also
// allows additional MVC functionality to be inserted if required. Inheritance
// from the common library class does not support an easy way to do data
// annotation, and this embedding mechanism avoids copying/remapping
// the data between layers
//
// it is possible that a Generic Base Class can be devised to simplify this
// model
// -- followup: After expirimenting with the generic behavior, I was able
// to make a generic implementation, but I do not feel that it is
// understandable or explainable to entry level programmers.
// The code is in the class VMBase if you want to see it.
public static VMBorrowing MakeNew(TypeDRatedBorrowing theList)
{
if (theList == null)
{
return(null);
}
var rv = new VMBorrowing(theList);
return(rv);
}
public VMBorrowing(Book theBook, Borrower theBorrower)
{
TheEmbeddedItem = new TypeDRatedBorrowing();
TheEmbeddedItem.BookID = theBook.BookID;
TheEmbeddedItem.BookName = theBook.BookName;
TheEmbeddedItem.BorrowerDOB = theBorrower.BorrowerDOB;
TheEmbeddedItem.BorrowerEMail = theBorrower.BorrowerEMail;
TheEmbeddedItem.BorrowerID = theBorrower.BorrowerID;
TheEmbeddedItem.BorrowerName = theBorrower.BorrowerName;
TheEmbeddedItem.GenreID = theBook.GenreID;
TheEmbeddedItem.GenreName = theBook.GenreName;
TheEmbeddedItem.ISBN = theBook.ISBN;
TheEmbeddedItem.Pages = theBook.Pages;
TheEmbeddedItem.Price = theBook.Price;
TheEmbeddedItem.RoleID = theBorrower.RoleID;
TheEmbeddedItem.RoleName = theBorrower.RoleName;
}
public TypeDRatedBorrowing TypeDRatedBorrowingFindByID(int BorrowingID)
{
TypeDRatedBorrowing rv = null;
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TypeDRatedBorrowingFindByID";
IDbDataParameter p = command.CreateParameter();
p.DbType = DbType.Int32;
p.Direction = ParameterDirection.Input;
p.ParameterName = "@BorrowingID";
p.Value = BorrowingID;
command.Parameters.Add(p);
using (IDataReader reader = command.ExecuteReader())
{
int count = 0;
TypeDRatedBorrowingMapper m = new TypeDRatedBorrowingMapper(reader);
while (reader.Read())
{
rv = m.ToBorrowing(reader);
count++;
}
if (count > 1)
{
throw new Exception($"Multiple reccords found with id: {BorrowingID}");
}
}
}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
}
return(rv);
}
public ActionResult ForceReturn(TypeDRatedBorrowing item)
{
try
{
// context on outside because both sides of modelstate
// need context (then needs to update, and else needs to repopulate)
using (Context ctx = new Context())
{
if (ModelState.IsValid)
{
ctx.RatedBorrowingUpdateJust(item);
return(RedirectToAction("Index"));
}
else
{
return(View(item));
}
}
}
catch (Exception ex)
{
return(View("Exception", ex));
}
}
public int RatedBorrowingUpdateJust(int BorrowingID, TypeDRatedBorrowing borrowing)
{
return(RatedBorrowingUpdateJust(BorrowingID, borrowing.BookID, borrowing.BorrowerID, borrowing.DueDate, borrowing.ReturnedDate, borrowing.Rating));
}
public int RatedBorrowingUpdateJust(TypeDRatedBorrowing item)
{
return(RatedBorrowingUpdateJust(item.BorrowingID, item.BookID, item.BorrowerID, item.DueDate, item.ReturnedDate, item.Rating));
}
public int RatedBorrowingCreate(TypeDRatedBorrowing borrowing)
{
return(RatedBorrowingCreate(borrowing.BookID, borrowing.BorrowerID, borrowing.DueDate, borrowing.ReturnedDate, borrowing.Rating));
}
public List <TypeDRatedBorrowing> TypeDRatedBorrowingGetBorrowersRelatedToBookID(int bookID, int Skip, int Take)
{
List <TypeDRatedBorrowing> actualrv = new List <TypeDRatedBorrowing>();
// List<Borrower> rv = new List<Borrower>();
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TypeDRatedBorrowingsGetRelatedToBookID";
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
TypeDRatedBorrowingMapper mapper = new TypeDRatedBorrowingMapper(reader);
while (reader.Read())
{
TypeDRatedBorrowing a = mapper.ToBorrowing(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
{
actualrv.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 (Borrower a in rv)
//{
// TypeDRatedBorrowing writing = new TypeDRatedBorrowing();
// writing.BookID = book.BookID;
// writing.theBook = book;
// writing.BorrowerID = a.BorrowerID;
// writing.theBorrower = a;
// actualrv.Add(writing);
//}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
}
return(actualrv);
}
public TypeDRatedBorrowing ToBorrowing(IDataReader reader)
{
TypeDRatedBorrowing rv = new TypeDRatedBorrowing();
// the values of the GetXXX have been validated by the constructor
rv.BorrowingID = reader.GetInt32(0);
rv.BookID = reader.GetInt32(1);
rv.BorrowerID = reader.GetInt32(2);
if (reader.IsDBNull(3))
{
rv.DueDate = null;
}
else
{
rv.DueDate = reader.GetDateTime(3);
}
if (reader.IsDBNull(4))
{
rv.ReturnedDate = null;
}
else
{
rv.ReturnedDate = reader.GetDateTime(4);
}
if (reader.IsDBNull(5))
{
rv.Rating = 0;
}
else
{
rv.Rating = reader.GetDecimal(5);
}
if (reader.IsDBNull(6))
{
rv.ISBN = "";
}
else
{
rv.ISBN = reader.GetString(6);
}
if (reader.IsDBNull(7))
{
rv.BookName = "";
}
else
{
rv.BookName = reader.GetString(7);
}
if (reader.IsDBNull(8))
{
rv.Pages = 0;
}
else
{
rv.Pages = reader.GetInt32(8);
}
if (reader.IsDBNull(9))
{
rv.Price = 0M;
}
else
{
rv.Price = reader.GetDecimal(9);
}
rv.GenreID = reader.GetInt32(10);
if (reader.IsDBNull(11))
{
rv.GenreName = "";
}
else
{
rv.GenreName = reader.GetString(11);
}
if (reader.IsDBNull(12))
{
rv.BorrowerName = "";
}
else
{
rv.BorrowerName = reader.GetString(12);
}
if (reader.IsDBNull(13))
{
rv.BorrowerEMail = "";
}
else
{
rv.BorrowerEMail = reader.GetString(13);
}
if (reader.IsDBNull(14))
{
rv.BorrowerDOB = null;
}
else
{
rv.BorrowerDOB = reader.GetDateTime(14);
}
rv.RoleID = reader.GetInt32(15);
if (reader.IsDBNull(16))
{
rv.RoleName = "";
}
else
{
rv.RoleName = reader.GetString(16);
}
return(rv);
}
public List <TypeDRatedBorrowing> TypeDRatedBorrowingsGetAll(int Skip = 0, int Take = 0)
{
List <TypeDRatedBorrowing> rv = new List <TypeDRatedBorrowing>();
try
{
EnsureConnected();
using (IDbCommand command = _connection.CreateCommand())
{
// configure the command object
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "TypeDRatedBorrowingsGetAll";
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
TypeDRatedBorrowingMapper mapper = new TypeDRatedBorrowingMapper(reader);
while (reader.Read())
{
TypeDRatedBorrowing a = mapper.ToBorrowing(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);
}
}
}
}
foreach (TypeDRatedBorrowing writing in rv)
{
// the integer ids for book and author are already loaded from SQL
// this procedure now loads the book and author data from
// SQL
// this part of this method can be optimized
// it currently is making several round trips to the server
// optimization could be achieved by recreating the
// stored procedure to return multiple record sets
// and refactor this routine to read all the record sets
// I envision that the stored procedure would return all
// the junction items in the first set, then all the
// matching authors in the second set, then all the
// matching books.
// processing would involve calling reader.read until
// it fails
// then call reader.nextresult to get the next result set
// (the authors) then call reader.nextresult to get
// the books
// the advantage of this is that it could be done in a single round trip to the server
// i leave this refactoring as an exercise for a future
// developer
}
}
catch (Exception ex) when(Logger.Log(ex, "DAL"))
{
}
return(rv);
}
public VMBorrowing(TypeDRatedBorrowing b)
{
TheEmbeddedItem = b;
}
public VMBorrowing()
{
TheEmbeddedItem = new TypeDRatedBorrowing();
}
