//Method for Deleting an author
public bool DeleteAuthor(Authors authorToDelete)
{
bool success = false;
try
{
//Create a connection to the database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//Specify what type of command is being uesd
using (SqlCommand _command = new SqlCommand("sp_deleteAuthor", _connection))
{
//Specify what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//Where values arer going to be passed to the command
_command.Parameters.AddWithValue("@Author_ID", authorToDelete.Author_ID);
//Open Connection
_connection.Open();
//Execute command
_command.ExecuteNonQuery();
success = true;
_connection.Close();
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(success);
}
//Method for Viewing books
public List <Books> GetBooks()
{
//Create list variable named _booklist
List <Books> _booklist = new List <Books>();
try
{
//Create connection to database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//Specify what type of command object for the database
using (SqlCommand _command = new SqlCommand("sp_readBook", _connection))
{
//Connect to database
_command.CommandType = CommandType.StoredProcedure;
//Open connection
_connection.Open();
//Open SQLDataReader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop throug dataset & write each element to the _bookToList
//Use Books object class
while (_reader.Read())
{
Books _bookToList = new Books();
_bookToList.Book_ID = _reader.GetInt32(0);
_bookToList.Book_title = _reader.GetString(1);
_bookToList.Book_descript = _reader.GetString(2);
_bookToList.Book_price = _reader.GetDecimal(3);
_bookToList.IsPaperback = _reader.GetString(4);
_bookToList.Author_Name = _reader.GetString(5);
_bookToList.Genre_Name = _reader.GetString(6);
_booklist.Add(_bookToList);
}
}
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(_booklist);
}
public User Login(User userToLogin)
{
//Create user
User _userToList = new User();
try
{
//Create connection to database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//Specify what type of command object for the database
using (SqlCommand _command = new SqlCommand("sp_login", _connection))
{
//Connect to database
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@userName", _userToList.username);
_command.Parameters.AddWithValue("@password", _userToList.password);
//Open connection
_connection.Open();
//Open SQLDataReader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through dataset & write each element to the _genreToList
//Use Genre object class
while (_reader.Read())
{
_userToList.user_ID = _reader.GetInt32(0);
_userToList.username = _reader.GetString(1);
_userToList.password = _reader.GetString(2);
_userToList.role_ID = _reader.GetInt32(3);
}
}
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(_userToList);
}
//Method for Viewing an author
public List <Authors> GetAuthors()
{
//Create list variable named _genrelist
List <Authors> _authorlist = new List <Authors>();
try
{
//Create connection to database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//Specify what type of command object for the database
using (SqlCommand _command = new SqlCommand("sp_readAuthor", _connection))
{
//Connect to database
_command.CommandType = CommandType.StoredProcedure;
//Open connection
_connection.Open();
//Open SQLDataReader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through dataset & write each element to the _genrelist
//Use Genre object class
while (_reader.Read())
{
Authors _authorToList = new Authors();
_authorToList.Author_ID = _reader.GetInt32(0);
_authorToList.Author_Name = _reader.GetString(1);
_authorToList.Author_Bio = _reader.GetString(2);
_authorToList.Author_BirthLoc = _reader.GetString(3);
_authorToList.Author_DOB = _reader.GetDateTime(4);
_authorlist.Add(_authorToList);
}
}
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(_authorlist);
}
//Method for Viewing genres
public List <Genres> GetGenres()
{
//Create list variable named _genrelist
List <Genres> _genrelist = new List <Genres>();
try
{
//Create connection to database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//Specify what type of command object for the database
using (SqlCommand _command = new SqlCommand("sp_readGenre", _connection))
{
//Connect to database
_command.CommandType = CommandType.StoredProcedure;
//Open connection
_connection.Open();
//Open SQLDataReader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through dataset & write each element to the _genreToList
//Use Genre object class
while (_reader.Read())
{
Genres _genreToList = new Genres();
_genreToList.Genre_ID = _reader.GetInt32(0);
_genreToList.Genre_descript = _reader.GetString(1);
_genreToList.Genre_Name = _reader.GetString(2);
_genreToList.IsFiction = _reader.GetBoolean(3);
_genrelist.Add(_genreToList);
}
}
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(_genrelist);
}
//Method for Updating books
public bool UpdateBook(Books bookToUpdate)
{
bool success = false;
try
{
//Create a connection to the database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//Specify what command is being used
using (SqlCommand _command = new SqlCommand("sp_updateBook", _connection))
{
//Specify what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//Where values are going to be passed to the command
_command.Parameters.AddWithValue("@Book_ID", bookToUpdate.Book_ID);
_command.Parameters.AddWithValue("@Book_title", bookToUpdate.Book_title);
_command.Parameters.AddWithValue("@Book_descript", bookToUpdate.Book_descript);
_command.Parameters.AddWithValue("@Book_price", bookToUpdate.Book_price);
_command.Parameters.AddWithValue("@IsPaperback", bookToUpdate.IsPaperback);
_command.Parameters.AddWithValue("@Author_ID", bookToUpdate.Author_ID);
_command.Parameters.AddWithValue("@Genre_ID", bookToUpdate.Genre_ID);
//Open connection
_connection.Open();
//Execute command
_command.ExecuteNonQuery();
success = true;
_connection.Close();
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(success);
}
//Method for Creating books
public bool NewBook(Books bookToAdd)
{
bool success = false;
{
try
{
//connection to database
using (SqlConnection _newbook = new SqlConnection(connectionstring))
{
//command object to database
using (SqlCommand _command = new SqlCommand("sp_createBook", _newbook))
{
//What command type is being used
_command.CommandType = CommandType.StoredProcedure;
//Values being called from the database
_command.Parameters.AddWithValue("@Book_title", bookToAdd.Book_title);
_command.Parameters.AddWithValue("@Book_descript", bookToAdd.Book_descript);
_command.Parameters.AddWithValue("@Book_price", bookToAdd.Book_price);
_command.Parameters.AddWithValue("@IsPaperback", bookToAdd.IsPaperback);
_command.Parameters.AddWithValue("@Author_ID", bookToAdd.Author_ID);
_command.Parameters.AddWithValue("@Genre_ID", bookToAdd.Genre_ID);
//Open newbook
_newbook.Open();
//Execute command
_command.ExecuteNonQuery();
success = true;
_newbook.Close();
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(success);
}
}
//Method for Updating an author
public bool UpdateAuthor(Authors authorToUpdate)
{
bool success = false;
{
try
{
//connection to database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//command object to database
using (SqlCommand _command = new SqlCommand("sp_updateAuthor", _connection))
{
//What command type is being used
_command.CommandType = CommandType.StoredProcedure;
//Values being called from the database
_command.Parameters.AddWithValue("@Author_ID", authorToUpdate.Author_ID);
_command.Parameters.AddWithValue("@Author_Name", authorToUpdate.Author_Name);
_command.Parameters.AddWithValue("@Author_Bio", authorToUpdate.Author_Bio);
_command.Parameters.AddWithValue("@Author_BirthLoc", authorToUpdate.Author_BirthLoc);
_command.Parameters.AddWithValue("@Author_DOB", authorToUpdate.Author_DOB);
//Open connection
_connection.Open();
//Execute command
_command.ExecuteNonQuery();
success = true;
_connection.Close();
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(success);
}
}
//Method for Creating a genre
public bool NewGenre(Genres genreToAdd)
{
bool success = false;
{
try
{
//connection to database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//command object to database
using (SqlCommand _command = new SqlCommand("sp_createGenre", _connection))
{
//What command type is being used
_command.CommandType = CommandType.StoredProcedure;
//Values being called from the database
_command.Parameters.AddWithValue("@Genre_Name", genreToAdd.Genre_Name);
_command.Parameters.AddWithValue("@Genre_descript", genreToAdd.Genre_descript);
_command.Parameters.AddWithValue("@IsFiction", genreToAdd.IsFiction);
//Open connection
_connection.Open();
//Execute command
_command.ExecuteNonQuery();
success = true;
_connection.Close();
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog & name it log
DAL.ErrorLogger log = new DAL.ErrorLogger();
//Call the log error
log.LogError(error);
}
return(success);
}
}
