//------------------------------------//
//UPDATE TEAMS
//------------------------------------//
public bool UpdateTeam(TeamsDAO teamToUpdate)
{
bool success = false;
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand command = new SqlCommand("sp_TeamsTable_UpdateTeam", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@TeamID", teamToUpdate.TeamID);
command.Parameters.AddWithValue("@TeamName", teamToUpdate.TeamName);
command.Parameters.AddWithValue("@TeamRanking", teamToUpdate.TeamRanking);
connection.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception error)
{
success = false;
Logger.LogError(error);
}
return(success);
}
//------------------------------------//
//VIEW TEAMS
//------------------------------------//
public List <TeamsDAO> GetAllTeams()
{
List <TeamsDAO> teamList = new List <TeamsDAO>();
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand command = new SqlCommand("sp_TeamsTable_GetAllTeams", connection))
{
command.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TeamsDAO teamToList = new TeamsDAO();
teamToList.TeamID = reader.GetInt32(0);
teamToList.TeamName = reader.GetString(1);
teamToList.TeamRanking = reader.GetInt32(2);
teamList.Add(teamToList);
}
}
}
}
}
catch (Exception error)
{
Logger.LogError(error);
}
return(teamList);
}
//------------------------------------//
//GET TEAMS BY ID
//------------------------------------//
public TeamsDAO GetTeamByID(int TeamID)
{
TeamsDAO teamToReturn = new TeamsDAO();
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
using (SqlCommand command = new SqlCommand("sp_TeamsTable_GetTeamByID", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@TeamID", TeamID);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
teamToReturn.TeamID = reader.GetInt32(0);
teamToReturn.TeamName = reader.GetString(1);
teamToReturn.TeamRanking = reader.GetInt32(2);
}
}
}
}
}
catch (Exception error)
{
Logger.LogError(error);
}
return(teamToReturn);
}
//TEAM MAPPER OBJECTS
public TeamsDAO teamMap(Team teamToMap)
{
TeamsDAO teamToReturn = new TeamsDAO();
teamToReturn.TeamID = teamToMap.TeamID;
teamToReturn.TeamName = teamToMap.TeamName.ToUpper();
teamToReturn.TeamRanking = teamToMap.TeamRanking;
return(teamToReturn);
}
public TeamsModel Map(TeamsDAO _TeamsToMap)
{
TeamsModel _TeamsToReturn = new TeamsModel();
_TeamsToReturn.TeamName = _TeamsToMap.TeamName;
_TeamsToReturn.TeamDescription = _TeamsToMap.TeamDescription;
_TeamsToReturn.FKPlayerName = _TeamsToMap.FKPlayerName;
_TeamsToReturn.PositionsAvaliable = _TeamsToMap.PositionsAvaliable;
_TeamsToReturn.PositionsTaken = _TeamsToMap.PositionsTaken;
return(_TeamsToReturn);
}
public List <TeamsDAO> GetAllTeams()
{
List <TeamsDAO> _TeamsList = new List <TeamsDAO>();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("SP_ViewAllTeams", _connection))
{
_connection.Open();
//Specify why kind of command used
_command.CommandType = CommandType.StoredProcedure;
using (SqlDataReader _reader = _command.ExecuteReader())
{
if (_reader.HasRows)
{
while (_reader.Read())
{
TeamsDAO TeamsToList = new TeamsDAO();
TeamsToList.TeamName = _reader.GetString((_reader.GetOrdinal("TeamName")));
TeamsToList.TeamDescription = _reader.GetString((_reader.GetOrdinal("TeamDescription")));
TeamsToList.FKPlayerName = _reader.GetString((_reader.GetOrdinal("FKPlayerName")));
TeamsToList.PositionsAvaliable = _reader.GetInt32((_reader.GetOrdinal("PositionsAvaliable")));
TeamsToList.PositionsTaken = _reader.GetInt32((_reader.GetOrdinal("PositionsTaken")));
_TeamsList.Add(TeamsToList);
}
}
else
{
Console.WriteLine("Sorry, no data found");
}
}
}
}
}
catch (Exception _Error)
{
_Logger.LogError(_Error);
}
return(_TeamsList);
}
public TeamsDAO viewSingleTeam(string TeamName)
{
TeamsDAO TeamToView = new TeamsDAO();
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_ViewSingleTeam", _connection))
{
// specify what type of command to use
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@TeamName", TeamName);
// 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
TeamToView.TeamName = _reader.GetString((_reader.GetOrdinal("TeamName")));
TeamToView.TeamDescription = _reader.GetString((_reader.GetOrdinal("TeamDescription")));
TeamToView.PositionsAvaliable = _reader.GetInt32((_reader.GetOrdinal("PositionsAvaliable")));
TeamToView.PositionsTaken = _reader.GetInt32((_reader.GetOrdinal("PoitionsTaken")));
}
}
}
}
}
catch (Exception _error)
{
_Logger.LogError(_error);
}
return(TeamToView);
}
public bool UpdateTeams(TeamsDAO TeamsToUpdate)
{
bool success = false;
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//command that will update a Teams basic info
using (SqlCommand _command = new SqlCommand("SP_UpdateTeam", _connection))
{
//Specify the command that is going to be used
_command.CommandType = CommandType.StoredProcedure;
//Command type being used
_command.Parameters.AddWithValue("@TeamName", TeamsToUpdate.TeamName);
_command.Parameters.AddWithValue("@TeamDescription", TeamsToUpdate.TeamDescription);
_command.Parameters.AddWithValue("@PositionsAvaliable", TeamsToUpdate.PositionsAvaliable);
_command.Parameters.AddWithValue("@PositionsTaken", TeamsToUpdate.PositionsTaken);
// _command.Parameters.AddWithValue("@FKPlayerName", TeamsToUpdate.FKPlayerName);
//connection open
_connection.Open();
//Execute command
_command.ExecuteNonQuery();
//set bool to true
success = true;
}
}
}
catch (Exception _Error)
{
//Error Logger
_Logger.LogError(_Error);
}
return(success);
}
//Team Methods
public bool AddTeams(TeamsDAO TeamsToAdd)
{
bool success = false;
try
{
//connection to the DB with a ConnectionString Variable
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//create the commnd to Add Stats to the DB
using (SqlCommand _command = new SqlCommand("SP_AddTeam", _connection))
{
//Specify what command type will be used
_command.CommandType = CommandType.StoredProcedure;
//Below are the Values that will be sent to the command
_command.Parameters.AddWithValue("@FKPlayerName", TeamsToAdd.FKPlayerName);
_command.Parameters.AddWithValue("@TeamName", TeamsToAdd.TeamName);
_command.Parameters.AddWithValue("@TeamDescription", TeamsToAdd.TeamDescription);
_command.Parameters.AddWithValue("@PositionsAvaliable", TeamsToAdd.PositionsAvaliable);
_command.Parameters.AddWithValue("@PositionsTaken", TeamsToAdd.PositionsTaken);
//Connection Open
_connection.Open();
//Execute the command
_command.ExecuteNonQuery();
//set the bool to true
success = true;
}
}
}
catch
{
//set the bool to false since it failed
success = false;
}
return(success);
}
static void Main(string[] args)
{
//create a new instance of DataAccessLayer class
DataAccess data = new DataAccess();
DataAccess delete = new DataAccess();
//Creating new instances of the LogicLayer Class
TeamsLogic TLogic = new TeamsLogic();
StatsLogic SLogic = new StatsLogic();
string yes;
do
{
// create a console menu for the user
Console.WriteLine("What would you like to do?");
Console.WriteLine("(A) Add something");
Console.WriteLine("(D) Delete something");
Console.WriteLine("(U) update something");
Console.WriteLine("(V) View something");
Console.WriteLine("(L) Leave Team");
Console.WriteLine("(J) Join Team");
//String left "Empty"
string PST = "Empty";
//create a variable for CRUD, Team
//ToUpper to Convert User Input to UpperCase
string CRUD = Console.ReadLine().ToUpper().Trim();
if (CRUD.Equals("L") && CRUD.Equals("J"))
{
//Only applies to the Teams Data
}
else
{
PST = whichtable();
}
bool success = false;
//create a switch case to determine which method will be applied
switch (CRUD)
{
//Adding Case
case "A":
switch (PST)
{
//"P" to add a Player to the DB
case "P":
success = data.AddPlayer(_mapper.Map(PlayerInfo()));
//indicate wheather a record was created based on the bool above
if (success)
{
Console.WriteLine("You have added a new Player!");
}
else
{
Console.WriteLine("Sorry, Something went wrong. No Player Was Added.");
}
break;
case "S":
PO_Stats NewStats = StatsInfo();
success = SLogic.AddStats(_LLMap.map(_mapper.Map(NewStats)));
//indicate wheather a record was created based on the bool above
if (success)
{
Console.WriteLine("You have added a new Stats!");
}
else
{
Console.WriteLine("Sorry, Something went wrong. No Stats Were Added.");
}
break;
case "T":
success = data.AddTeams(_mapper.Map(TeamsInfo()));
//indicate wheather a record was created based on the bool above
if (success)
{
Console.WriteLine("You have added a new Team!");
}
else
{
Console.WriteLine("Sorry, Something went wrong. No Team Was Added.");
}
break;
}
break;
//this case deletes
case "D":
switch (PST)
{
//this is to delete a book
case "P":
success = data.DeletePlayer(Name("PlayerName"));
if (success)
{
Console.WriteLine("The Player was successfully deleted.");
}
else
{
Console.WriteLine("Sorry, Something went wrong.");
}
break;
case "S":
success = data.DeleteStats(Name("PlayerName"));
if (success)
{
Console.WriteLine("The Stat was successfully deleted.");
}
else
{
Console.WriteLine("Sorry, Something went wrong.");
}
break;
case "T":
success = data.DeleteTeam(Name("Team"));
if (success)
{
Console.WriteLine("The Team was successfully deleted.");
}
else
{
Console.WriteLine("Sorry, Something went wrong.");
}
break;
}
break;
//this case updates
case "U":
switch (PST)
{
case "P":
PO_Player updatePlayer = PlayerInfo();
updatePlayer.PlayerName = Name("PlayerName");
success = data.UpdatePlayer(_mapper.Map(updatePlayer));
if (success)
{
Console.WriteLine("The Player was successfully Updated.");
}
else
{
Console.WriteLine("Sorry, Something went wrong.");
}
break;
case "S":
PO_Stats updateStats = StatsInfo();
updateStats.FKPlayerName = Name("PlayerName");
success = data.UpdateStats(_mapper.Map(updateStats));
if (success)
{
Console.WriteLine("The Stat was successfully Updated.");
}
else
{
Console.WriteLine("Sorry, Something went wrong.");
}
break;
case "T":
PO_Teams updateTeam = TeamsInfo();
updateTeam.TeamName = Name("TeamName");
success = data.UpdateTeams(_mapper.Map(updateTeam));
if (success)
{
Console.WriteLine("The Team was successfully Updated.");
}
else
{
Console.WriteLine("Sorry, Something went wrong.");
}
break;
}
break;
//this case Views
case "V":
switch (PST)
{
case "P":
List <PO_Player> PlayerToView = new List <PO_Player>();
PlayerToView = _mapper.Map(data.GetAllPlayers());
foreach (PO_Player singlePlayer in PlayerToView)
{
Console.WriteLine(singlePlayer.PlayerID + " | Player Name: " + singlePlayer.PlayerName + " | First Name: "
+ singlePlayer.PlayerFirstName + " | Last Name: " + singlePlayer.PlayerLastName +
" | City: " + singlePlayer.PlayerCity + " | State: " + singlePlayer.PlayerState + " | Age: " +
singlePlayer.PlayerAge);
}
break;
case "S":
List <PO_Stats> StatsToView = new List <PO_Stats>();
StatsToView = _mapper.Map(data.GetAllStats());
foreach (PO_Stats SingleStat in StatsToView)
{
Console.WriteLine(" | Player Name: " + SingleStat.FKPlayerName + " | Kills: " + SingleStat.Kills +
" | Deaths: " + SingleStat.Deaths + " | Average: " + SingleStat.Average);
}
break;
case "T":
List <PO_Teams> TeamsToView = new List <PO_Teams>();
TeamsToView = _mapper.Map(data.GetAllTeams());
foreach (PO_Teams SingleTeam in TeamsToView)
{
Console.WriteLine("|` Team name:" + SingleTeam.TeamName + "| Description: " + SingleTeam.TeamDescription +
"| Open Slots: " + SingleTeam.PositionsAvaliable + "| Filled Spots:" + SingleTeam.PositionsTaken +
"| Team Members:" + SingleTeam.FKPlayerName);
}
break;
}
break;
// this case checks in
case "L":
// Get TeamName to find what team to Leave
string LeaveTeam = Name("Teams");
TeamsDAO LeaveTeamDAO = data.viewSingleTeam(LeaveTeam);
// Makes sure there is is a position to leave
if (LeaveTeamDAO.PositionsTaken > 0)
{
success = TLogic._LeaveTeam(_LLMap.map(LeaveTeamDAO));
}
if (success)
{
Console.WriteLine("You have left the team.");
}
else
{
Console.WriteLine("Sorry,Something happend. You were unable to leave the team.");
}
break;
case "J":
// Get Team Name to find the team wanting to be joined
string JoinTeam = Name("Teams");
TeamsDAO JoinTeamDAO = data.viewSingleTeam(JoinTeam);
// Makes sure there is a position to join
if (JoinTeamDAO.PositionsAvaliable > 0)
{
success = TLogic._JoinTeam(_LLMap.map(JoinTeamDAO));
}
if (success)
{
Console.WriteLine("You have successfully Joined the Team! Welcome!.");
}
else
{
Console.WriteLine("Sorry The position you are trying to Join is unavaliable. You were Unable to Join the team.");
}
break;
}
Console.WriteLine("Do you wish to continue? Y/N");
yes = Console.ReadLine().ToUpper().Trim();
}while (yes.Equals("Y"));
Console.ReadLine();
}