//Updates a exixsting list in the SQL database. public static void UpdateList(int userId, int listId, string currentTitle, string newTitle, string newLocation) { if (GetListId(userId, currentTitle) != listId) { throw new System.InvalidOperationException("Invalid data. Incorrect listId"); } GroceryListEngine.UserListTitleCheck(userId, newTitle); try { SqlConnectionStringBuilder builder = Database.ConnectionBuilder(); using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { connection.Open(); SqlCommand query = new SqlCommand(null, connection); query.CommandText = "UPDATE dbo.Grocery_List SET title = @title, locationSite = @location, dateCreated = @date WHERE listId = @Id;"; SqlParameter titlePar = new SqlParameter("@title", SqlDbType.VarChar, 255); SqlParameter locationPar = new SqlParameter("@location", SqlDbType.VarChar, 255); SqlParameter datePar = new SqlParameter("@date", SqlDbType.VarChar, 255); SqlParameter idPar = new SqlParameter("@Id", SqlDbType.Int, 255); titlePar.Value = newTitle; locationPar.Value = newLocation; datePar.Value = DateTime.Now; idPar.Value = userId; query.Parameters.Add(titlePar); query.Parameters.Add(locationPar); query.Parameters.Add(datePar); query.Parameters.Add(idPar); query.Prepare(); query.ExecuteNonQuery(); } } catch (SqlException e) { Console.WriteLine(e.ToString()); } return; }
//Adds a new list to the SQL database. public static void AddList(string title, string location, DateTime date, int userId) { GroceryListEngine.UserListTitleCheck(userId, title); try { SqlConnectionStringBuilder builder = Database.ConnectionBuilder(); using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { connection.Open(); SqlCommand query = new SqlCommand(null, connection); query.CommandText = "INSERT INTO dbo.Grocery_List(title, locationSite, dateCreated, userId) VALUES (@title, @location, @date, @Id)"; SqlParameter titlePar = new SqlParameter("@title", SqlDbType.VarChar, 255); SqlParameter locationPar = new SqlParameter("@location", SqlDbType.VarChar, 255); SqlParameter datePar = new SqlParameter("@date", SqlDbType.DateTime, 255); SqlParameter idPar = new SqlParameter("@Id", SqlDbType.Int, 255); titlePar.Value = title; locationPar.Value = location; datePar.Value = date; idPar.Value = userId; query.Parameters.Add(titlePar); query.Parameters.Add(locationPar); query.Parameters.Add(datePar); query.Parameters.Add(idPar); query.Prepare(); query.ExecuteNonQuery(); } } catch (SqlException e) { Console.WriteLine(e.ToString()); } return; }