//Create
public int?Add(DivisionDTO entity)
{
try
{
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO division (DivisionName,DivisionDescription) VALUES(?DivisionName, " +
"?DivisionDescription)", conn);
//values
//cmd.Parameters.AddWithValue("DivisionID", entity.DivisionID ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("DivisionName", entity.DivisionName ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("DivisionDescription", entity.DivisionDescription ?? (object)DBNull.Value);
//execute command
int rowsAffected = cmd.ExecuteNonQuery();
AddTeamsToDivision(entity, conn);
//should return if a row is affected or not
return(rowsAffected);
}
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
//Update
public int?Update(DivisionDTO entity)
{
try
{
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("UPDATE division SET DivisionName = ?DivisionName," +
"DivisionDescription = ?DivisionDescription WHERE DivisionID = ?id", conn);
//where id is
cmd.Parameters.AddWithValue("id", entity.DivisionID);
//values
cmd.Parameters.AddWithValue("DivisionName", entity.DivisionName ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("DivisionDescription", entity.DivisionDescription ?? (object)DBNull.Value);
//execute command
int rowsAffected = cmd.ExecuteNonQuery();
//should return if a row is affected or not
return(rowsAffected);
}
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
public IHttpActionResult Put(DivisionDTO divisionDto)
{
if (!ModelState.IsValid)
{
return(BadRequest("Not a valid model"));
}
DivisionService.Put(divisionDto);
return(Ok());
}
public IHttpActionResult Get(int id)
{
DivisionDTO division = DivisionService.GetDivision(id);
if (division == null)
{
return(NotFound());
}
return(Ok(division));
}
public void Create(DivisionDTO divisionDTO)
{
using (var db = new ModelContext())
{
Division division = db.Divisions.Create();
division.Name = divisionDTO.Name;
db.Divisions.Add(division);
db.SaveChanges();
}
}
public DivisionDTO GetDivision(DivisionDTO division)
{
var divisions = DivisionDAL.GetDivision(Map <Division>(division));
if (divisions == null)
{
return(null);
}
return(Map <DivisionDTO>(divisions));
}
public void Put(DivisionDTO divisionDto)
{
using (var db = new ModelContext())
{
Division existingDivision = db.Divisions
.Where(m => m.Id == divisionDto.Id)
.FirstOrDefault();
if (existingDivision != null)
{
existingDivision.Name = divisionDto.Name;
db.SaveChanges();
}
}
}
/*Returns List of all Division Objects*/
public List <DivisionDTO> GetAllDivisions()
{
con.Open();
List <DivisionDTO> _divisions = new List <DivisionDTO>();
SqlCommand sql = new SqlCommand("Select * from Division", con);
SqlDataReader data = sql.ExecuteReader();
while (data.Read())
{
DivisionDTO div = new DivisionDTO();
div.DivisionId = data.GetInt32(0);
div.DivisionName = data.GetString(1);
_divisions.Add(div);
}
con.Close();
return(_divisions);
}
//Methods for division teams
private static void AddTeamsToDivision(DivisionDTO entity, MySqlConnection conn)
{
//build a string with all the values in it. no mysql.escapestring needed because integers cannot be used for SQL injection
StringBuilder sCommand = new StringBuilder("INSERT INTO divisionteam (DivisionID, TeamID) VALUES ");
List <string> Rows = new List <string>();
foreach (var team in entity.DivisionTeamIDs)
{
Rows.Add(string.Format("('{0}','{1}')", entity.DivisionID, team));
}
sCommand.Append(string.Join(",", Rows));
sCommand.Append(";");
MySqlCommand addTeamsToDivisionCmd = new MySqlCommand(sCommand.ToString(), conn);
addTeamsToDivisionCmd.ExecuteNonQuery();
}
public DivisionDTO GetByID(int?id)
{
try
{
DivisionDTO division = new DivisionDTO();
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT DivisionID,DivisionName,DivisionDescription FROM division WHERE DivisionID = ?id", conn);
cmd.Parameters.AddWithValue("id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
division.DivisionID = reader[0] as int? ?? default;
division.DivisionName = reader[1] as string ?? default;
division.DivisionDescription = reader[2] as string ?? default;
}
}
//get the teams that are in the division and add them in a list
MySqlCommand GetDivisionTeamsCmd = new MySqlCommand("SELECT TeamID FROM team WHERE TeamDivisionID = ?id", conn);
GetDivisionTeamsCmd.Parameters.AddWithValue("id", id);
List <int?> teamList = new List <int?>();
using (var reader = GetDivisionTeamsCmd.ExecuteReader())
{
while (reader.Read())
{
teamList.Add(reader[0] as int? ?? default);
}
}
division.DivisionTeamIDs = teamList;
}
return(division);
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
public List <DivisionDTO> GetAll()
{
using (var db = new ModelContext())
{
List <Division> divisions = db.Divisions.ToList();
List <DivisionDTO> dtos = new List <DivisionDTO>();
foreach (var division in divisions)
{
DivisionDTO dto = new DivisionDTO();
dto.Id = division.Id;
dto.Name = division.Name;
dto.SubDivisionsIds = division.SubDivisions
.Select(m => m.Id)
.ToList();
dtos.Add(dto);
}
return(dtos);
}
}
//Delete
public int?Remove(DivisionDTO entity)
{
try
{
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("DELETE FROM divsion WHERE DivisionID = ?id", conn);
//where id =
cmd.Parameters.AddWithValue("id", entity.DivisionID);
//execute command
int rowsAffected = cmd.ExecuteNonQuery();
//should return if a row is affected or not
return(rowsAffected);
}
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
public async Task <int> UpsertDivisionAsync(DivisionDTO division)
{
try
{
var context = new EmployeeContext();
var entity = AutoMapperConfiguration.Mapper.Map <Division>(division);
var result = context.Divisions.Find(division.DivisionId);
if (result != null)
{
result.Description = entity.Description;
result.DivisionInfoId = entity.DivisionInfoId;
result.DivisionType = entity.DivisionType;
result.Name = entity.Name;
context.Entry(result).State = EntityState.Modified;
context.Entry(result.DivisionType).State = EntityState.Modified;
return(await context.SaveChangesAsync());
}
else
{
context.Divisions.Attach(entity);
context.Divisions.Add(entity);
var divisionId = await context.SaveChangesAsync();
if (divisionId > 0)
{
return(await Task.FromResult(entity.DivisionId));
}
else
{
return(await Task.FromResult(0));
}
}
}
catch (Exception ex)
{
PrintException(ex);
throw new FaultException(ex.Message, new FaultCode("UpsertDivisionAsync"), "UpsertDivisionAsync");
}
}
public HttpResponseMessage Update(HttpRequestMessage request, DivisionDTO item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}
var dbObject = Mapper.Map <Division>(item);
if (_instituteRepository.GetById(dbObject.InstituteId) == null)
{
return(request.CreateErrorResponse(
HttpStatusCode.PreconditionFailed,
string.Format("Not found institute with id: {0}", dbObject.InstituteId)));
}
_divisionRepository.Update(dbObject);
_db.SaveChanges();
var mapped = Mapper.Map <DivisionDTO>(dbObject);
return(request.CreateResponse(HttpStatusCode.NoContent, mapped));
}
public void DeleteDivisions(DivisionDTO divisions)
{
DivisionDAL.DeleteDivisions(Map <Division>(divisions));
}
public void UpdateDivisions(DivisionDTO divisions)
{
DivisionDAL.UpdateDivisions(Map <Division>(divisions));
}
private Team BuildTeamModel(TeamDTO tDTO)
{
//team members
PlayerDTO CaptainDTO = _playerContext.GetByID(tDTO.TeamCaptainID) ?? new PlayerDTO();
PlayerDTO tm2DTO = _playerContext.GetByID(tDTO.TeamMember2ID) ?? new PlayerDTO();
PlayerDTO tm3DTO = _playerContext.GetByID(tDTO.TeamMember3ID) ?? new PlayerDTO();
PlayerDTO tm4DTO = _playerContext.GetByID(tDTO.TeamMember4ID) ?? new PlayerDTO();
PlayerDTO tm5DTO = _playerContext.GetByID(tDTO.TeamMember5ID) ?? new PlayerDTO();
//get player role with the role context
var roles = _roleContext.GetAll();
var cRole = roles.Where(i => i.RoleID == CaptainDTO.PlayerRoleID).FirstOrDefault() ?? new RoleDTO();
var t2Role = roles.Where(i => i.RoleID == tm2DTO.PlayerRoleID).FirstOrDefault() ?? new RoleDTO();
var t3Role = roles.Where(i => i.RoleID == tm3DTO.PlayerRoleID).FirstOrDefault() ?? new RoleDTO();
var t4Role = roles.Where(i => i.RoleID == tm4DTO.PlayerRoleID).FirstOrDefault() ?? new RoleDTO();
var t5Role = roles.Where(i => i.RoleID == tm5DTO.PlayerRoleID).FirstOrDefault() ?? new RoleDTO();
//create list of team members excluding the captain
List <Player> teamMembers = new List <Player>
{
new Player {
PlayerID = tm2DTO.PlayerID, PlayerName = tm2DTO.PlayerName, PlayerPlatformID = tm2DTO.PlayerPlatformID, PlayerRole = new Role {
RoleID = t2Role.RoleID, RoleName = t2Role.RoleName, RoleDescription = t2Role.RoleDescription
}
},
new Player {
PlayerID = tm3DTO.PlayerID, PlayerName = tm3DTO.PlayerName, PlayerPlatformID = tm3DTO.PlayerPlatformID, PlayerRole = new Role {
RoleID = t3Role.RoleID, RoleName = t3Role.RoleName, RoleDescription = t3Role.RoleDescription
}
},
new Player {
PlayerID = tm4DTO.PlayerID, PlayerName = tm4DTO.PlayerName, PlayerPlatformID = tm4DTO.PlayerPlatformID, PlayerRole = new Role {
RoleID = t4Role.RoleID, RoleName = t4Role.RoleName, RoleDescription = t4Role.RoleDescription
}
},
new Player {
PlayerID = tm5DTO.PlayerID, PlayerName = tm5DTO.PlayerName, PlayerPlatformID = tm5DTO.PlayerPlatformID, PlayerRole = new Role {
RoleID = t5Role.RoleID, RoleName = t5Role.RoleName, RoleDescription = t5Role.RoleDescription
}
},
};
//TeamDivision
DivisionDTO div = _divisionContext.GetByID(tDTO.TeamDivisionID) ?? new DivisionDTO(); //could do something with the division
Team t = new Team
{
TeamID = tDTO.TeamID,
TeamName = tDTO.TeamName,
TeamLogo = tDTO.TeamLogo,
TeamCaptain = new Player {
PlayerID = CaptainDTO.PlayerID, PlayerName = CaptainDTO.PlayerName, PlayerPlatformID = CaptainDTO.PlayerPlatformID, PlayerRole = new Role {
RoleID = cRole.RoleID, RoleName = cRole.RoleName, RoleDescription = cRole.RoleDescription
}
},
TeamMembers = teamMembers,
TeamDivision = new Division {
DivisionID = div.DivisionID, DivisionName = div.DivisionName
},
};
return(t);
}