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