Пример #1
0
        //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;
        }
Пример #2
0
        //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;
        }