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