public void TestAddNewBorrowerToDB() { Borrower newBorrower = new Borrower() { IsOwner = false, FirstName = "Pizza", LastName = "King", Email = "*****@*****.**", Address = "1234 Pizza Blvd", City = "New York City", State = "NY", Zipcode = "10001", Phone = "855-911-1337" }; var result = repo.AddNewBorrowerToDB(newBorrower).BorrowerId; Assert.AreEqual(result, 10); }
//Add a New Borrower to DB public Borrower AddNewBorrowerToDB(Borrower newBorrower) { using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { var p = new DynamicParameters(); p.Add("IsOwner", newBorrower.IsOwner); p.Add("FirstName", newBorrower.FirstName); p.Add("LastName", newBorrower.LastName); p.Add("Email", newBorrower.Email); p.Add("StreetAddress", newBorrower.Address); p.Add("City", newBorrower.City); p.Add("State", newBorrower.State); p.Add("Zipcode", newBorrower.Zipcode); p.Add("Phone", newBorrower.Phone); p.Add("BorrowerID", DbType.Int32, direction: ParameterDirection.Output); cn.Execute("AddNewBorrowerToBorrowers", p, commandType: CommandType.StoredProcedure); newBorrower.BorrowerId = p.Get<int>("BorrowerID"); } return newBorrower; }
//Retrieve Full Borrowers List with All DVD Rental Statuses in there public List<Borrower> RetrieveFullBorrowersList() { List<Borrower> listOfBorrowers = new List<Borrower>(); using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select FirstName, LastName, BorrowerID, IsOwner, Email, StreetAddress, City," + " State, Zipcode, Phone from Borrowers"; cmd.Connection = cn; cn.Open(); //Error connection not establised using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { var newBorrower = new Borrower(); newBorrower.FirstName = dr["FirstName"].ToString(); newBorrower.LastName = dr["LastName"].ToString(); newBorrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); newBorrower.IsOwner = bool.Parse(dr["IsOwner"].ToString()); newBorrower.Email = dr["Email"].ToString(); newBorrower.Address = dr["StreetAddress"].ToString(); newBorrower.City = dr["City"].ToString(); newBorrower.State = dr["State"].ToString(); newBorrower.Zipcode = dr["Zipcode"].ToString(); newBorrower.Phone = dr["Phone"].ToString(); listOfBorrowers.Add(newBorrower); } } cn.Close(); //Add all BorrowerStatuses to each User foreach (var b in listOfBorrowers) { cmd.CommandText = ("select bs.BorrowerStatusID, bs.BorrowerID, bs.DVDID, bs.DateBorrowed, bs.DateReturned," + " b.FirstName, b.LastName, m.MovieID, m.MovieTitle" + " from BorrowerStatuses bs" + " LEFT JOIN Borrowers b on bs.BorrowerID = b.BorrowerID" + " LEFT JOIN DVDs d on bs.DVDID=d.DVDID" + " LEFT JOIN Movies m on d.MovieID=m.MovieID" + " WHERE bs.BorrowerID = @BorrowerID"); cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@BorrowerID", b.BorrowerId); cmd.Connection = cn; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { var newStatus = new Status(); newStatus.StatusId = int.Parse(dr["BorrowerStatusID"].ToString()); newStatus.Borrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); newStatus.Borrower.FirstName = dr["FirstName"].ToString(); newStatus.Borrower.LastName = dr["LastName"].ToString(); newStatus.DVDId = int.Parse(dr["DVDID"].ToString()); newStatus.DateBorrowed = DateTime.Parse(dr["DateBorrowed"].ToString()); if (dr["DateReturned"] != DBNull.Value) { newStatus.DateReturned = DateTime.Parse(dr["DateReturned"].ToString()); } newStatus.MovieId = int.Parse(dr["MovieID"].ToString()); newStatus.MovieTitle = dr["MovieTitle"].ToString(); b.ListOfStatuses.Add(newStatus); } } b.MoviesCheckedOut = b.ListOfStatuses.Count(s => s.DateReturned == null); cn.Close(); } } return listOfBorrowers; }
//Return a list of Borrowers Names in the DB and their BorrowerID (smaller scale for select list item) public List<Borrower> RetrieveSmallListOfBorrowers() { List<Borrower> listOfBorrowers = new List<Borrower>(); using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { SqlCommand cmd = new SqlCommand(); //Get Owner Name first!! cmd.CommandText = "select FirstName, LastName, BorrowerID, IsOwner from Borrowers " + "where IsOwner = 1"; cmd.Connection = cn; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { var newBorrower = new Borrower(); newBorrower.FirstName = dr["FirstName"].ToString(); newBorrower.LastName = dr["LastName"].ToString(); newBorrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); newBorrower.IsOwner = bool.Parse(dr["IsOwner"].ToString()); listOfBorrowers.Add(newBorrower); } } cn.Close(); //Get Rest of Users and order by First Name List<Borrower> nonOwnerBorrowers = new List<Borrower>(); cmd.CommandText = "select FirstName, LastName, BorrowerID, IsOwner from Borrowers " + "where IsOwner = 0"; cmd.Connection = cn; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { var newBorrower = new Borrower(); newBorrower.FirstName = dr["FirstName"].ToString(); newBorrower.LastName = dr["LastName"].ToString(); newBorrower.BorrowerId = int.Parse(dr["BorrowerID"].ToString()); newBorrower.IsOwner = bool.Parse(dr["IsOwner"].ToString()); nonOwnerBorrowers.Add(newBorrower); } } cn.Close(); var ordered = nonOwnerBorrowers.OrderBy(b => b.FirstName); listOfBorrowers.AddRange(ordered); } return listOfBorrowers; }
public Status() { Borrower = new Borrower(); }
public AddBorrowerVM() { Borrower = new Borrower(); ListOfStates = new List<SelectListItem>() { new SelectListItem() { Text = "AK", Value = "AK" }, new SelectListItem() { Text = "AL", Value = "AL" }, new SelectListItem() { Text = "AR", Value = "AR" }, new SelectListItem() { Text = "AZ", Value = "AZ" }, new SelectListItem() { Text = "CA", Value = "CA" }, new SelectListItem() { Text = "CO", Value = "CO" }, new SelectListItem() { Text = "CT", Value = "CT" }, new SelectListItem() { Text = "DC", Value = "DC" }, new SelectListItem() { Text = "DE", Value = "DE" }, new SelectListItem() { Text = "FL", Value = "FL" }, new SelectListItem() { Text = "GA", Value = "GA" }, new SelectListItem() { Text = "HI", Value = "HI" }, new SelectListItem() { Text = "IA", Value = "IA" }, new SelectListItem() { Text = "ID", Value = "ID" }, new SelectListItem() { Text = "IL", Value = "IL" }, new SelectListItem() { Text = "IN", Value = "IN" }, new SelectListItem() { Text = "KS", Value = "KS" }, new SelectListItem() { Text = "KY", Value = "KY" }, new SelectListItem() { Text = "LA", Value = "LA" }, new SelectListItem() { Text = "MA", Value = "MA" }, new SelectListItem() { Text = "MD", Value = "MD" }, new SelectListItem() { Text = "ME", Value = "ME" }, new SelectListItem() { Text = "MI", Value = "MI" }, new SelectListItem() { Text = "MN", Value = "MN" }, new SelectListItem() { Text = "MO", Value = "MO" }, new SelectListItem() { Text = "MS", Value = "MS" }, new SelectListItem() { Text = "MT", Value = "MT" }, new SelectListItem() { Text = "NC", Value = "NC" }, new SelectListItem() { Text = "ND", Value = "ND" }, new SelectListItem() { Text = "NE", Value = "NE" }, new SelectListItem() { Text = "NY", Value = "NY" }, new SelectListItem() { Text = "OH", Value = "OH" }, new SelectListItem() { Text = "OK", Value = "OK" }, new SelectListItem() { Text = "OR", Value = "OR" }, new SelectListItem() { Text = "PA", Value = "PA" }, new SelectListItem() { Text = "RI", Value = "RI" }, new SelectListItem() { Text = "SC", Value = "SC" }, new SelectListItem() { Text = "SD", Value = "SD" }, new SelectListItem() { Text = "TN", Value = "TN" }, new SelectListItem() { Text = "TX", Value = "TX" }, new SelectListItem() { Text = "UT", Value = "UT" }, new SelectListItem() { Text = "VA", Value = "VA" }, new SelectListItem() { Text = "VT", Value = "VT" }, new SelectListItem() { Text = "WA", Value = "WA" }, new SelectListItem() { Text = "WI", Value = "WI" }, new SelectListItem() { Text = "WV", Value = "WV" }, new SelectListItem() { Text = "WY", Value = "WY" }, }; }
public RentalInfo() { Movie = new MovieInfo(); Borrower = new Borrower(); }