public List <GameDO> ViewGames()
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
List <GameDO> gameList = new List <GameDO>();
GameDO gameObject = new GameDO();
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("OBTAIN_GAMES", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Opens the connection to SQL.
connectionToSql.Open();
//Tell SQLDataReader to gather the information from SQL as determined by the stored procedure.
reader = storedProcedure.ExecuteReader();
//Cycle through the database and apply each property in SQL to an object
//Then add that object to a list of objects.
while (reader.Read())
{
gameObject = new GameDO();
gameObject.GameID = int.Parse(reader["GameID"].ToString());
gameObject.GameName = reader["GameName"].ToString();
gameObject.ReleaseYear = short.Parse(reader["ReleaseYear"].ToString());
gameObject.Genre = reader["Genre"].ToString();
gameObject.Developer = reader["Developer"].ToString();
gameObject.Publisher = reader["Publisher"].ToString();
gameObject.Platform = reader["Platform"].ToString();
gameObject.Download = reader["Download"].ToString();
gameObject.Picture = reader["Picture"].ToString();
gameObject.Description = reader["Description"].ToString();
gameList.Add(gameObject);
}
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(gameList);
}
public List <CommentDO> ViewGameComments(int GameID)
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
List <CommentDO> commentList = new List <CommentDO>();
CommentDO commentObject = new CommentDO();
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("OBTAIN_COMMENTS", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Applies the game Id given to the method to the stored procedure.
storedProcedure.Parameters.AddWithValue("@GameID", GameID);
//Opens the connection to SQL.
connectionToSql.Open();
//Tell SQLDataReader to gather the information from SQL as determined by the stored procedure.
reader = storedProcedure.ExecuteReader();
//Cycle through the database and apply each property in SQL to an object
//Then add that object to a list of objects.
while (reader.Read())
{
commentObject = new CommentDO();
commentObject.CommentID = int.Parse(reader["CommentID"].ToString());
commentObject.CommentTime = DateTime.Parse(reader["CommentTime"].ToString());
commentObject.CommentText = reader["CommentText"].ToString();
commentObject.GameID = int.Parse(reader["GameID"].ToString());
commentObject.UserID = int.Parse(reader["UserID"].ToString());
commentObject.Username = reader["Username"].ToString();
commentObject.GameID = int.Parse(reader["GameID"].ToString());
commentList.Add(commentObject);
}
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(commentList);
}
public GameDO GameDetails(int GameID)
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
GameDO gameObject = new GameDO();
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("VIEW_GAME", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Applies the game Id given to the method to the stored procedure.
storedProcedure.Parameters.AddWithValue("@GameID", GameID);
//Opens the connection to SQL.
connectionToSql.Open();
//Tell SQLDataReader to gather the information from SQL as determined by the stored procedure.
reader = storedProcedure.ExecuteReader();
//Reads all the information in SQL via the stored procedure and applies the data to the object.
while (reader.Read())
{
gameObject.GameID = int.Parse(reader["GameID"].ToString());
gameObject.GameName = reader["GameName"].ToString();
gameObject.ReleaseYear = short.Parse(reader["ReleaseYear"].ToString());
gameObject.Genre = reader["Genre"].ToString();
gameObject.Developer = reader["Developer"].ToString();
gameObject.Publisher = reader["Publisher"].ToString();
gameObject.Platform = reader["Platform"].ToString();
gameObject.Download = reader["Download"].ToString();
gameObject.Picture = reader["Picture"].ToString();
gameObject.Description = reader["Description"].ToString();
}
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(gameObject);
}
public List <UserDO> ViewUsers()
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
List <UserDO> userList = new List <UserDO>();
UserDO userObject = new UserDO();
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("OBTAIN_USERS", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Opens the connection to SQL.
connectionToSql.Open();
//Tell SQLDataReader to gather the information from SQL as determined by the stored procedure.
reader = storedProcedure.ExecuteReader();
//Cycle through the database and apply each property in SQL to an object
//Then add that object to a list of objects.
while (reader.Read())
{
userObject = new UserDO();
userObject.UserID = int.Parse(reader["UserID"].ToString());
userObject.Username = reader["Username"].ToString();
userObject.FirstName = reader["FirstName"].ToString();
userObject.LastName = reader["LastName"].ToString();
userObject.Email = reader["Email"].ToString();
userObject.RoleID = int.Parse(reader["RoleID"].ToString());
userObject.Title = reader["Title"].ToString();
userList.Add(userObject);
}
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(userList);
}
public UserDO UserDetails(int UserID)
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
UserDO userObject = new UserDO();
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("VIEW_USER", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Applies the game Id given to the method to the stored procedure.
storedProcedure.Parameters.AddWithValue("@UserID", UserID);
//Opens the connection to SQL.
connectionToSql.Open();
//Tell SQLDataReader to gather the information from SQL as determined by the stored procedure.
reader = storedProcedure.ExecuteReader();
//Reads all the information in SQL via the stored procedure and applies the data to the object.
while (reader.Read())
{
userObject.UserID = int.Parse(reader["UserID"].ToString());
userObject.Username = reader["Username"].ToString();
userObject.Password = reader["Password"].ToString();
userObject.FirstName = reader["FirstName"].ToString();
userObject.LastName = reader["LastName"].ToString();
userObject.Email = reader["Email"].ToString();
userObject.RoleID = int.Parse(reader["RoleID"].ToString());
userObject.Title = reader["Title"].ToString();
}
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(userObject);
}
public List <byte> AllRatings(int GameID)
{
//Setting the variabes that will be used within the method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataReader reader = null;
List <byte> ratingList = new List <byte>();
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("OBTAIN_RATINGS", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Gives the value passed to the method to the stored procedure parameter.
storedProcedure.Parameters.AddWithValue("@GameID", GameID);
//Opens the connection to SQL.
connectionToSql.Open();
//Tell SQLDataReader to gather the information from SQL as determined by the stored procedure.
reader = storedProcedure.ExecuteReader();
//Cycle through the database at a gameId and add all ratings to a list.
while (reader.Read())
{
ratingList.Add(byte.Parse(reader["Rating"].ToString()));
}
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
return(ratingList);
}
public void AddGame(GameDO form)
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
//Setup for connecting to SQl and accessing the stored procedure.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ADD_GAME", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Add the value from the object to the parameters in the stored procedure to SqlCommand.
storedProcedure.Parameters.AddWithValue("@GameName", form.GameName);
storedProcedure.Parameters.AddWithValue("@ReleaseYear", form.ReleaseYear);
storedProcedure.Parameters.AddWithValue("@Genre", form.Genre);
storedProcedure.Parameters.AddWithValue("@Developer", form.Developer);
storedProcedure.Parameters.AddWithValue("@Publisher", form.Publisher);
storedProcedure.Parameters.AddWithValue("@Platform", form.Platform);
storedProcedure.Parameters.AddWithValue("@Download", form.Download);
storedProcedure.Parameters.AddWithValue("@Picture", form.Picture);
storedProcedure.Parameters.AddWithValue("@Description", form.Description);
//Open the connection to SQL.
connectionToSql.Open();
//Applies all the values stored in the SqlCommand to the database.
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
public void UpdateUser(UserDO form)
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("UPDATE_USER", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Add the value from the object to the parameters in the stored procedure to SqlCommand.
storedProcedure.Parameters.AddWithValue("@UserID", form.UserID);
storedProcedure.Parameters.AddWithValue("@Username", form.Username);
storedProcedure.Parameters.AddWithValue("@Password", form.Password);
storedProcedure.Parameters.AddWithValue("@FirstName", form.FirstName);
storedProcedure.Parameters.AddWithValue("@LastName", form.LastName);
storedProcedure.Parameters.AddWithValue("Email", form.Email);
storedProcedure.Parameters.AddWithValue("@RoleID", form.RoleID);
//Open the connection to SQL.
connectionToSql.Open();
//Applies all the values stored in the SqlCommand to the database.
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
public void AddComment(CommentDO form)
{
//Setting the variables to be used for this method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
//Setup for connecting to SQl and accessing the stored procedure.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("ADD_COMMENT", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Add the value from the object to the parameters in the stored procedure to SqlCommand.
storedProcedure.Parameters.AddWithValue("@CommentTime", form.CommentTime);
storedProcedure.Parameters.AddWithValue("@CommentText", form.CommentText);
//When the rating given is the default of byte, make it null in the database, otherwise keep it's value.
storedProcedure.Parameters.AddWithValue("@Rating", form.Rating == default(byte) ? (object)DBNull.Value : form.Rating);
storedProcedure.Parameters.AddWithValue("@GameID", form.GameID);
storedProcedure.Parameters.AddWithValue("@UserID", form.UserID);
//Open connection to Sql.
connectionToSql.Open();
//Applies all the values stored in the SqlCommand to the database.
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
public void RemoveComment(int CommentID)
{
//Setting the variabes that will be used within the method.
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
try
{
//Set up for connecting to SQL, using the stored procedure and how to access the information.
connectionToSql = new SqlConnection(_connectionString);
storedProcedure = new SqlCommand("REMOVE_COMMENT", connectionToSql);
storedProcedure.CommandType = System.Data.CommandType.StoredProcedure;
//Gives the value passed to the method to the stored procedure parameter.
storedProcedure.Parameters.AddWithValue("@CommentID", CommentID);
//Opens the connection to SQL.
connectionToSql.Open();
//Applies the stored procedure in the SqlCommand to the database.
storedProcedure.ExecuteNonQuery();
}
catch (Exception ex)
{
LoggerDAL.Log(ex, "Fatal");
//If an issue occurs, the result would likely be fatal, throw the exception.
throw ex;
}
finally
{
//When the connection has been established, close and dispose the connection before finishing.
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
}
}
public List <ActorDO> ViewAllActors()
{
List <ActorDO> actorList = new List <ActorDO>();
DataTable actorsTable = new DataTable();
SqlConnection connectionToSql = null;
SqlCommand storedProcedure = null;
SqlDataAdapter adapter = null;
try
{
connectionToSql = new SqlConnection(_ConnectionString);
storedProcedure = new SqlCommand("OBTAIN_ALL_ACTORS", connectionToSql);
storedProcedure.CommandType = CommandType.StoredProcedure;
connectionToSql.Open();
adapter = new SqlDataAdapter(storedProcedure);
adapter.Fill(actorsTable);
foreach (DataRow row in actorsTable.Rows)
{
ActorDO actorDO = new ActorDO();
actorDO.ActorID = int.Parse(row["ActorID"].ToString());
actorDO.FirstName = row["FirstName"].ToString().Trim();
actorDO.LastName = row["LastName"].ToString().Trim();
if (DateTime.TryParse(row["BirthDate"].ToString(), out DateTime BirthDate))
{
actorDO.BirthDate = BirthDate;
}
else
{
}
actorDO.Bio = row["Bio"].ToString().Trim();
actorDO.Trivia = row["Trivia"].ToString().Trim();
actorDO.Quotes = row["Quotes"].ToString().Trim();
actorList.Add(actorDO);
}
}
catch (Exception exception)
{
_Logger.Log("Fatal", exception.Source, exception.TargetSite.ToString(), exception.Message, exception.StackTrace);
throw exception;
}
finally
{
if (connectionToSql != null)
{
connectionToSql.Close();
connectionToSql.Dispose();
}
else
{
}
if (adapter != null)
{
adapter.Dispose();
}
else
{
}
}
return(actorList);
}
