//Update Start Time and End Times for Practice
public void UpdatePractice(Practice practice)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_UpdatePractice", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@start", practice.StartTime);
command.Parameters.AddWithValue("@end", practice.EndTime);
command.Parameters.AddWithValue("@id", practice.PracticeID);
command.Parameters.AddWithValue("@type", practice.PracticeType);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Update Teams using Team common objects passes all values
public void UpdateTeam(Team team)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_UpdateTeam", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@cap", team.SalaryCap);
command.Parameters.AddWithValue("@name", team.TeamName);
command.Parameters.AddWithValue("@type", team.TeamType);
command.Parameters.AddWithValue("@wins", team.Wins);
command.Parameters.AddWithValue("@losses", team.Losses);
command.Parameters.AddWithValue("@id", team.TeamID);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
public bool DeleteRoles(string name)
{
//store the name of the Role into the Data Base
List <Roles> getRoles = GetRoles();
Roles role = getRoles.Find(m => m.RoleType == name);
//Update the User with the Role and the Name provided
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_DeleteRole", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@id", role.RoleID);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(true);
}
//Method that adds users to the Database without team
//Create Users
public void InsertUser(Users insert)
{
insert.FullName = insert.FirstName + " " + insert.LastName;
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_userRegister", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@fullname", insert.FullName.ToString());
command.Parameters.AddWithValue("@address", insert.Address.ToString());
command.Parameters.AddWithValue("@email", insert.Email.ToString());
command.Parameters.AddWithValue("@phone", insert.Phone.ToString());
command.Parameters.AddWithValue("@username", insert.UserName.ToString());
command.Parameters.AddWithValue("@password", insert.Password.ToString());
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception except)
{
ExeceptionDataAccess ex = new ExeceptionDataAccess();
ex.StoreExceptions(except);
}
}
public List <GameAttendance> getGameAttendance()
{
//make a list to store Games Attendance
List <GameAttendance> getAttended = new List <GameAttendance>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_AllAttendance", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
GameAttendance attendance = new GameAttendance();
if (reader["gameID"] != DBNull.Value)
{
attendance.GameID = (int)reader["gameID"];
}
else
{
attendance.GameID = GameAttendance.Null.GameID;
}
if (reader["userID_fk"] != DBNull.Value)
{
attendance.UserID = (int)reader["userID_fk"];
}
else
{
attendance.UserID = GameAttendance.Null.UserID;
}
if (reader["attended"] != DBNull.Value)
{
attendance.Attended = (bool)reader["attended"];
}
else
{
attendance.Attended = GameAttendance.Null.Attended;
}
getAttended.Add(attendance);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getAttended);
}
//Update Player Stats
public void UpdatePlayerStats(PlayerStats player)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_UpdatePlayerStats", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@sid", player.StatID);
command.Parameters.AddWithValue("@uid", player.UserID);
command.Parameters.AddWithValue("@gid", player.GameID);
command.Parameters.AddWithValue("@points", player.Points);
command.Parameters.AddWithValue("@assists", player.Assists);
command.Parameters.AddWithValue("@rebounds", player.Rebounds);
command.Parameters.AddWithValue("@misses", player.Misses);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Update Contracts By Id
public bool UpdateContract(Contracts contract)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_UpdateContract", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@type", contract.ContractType);
command.Parameters.AddWithValue("@salary", contract.Salary);
command.Parameters.AddWithValue("@id", contract.ContractID);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
return(false);
}
return(true);
}
//Read Games
public List <Game> GetGames()
{
//make a list to store Games
List <Game> getGames = new List <Game>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_GetGames", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Game game = new Game();
game.GameID = (int)reader["gameID"];
if (reader["start_time"] != DBNull.Value)
{
game.StartTime = (DateTime)reader["start_time"];
}
if (reader["end_time"] != DBNull.Value)
{
game.EndTime = (DateTime)reader["end_time"];
}
if (reader["hometeam_fk"] != DBNull.Value)
{
game.HomeTeam = (int)reader["hometeam_fk"];
}
if (reader["awayteam_fk"] != DBNull.Value)
{
game.AwayTeam = (int)reader["awayteam_sk"];
}
if (reader["hometeam_score"] != DBNull.Value)
{
game.HomeTeamScore = (int)reader["hometeam_score"];
}
if (reader["awayteam_score"] != DBNull.Value)
{
game.AwayTeamScore = (int)reader["awayteam_score"];
}
getGames.Add(game);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getGames);
}
//Method that Updates allfields
public bool UpdateUser(Users user)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("UpdateUsers", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 30;
command.Parameters.AddWithValue("@uid", user.UserID);
command.Parameters.AddWithValue("@tid", user.TeamID);
command.Parameters.AddWithValue("@cid", user.ContractID);
command.Parameters.AddWithValue("@full", user.FullName);
command.Parameters.AddWithValue("@usermod", user.UserModified);
command.Parameters.AddWithValue("@role", user.RoleID);
command.Parameters.AddWithValue("@address", user.Address);
if (user.Email is null)
{
command.Parameters.AddWithValue("@email", Users.Null.Email);
}
else
{
command.Parameters.AddWithValue("@email", user.Email);
}
command.Parameters.AddWithValue("@phone", user.Phone);
command.Parameters.AddWithValue("@username", user.UserName);
command.Parameters.AddWithValue("@pass", user.Password);
command.Parameters.AddWithValue("@injury", user.InjuryStatus);
if (user.InjuryDescription is null)
{
command.Parameters.AddWithValue("@injurydesc", Users.Null.InjuryDescription);
}
else
{
command.Parameters.AddWithValue("@injurydesc", user.InjuryDescription);
}
command.Parameters.AddWithValue("@time", user.ContractDuration);
command.Parameters.AddWithValue("@start", user.ContractStart);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
return(false);
}
return(true);
}
//Create Practice
public void CreatePractice(Practice practice)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_addpractice", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@type", practice.PracticeType);
if (practice.StartTime != DateTime.MinValue)
{
command.Parameters.AddWithValue("@start", practice.StartTime);
}
else
{
command.Parameters.AddWithValue("@start", DateTime.Now);
}
if (practice.StartTime != DateTime.MinValue)
{
command.Parameters.AddWithValue("@end", practice.EndTime);
}
else
{
command.Parameters.AddWithValue("@end", DateTime.Now);
}
if (practice.TeamID != int.MinValue)
{
command.Parameters.AddWithValue("@tid", practice.TeamID);
}
else
{
}
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Method that checks User Access Rights
public Roles CheckRoleAccess(Users user)
{
Roles getRoleAccess = new Roles();
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_checkroleaccessbyname", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@name", user.FullName);
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["role_type"] != DBNull.Value)
{
getRoleAccess.RoleType = (string)reader["role_type"];
}
else
{
getRoleAccess.RoleType = "";
}
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getRoleAccess);
}
//Delete Player Stats
public void DeletePlayerStats(int id)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_DeleteStats", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@sid", id);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Method that can insert Roles
public bool InsertRole(Roles role)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_addroles", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@type", role.RoleType);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(true);
}
//Method that deletes players by name
public void DeleteUserByName(Users user)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_DeleteByName", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@fullname", user.FullName);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Delete Teams using Team common objects passes in team name only
public void DeleteTeam(Team team)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("DeleteTeam", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@name", team.TeamName);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Store all contracts in the database in a list
public List <Contracts> GetContracts()
{
//make a list to store contracts
List <Contracts> getContracts = new List <Contracts>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_GetContracts", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Contracts contract = new Contracts();
contract.ContractID = reader["contractID"] != DBNull.Value?(int)reader["contractID"]:Contracts.Null.ContractID;
contract.ContractType = reader["contract_type"] != DBNull.Value?(string)reader["contract_type"]:Contracts.Null.ContractType;
contract.Salary = reader["salary"] != DBNull.Value?(decimal)reader["salary"]:Contracts.Null.Salary;
getContracts.Add(contract);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getContracts);
}
public void CreatePracticeAttendance(PracticeAttended practiceAttended)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("CreatePracticeAttendance", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@uid", practiceAttended.UserID);
command.Parameters.AddWithValue("@pid", practiceAttended.PracticeID);
command.Parameters.AddWithValue("@attend", practiceAttended.Attended);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
//Update Game Store
public void UpdateGameScore(Game game)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_UpdateGameScore", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@home", game.HomeTeamScore);
command.Parameters.AddWithValue("@away", game.AwayTeamScore);
command.Parameters.AddWithValue("@id", game.GameID);
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
public List <Roles> GetRoles()
{
//make a list to store roles
List <Roles> getRoles = new List <Roles>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_ViewRoles", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Roles role = new Roles();
role.RoleID = (int)reader["roleID"];
role.RoleType = (string)reader["role_type"];
getRoles.Add(role);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getRoles);
}
//Get A List of Players Stats
public List <PlayerStats> GetPlayerStats()
{
//make a list to store Players Stats
List <PlayerStats> getStats = new List <PlayerStats>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_ViewStats", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
PlayerStats player = new PlayerStats();
player.StatID = (int)reader[statID];
if (reader[userID] != DBNull.Value)
{
player.UserID = (int)reader[userID];
}
else
{
}
if (reader[gameID] != DBNull.Value)
{
player.GameID = (int)reader[gameID];
}
else
{
}
if (reader[points] != DBNull.Value)
{
player.Points = (int)reader[points];
}
else
{
}
if (reader[assists] != DBNull.Value)
{
player.Assists = (int)reader[assists];
}
else
{
}
if (reader[rebounds] != DBNull.Value)
{
player.Rebounds = (int)reader[rebounds];
}
else
{
}
if (reader[misses] != DBNull.Value)
{
player.Misses = (int)reader[misses];
}
else
{
}
if (reader[teamID] != DBNull.Value)
{
player.TeamID = (int)reader[teamID];
}
else
{
}
getStats.Add(player);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getStats);
}
//Method that retrieves all Users
//Read Users
public List <Users> GetUsers()
{
List <Users> getUsers = new List <Users>();
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_GetUsers", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
//Add to objects send to list
Users users = new Users();
users.UserID = (int)reader["userID"];
users.FullName = reader["full_name"] != DBNull.Value?(string)reader["full_name"]:Users.Null.FirstName;
users.Address = reader["address"] != DBNull.Value?(string)reader["address"]:Users.Null.Address;
if (reader["email"] != DBNull.Value)
{
users.Email = (string)reader["email"];
}
else
{
users.Email = Users.Null.Email;
}
if (reader["phone"] != DBNull.Value)
{
users.Phone = (string)reader["phone"];
}
else
{
users.Phone = Users.Null.Phone;
}
if (reader["teamID_fk"] != DBNull.Value)
{
users.TeamID = (int)reader["teamID_fk"];
}
else
{
users.TeamID = Users.Null.TeamID;
}
if (reader["role_id"] != DBNull.Value)
{
users.RoleID = (int)reader["role_id"];
}
//set to default null values if db returns null
else
{
users.RoleID = Users.Null.RoleID;
}
if (reader["contractID_fk"] != DBNull.Value)
{
users.ContractID = (int)reader["contractID_fk"];
}
else
{
users.ContractID = Users.Null.ContractID;
}
if (reader["user_modified_by_fk"] != DBNull.Value)
{
users.UserModified = (int)reader["user_modified_by_fk"];
}
else
{
users.UserModified = Users.Null.UserModified;
}
if (reader["injury_status"] != DBNull.Value)
{
users.InjuryStatus = (bool)reader["injury_status"];
}
else
{
users.InjuryStatus = Users.Null.InjuryStatus;
}
if (reader["injurydesc"] != DBNull.Value)
{
users.InjuryDescription = (string)reader["injurydesc"];
}
if (reader["username"] != DBNull.Value)
{
users.UserName = (string)reader["username"];
}
else
{
users.UserName = Users.Null.UserName;
}
if (reader["password"] != DBNull.Value)
{
users.Password = (string)reader["password"];
}
else
{
users.Password = Users.Null.Password;
}
if (reader["contract_duration"] != DBNull.Value)
{
users.ContractDuration = (int)reader["contract_duration"];
}
else
{
users.ContractDuration = Users.Null.ContractDuration;
}
if (reader["contract_start"] != DBNull.Value)
{
users.ContractStart = (DateTime)reader["contract_start"];
}
else
{
users.ContractStart = Users.Null.ContractStart;
}
getUsers.Add(users);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getUsers);
}
public List <PracticeAttended> getPracticeAttendaned(int id)
{
//make a list to store Games Attendance
List <PracticeAttended> getAttended = new List <PracticeAttended>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_ViewPracticeAttendance", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
command.Parameters.AddWithValue("@id", id);
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
PracticeAttended attendance = new PracticeAttended();
if (reader["userID_fk"] != DBNull.Value)
{
attendance.UserID = (int)reader["userID_fk"];
}
else
{
attendance.UserID = PracticeAttended.Null.UserID;
}
if (reader["practiceID"] != DBNull.Value)
{
attendance.PracticeID = (int)reader["practiceID"];
}
else
{
attendance.PracticeID = PracticeAttended.Null.PracticeID;
}
if (reader["attended"] != DBNull.Value)
{
attendance.Attended = (bool)reader["attended"];
}
else
{
attendance.Attended = PracticeAttended.Null.Attended;
}
getAttended.Add(attendance);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getAttended);
}
//Get a List of Practices
public List <Practice> GetPractice()
{
//make a list to store Practice
List <Practice> getPractice = new List <Practice>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_GetPractice", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Practice practice = new Practice();
practice.PracticeID = (int)reader["practiceID"];
if (reader["practice_type"] == DBNull.Value)
{
practice.PracticeType = "No Practice Recorded";
}
else
{
practice.PracticeType = (string)reader["practice_type"];
}
if (reader["start_time"] == DBNull.Value)
{
}
else
{
practice.StartTime = (DateTime)reader["start_time"];
}
if (reader["end_time"] == DBNull.Value)
{
}
else
{
practice.EndTime = (DateTime)reader["end_time"];
}
if (reader["teamID_fk"] == DBNull.Value)
{
}
else
{
practice.TeamID = (int)reader["teamID_fk"];
}
getPractice.Add(practice);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getPractice);
}
//Uses the stored procedure Get teams to get the current Teams stored in the database
public List <Team> GetTeams()
{
//make a list to store roles
List <Team> getTeams = new List <Team>();
//try to populate the list
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("sp_GetTeams", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 30;
con.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Team team = new Team();
if (reader["teamID"] != DBNull.Value)
{
team.TeamID = (int)reader["teamID"];
}
else
{
team.TeamID = Team.Null.TeamID;
}
team.SalaryCap = reader["salary_cap"] != DBNull.Value?(decimal)reader["salary_cap"]:Team.Null.SalaryCap;
team.TeamName = reader["team_name"] != DBNull.Value?(string)reader["team_name"]:Team.Null.TeamName;
team.TeamType = reader["team_type"] != DBNull.Value?(string)reader["team_type"]:Team.Null.TeamType;
if (reader["wins"] != DBNull.Value)
{
team.Wins = (int)reader["wins"];
}
else
{
team.Wins = Team.Null.Wins;
}
if (reader["losses"] != DBNull.Value)
{
team.Losses = (int)reader["losses"];
}
else
{
team.Losses = Team.Null.Losses;
}
getTeams.Add(team);
}
}
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
return(getTeams);
}
//Create Games
public void CreateGame(Game game)
{
try
{
using (SqlConnection con = new SqlConnection(Connection))
{
using (SqlCommand command = new SqlCommand("CreateGame", con))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 10;
if (game.StartTime != DateTime.MinValue)
{
command.Parameters.AddWithValue("@start", game.StartTime);
}
else
{
command.Parameters.AddWithValue("@start", DateTime.Now);
}
if (game.EndTime != DateTime.MinValue)
{
command.Parameters.AddWithValue("@end", game.EndTime);
}
else
{
command.Parameters.AddWithValue("@end", DateTime.Now);
}
if (game.HomeTeam != int.MinValue)
{
command.Parameters.AddWithValue("@ht", game.HomeTeam);
}
else
{
command.Parameters.AddWithValue("@ht", 0);
}
if (game.AwayTeam != int.MinValue)
{
command.Parameters.AddWithValue("@at", game.AwayTeam);
}
else
{
command.Parameters.AddWithValue("@at", 0);
}
if (game.HomeTeamScore != int.MinValue)
{
command.Parameters.AddWithValue("@hts", game.HomeTeamScore);
}
else
{
command.Parameters.AddWithValue("@hts", 0);
}
if (game.HomeTeamScore != int.MinValue)
{
command.Parameters.AddWithValue("@ats", game.AwayTeamScore);
}
else
{
command.Parameters.AddWithValue("@ats", 0);
}
con.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
ExeceptionDataAccess exception = new ExeceptionDataAccess();
exception.StoreExceptions(ex);
}
}
