public bool DataExists(SQLData data)
{
int result = 0;
string cmdStr = "SELECT COUNT(*) AS count FROM data " +
"WHERE LOWER(user_id) = @user AND time = @time AND (mean = @mean OR median = @median OR std_dev = @std_dev OR count = @count) ;";
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@user", data.User);
command.Parameters.AddWithValue("@time", data.Time);
command.Parameters.AddWithValue("@mean", data.Mean);
command.Parameters.AddWithValue("@median", data.Median);
command.Parameters.AddWithValue("@std_dev", data.StdDev);
command.Parameters.AddWithValue("@count", data.Count);
SqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
result = Convert.ToInt32(reader["count"]);
}
reader.Close();
}
catch (Exception e)
{
Supporting.WriteLog(e.Message);
return(true);
}
return(result == 0 ? false : true);
}
private string InsertData(SQLData data)
{
string cmdStr = " INSERT INTO data (user_id, time_zone, time, active, count, mean, median, std_dev) " +
" VALUES (@user , @time_zone, @time , @active, @count, @mean, @median, @std_dev);";
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@user", data.User);
command.Parameters.AddWithValue("@time_zone", data.TimeZone);
command.Parameters.AddWithValue("@time", data.Time);
command.Parameters.AddWithValue("@active", data.Active);
command.Parameters.AddWithValue("@count", data.Count);
command.Parameters.AddWithValue("@mean", data.Mean);
command.Parameters.AddWithValue("@median", data.Median);
command.Parameters.AddWithValue("@std_dev", data.StdDev);
try { command.ExecuteNonQuery(); }
catch (Exception e)
{
Supporting.WriteLog(e.Message);
return("error: adding data");
}
return("info: added data");
}
public string AddData(SQLData data)
{
if (DataExists(data))
{
return("error: data exists");
}
else
{
return(InsertData(data));
}
}
public List <SQLData> GetDataListTimeZone(int zone, TimeSpan time)
{
List <SQLData> result = new List <SQLData>();
string cmdStr = " SELECT * FROM data " +
" WHERE time_zone = @zone AND time > @start_time";
DateTime start_time = DateTime.UtcNow - time;
SqlCommand command = new SqlCommand(cmdStr, SQLraw);
command.Parameters.AddWithValue("@zone", zone);
command.Parameters.AddWithValue("@start_time", start_time);
SqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
SQLData current = new SQLData();
current.User = Convert.ToString(reader["user_id"]);
current.TimeZone = Convert.ToInt32(reader["time_zone"]);
current.Time = Convert.ToDateTime(reader["time"]);
current.Active = Convert.ToBoolean(reader["active"]);
current.Count = Convert.ToInt32(reader["count"]);
current.Mean = Convert.ToDouble(reader["mean"]);
current.Median = Convert.ToDouble(reader["median"]);
current.StdDev = Convert.ToDouble(reader["std_dev"]);
result.Add(current);
}
reader.Close();
}
catch (Exception e)
{
Supporting.WriteLog(e.Message);
}
return(result);
}