/// <summary> /// Delete Daily Activities from Database /// </summary> /// <param name="dailyActivities">Daily Activities</param> public void DailyActivitiesDataDelete(DailyActivitie dailyActivities) { connection.Open(); string query = "DELETE FROM DailyActivities WHERE Id = @USERID"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters["@USERID"].Value = dailyActivities.Id; cmd.ExecuteNonQuery(); connection.Close(); }
/// <summary> /// Add new DailyActivities to Database /// </summary> /// <param name="dailyActivities">Daily Activities</param> /// <param name="user"User>User</param> public void DailyActivitiesDataAdd(DailyActivitie dailyActivities, User user) { connection.Open(); string query = "INSERT INTO DailyActivities(Activity, Date, UserId) VALUES (@ACTIVITY, @DATE, @USERID)"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@ACTIVITY", SqlDbType.Int); cmd.Parameters.Add("@DATE", SqlDbType.DateTime); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters["@ACTIVITY"].Value = dailyActivities.Activitie.Id; cmd.Parameters["@DATE"].Value = dailyActivities.Date; cmd.Parameters["@USERID"].Value = user.Id; cmd.ExecuteNonQuery(); connection.Close(); }
/// <summary> /// Collect all of Daily Activities form Database, which are connected to User Id, at specified Date /// </summary> /// <param name="user">User</param> /// <param name="date">Date</param> /// <returns></returns> public List <DailyActivitie> DailyActivitiesDataCollectionDate(User user, DateTime date) { List <DailyActivitie> dailyActivities = new List <DailyActivitie>(); connection.Open(); string query = "SELECT * FROM DailyActivities WHERE UserId = @USERID AND Date = @DATE"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters.Add("@DATE", SqlDbType.DateTime); cmd.Parameters["@USERID"].Value = user.Id; cmd.Parameters["@DATE"].Value = date.Date; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Activity activity = new Activity(1, "a", "a", 1); int activityId = reader.GetInt32(1); string query2 = "SELECT * FROM Activities WHERE Id = @ID"; SqlCommand cmd2 = new SqlCommand(query2, connection); cmd2.Parameters.Add("@ID", SqlDbType.Int); cmd2.Parameters["@ID"].Value = activityId; SqlDataReader reader2 = cmd2.ExecuteReader(); int a = 0; while (reader2.Read() && a == 0) { Activity newActivity = new Activity((uint)reader2.GetInt32(0), reader2.GetString(1), reader2.GetString(2), reader2.GetInt32(3)); a++; activity = newActivity; } reader2.Close(); DailyActivitie dailyActivity = new DailyActivitie((uint)reader.GetInt32(0), activity, date.Date); dailyActivities.Add(dailyActivity); } reader.Close(); connection.Close(); return(dailyActivities); }
/// <summary> /// Update DailyActivities in Database /// </summary> /// <param name="activity">Activity</param> /// <param name="dailyActivities">Daily Activities</param> /// <param name="user">User</param> public void DailyActivitiesDataUpdate(Activity activity, DailyActivitie dailyActivities, User user) { connection.Open(); string query = "UPDATE DailyActivities SET Activity = @ACTIVITY, Date = @DATE, UserId = @USERID WHERE Id = @ID"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@ACTIVITY", SqlDbType.Int); cmd.Parameters.Add("@DATE", SqlDbType.DateTime); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters.Add("@ID", SqlDbType.Int); cmd.Parameters["@ACTIVITY"].Value = activity.Id; cmd.Parameters["@DATE"].Value = dailyActivities.Date; cmd.Parameters["@USERID"].Value = user.Id; cmd.Parameters["@ID"].Value = dailyActivities.Id; cmd.ExecuteNonQuery(); connection.Close(); }