public bool updateTeam(MaddenTeamRankings.Teamstandinginfolist t, int iLeagueId)
{
StoredProc SP = new StoredProc();
SP.Name = "TeamStandings_update";
SP.DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SP.ParameterSet = new System.Data.SqlClient.SqlCommand();
SP.ParameterSet.Parameters.AddWithValue("@leagueId", iLeagueId);
SP.ParameterSet.Parameters.AddWithValue("@teamId", t.teamId);
SP.ParameterSet.Parameters.AddWithValue("@teamName", t.teamName);
SP.ParameterSet.Parameters.AddWithValue("@teamOvr", t.teamOvr);
SP.ParameterSet.Parameters.AddWithValue("@calendarYear", t.calendarYear);
SP.ParameterSet.Parameters.AddWithValue("@weekIndex", t.weekIndex);
SP.ParameterSet.Parameters.AddWithValue("@seasonIndex", t.seasonIndex);
SP.ParameterSet.Parameters.AddWithValue("@capRoom", t.capRoom);
SP.ParameterSet.Parameters.AddWithValue("@capSpent", t.capSpent);
SP.ParameterSet.Parameters.AddWithValue("@conferenceId", t.conferenceId);
SP.ParameterSet.Parameters.AddWithValue("@conferenceName", t.conferenceName);
SP.ParameterSet.Parameters.AddWithValue("@defPassYds", t.defPassYds);
SP.ParameterSet.Parameters.AddWithValue("@defPassYdsRank", t.defPassYdsRank);
SP.ParameterSet.Parameters.AddWithValue("@defRushYds", t.defRushYds);
SP.ParameterSet.Parameters.AddWithValue("@defRushYdsRank", t.defRushYdsRank);
SP.ParameterSet.Parameters.AddWithValue("@defTotalYds", t.defTotalYds);
SP.ParameterSet.Parameters.AddWithValue("@defTotalYdsRank", t.defTotalYdsRank);
SP.ParameterSet.Parameters.AddWithValue("@divisionId", t.divisionId);
SP.ParameterSet.Parameters.AddWithValue("@divisionName", t.divisionName);
SP.ParameterSet.Parameters.AddWithValue("@netPts", t.netPts);
SP.ParameterSet.Parameters.AddWithValue("@offPassYds", t.offPassYds);
SP.ParameterSet.Parameters.AddWithValue("@offRushYds", t.offRushYds);
SP.ParameterSet.Parameters.AddWithValue("@offTotalYds", t.offTotalYds);
SP.ParameterSet.Parameters.AddWithValue("@playoffStatus", t.playoffStatus);
SP.ParameterSet.Parameters.AddWithValue("@prevRank", t.prevRank);
SP.ParameterSet.Parameters.AddWithValue("@ptsAgainst", t.ptsAgainst);
SP.ParameterSet.Parameters.AddWithValue("@ptsFor", t.ptsFor);
SP.ParameterSet.Parameters.AddWithValue("@rank", t.rank);
SP.ParameterSet.Parameters.AddWithValue("@seed", t.seed);
SP.ParameterSet.Parameters.AddWithValue("@stageIndex", t.stageIndex);
SP.ParameterSet.Parameters.AddWithValue("@tODiff", t.tODiff);
SP.ParameterSet.Parameters.AddWithValue("@totalLosses", t.totalLosses);
SP.ParameterSet.Parameters.AddWithValue("@totalTies", t.totalTies);
SP.ParameterSet.Parameters.AddWithValue("@totalWins", t.totalWins);
SP.ParameterSet.Parameters.AddWithValue("@winLossStreak", t.winLossStreak);
SP.ParameterSet.Parameters.AddWithValue("@winPct", t.winPct);
var status = StoredProc.NonQuery(SP);
return(status);
}
/// <summary>
/// Pass the stored proc object to retrieve data
/// </summary>
public static DataSet ShowMeTheData(StoredProc SP)
{
var myData = new DataSet();
try
{
using (var conn = new SqlConnection(SP.DataConnectionString))
{
using (var command = new SqlCommand(SP.Name, conn))
{
conn.Open();
if (!SP.IsSqlCommand)
{
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter item in SP.ParameterSet.Parameters)
{
command.Parameters.AddWithValue(item.ParameterName, item.Value);
}
}
var da = new SqlDataAdapter(command);
da.Fill(myData);
}
}
}
catch (Exception ex)
{
var dt = new DataTable();
dt.Columns.Add("kjdStatus", typeof(string));
dt.Columns.Add("kjdMessage", typeof(string));
dt.Rows.Add("Error", ex.Message);
myData.Tables.Add(dt);
}
return(myData);
}
/// <summary>
/// use this for updates/deletes/inserts
/// </summary>
public static bool NonQuery(StoredProc sp)
{
var isSuccessful = false;
try
{
using (var conn = new SqlConnection(sp.DataConnectionString))
{
using (var command = new SqlCommand(sp.Name, conn))
{
conn.Open();
if (sp.IsSqlCommand == false)
{
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter item in sp.ParameterSet.Parameters)
{
command.Parameters.AddWithValue(item.ParameterName, item.Value);
}
}
var da = new SqlDataAdapter(command);
command.ExecuteNonQuery();
isSuccessful = true;
}
}
}
catch (Exception ex)
{
Console.WriteLine($"error {ex.Message}");
isSuccessful = false;
}
return(isSuccessful);
}
public bool UpdateSchedule(JSONSchedule.Gamescheduleinfolist i, int leagueId)
{
var sp = new StoredProc
{
Name = "Schedule_update", DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString, ParameterSet = new SqlCommand()
};
sp.ParameterSet.Parameters.AddWithValue("@leagueId", leagueId);
sp.ParameterSet.Parameters.AddWithValue("@scheduleId", i.scheduleId);
sp.ParameterSet.Parameters.AddWithValue("@seasonIndex", i.seasonIndex);
sp.ParameterSet.Parameters.AddWithValue("@weekIndex", i.weekIndex);
sp.ParameterSet.Parameters.AddWithValue("@awayScore", i.awayScore);
sp.ParameterSet.Parameters.AddWithValue("@awayTeamID", i.awayTeamId);
sp.ParameterSet.Parameters.AddWithValue("@homeScore", i.homeScore);
sp.ParameterSet.Parameters.AddWithValue("@homeTeamId", i.homeTeamId);
sp.ParameterSet.Parameters.AddWithValue("@isGameOfTheWeek", i.isGameOfTheWeek);
sp.ParameterSet.Parameters.AddWithValue("@status", i.status);
sp.ParameterSet.Parameters.AddWithValue("@stageIndex", i.stageIndex);
var status = StoredProc.NonQuery(sp);
return(status);
}
public PlayerAbilityRepository GetPlayerAbilities(int playerId)
{
var collection = new PlayerAbilityRepository();
var sp = new StoredProc
{
Name = "[GetPlayerAbilities]", DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString, ParameterSet = new SqlCommand()
};
sp.ParameterSet.Parameters.AddWithValue("@playerId", playerId);
var ds = StoredProc.ShowMeTheData(sp);
if (ds.Tables.Count == 0)
{
return(collection);
}
foreach (DataRow item in ds.Tables[0].Rows)
{
var a = new PlayerAbility
{
Id = Helper.IntegerNull(item["signatureLogoId"]),
OvrThreshold = Helper.IntegerNull(item["OvrThreshold"]),
IsEmpty = Helper.BooleanNull(item["isEmpty"]),
IsLocked = Helper.BooleanNull(item["isLocked"]),
Title = Helper.StringNull(item["SignatureTitle"]),
Description = Helper.StringNull(item["SignatureDescription"]),
ActivationDescription = Helper.StringNull(item["SignatureActivationDescription"]),
DeactivationDescription = Helper.StringNull(item["SignatureDeactivationDescription"])
};
collection.Add(a.Id, a);
}
return(collection);
}
public bool UpdateTeamStatsInfo(JSONTeamStats.Teamstatinfolist i, int iLeagueId)
{
var sp = new StoredProc
{
Name = "StatsTeam_update",
DataConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString,
ParameterSet = new System.Data.SqlClient.SqlCommand()
};
sp.ParameterSet.Parameters.AddWithValue("@leagueId", iLeagueId);
sp.ParameterSet.Parameters.AddWithValue("@weekIndex", i.weekIndex);
sp.ParameterSet.Parameters.AddWithValue("@stageIndex", i.stageIndex);
sp.ParameterSet.Parameters.AddWithValue("@statId", i.statId);
sp.ParameterSet.Parameters.AddWithValue("@seasonIndex", i.seasonIndex);
sp.ParameterSet.Parameters.AddWithValue("@seed", i.seed);
sp.ParameterSet.Parameters.AddWithValue("@teamId", i.teamId);
sp.ParameterSet.Parameters.AddWithValue("@scheduleId", i.scheduleId);
sp.ParameterSet.Parameters.AddWithValue("@defForcedFum", i.defForcedFum);
sp.ParameterSet.Parameters.AddWithValue("@defFumRec", i.defFumRec);
sp.ParameterSet.Parameters.AddWithValue("@defIntsRec", i.defIntsRec);
sp.ParameterSet.Parameters.AddWithValue("@defPassYds", i.defPassYds);
sp.ParameterSet.Parameters.AddWithValue("@defPtsPerGame", i.defPtsPerGame);
sp.ParameterSet.Parameters.AddWithValue("@defRedZoneFGs", i.defRedZoneFGs);
sp.ParameterSet.Parameters.AddWithValue("@defRedZonePct", i.defRedZonePct);
sp.ParameterSet.Parameters.AddWithValue("@defRedZoneTDs", i.defRedZoneTDs);
sp.ParameterSet.Parameters.AddWithValue("@defRedZones", i.defRedZones);
sp.ParameterSet.Parameters.AddWithValue("@defRushYds", i.defRushYds);
sp.ParameterSet.Parameters.AddWithValue("@defSacks", i.defSacks);
sp.ParameterSet.Parameters.AddWithValue("@defTotalYds", i.defTotalYds);
sp.ParameterSet.Parameters.AddWithValue("@off1stDowns", i.off1stDowns);
sp.ParameterSet.Parameters.AddWithValue("@off2PtAtt", i.off2PtAtt);
sp.ParameterSet.Parameters.AddWithValue("@off2PtConv", i.off2PtConv);
sp.ParameterSet.Parameters.AddWithValue("@off2PtConvPct", i.off2PtConvPct);
sp.ParameterSet.Parameters.AddWithValue("@off3rdDownAtt", i.off3rdDownAtt);
sp.ParameterSet.Parameters.AddWithValue("@off3rdDownConv", i.off3rdDownConv);
sp.ParameterSet.Parameters.AddWithValue("@off3rdDownConvPct", i.off3rdDownConvPct);
sp.ParameterSet.Parameters.AddWithValue("@off4thDownAtt", i.off4thDownAtt);
sp.ParameterSet.Parameters.AddWithValue("@off4thDownConv", i.off4thDownConv);
sp.ParameterSet.Parameters.AddWithValue("@off4thDownConvPct", i.off4thDownConvPct);
sp.ParameterSet.Parameters.AddWithValue("@offFumLost", i.offFumLost);
sp.ParameterSet.Parameters.AddWithValue("@offIntsLost", i.offIntsLost);
sp.ParameterSet.Parameters.AddWithValue("@offPassTDs", i.offPassTDs);
sp.ParameterSet.Parameters.AddWithValue("@offPassYds", i.offPassYds);
sp.ParameterSet.Parameters.AddWithValue("@offPtsPerGame", i.offPtsPerGame);
sp.ParameterSet.Parameters.AddWithValue("@offRedZoneFGs", i.offRedZoneFGs);
sp.ParameterSet.Parameters.AddWithValue("@offRedZonePct", i.offRedZonePct);
sp.ParameterSet.Parameters.AddWithValue("@offRedZoneTDs", i.offRedZoneTDs);
sp.ParameterSet.Parameters.AddWithValue("@offRedZones", i.offRedZones);
sp.ParameterSet.Parameters.AddWithValue("@offRushTDs", i.offRushTDs);
sp.ParameterSet.Parameters.AddWithValue("@offRushYds", i.offRushYds);
sp.ParameterSet.Parameters.AddWithValue("@offSacks", i.offSacks);
sp.ParameterSet.Parameters.AddWithValue("@offTotalYds", i.offTotalYds);
sp.ParameterSet.Parameters.AddWithValue("@offTotalYdsGained", i.offTotalYdsGained);
sp.ParameterSet.Parameters.AddWithValue("@penalties", i.penalties);
sp.ParameterSet.Parameters.AddWithValue("@penaltyYds", i.penaltyYds);
sp.ParameterSet.Parameters.AddWithValue("@tODiff", i.tODiff);
sp.ParameterSet.Parameters.AddWithValue("@tOGiveaways", i.tOGiveaways);
sp.ParameterSet.Parameters.AddWithValue("@tOTakeaways", i.tOTakeaways);
sp.ParameterSet.Parameters.AddWithValue("@totalLosses", i.totalLosses);
sp.ParameterSet.Parameters.AddWithValue("@totalTies", i.totalTies);
sp.ParameterSet.Parameters.AddWithValue("@totalWins", i.totalWins);
var status = StoredProc.NonQuery(sp);
return(status);
}
