/// <summary> /// locates an author record by its primary id /// </summary> /// <param name="AuthorID">the primary key to the record to return</param> /// <returns>an author record</returns> public Author AuthorFindByID(int AuthorID) { Author rv = null; // a default return value try { EnsureConnected(); using (IDbCommand command = _connection.CreateCommand()) { // configure the command object command.CommandType = CommandType.StoredProcedure; command.CommandText = "AuthorFindByID"; IDbDataParameter 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 AuthorMapper mapper = new AuthorMapper(reader); int count = 0; while (reader.Read()) { rv = mapper.ToAuthor(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: {AuthorID}"); } } } } 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); }
public List <TypeAWriting> TypeAWritingGetAuthorsRelatedToBookID(int bookID, int Skip, int Take) { List <TypeAWriting> actualrv = new List <TypeAWriting>(); List <Author> rv = new List <Author>(); try { EnsureConnected(); using (IDbCommand command = _connection.CreateCommand()) { // configure the command object command.CommandType = CommandType.StoredProcedure; command.CommandText = "TypeAauthorsGetRelatedToBookID"; 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 AuthorMapper mapper = new AuthorMapper(reader); while (reader.Read()) { Author a = mapper.ToAuthor(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); } } } } BookDAL bookdal = new BookDAL(_connection); Book book = bookdal.BookFindByID(bookID); if (null == book) { throw new Exception($"cant find book with id of {bookID}"); } foreach (Author a in rv) { TypeAWriting writing = new TypeAWriting(); writing.BookID = book.BookID; writing.theBook = book; writing.AuthorID = a.AuthorID; writing.theAuthor = a; actualrv.Add(writing); } } catch (Exception ex) when(Logger.Log(ex, "DAL")) { } return(actualrv); }
/// <summary> /// returns a list of authors, 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 <Author> AuthorsGetAll(int Skip = 0, int Take = 0) { List <Author> rv = new List <Author>(); // 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 = "AuthorsGetAll"; 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 AuthorMapper mapper = new AuthorMapper(reader); while (reader.Read()) { Author a = mapper.ToAuthor(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); }