//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");
}
GroceryListController.UserListTitleCheck(userId, newTitle);
try
{
SqlConnectionStringBuilder builder = DatabaseController.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)
{
GroceryListController.UserListTitleCheck(userId, title);
try
{
SqlConnectionStringBuilder builder = DatabaseController.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;
}