public static Team LoadTeamByKey(string teamKey, int year, Db database)
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetTeamIDFromFranchiseID");
sp.Parameters = new object[] { teamKey };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
string[] franchiseName = result.DataTable.Rows[0][1].ToString().Split(' ');
ValueTuple <string, string> parts = Team.GetTeamNameParts(franchiseName);
string teamName = parts.Item1;
string teamMascot = parts.Item2;
Team team = new Team(teamName, teamMascot, year);
Roster roster = null;
Coach coach = null;
//Load roster
//Batting, Pitching, and Fielding stats are loading with the roster
roster = LoadRosterFromDatabase(team, teamKey, year, database);
team.Roster = roster;
//Load manager/coach
coach = LoadCoachFromDatabase(teamKey, year, database);
team.Coach = coach;
//TODO: Load uniforms
// LoadUniforms(ref roster, directory);
return(team);
}
public static Team LoadTeam(string teamName, string mascot, int year, Db database)
{
Team team = new Team(teamName, mascot, year);
Roster roster = null;
Coach coach = null;
SQLStoredProcedure sp = StoredProcedureManager.Get("GetTeamID");
sp.Parameters = new object[] { String.Concat(teamName, " ", mascot), year };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
string teamKey = result.DataTable.Rows[0][0].ToString();
//Load roster
//Batting, Pitching, and Fielding stats are loading with the roster
try
{
roster = LoadRosterFromDatabase(team, teamKey, year, database);
team.Roster = roster;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Load manager/coach
coach = LoadCoachFromDatabase(teamKey, year, database);
team.Coach = coach;
return(team);
}
public static Team LoadTeam(string teamName, int year, Db database)
{
Team team = null;
Roster roster = null;
Coach coach = null;
SQLStoredProcedure sp = StoredProcedureManager.Get("GetTeamID");
sp.Parameters = new object[] { teamName, year };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
string teamKey = result.DataTable.Rows[0][0].ToString();
//Load roster
//Batting, Pitching, and Fielding stats are loading with the roster
roster = LoadRosterFromDatabase(team, teamKey, year, database);
string teamMascot = string.Empty;
team = new Team(teamName, teamMascot, year);
team.Roster = roster;
//Load manager/coach
coach = LoadCoachFromDatabase(teamKey, year, database);
team.Coach = coach;
return(team);
}
/// <summary>
/// Loads the roster from database.
/// </summary>
/// <returns>Roster</returns>
/// <param name="team">Team</param>
/// <param name="teamKey">string</param>
/// <param name="year">int</param>
/// <param name="database">Db</param>
private static Roster LoadRosterFromDatabase(Team team, string teamKey, int year, Db database)
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetPlayerInfo");
sp.Parameters = new object[] { teamKey, year };
return(new Roster(team, (Player[])new DatabaseRosterLoader(teamKey, year, database, sp.Text).Load()));
}
/// <summary>
/// Loads the coach from database.
/// </summary>
/// <returns>Coach</returns>
/// <param name="teamName">string</param>
/// <param name="year">int</param>
/// <param name="database">Db</param>
private static Coach LoadCoachFromDatabase(string teamName, int year, Db database)
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetCoachInfo");
sp.Parameters = new object[] { teamName, year };
return((Coach) new DatabaseCoachLoader(teamName, year, database, sp.Text).Load());
}
/// <summary>
/// Gets the series info.
/// </summary>
/// <returns>SQLQueryResult</returns>
/// <param name="year">int</param>
/// <param name="database">Db</param>
public static SQLQueryResult GetSeriesInfo(int year, Db database)
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetChampionshipSeriesInfo");
sp.Parameters = new object[] { year };
return(database.ExecuteQuery(sp.Text));
}
public void TestDatabaseRosterLoader()
{
database = new Db(conn);
SQLStoredProcedure sql = StoredProcedureManager.Get("GetPlayerInfo");
sql.Parameters = new object[] { "NYA", 2001 };
Player[] players = (Player[])new DatabaseRosterLoader("NYA", 2001, database, sql.Text).Load();
Roster roster = new Roster(null, players);
}
/// <summary>
/// Gets the series teams.
/// </summary>
/// <returns>Tuple(string,string)</returns>
/// <param name="year">int</param>
/// <param name="round">string</param>
/// <param name="database">Db</param>
public static Tuple <string, string> GetSeriesTeams(int year, string round, Db database)
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetChampionshipSeriesTeams");
sp.Parameters = new object[] { year, round };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
string roadTeamKey = result.DataTable.Rows[0][2].ToString();
string homeTeamKey = result.DataTable.Rows[0][3].ToString();
return(new Tuple <string, string>(roadTeamKey, homeTeamKey));
}
public void TestStoredProcedureTextTwoParameters()
{
int EXPECTED_ROWS = 20;
database = new Db(conn);
SQLStoredProcedure sp = StoredProcedureManager.Get("GetPitcherInfo");
sp.Parameters = new object[] { "NYA", 2001 };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
Assert.IsTrue(result.RowsAffected == EXPECTED_ROWS);
}
/// <summary>
/// Helper class to load a roster from a database.
/// </summary>
/// <param name="teamName">string</param>
/// <param name="year">int</param>
/// <param name="database">Db</param>
/// <param name="sql">string</param>
public DatabaseRosterLoader(string teamName, int year, Db database, string sql) : base(teamName, year, database, sql)
{
SQLStoredProcedure sql1 = StoredProcedureManager.Get("GetBattingInfo");
sql1.Parameters = new object[] { teamName, year };
SQLStoredProcedure sql2 = StoredProcedureManager.Get("GetPitchingInfo");
sql2.Parameters = new object[] { teamName, year };
SQLStoredProcedure sql3 = StoredProcedureManager.Get("GetFieldingInfo");
sql3.Parameters = new object[] { teamName, year };
this.dataTables = (Dictionary <string, DataTable>) new DatabasePlayerStatisticsLoader(this.teamName, this.year, database, string.Empty, sql1, sql2, sql3).Load();
}
public void TestStoredProcedureMultipleQueries()
{
int EXPECTED_ROWS = 47;
database = new Db(conn);
SQLStoredProcedure sp = StoredProcedureManager.Get("GetPlayerInfo");
sp.Parameters = new object[] { "NYA", 2001 };//two params but for instances of ? placeholder
SQLQueryResult result = database.ExecuteQuery(sp.Text);
Console.WriteLine(sp.Text);
Assert.IsTrue(result.RowsAffected == EXPECTED_ROWS);
}
/// <summary>
/// Gets the multiple keys.
/// </summary>
/// <returns>The multiple keys.</returns>
/// <param name="searchTerm">Search term.</param>
/// <param name="year">Year.</param>
/// <param name="database">Database.</param>
public static Dictionary <string, string> GetMultipleKeys(string searchTerm, int year, Db database)
{
Dictionary <string, string> retKeys = new Dictionary <string, string>();
SQLStoredProcedure sp = StoredProcedureManager.Get("GetFranchiseID");
sp.Parameters = new object[] { searchTerm, year };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
foreach (DataRow row in result.DataTable.Rows)
{
retKeys.Add(row[0].ToString(), row[1].ToString());
}
return(retKeys);
}
private void _SQLStoredProcedure(SqlInstances sqlInstances)
{
if (0 == databases.Count)
{
sD = new SQLDatabase(credentials);
sD.SetComputerName(sqlInstances.Server);
sD.SetInstance(sqlInstances.ServerInstance);
if (nodefaults)
{
sD.EnableNoDefaultsFilter();
}
if (hasAccess)
{
sD.EnableHasAccessFilter();
}
if (sysadmin)
{
sD.EnableSysAdminFilter();
}
sD.Query();
databases = sD.GetResults();
}
SQLStoredProcedure sP = new SQLStoredProcedure(credentials);
sP.SetComputerName(sqlInstances.Server);
sP.SetInstance(sqlInstances.ServerInstance);
if (!string.IsNullOrEmpty(ProcedureNameFilter))
{
sP.SetProcedureNameFilter(ProcedureNameFilter);
}
if (!string.IsNullOrEmpty(keywordFilter))
{
sP.SetKeywordFilter(keywordFilter);
}
if (AutoExecFilter)
{
sP.SetAutoExecFilter();
}
var results = new List <SQLStoredProcedure.AssemblyFiles>();
foreach (var d in databases)
{
sP.SetDatabase(d.DatabaseName);
sP.Query();
results.AddRange(sP.GetResults());
}
_PrintOutput(results);
}
/// <summary>
/// Loads the team group.
/// </summary>
/// <returns>TeamGroup</returns>
/// <param name="leagueName">string</param>
/// <param name="year">int</param>
/// <param name="database">Db</param>
/// <param name="seriesLength">int</param>
public static TeamGroupTree LoadRoot(string leagueName, int year, Db database, int seriesLength = 3)
{
TeamGroupTree league = null;
Dictionary <string, TeamGroup> divisions = new Dictionary <string, TeamGroup>();
SQLStoredProcedure sp = StoredProcedureManager.Get("GetDivisionInfo");
sp.Parameters = new object[] { leagueName, year };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
DataTable teamGroupInfo = result.DataTable;
string leagueID = teamGroupInfo.Rows[0]["lgID"].ToString();
foreach (DataRow row in teamGroupInfo.Rows)
{
string teamID = row["teamID"].ToString();
string teamName = row["name"].ToString();
string divID = row["divID"].ToString();
TeamGroup group = null;
Team team = null;
team = DatabaseTeamLoader.LoadTeam(teamName, year, database);
if (!divisions.ContainsKey(divID))
{
group = new TeamGroup(divID, divID);
divisions.Add(divID, group);
if (!group.Contains(team))
{
group.Add(team);
}
}
else
{
divisions[divID].Add(team);
}
Console.WriteLine($"Added {team} to division '{divID}'");
System.Threading.Thread.Sleep(150);
}
league = new TeamGroupTree(leagueID, leagueName, seriesLength);
foreach (TeamGroup division in divisions.Values)
{
league.Add(division);
Console.WriteLine($"Added division '{division.Name}' to league '{league.Name}'");
System.Threading.Thread.Sleep(150);
}
return(league);
}
/// <summary>
/// Loads the team.
/// </summary>
/// <returns>Team</returns>
/// <param name="searchTerm">string</param>
/// <param name="year">int</param>
/// <param name="database">Db</param>
public static Team LoadTeamFromTeamID(string searchTerm, int year, Db database)
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetFranchiseIDFromTeamID");
sp.Parameters = new object[] { searchTerm };
SQLQueryResult result = database.ExecuteQuery(sp.Text);
string franchKey = result.DataTable.Rows[0][0].ToString();
SQLStoredProcedure sp2 = StoredProcedureManager.Get("GetFranchiseInfo");
sp2.Parameters = new object[] { franchKey };
SQLQueryResult result2 = database.ExecuteQuery(sp2.Text);
string [] franchiseName = result2.DataTable.Rows[0][1].ToString().Split(' ');
ValueTuple <string, string> parts = Team.GetTeamNameParts(franchiseName);
string teamName = parts.Item1;
string teamMascot = parts.Item2;
Team team = new Team(teamName, teamMascot, year);
Roster roster = null;
Coach coach = null;
//Load roster
//Batting, Pitching, and Fielding stats are loading with the roster
try
{
roster = LoadRosterFromDatabase(team, searchTerm, year, database);
team.Roster = roster;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//Load manager/coach
try
{
coach = LoadCoachFromDatabase(searchTerm, year, database);
team.Coach = coach;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return(team);
}
public void TestDatabaseCoachLoaderDiamondbacks2001()
{
database = new Db(conn);
string teamKey = "ARI";
int year = 2001;
SQLStoredProcedure sp = StoredProcedureManager.Get("GetCoachInfo");
sp.Parameters = new object[] { teamKey, year };
Coach coach = (Coach) new DatabaseCoachLoader(teamKey, year, database, sp.Text).Load();
Assert.IsTrue(coach.FirstName == "Bob");
Assert.IsTrue(coach.LastName == "Brenly");
Assert.IsTrue(coach.Handedness == Handedness.Right);
Assert.IsTrue(coach.Height.ToString() == "6-2");
Assert.IsTrue(coach.Birthday.Year == 1954);
Console.WriteLine(coach);
//Dumper.Dump(coach);
}
/// <summary>
/// Load a coach from a database
/// </summary>
/// <returns>object</returns>
public override object Load()
{
PersonBasicInformation [] coachBasicInfo = (PersonBasicInformation[])new DatabasePersonLoader(this.teamName, this.year, this.database, this.sql).Load();
SQLStoredProcedure sp = StoredProcedureManager.Get("GetCoachAwards");
sp.Parameters = new object[] { coachBasicInfo[0].Id };
string[] awards = (string[])new DatabaseCoachingAwardsLoader(database, sp.Text).Load();
Coach ret = null;
ret = new Coach(coachBasicInfo[0], awards);
DataTable dt = this.database.ExecuteQuery(this.sql).DataTable;
SQLStoredProcedure spGetTeamCount = StoredProcedureManager.Get("GetTeamCountByYear");
spGetTeamCount.Parameters = new object[] { this.year };
DataTable dtTeamCount = this.database.ExecuteQuery(spGetTeamCount).DataTable;
int teamCount = Convert.ToInt32(dtTeamCount.Rows[0]["count"]);
int teamRank = Convert.ToInt32(dt.Rows[0]["teamRank"]);
int teamWins = Convert.ToInt32(dt.Rows[0]["W"]);
int teamLosses = Convert.ToInt32(dt.Rows[0]["L"]);
int rating = (int)(Constants.GetValueFromDouble(teamWins, teamWins + teamLosses) * 1.5);
if (rating > 100)
{
rating = 100;
}
int prestige = awards.Length * 30;
prestige = prestige > 100 ? 100 : prestige;
prestige = prestige == 0 ? rating / 2 : prestige;
int substitutionThreshold = Dice.Roll(10, 30);
int steal2nd = Dice.Roll(5, 10);
int steal3rd = Dice.Roll(1, 7);
int sacrificeBunt = Dice.Roll(1, 10);
int intentionalWalk = Dice.Roll(5, 10);
ret.CoachingStats = new CoachingStats(rating, prestige, steal2nd, steal3rd, sacrificeBunt, intentionalWalk, substitutionThreshold, teamWins, teamLosses);
return(ret);
}
public void TestDatabaseCoachLoaderYankees2001()
{
string teamKey = "NYA";
int year = 2001;
database = new Db(conn);
SQLStoredProcedure sp = StoredProcedureManager.Get("GetCoachInfo");
sp.Parameters = new object[] { teamKey, year };
Coach coach = (Coach) new DatabaseCoachLoader(teamKey, year, database, sp.Text).Load();
Assert.IsTrue(coach.FirstName == "Joe");
Assert.IsTrue(coach.LastName == "Torre");
Assert.IsTrue(coach.Handedness == Handedness.Right);
Assert.IsTrue(coach.Height.ToString() == "6-2");
Assert.IsTrue(coach.Birthday.Year == 1940);
Assert.IsTrue(coach.CoachingAwards.Length == 3);
Console.WriteLine(coach);
//Dumper.Dump(coach);
}
public void TestDatabasePlayerStatisticsLoader()
{
database = new Db(conn);
SQLStoredProcedure sql1 = StoredProcedureManager.Get("GetBattingInfo");
sql1.Parameters = new object[] { "NYA", 2001 };
SQLStoredProcedure sql2 = StoredProcedureManager.Get("GetPitchingInfo");
sql2.Parameters = new object[] { "NYA", 2001 };
SQLStoredProcedure sql3 = StoredProcedureManager.Get("GetFieldingInfo");
sql3.Parameters = new object[] { "NYA", 2001 };
Dictionary <string, DataTable> results = (Dictionary <string, DataTable>) new DatabasePlayerStatisticsLoader("NYA", 2001, database, string.Empty, new SQLStoredProcedure[] { sql1, sql2, sql3 }).Load();
Assert.IsTrue(results.Count == 3);
Assert.IsTrue(results.ContainsKey("Batting"));
Assert.IsTrue(results.ContainsKey("Fielding"));
}
public void TestStoredProcedureParameterExceptionThrown()
{
database = new Db(conn);
bool exceptionThrown = false;
try
{
SQLStoredProcedure sp = StoredProcedureManager.Get("GetPlayerInfo");
SQLQueryResult result = database.ExecuteQuery(sp.Text);
}
catch (Exception ex)
{
exceptionThrown = true;
Console.WriteLine(ex.Message);
}
finally
{
Assert.IsTrue(exceptionThrown);
}
}
protected StoredProcMeta BuildStoredProc(SQLStoredProcedure spDef, ref ArrayList grantList, ref ArrayList denyList)
{
if (ValidateVersion(spDef) == DDLActionEnum.NONE) // not in range!
{
return(null);
}
// Always treated as rebuild or create...
StoredProcMeta spTmp = new StoredProcMeta(spDef.ProcName, _context);
spTmp.DDLBodyText = spDef.ProcedureText.ToString();
foreach (SQLStoredProcedureParameter param in spDef.Parameters)
{
ParameterMeta pramMeta = new ParameterMeta(param.Name, param.DataType, param.ParameterDirection, _dtMap);
pramMeta.DataType.StringLength = param.StringLength;
spTmp.GetParameterList().Add(pramMeta);
}
SQLRoleAssignmentCollection roles = spDef.Roles;
foreach (SQLRoleAssignment roleAssign in roles)
{
GranteeMeta roleGrantee = new GranteeMeta(spDef.ProcName);
roleGrantee.GranteeList.Add(roleAssign.RoleName);
roleGrantee.ObjPermissions.Add(roleAssign.GrantAssignments);
grantList.Add(roleGrantee);
GranteeMeta roleDenied = new GranteeMeta(spDef.ProcName);
roleDenied.GranteeList.Add(roleAssign.RoleName);
roleDenied.ObjPermissions.Add(roleAssign.DenyAssignments);
denyList.Add(roleDenied);
}
return(spTmp);
}
