//Query For Adding New Chars
public ArrayList getUserAddNewCharList(int userID)
{
//ArrayList addNewCharList = new ArrayList();
addNewCharList.Clear(); //Clear ArrayList Before Doing
DataTable myTable = new DataTable();
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["_db"].ConnectionString);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
string sql = @"SELECT * FROM _genshindynamicplanner_db_characters t1 WHERE NOT EXISTS
(SELECT charID FROM _genshindynamicplanner_usersavedcharacterdata t2
WHERE t1.ID = t2.charID AND userID = @userID) ORDER BY t1.name ASC;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@userID", userID);
MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd);
myAdapter.Fill(myTable);
Console.WriteLine("Table is ready.");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
//Convert the retrieved data to events and save them to the list
foreach (DataRow row in myTable.Rows)
{
//Character newChar = new Character();
UserSavedCharacterData thisCharRecord = new UserSavedCharacterData();
//[Character Data Attributes]
thisCharRecord.charID = (int)row["ID"];
thisCharRecord.Name = row["name"].ToString();
addNewCharList.Add(thisCharRecord);
}
Console.WriteLine("[addNewCharList Returned]");
return(addNewCharList);
}
//[Query DB -> Retrieve Whole List]
public ArrayList getUserCharacterData(int userID)
{
//ArrayList userCharList = new ArrayList();
userCharList.Clear();
DataTable myTable = new DataTable();
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["_db"].ConnectionString);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
string sql = @"SELECT * FROM _genshindynamicplanner_usersavedcharacterdata t1
INNER JOIN _genshindynamicplanner_db_characters t2 ON t1.charID = t2.ID
INNER JOIN _genshindynamicplanner_db_weapons t3 ON t1.wepID = t3.ID
WHERE userID = 1 ORDER BY rowID ASC;" ;
/*string sql = @"SELECT * FROM _genshindynamicplanner_usersavedcharacterdata t1
* INNER JOIN _genshindynamicplanner_db_characters t2 ON t1.charID = t2.ID
* WHERE userID = @userID ORDER BY rowID ASC;";*/
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@userID", userID);
MySqlDataAdapter myAdapter = new MySqlDataAdapter(cmd);
myAdapter.Fill(myTable);
Console.WriteLine("Table is ready.");
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
//Convert the retrieved data to events and save them to the list
foreach (DataRow row in myTable.Rows)
{
//Character newChar = new Character();
UserSavedCharacterData thisCharRecord = new UserSavedCharacterData();
//[Character Data Attributes]
thisCharRecord.charID = (int)row["ID"];
thisCharRecord.Name = row["name"].ToString();
thisCharRecord.starRating = (int)row["starRating"];
thisCharRecord.elementType = (int)row["elementType"];
thisCharRecord.weaponType = (int)row["weaponType"];
//
thisCharRecord.talent1Name = row["talent1Name"].ToString();
thisCharRecord.talent1Desc = row["talent1Desc"].ToString();
thisCharRecord.talent2Name = row["talent2Name"].ToString();
thisCharRecord.talent2Desc = row["talent2Desc"].ToString();
thisCharRecord.talent3Name = row["talent3Name"].ToString();
thisCharRecord.talent3Desc = row["talent3Desc"].ToString();
//
thisCharRecord.imgURLchar = row["imgURLchar"].ToString();
thisCharRecord.imgURLt1 = row["imgURLt1"].ToString();
thisCharRecord.imgURLt2 = row["imgURLt2"].ToString();
thisCharRecord.imgURLt3 = row["imgURLt3"].ToString();
//
thisCharRecord.ascElementalMat = (int)row["ascElementalMat"];
thisCharRecord.ascElementalBossMat = (int)row["ascElementalBossMat"];
thisCharRecord.ascRegionalSpecialty = (int)row["ascRegionalSpecialty"];
thisCharRecord.ascCommonAndTalentMat = (int)row["ascCommonAndTalentMat"];
thisCharRecord.ascTalentBossMat = (int)row["ascTalentBossMat"];
//
//
//Records Joined Information:
//[UserSaveData Attributes]
thisCharRecord.charLevel = (int)row["charLevel"];
thisCharRecord.t1Level = (int)row["t1Level"];
thisCharRecord.t2Level = (int)row["t2Level"];
thisCharRecord.t3Level = (int)row["t3Level"];
thisCharRecord.wepID = (int)row["wepID"];
thisCharRecord.wepLevel = (int)row["wepLevel"];
thisCharRecord.ArtifactPlaceholder = (int)row["ArtifactPlaceholder"];
//
//toggleCalc Flag Value
thisCharRecord.toggleCalc = (int)row["toggleCalc"];
//DesiredLevels | Calc
thisCharRecord.charDesiredLevel = (int)row["charDesiredLevel"];
thisCharRecord.t1DesiredLevel = (int)row["t1DesiredLevel"];
thisCharRecord.t2DesiredLevel = (int)row["t2DesiredLevel"];
thisCharRecord.t3DesiredLevel = (int)row["t3DesiredLevel"];
thisCharRecord.wepDesiredLevel = (int)row["wepDesiredLevel"];
userCharList.Add(thisCharRecord);
}
Console.WriteLine("[userCharList Returned]");
return(userCharList);
}