public static bool UpdatePublisher(Publisher publisher)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_update_publisher";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@publisherID", publisher.PublisherID);
cmd.Parameters.AddWithValue("@phone", publisher.Phone);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool UpdateReview(Review review)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_update_review";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@rentalID", review.RentalID);
cmd.Parameters.AddWithValue("@bookID", review.ISBN);
cmd.Parameters.AddWithValue("@content", review.Content);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool InactivateAuthor(Author author, bool toRestore)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_delete_author";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@authorID", author.AuthorID);
cmd.Parameters.AddWithValue("@active", toRestore);
try
{
conn.Open();
if (cmd.ExecuteNonQuery() == 1)
{
inserted = true;
}
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool UpdateAuthor(Author author)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_update_author";
var cmd = new SqlCommand(query, conn);
Console.WriteLine(author.FirstName + author.LastName + author.AuthorID);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstName", author.FirstName);
cmd.Parameters.AddWithValue("@lastName", author.LastName);
cmd.Parameters.AddWithValue("@authorID", author.AuthorID);
try
{
conn.Open();
Console.WriteLine("it is " + cmd.ExecuteNonQuery());
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool InsertOrder(int customerID, string isbn, int quantity)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_insert_order";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerID", customerID);
cmd.Parameters.AddWithValue("@bookID", isbn);
cmd.Parameters.AddWithValue("@quantity", quantity);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool UpdateCustomer(Customer customer)
{
bool updated = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_update_customer";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstName", customer.FirstName);
cmd.Parameters.AddWithValue("@lastName", customer.LastName);
cmd.Parameters.AddWithValue("@email", customer.Email);
cmd.Parameters.AddWithValue("@phone", customer.Phone);
cmd.Parameters.AddWithValue("@username", customer.UserName);
cmd.Parameters.AddWithValue("@active", customer.Active);
try
{
conn.Open();
cmd.ExecuteNonQuery();
updated = true;
}
catch (Exception)
{
throw;
}
return(updated);
}
public static int FindUserByUsernameAndPassword(string username, string password)
{
int count = 0;
var conn = DBConnection.GetDBConnection();
var query = @"sp_validate_customer_username_and_password";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@password", password);
try
{
conn.Open();
count = (int)cmd.ExecuteScalar();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(count);
}
public static bool InsertCustomer(Customer customer)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_insert_customer";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstName", customer.FirstName);
cmd.Parameters.AddWithValue("@lastName", customer.LastName);
cmd.Parameters.AddWithValue("@email", customer.Email);
cmd.Parameters.AddWithValue("@phone", customer.Phone);
cmd.Parameters.AddWithValue("@username", customer.UserName);
cmd.Parameters.AddWithValue("@password", customer.Password);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool UpdateBook(Book book)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_update_book";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bookID", book.ISBN);
cmd.Parameters.AddWithValue("@publisherID", book.Publisher);
cmd.Parameters.AddWithValue("@authorID", book.AuthorID);
cmd.Parameters.AddWithValue("@title", book.Title);
cmd.Parameters.AddWithValue("@pages", book.Pages);
cmd.Parameters.AddWithValue("@copies", book.Copies);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool InsertRental(Rental rental, string isbn)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_insert_rental_with_book";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerID", rental.CustomerID);
cmd.Parameters.AddWithValue("@dateRented", rental.DateRented);
cmd.Parameters.AddWithValue("@dateDue", rental.DateDue);
cmd.Parameters.AddWithValue("@lateFee", rental.LateFee);
cmd.Parameters.AddWithValue("@bookID", isbn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static bool InactivateRental(Book book, Customer user)
{
//int count = 0;
bool inserted = false;
var conn = DBConnection.GetDBConnection();
var query = @"sp_delete_rental";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bookID", book.ISBN);
cmd.Parameters.AddWithValue("@customerID", user.CustomerID);
try
{
conn.Open();
cmd.ExecuteNonQuery();
inserted = true;
}
catch (Exception)
{
throw;
}
//return count;
return(inserted);
}
public static int SetPasswordForUsername(string username, string oldPassword, string newPassword)
{
int count = 0;
var conn = DBConnection.GetDBConnection();
var query = @"sp_update_customer_password_for_username";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@oldPassword", oldPassword);
cmd.Parameters.AddWithValue("@newPassword", newPassword);
try
{
conn.Open();
count = (int)cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(count);
}
/// <summary>
/// Fetches the customer list.
/// </summary>
/// <returns>List of Customer;.</returns>
public static List <Customer> FetchCustomerList()
{
// create a list to hold the returned data
var customerList = new List <Customer>();
// get a connection to the database
var conn = DBConnection.GetDBConnection();
// create a query to send through the connection
string query = "sp_CustomersGetAll";
// create a command object - SP
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
// try-catch
try
{
// open connection
conn.Open();
// execute the command and return a data reader
SqlDataReader reader = cmd.ExecuteReader();
// before trying to read the reader, be sure it has data
if (reader.HasRows)
{
// now we just need a loop to process the reader
while (reader.Read())
{
Customer customer = new Customer()
{
CustomerID = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
EmailID = reader.GetString(3),
PhoneNo1 = reader.GetString(4),
Address1 = reader.GetString(5),
PostalCode = reader.GetString(6),
City = reader.GetString(7),
State = reader.GetString(8)
};
customerList.Add(customer);
}
}
}
catch (Exception)
{
// rethrow all Exceptions, let the logic layer sort them out
throw;
}
finally
{
conn.Close();
}
// this list may be empty, if so, the logic layer will need to deal with it
return(customerList);
}
public static bool IsCustomerStaff(string username)
{
bool isStaff = false;
SqlConnection conn = DBConnection.GetDBConnection();
var query = @"sp_validate_customer_is_staff";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
try
{
conn.Open();
if ((int)cmd.ExecuteScalar() == 1)
{
isStaff = true;
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(isStaff);
}
public static int CheckReview(int customerID, string isbn)
{
int count = 0;
var conn = DBConnection.GetDBConnection();
var query = @"sp_check_review";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@customerID", customerID);
cmd.Parameters.AddWithValue("@bookID", isbn);
try
{
conn.Open();
count = (int)cmd.ExecuteScalar();
}
catch (Exception)
{
throw;
}
return(count);
}
public static List <Item> RetrieveItems()
{
List <Item> itemList = new List <Item>();
// connection
var conn = DBConnection.GetDBConnection();
// cmdtext
var cmdText = @"sp_retrieve_active_item_list";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// no parameters
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
var reader = cmd.ExecuteReader();
// check for results
if (reader.HasRows)
{
// multiple rows are possible, so use a while loop
while (reader.Read())
{
// create a character object
var item = new Item()
{
ItemID = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
AttackBoost = (float)reader.GetDouble(3),
DefenseBoost = (float)reader.GetDouble(4)
};
// add character to the list
itemList.Add(item);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(itemList);
}
public static List <Equipment> RetrieveEquipment()
{
List <Equipment> equipmentList = new List <Equipment>();
// connection
var conn = DBConnection.GetDBConnection();
// cmdtext
var cmdText = @"sp_retrieve_active_equipment_list";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// no parameters
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
var reader = cmd.ExecuteReader();
// check for results
if (reader.HasRows)
{
// multiple rows are possible, so use a while loop
while (reader.Read())
{
// create a character object
var equipment = new Equipment()
{
EquipmentID = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
Defense = reader.GetInt32(3)
};
// add character to the list
equipmentList.Add(equipment);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(equipmentList);
}
public static int ResetStats(int StatID, int strength, int stamina, int dexterity, int intelligence)
{
int result = 0;
// connection
var conn = DBConnection.GetDBConnection();
// command text
var cmdText = @"sp_reset_stats";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@StatID", SqlDbType.Int);
cmd.Parameters.Add("@OldStrength", SqlDbType.Int);
cmd.Parameters.Add("@OldStamina", SqlDbType.Int);
cmd.Parameters.Add("@OldDexterity", SqlDbType.Int);
cmd.Parameters.Add("@OldIntelligence", SqlDbType.Int);
// parameter values
cmd.Parameters["@StatID"].Value = StatID;
cmd.Parameters["@OldStrength"].Value = strength;
cmd.Parameters["@OldStamina"].Value = stamina;
cmd.Parameters["@OldDexterity"].Value = dexterity;
cmd.Parameters["@OldIntelligence"].Value = intelligence;
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
result = cmd.ExecuteNonQuery();
// display error if there are no rows affected
if (result == 0)
{
throw new ApplicationException("Stat reset failed.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
public static int UpgradeAccount(int gameUserID, string oldEmail, string newEmail, string oldPasswordHash, string newPasswordHash)
{
int result = 0;
// connection
var conn = DBConnection.GetDBConnection();
// command text
var cmdText = @"sp_upgrade_account";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@GameUserID", SqlDbType.Int);
cmd.Parameters.Add("@OldEmail", SqlDbType.NVarChar, 200);
cmd.Parameters.Add("@NewEmail", SqlDbType.NVarChar, 200);
cmd.Parameters.Add("@oldPasswordHash", SqlDbType.NVarChar, 100);
cmd.Parameters.Add("@newPasswordHash", SqlDbType.NVarChar, 100);
// parameter values
cmd.Parameters["@GameUserID"].Value = gameUserID;
cmd.Parameters["@OldEmail"].Value = oldEmail;
cmd.Parameters["@NewEmail"].Value = newEmail;
cmd.Parameters["@oldPasswordHash"].Value = oldPasswordHash;
cmd.Parameters["@newPasswordHash"].Value = newPasswordHash;
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
result = cmd.ExecuteNonQuery();
// display error if there are no rows affected
if (result == 0)
{
throw new ApplicationException("Account upgrade failed.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
public static List <Book> FetchBookList(Active group = Active.active)
{
List <Book> books = new List <Book>();
SqlConnection conn = DBConnection.GetDBConnection();
string query = @"SELECT BookID, PublisherID, AuthorID, " +
@"Title, Pages, Copies, Active " +
@"FROM Book ";
if (group == Active.active)
{
query += @"WHERE Active = 1 ";
}
else if (group == Active.inactive)
{
query += @"WHERE Active = 0 ";
}
SqlCommand cmd = new SqlCommand(query, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Book currentBook = new Book()
{
ISBN = reader.GetString(0),
Publisher = reader.GetString(1),
AuthorID = reader.GetInt32(2),
Title = reader.GetString(3),
Pages = reader.GetInt32(4),
Copies = reader.GetInt32(5),
Active = reader.GetBoolean(6)
};
books.Add(currentBook);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(books);
}
public static List <Rental> FetchRentalList(Active group = Active.active)
{
List <Rental> rentals = new List <Rental>();
SqlConnection conn = DBConnection.GetDBConnection();
string query = @"SELECT RentalID, CustomerID, DateRented, " +
@"DateDue, LateFee, Active " +
@"FROM Rental ";
if (group == Active.active)
{
query += @"WHERE Active = 1 ";
}
else if (group == Active.inactive)
{
query += @"WHERE Active = 0 ";
}
SqlCommand cmd = new SqlCommand(query, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Rental currentRental = new Rental()
{
RentalID = reader.GetInt32(0),
CustomerID = reader.GetInt32(1),
DateRented = reader.GetDateTime(2),
DateDue = reader.GetDateTime(3),
LateFee = reader.GetDecimal(4),
Active = reader.GetBoolean(5)
};
rentals.Add(currentRental);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(rentals);
}
public static int CreateCharacter(int gameUserID, int fileNumber, string name, string race, string playerClass,
string image)
{
int results = 0;
// connection
var conn = DBConnection.GetDBConnection();
// cmdtext
var cmdText = @"sp_create_character_by_gameuserid";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@GameUserID", SqlDbType.Int);
cmd.Parameters.Add("@PlayerName", SqlDbType.NVarChar, 40);
cmd.Parameters.Add("@PlayerRace", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@PlayerClass", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@PlayerImage", SqlDbType.NVarChar, 40);
cmd.Parameters.Add("@PlayerSlot", SqlDbType.Int);
// parameter values
cmd.Parameters["@GameUserID"].Value = gameUserID;
cmd.Parameters["@PlayerName"].Value = name;
cmd.Parameters["@PlayerRace"].Value = race;
cmd.Parameters["@PlayerClass"].Value = playerClass;
cmd.Parameters["@PlayerImage"].Value = image;
cmd.Parameters["@PlayerSlot"].Value = fileNumber;
// execute the command surrounded by try-catch
try
{
// open the connection
conn.Open();
// execute the command
results = (int)cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
// close the connection
conn.Close();
}
return(results);
}
public static int UpdateCharacterItem(int PlayerCharacterID, int ItemID, int OldQuantity, int NewQuantity)
{
int result = 0;
// connection
var conn = DBConnection.GetDBConnection();
// command text
var cmdText = @"sp_update_item_quantity";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@PlayerCharacterID", SqlDbType.Int);
cmd.Parameters.Add("@ItemID", SqlDbType.Int);
cmd.Parameters.Add("@OldQuantity", SqlDbType.Int);
cmd.Parameters.Add("@NewQuantity", SqlDbType.Int);
// parameter values
cmd.Parameters["@PlayerCharacterID"].Value = PlayerCharacterID;
cmd.Parameters["@ItemID"].Value = ItemID;
cmd.Parameters["@OldQuantity"].Value = OldQuantity;
cmd.Parameters["@NewQuantity"].Value = NewQuantity;
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
result = cmd.ExecuteNonQuery();
// display error if there are no rows affected
if (result == 0)
{
throw new ApplicationException("Item quantity update failed.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Fetches the event type list.
/// </summary>
/// <returns>List of EventType;.</returns>
public static List <EventType> FetchEventTypeList()
{
// create a list to hold the returned data
var eventTypeList = new List <EventType>();
// get a connection to the database
var conn = DBConnection.GetDBConnection();
//query to select
string query = @"SELECT EventTypeID, EventType, EventTypeDescription " +
@"FROM EventType ";
//ommand object
var cmd = new SqlCommand(query, conn);
// try-catch
try
{
// open connection
conn.Open();
// execute the command and return a data reader
SqlDataReader reader = cmd.ExecuteReader();
// before trying to read the reader, be sure it has data
if (reader.HasRows)
{
// now we just need a loop to process the reader
while (reader.Read())
{
EventType eventType = new EventType()
{
EventTypeID = reader.GetInt32(0),
EventTypeName = reader.GetString(1),
EventTypeDescription = reader.GetString(2),
};
eventTypeList.Add(eventType);
}
}
}
catch (Exception)
{
// rethrow all Exceptions, let the logic layer sort them out
throw;
}
finally
{
conn.Close();
}
// this list may be empty, if so, the logic layer will need to deal with it
return(eventTypeList);
}
public static int UpdateCharacter(int PlayerCharacterID, string oldClass, string newClass)
{
int result = 0;
// connection
var conn = DBConnection.GetDBConnection();
// command text
var cmdText = @"sp_update_character_by_playercharacterid";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@PlayerCharacterID", SqlDbType.Int);
cmd.Parameters.Add("@OldClass", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@NewClass", SqlDbType.NVarChar, 20);
// parameter values
cmd.Parameters["@PlayerCharacterID"].Value = PlayerCharacterID;
cmd.Parameters["@OldClass"].Value = oldClass;
cmd.Parameters["@NewClass"].Value = newClass;
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
result = cmd.ExecuteNonQuery();
// display error if there are no rows affected
if (result == 0)
{
throw new ApplicationException("Character update failed.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
public static List <Review> FetchReviewList(Active group = Active.active)
{
List <Review> reviews = new List <Review>();
SqlConnection conn = DBConnection.GetDBConnection();
string query = @"SELECT BookID, RentalID, Content, Active " +
@"FROM Review ";
if (group == Active.active)
{
query += @"Where Active = 1 ";
}
else if (group == Active.inactive)
{
query += @"Where Active = 0 ";
}
SqlCommand cmd = new SqlCommand(query, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Review currentReview = new Review()
{
ISBN = reader.GetString(0),
RentalID = reader.GetInt32(1),
Content = reader.GetString(2),
Active = reader.GetBoolean(3)
};
reviews.Add(currentReview);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(reviews);
}
public static List <Author> FetchAuthorList(Active group = Active.active)
{
List <Author> authors = new List <Author>();
SqlConnection conn = DBConnection.GetDBConnection();
string query = @"SELECT AuthorID, FirstName, LastName, Active " +
@"FROM Author ";
if (group == Active.active)
{
query += @"WHERE Active = 1 ";
}
else if (group == Active.inactive)
{
query += @"WHERE Active = 0 ";
}
SqlCommand cmd = new SqlCommand(query, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Author currentAuthor = new Author()
{
AuthorID = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Active = reader.GetBoolean(3)
};
authors.Add(currentAuthor);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(authors);
}
/// <summary>
/// Fetches the event type identifier.
/// </summary>
/// <param name="eventTypeID">The event type identifier.</param>
/// <returns>EventType.</returns>
public static EventType FetchEventTypeID(int eventTypeID)
{
EventType eventType = new EventType();
// get a connection to the database
var conn = DBConnection.GetDBConnection();
//query to select
string query = @"SELECT EventType, EventTypeDescription " +
@"FROM EventType Where EventTypeID = @EventTypeID";
//ommand object
var cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(new SqlParameter("EventTypeID", SqlDbType.Int)).Value = eventTypeID;
// try-catch
try
{
// open connection
conn.Open();
// execute the command and return a data reader
SqlDataReader reader = cmd.ExecuteReader();
// before trying to read the reader, be sure it has data
if (reader.HasRows)
{
// now we just need a loop to process the reader
while (reader.Read())
{
eventType = new EventType()
{
EventTypeID = eventTypeID,
EventTypeName = reader.GetString(0),
EventTypeDescription = reader.GetString(1)
};
}
}
}
catch (Exception)
{
// rethrow all Exceptions, let the logic layer sort them out
throw;
}
finally
{
conn.Close();
}
// this list may be empty, if so, the logic layer will need to deal with it
return(eventType);
}
public static int DeleteCharacterEquipment(int PlayerCharacterID, int EquipmentID)
{
int result = 0;
// connection
var conn = DBConnection.GetDBConnection();
// command text
var cmdText = @"sp_delete_equipment_from_character_equipment_inventory";
// command
var cmd = new SqlCommand(cmdText, conn);
// command type
cmd.CommandType = CommandType.StoredProcedure;
// parameters
cmd.Parameters.Add("@PlayerCharacterID", SqlDbType.Int);
cmd.Parameters.Add("@EquipmentID", SqlDbType.Int);
// parameter values
cmd.Parameters["@PlayerCharacterID"].Value = PlayerCharacterID;
cmd.Parameters["@EquipmentID"].Value = EquipmentID;
// all set? need a try-catch
try
{
// open a connection
conn.Open();
// execute the command
result = cmd.ExecuteNonQuery();
// display error if there are no rows affected
if (result == 0)
{
throw new ApplicationException("Equipment deletion failed.");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(result);
}
/// <summary>
/// Fetches the caterers by identifier.
/// </summary>
/// <param name="caterersID">The caterers identifier.</param>
/// <returns>Caterers.</returns>
public static Caterers FetchCaterersByID(int caterersID)
{
Caterers caterers = new Caterers();
// get a connection to the database
var conn = DBConnection.GetDBConnection();
//query to select
string query = @"SELECT CatererID, CatererName " +
@"FROM Caterers Where CatererID = @CatererID";
//ommand object
var cmd = new SqlCommand(query, conn);
cmd.Parameters.Add(new SqlParameter("CatererID", SqlDbType.Int)).Value = caterersID;
// try-catch
try
{
// open connection
conn.Open();
// execute the command and return a data reader
SqlDataReader reader = cmd.ExecuteReader();
// before trying to read the reader, be sure it has data
if (reader.HasRows)
{
// now we just need a loop to process the reader
while (reader.Read())
{
caterers = new Caterers()
{
CatererID = caterersID,
CatererName = reader.GetString(0)
};
}
}
}
catch (Exception)
{
// rethrow all Exceptions, let the logic layer sort them out
throw;
}
finally
{
conn.Close();
}
// this list may be empty, if so, the logic layer will need to deal with it
return(caterers);
}