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); }