// 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(); }