public PlayerDAO viewSinglePlayer(string PlayerName) { PlayerDAO PlayerToView = new PlayerDAO(); try { // create connection to database using connection string variable using (SqlConnection _connection = new SqlConnection(connectionstring)) { // create command to view a team from the database using (SqlCommand _command = new SqlCommand("sp_ViewSinglePlayer", _connection)) { // specify what type of command to use _command.CommandType = CommandType.StoredProcedure; _command.Parameters.AddWithValue("@PlayerName", PlayerName); // this is where the connection is opened _connection.Open(); // this is where all commands will be executed _command.ExecuteNonQuery(); // this accesses all the data given by the stored procedure using (SqlDataReader _reader = _command.ExecuteReader()) { while (_reader.Read()) { // create object to hold info from database //use .GetOrdinal so that if the DB gets rearranged it will not throw error because the Column numbers dont match PlayerToView.PlayerName = _reader.GetString(_reader.GetOrdinal("PlayerName")); PlayerToView.PlayerFirstName = _reader.GetString((_reader.GetOrdinal("PlayerFirstName"))); PlayerToView.PlayerLastName = _reader.GetString((_reader.GetOrdinal("PlayerLastName"))); PlayerToView.PlayerCity = _reader.GetString((_reader.GetOrdinal("PlayerCity"))); PlayerToView.PlayerState = _reader.GetString((_reader.GetOrdinal("PlayerState"))); PlayerToView.PlayerAge = _reader.GetInt32((_reader.GetOrdinal("PlayerAge"))); PlayerToView.FKRoleID = _reader.GetInt32((_reader.GetOrdinal("FKRoleID"))); } } } } } catch (Exception _error) { _Logger.LogError(_error); } return(PlayerToView); }
public List <PlayerDAO> GetAllPlayers() { List <PlayerDAO> _PlayerList = new List <PlayerDAO>(); try { using (SqlConnection _connection = new SqlConnection(connectionstring)) { using (SqlCommand _command = new SqlCommand("SP_ViewAllPlayers", _connection)) { _connection.Open(); _command.CommandType = CommandType.StoredProcedure; using (SqlDataReader _reader = _command.ExecuteReader()) { if (_reader.HasRows) { while (_reader.Read()) { PlayerDAO playerToList = new PlayerDAO(); playerToList.PlayerName = _reader.GetString((_reader.GetOrdinal("PlayerName"))); playerToList.PlayerFirstName = _reader.GetString((_reader.GetOrdinal("PlayerFirstName"))); playerToList.PlayerLastName = _reader.GetString((_reader.GetOrdinal("PlayerLastName"))); playerToList.PlayerCity = _reader.GetString((_reader.GetOrdinal("PlayerCity"))); playerToList.PlayerState = _reader.GetString((_reader.GetOrdinal("PlayerState"))); playerToList.PlayerAge = _reader.GetInt32((_reader.GetOrdinal("PlayerAge"))); playerToList.FKRoleID = _reader.GetInt32((_reader.GetOrdinal("FKRoleID"))); playerToList.PlayerPassword = _reader.GetString((_reader.GetOrdinal("PlayerPassword"))); _PlayerList.Add(playerToList); } } else { Console.WriteLine("Sorry,there is no data found."); } } } } } catch (Exception _Error) { _Logger.LogError(_Error); } return(_PlayerList); }
//Method Called when Logging in a Player public PlayerDAO LoginPlayer(PlayerDAO _Login) { PlayerDAO _PlayerToList = new PlayerDAO(); try { using (SqlConnection _connection = new SqlConnection(connectionstring)) { using (SqlCommand _command = new SqlCommand("SP_PlayerLogin", _connection)) { //What Command Type will be used _command.CommandType = CommandType.StoredProcedure; //What commands are going to be used _command.Parameters.AddWithValue("@PlayerName", _Login.PlayerName); _command.Parameters.AddWithValue("@PlayerPassword", _Login.PlayerPassword); //open connection _connection.Open(); //Execute commands //_command.ExecuteNonQuery(); //Accesses SP to get the Data using (SqlDataReader _reader = _command.ExecuteReader()) { while (_reader.Read()) { _PlayerToList.PlayerName = _reader.GetString((_reader.GetOrdinal("PlayerName"))); _PlayerToList.PlayerPassword = _reader.GetString((_reader.GetOrdinal("PlayerPassword"))); _PlayerToList.PlayerFirstName = _reader.GetString((_reader.GetOrdinal("PlayerFirstName"))); _PlayerToList.PlayerLastName = _reader.GetString((_reader.GetOrdinal("PlayerLastName"))); _PlayerToList.PlayerCity = _reader.GetString((_reader.GetOrdinal("PlayerCity"))); _PlayerToList.PlayerState = _reader.GetString((_reader.GetOrdinal("PlayerState"))); _PlayerToList.PlayerAge = _reader.GetInt32((_reader.GetOrdinal("PlayerAge"))); _PlayerToList.FKRoleID = _reader.GetInt32((_reader.GetOrdinal("FKRoleID"))); } } } } } catch (Exception _Error) { _Logger.LogError(_Error); } return(_PlayerToList); }
//Player Methods public bool AddPlayer(PlayerDAO playerTOAdd) { bool success = false; try { //Create a connection to the DB using a ConnectionString Variable using (SqlConnection _connection = new SqlConnection(connectionstring)) { //Creates a command that will Add a Player To the DB using (SqlCommand _command = new SqlCommand("SP_AddPlayer", _connection)) { //Specify the command type that is going to be used //in this case we are going to be useing StoredProcedure _command.CommandType = CommandType.StoredProcedure; //Below is where the values are sent to the command _command.Parameters.AddWithValue("@PlayerName", playerTOAdd.PlayerName); _command.Parameters.AddWithValue("@PlayerFirstName", playerTOAdd.PlayerFirstName); _command.Parameters.AddWithValue("@PlayerLastName", playerTOAdd.PlayerLastName); _command.Parameters.AddWithValue("@PlayerCity", playerTOAdd.PlayerCity); _command.Parameters.AddWithValue("@PlayerState", playerTOAdd.PlayerState); _command.Parameters.AddWithValue("@PlayerAge", playerTOAdd.PlayerAge); _command.Parameters.AddWithValue("@PlayerPassword", playerTOAdd.PlayerPassword); //This is where the connection Opens _connection.Open(); //this is where it executes the command _command.ExecuteNonQuery(); //Set the Bool to TRUE success = true; } } } catch (Exception _Error) { //Set the bool to false because it Failed success = false; //Below is the Error Log that will Log Errors into a text document _Logger.LogError(_Error); } return(success); }
public bool UpdatePlayer(PlayerDAO PlayerToUpdate) { bool success = false; try { using (SqlConnection _connection = new SqlConnection(connectionstring)) { //command used to Update a Player in the DB using (SqlCommand _command = new SqlCommand("SP_UpdatePlayer", _connection)) { //Specify the command that is going to be used _command.CommandType = CommandType.StoredProcedure; //Below are the values that will be sent to the command _command.Parameters.AddWithValue("@PlayerName", PlayerToUpdate.PlayerName); _command.Parameters.AddWithValue("@PlayerFirstName", PlayerToUpdate.PlayerFirstName); _command.Parameters.AddWithValue("@PlayerLastName", PlayerToUpdate.PlayerLastName); _command.Parameters.AddWithValue("@PlayerCity", PlayerToUpdate.PlayerCity); _command.Parameters.AddWithValue("@PlayerState", PlayerToUpdate.PlayerState); _command.Parameters.AddWithValue("@PlayerAge", PlayerToUpdate.PlayerAge); _command.Parameters.AddWithValue("@FKRoleID", PlayerToUpdate.FKRoleID); //Open the Connection _connection.Open(); //Execute the command _command.ExecuteNonQuery(); //set the bool to true success = true; } } } catch (Exception _Error) { //ErrorLogger _Logger.LogError(_Error); } return(success); }