public TierListModel DeserializeTierList(string serializedTierList)
{
//The serialized tierlist starts with the name and then splits the rest of the list with a ']' so as followed (name):(restofTierList)
//The rest of the serialized tierlist is delimeted by '|' and the patter goes as followed: (rowName)|(imagepath)|(rowName)|(imagepath)
string[] nameTierListSplit = serializedTierList.Split(']');
string[] seperatedTierList = nameTierListSplit[1].Split('|');
TierListModel tierList = new TierListModel()
{
Name = nameTierListSplit[0]
};
for (int i = 0; i < seperatedTierList.Length - 1; i++)
{
if (i % 2 == 0)
{
//Creates a new row using the (rowName) as the key (it is known that all even indexes in the array of strings is the name)
string[] rowAndColor = seperatedTierList[i].Split('[');
tierList.CreateNewRow(rowAndColor[0], i + 1, rowAndColor[1]);
}
else
{
//Adds a new item to a row which already exists in the dictionary
tierList.AddItemToRow(seperatedTierList[i - 1].Split('[')[0], seperatedTierList[i], i + 1);
}
}
return(tierList);
}
/// <summary>
/// Saves a new tierlist into the database.
/// </summary>
/// <param name="tierList"></param>
/// <returns>Whether or not the saving Succeeded.</returns>
public bool SaveTierList(TierListModel tierList)
{
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
/* The database is structured to be able to hold a tier list. There is a table which holds the name of
* the tier list and the id of the tier list. Then the rows have a foreign key which refers to the tier list so a
* tier list can have many rows, and then from there the items in the tier list have their own table which have
* row ids as their foreign keys allowing rows to have many items but not vice versa.
*
* To save it into the database I have to start with the thing with no foreign dependencies.
*/
//The OUTPUT INSERTED allows me to grab the id of the tier_list after it is inputted so that it can be used
//As a foreign key later
SqlCommand cmd = new SqlCommand("INSERT INTO tier_list OUTPUT INSERTED.id VALUES (@tierListName);", conn);
cmd.Parameters.AddWithValue("@tierListName", tierList.Name);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int tierId = Convert.ToInt32(reader["id"]);
reader.Close();
//I used kvps here so let me explain what it holds.
//the key, or the string is the name of each row within a givn tierlist
//The value reperesents all of the items in a list which the said row has
//I loop through each so that I can add every single row to the database
foreach (KeyValuePair <string, List <Image> > kvp in tierList.FullTierList)
{
cmd = new SqlCommand("INSERT INTO tier_rows (name, color, tier_list_id) OUTPUT INSERTED.id VALUES (@rowName, @rowColor, @tierId);", conn);
cmd.Parameters.AddWithValue("@rowName", kvp.Key);
cmd.Parameters.AddWithValue("@rowColor", tierList.RowColors[kvp.Key]);
cmd.Parameters.AddWithValue("@tierId", tierId);
reader = cmd.ExecuteReader();
reader.Read();
int rowId = Convert.ToInt32(reader["id"]);
reader.Close();
//I loop through this for the same reason as why I looped up above
foreach (var item in kvp.Value)
{
cmd = new SqlCommand("INSERT INTO items VALUES (@imagePath, @orderValue, @rowId);", conn);
cmd.Parameters.AddWithValue("@imagePath", item.ImagePath);
cmd.Parameters.AddWithValue("@orderValue", item.OrderValue);
cmd.Parameters.AddWithValue("@rowId", rowId);
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
return(true);
}
catch (SqlException)
{
throw;
return(false);
}
}
public IList <TierListModel> GetTierLists()
{
IList <TierListModel> tierLists = new List <TierListModel>();
try
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlCommand selectTierLists = new SqlCommand("SELECT * FROM tier_list;", conn);
SqlDataReader readTierLists = selectTierLists.ExecuteReader();
//Begin by selecting each individual tier list
while (readTierLists.Read())
{
string tierListName = Convert.ToString(readTierLists["name"]);
int tierListId = Convert.ToInt32(readTierLists["id"]);
//Create a whole new tierList Model
TierListModel currentTierList = new TierListModel()
{
Id = tierListId,
Name = tierListName
};
tierLists.Add(currentTierList);
}
readTierLists.Close();
foreach (var tierList in tierLists)
{
IList <Row> rows = new List <Row>();
SqlCommand selectRows = new SqlCommand("SELECT * FROM tier_rows WHERE @tierId = tier_rows.tier_list_id;", conn);
selectRows.Parameters.AddWithValue("@tierId", tierList.Id);
SqlDataReader readRows = selectRows.ExecuteReader();
//read each of the rows to the corresponding tier list
while (readRows.Read())
{
Row row = new Row()
{
Name = Convert.ToString(readRows["name"]),
RowId = Convert.ToInt32(readRows["id"]),
Color = Convert.ToString(readRows["color"])
};
//****************************
//In Developement
int orderValue = 0;
//****************************
//Create the current row in the current tierlist model
tierList.CreateNewRow(row.Name, orderValue, row.Color);
rows.Add(row);
//select all the items that are currently stored in the table
}
readRows.Close();
foreach (var row in rows)
{
SqlCommand selectItems = new SqlCommand("SELECT * FROM items WHERE @rowId = row_id;", conn);
selectItems.Parameters.AddWithValue("@rowId", row.RowId);
SqlDataReader readItems = selectItems.ExecuteReader();
//Read each individual item and then add it into the tierList
while (readItems.Read())
{
//add all the new items into objects
string imagePath = Convert.ToString(readItems["image_path"]);
int orderValue = Convert.ToInt32(readItems["order_value"]);
tierList.AddItemToRow(row.Name, imagePath, orderValue);
}
readItems.Close();
}
}
conn.Close();
}
return(tierLists);
}
catch (SqlException)
{
throw;
}
}
