public List<Build> GetBuildsForCharacter(int characterID)
{
List<Build> builds = new List<Build>();
string sql = "SELECT * FROM [Builds],[BuildsCharacters] WHERE [Builds].[id] = [BuildsCharacters].[buildID] AND [BuildsCharacters].[characterID] = " + characterID;
cmd.CommandText = sql;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
int buildID = Convert.ToInt32(dr["id"]);
string name = dr["name"].ToString();
string userName = dr["userName"].ToString();
Build b = new Build();
b.ID = buildID;
b.BuildName = name;
b.UserName = userName;
builds.Add(b);
}
}
dr.Close();
conn.Close();
return builds;
}
public Build GetRandomBuild()
{
string sqlNumberOfBuilds = "SELECT COUNT(*) FROM [Builds]";
cmd.CommandText = sqlNumberOfBuilds;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
int numberOfBuilds = 0;
if (dr.Read())
{
numberOfBuilds = Convert.ToInt32(dr[0]);
}
if (numberOfBuilds <= 0)
{
dr.Close();
conn.Close();
Build failedBuild = new Build();
failedBuild.ID = -1;
failedBuild.BuildName = "fail";
failedBuild.UserName = "******";
return failedBuild;
}
dr.Close();
string sqlSelectAllBuilds = "SELECT [id] FROM [Builds]";
cmd.CommandText = sqlSelectAllBuilds;
dr = cmd.ExecuteReader();
int randomRecordNumber = new Random().Next(numberOfBuilds);
for (int i = 0; i < randomRecordNumber + 1; i++)
{
dr.Read();
}
int buildID = Convert.ToInt32(dr[0]);
dr.Close();
conn.Close();
Build randomBuild = this.GetBuild(buildID);
return randomBuild;
}
public Build GetBuild(int BuildID)
{
Build requestedBuild = new Build();
string sql = "SELECT * FROM [Builds] WHERE [id] = " + BuildID;
cmd.CommandText = sql;
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
int id = Convert.ToInt32(dr["id"]);
string name = dr["name"].ToString();
string username = dr["userName"].ToString();
requestedBuild.ID = id;
requestedBuild.BuildName = name;
requestedBuild.UserName = username;
}
}
else
{
dr.Close();
conn.Close();
requestedBuild.ID = -1;
return requestedBuild;
}
dr.Close();
conn.Close();
requestedBuild.Character = this.GetCharacterForBuild(BuildID);
sql = "SELECT [Abilities].*, [BuildsAbilities].[abilityLevel] FROM [Abilities] INNER JOIN [BuildsAbilities] ON [Abilities].[name] = [BuildsAbilities].[abilityName] INNER JOIN [Builds] ON [BuildsAbilities].[buildID] = [Builds].[id] WHERE [Builds].[id] = " + BuildID + " ORDER BY [BuildsAbilities].[abilityLevel]";
cmd.CommandText = sql;
conn.Open();
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string name = dr["name"].ToString();
requestedBuild.Abilities.Add(new Ability(name));
}
}
dr.Close();
sql = "SELECT * FROM [Items] INNER JOIN [BuildsItems] ON [Items].[name] = [BuildsItems].[itemName] INNER JOIN [Builds] ON [BuildsItems].[buildID] = [Builds].[id] WHERE [Builds].[id] = " + BuildID;
cmd.CommandText = sql;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
string name = dr["name"].ToString();
double price = Convert.ToDouble(dr["price"]);
string description = dr["description"].ToString();
requestedBuild.Items.Add(new Item(name, price, description));
}
}
dr.Close();
conn.Close();
return requestedBuild;
}