Beispiel #1
0
        //Method for deleting a game from the database
        public void GameDelete(long gameId)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);

            //Beginning of method processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand deleteGame = new SqlCommand("GAME_DELETE", scon);
                //Defining what kind of command our SqlCommand is
                deleteGame.CommandType = CommandType.StoredProcedure;
                //opening sql connection
                scon.Open();
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                deleteGame.Parameters.AddWithValue("@gameId", gameId);
                //telling the code to use the stored procedure
                deleteGame.ExecuteNonQuery();
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
        }
Beispiel #2
0
        //method for reading a game by player id
        public List <GameDO> ReadGamesByPlayerId(int playerId)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);
            //establishing a data adapter
            SqlDataAdapter iDReader = null;
            //establishing a data table
            DataTable table = new DataTable();
            //establishing a new DO list
            List <GameDO> game = new List <GameDO>();

            //Beginning of method processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand readRowById = new SqlCommand("READ_GAME_BY_PLAYER_ID", scon);
                //Defining what kind of command our SqlCommand is
                readRowById.CommandType = CommandType.StoredProcedure;
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                readRowById.Parameters.AddWithValue("@White", playerId);
                readRowById.Parameters.AddWithValue("@Black", playerId);
                //opening sql connection
                scon.Open();
                //defining the adapter as an adapter using (ThisCommand)
                iDReader = new SqlDataAdapter(readRowById);
                //telling the code to use the stored procedure
                readRowById.ExecuteNonQuery();
                //telling the adapter to fill using the data from our data table
                iDReader.Fill(table);
                //setting our list equal to the data from the table after running through a mapper to define all necessary aspects of "game"
                game = GameListMap.DataTableToList(table);
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
            //sending our data back up to the PL
            return(game);
        }
Beispiel #3
0
        //Method for viewing more specific details of a game
        public List <GameDO> GameDetails(long gameId)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = null;
            //Establishing a new data adapter
            SqlDataAdapter iDReader = null;
            //Establishing a new data table
            DataTable table = new DataTable();
            //Establishing new list using model GameDO
            List <GameDO> game = new List <GameDO>();

            //Beginning of method processes
            try
            {
                scon = new SqlConnection(_conn);
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand readRowById = new SqlCommand("GAME_READ_BY_ID", scon);
                //Defining what kind of command our SqlCommand is
                readRowById.CommandType = CommandType.StoredProcedure;
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                readRowById.Parameters.AddWithValue("@GameId", gameId);
                //opening sql connection
                scon.Open();
                //defining our adapter and what command it uses
                iDReader = new SqlDataAdapter(readRowById);
                //telling the code to use the stored procedure
                readRowById.ExecuteNonQuery();
                //telling code to use the data table to fill itself
                iDReader.Fill(table);
                //setting our list equal to all information pulled from database by running it through a mapper
                game = GameListMap.DataTableToList(table);
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
            //Sending data we pulled back up to the PL
            return(game);
        }
        //Method for searching through all the players, counting all of their wins, and then arranging them from most to least wins
        public List <WinsDO> OrderByWinCount()
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);
            //Establishing a new data adapter
            SqlDataAdapter iDReader = null;
            //Establishing a new data table
            DataTable table = new DataTable();
            //Establishing new list using model PlayerDO
            List <WinsDO> winner = new List <WinsDO>();

            //Beginning the processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand readWins = new SqlCommand("ORDER_BY_WIN_COUNT", scon);
                //Defining what kind of command our SqlCommand is
                readWins.CommandType = CommandType.StoredProcedure;
                //opening sql connection
                scon.Open();
                //defining our adapter and what command it uses
                iDReader = new SqlDataAdapter(readWins);
                //telling the code to use the stored procedure
                readWins.ExecuteNonQuery();
                //telling code to use the data table to fill itself
                iDReader.Fill(table);
                //setting our list equal to all information pulled from database by running it through a mapper
                winner = WinMap.DataTableToList(table);
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
            //Sending all information pulled from the database up to the PL
            return(winner);
        }
Beispiel #5
0
        //Method for viewing all games with more general information
        public List <GameDO> GameReadAll()
        {
            //Establishing new list using model GameDO
            List <GameDO> allgames = new List <GameDO>();
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);
            //Establishing a new data adapter
            SqlDataAdapter adapter = null;
            //Establishing a new data table
            DataTable table = new DataTable();

            //Beginning of method processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand readRow = new SqlCommand("GAME_READ_ALL", scon);
                //Defining what kind of command our SqlCommand is
                readRow.CommandType = CommandType.StoredProcedure;
                //opening sql connection
                scon.Open();
                //defining our adapter and what command it uses
                adapter = new SqlDataAdapter(readRow);
                //telling code to use the data table to fill itself
                adapter.Fill(table);
                //setting our list equal to all information pulled from database by running it through a mapper
                allgames = GameListMap.DataTableToList(table);
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
            //Sending all information pulled from the database up to the PL
            return(allgames);
        }
        //Method for searching the database by username, and returning username, password, roleId, and userId
        public LoginDO ValidLogin(string username)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);
            //Establishing new list using model LoginDO
            LoginDO login = new LoginDO();
            //Establishing a new data adapter
            SqlDataAdapter adapter = null;
            //Establishing a new data table
            DataTable table = new DataTable();

            //Beginning the processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand loginCheck = new SqlCommand("LOGIN_INFO_RETRIEVAL", scon);
                //Defining what kind of command our SqlCommand is
                loginCheck.CommandType = CommandType.StoredProcedure;
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                loginCheck.Parameters.AddWithValue("@Username", username);
                //defining our adapter and what command it uses
                adapter = new SqlDataAdapter(loginCheck);
                //telling code to use the data table to fill itself
                adapter.Fill(table);
                //setting our object equal to all information pulled from database by running it through a mapper
                login = LoginListMap.RowToItem(table.Rows[0]);
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
            //Sending all information pulled from the database up to the PL
            return(login);
        }
Beispiel #7
0
        //Method for updating a game by passing in the game to update, with either new or old information
        public void GameUpdate(GameDO game)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);

            //Beginning of method processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand updateGame = new SqlCommand("GAME_UPDATE", scon);
                //Defining what kind of command our SqlCommand is
                updateGame.CommandType = CommandType.StoredProcedure;
                //opening sql connection
                scon.Open();
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                updateGame.Parameters.AddWithValue("@GameId", game.GameId);
                updateGame.Parameters.AddWithValue("@White", game.White);
                updateGame.Parameters.AddWithValue("@WhiteRating", game.WhiteRating);
                updateGame.Parameters.AddWithValue("@Black", game.Black);
                updateGame.Parameters.AddWithValue("@BlackRating", game.BlackRating);
                updateGame.Parameters.AddWithValue("@Location", game.Location);
                updateGame.Parameters.AddWithValue("@DatePlayed", game.DatePlayed);
                updateGame.Parameters.AddWithValue("@Winner", game.Winner);
                //telling the code to use the stored procedure
                updateGame.ExecuteNonQuery();
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
        }
        //Method for updating a players information in the database
        public void PlayerUpdate(PlayerDO player)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);

            //Beginning the processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand updatePlayer = new SqlCommand("PLAYER_UPDATE", scon);
                //Defining what kind of command our SqlCommand is
                updatePlayer.CommandType = CommandType.StoredProcedure;
                //opening sql connection
                scon.Open();
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                updatePlayer.Parameters.AddWithValue("@PlayerId", player.PlayerId);
                updatePlayer.Parameters.AddWithValue("@Name", player.Name);
                updatePlayer.Parameters.AddWithValue("@PeakRating", player.PeakRating);
                updatePlayer.Parameters.AddWithValue("@BirthDate", player.BirthDate);
                updatePlayer.Parameters.AddWithValue("@Dead", player.Dead);
                updatePlayer.Parameters.AddWithValue("@CountryOfOrigin", player.CountryOfOrigin);
                updatePlayer.Parameters.AddWithValue("@CountryRepresented", player.CountryRepresented);
                //telling the code to use the stored procedure
                updatePlayer.ExecuteNonQuery();
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
        }
        //Method for updating a user's information
        public void UserUpdate(UserDO user)
        {
            //Defining SqlConnection for this method
            SqlConnection scon = new SqlConnection(_conn);

            //Beginning the processes
            try
            {
                //Defining a variable for the SqlCommand, as well as the stored proc and connection we're using
                SqlCommand updateUser = new SqlCommand("USER_UPDATE", scon);
                //Defining what kind of command our SqlCommand is
                updateUser.CommandType = CommandType.StoredProcedure;
                //opening sql connection
                scon.Open();
                //Adding in all the variables. Format: ("@SqlVariable", C#VariableValue)
                updateUser.Parameters.AddWithValue("@UserId", user.UserId);
                updateUser.Parameters.AddWithValue("@UserRoleId", user.UserRoleId);
                updateUser.Parameters.AddWithValue("@Username", user.Username);
                updateUser.Parameters.AddWithValue("@LastName", user.FirstName);
                updateUser.Parameters.AddWithValue("@FirstName", user.LastName);
                updateUser.Parameters.AddWithValue("@Email", user.Email);
                //telling the code to use the stored procedure
                updateUser.ExecuteNonQuery();
            }
            //Catch for any errors that may happen
            catch (Exception ex)
            {
                //passing in the exception that was thrown to the errorhandler
                ErrorHandlerDAL.ErrorLogger(ex);
            }
            //finally cleans up any last loose ends
            finally
            {
                //closing SqlConnection
                scon.Close();
                //disposing of the SqlConnection
                scon.Dispose();
            }
        }