public void CreateStatColumns(Statistic[] statistics)
{
if (statistics == null)
return;
foreach (Statistic stat in statistics)
{
string sql = string.Format(@"IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = '{1}' AND object_id = OBJECT_ID('{0}'))
ALTER TABLE {0} ADD {1} int NULL", Constants.Table.GameStats, stat.name);
this.RunSQL(sql);
}
}
public Statistic[] LoadStats(int gameID)
{
SqlConnection sqlConnection = new SqlConnection(connString);
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
string sql = string.Format("SELECT TOP 1 * FROM {0} WHERE GameID = {1}", Constants.Table.GameStats, gameID.ToString());
cmd.CommandText = sql;
SqlDataReader dtr = cmd.ExecuteReader();
Statistic[] stats = new Statistic[dtr.FieldCount];
try
{
while (dtr.Read())
{
for (int i = 0; i < dtr.FieldCount; i++)
{
Statistic stat = new Statistic();
stat.name = dtr.GetName(i);
object value = dtr.GetValue(i);
if (value == DBNull.Value)
stat.value = 0;
else
stat.value = (int) value;
stats[i] = stat;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
dtr.Close();
}
return stats;
}
public void SaveStats(int gameID, Statistic[] stats)
{
if (stats == null)
return;
CreateStatColumns(stats);
string[] columnNames = new string[stats.Length];
int[] values = new int[stats.Length];
for (int i = 0; i < stats.Length; i++)
{
Statistic stat = stats[i];
columnNames[i] = stat.name;
values[i] = stat.value;
}
string insertClause = string.Format(@"IF NOT EXISTS(SELECT * FROM {0} WHERE GameID = {1}) INSERT INTO {0} (GameID, " + String.Join(",", columnNames) + ")", Constants.Table.GameStats, gameID.ToString());
string valueClause = "VALUES(" + gameID.ToString() + "," + String.Join(",", values) + ")";
string sql = insertClause + Environment.NewLine + valueClause;
this.RunSQL(sql);
}
public void SaveLoadStatsTest()
{
DatabaseManager manager = new DatabaseManager(myConnection);
Statistic[] saveStats = new Statistic[2];
Statistic stat1 = new Statistic();
stat1.name = "Test1";
stat1.value = 1;
Statistic stat2 = new Statistic();
stat2.name = "Test2";
stat2.value = 2;
saveStats[0] = stat1;
saveStats[1] = stat2;
manager.SaveStats(1, saveStats);
Statistic[] loadStats = manager.LoadStats(1);
foreach(Statistic stat in saveStats)
{
Statistic foundStat = Array.Find(loadStats, item => item.name == stat.name && item.value == stat.value);
Assert.IsNotNull(foundStat);
}
}
