Deleted products stay in Product table

Why do products stay in the product table after I have deleted them?  Is there something I'm missing or is the part of the store design?

see https://www.nopcommerce.com/boards/topic.aspx?topicid=2531

I totally agree with that explanation but I just wanted to delete the products that came with nopCommerce.  I've already added a lot of products to my database and starting over would be very time consuming.  Is there a way I could delete the products that won't ever be sold anyways?

Thanks.

I'm assuming you have all ready have your data in the database from your testing which you want to keep. But if not a simple option might be to do a fresh install and don't select the populate sample data option.

Otherwise yes you can delete there are a few options.

Log in to admin edit each product and hit the delete button. but all these website deletes may take some time. And i think you will need to delete any test orders before any products that linked to those orders and so on.

Best if you can use a sql database frontend program that lets you systematically delete all the records manually
I used microsoft access database and created a front end linked to the sql backend in nop.
Then opened and all the tables in order and deleted the records (noting that the foreign keys records usually need to be deleted first before the main record)

Or use Microsoft SQL Server Manager and do the same.

Or write some sql database code to do it.

Yes.  I think it is a good idea to implement a "Master Delete" button for products.  I'll put it in my to-do list.  Those sample products are going to bug me until I delete them.

Thanks for you suggestions.

Yidna,

I just wanted to add that I did use the delete button for products on the product page and what happens is that in the Nop_Product database table, a field called IsDeleted (data type is bit) turns to 1 and only products that have a value of 0 are displayed.  No products are actually removed from the database by pressing the delete button.

I understand that you can't delete a product from the Nop_Product DB table because other tables reference it but a simple solution would be to use an SqlTransaction to atomically delete all references and the product in one transaction.  This would be done with a solution like the following example:

SqlConnection conn = new SqlConnection("DB Connection String");
conn.Open();

SqlTransaction trans = conn.BeginTransaction();

try
{
      SqlCommand cmd = new SqlCommand("", conn, trans);

      cmd.CommandText = deleteFromTable;
      cmd.ExecuteNonQuery();

      //insert here any other commands to execute

      trans.Commit();

      //if your code reaches here then the transaction succeeded
}
catch (Exception ex)
{
      trans.RollBack();
      //if any exceptions are thrown during the transaction then all statements are rolled back and no changes are saved
}
finally
{
       conn.Close();
}

You can add as many cmd.CommandText and cmd.ExecuteNonQuery() statements to the transaction that you want.

Yidna, here is the code I developed to delete all records of the product in the database.

                int ProductID = Convert.ToInt32(Request.QueryString["ProductID"]);
                SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();
                try
                {
                    int paramCount = 0;
                    SqlCommand cmd = new SqlCommand("", conn, trans);

                    paramCount++;
                    cmd.CommandText = "SELECT PictureID FROM Nop_ProductPicture WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    SqlDataReader readPictureID = cmd.ExecuteReader();
                    List<string> picID = new List<string>();
                    //get all pictures for this product
                    while (readPictureID.Read())
                    {
                        picID.Add(readPictureID["PictureID"].ToString());
                    }
                    readPictureID.Close();
                    
                    //delete the picture mapping
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_ProductPicture WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete all pictures of this product in database
                    foreach (string key in picID)
                    {
                        paramCount++;
                        cmd.CommandText = "DELETE FROM Nop_Picture WHERE PictureID = @PictureID" + paramCount.ToString();
                        cmd.Parameters.Add("@PictureID" + paramCount.ToString(), Convert.ToInt32(key));
                        cmd.ExecuteNonQuery();
                    }
                    
                    //delete product variants for this product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_ProductVariant WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete manufacturer mapping for this product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_Product_Manufacturer_Mapping WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete category mapping for this product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_Product_Category_Mapping WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete reviews for this product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_ProductReview WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete specification attributes for this product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_Product_SpecificationAttribute_Mapping WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete product ratings for this product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_ProductRating WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();
                    
                    //delete the product
                    paramCount++;
                    cmd.CommandText = "DELETE FROM Nop_Product WHERE ProductID = @ProdID" + paramCount.ToString();
                    cmd.Parameters.AddWithValue("@ProdID" + paramCount.ToString(), ProductID);
                    cmd.ExecuteNonQuery();

                    trans.Commit();
                    string script = "<script language='javascript' type='text/javascript'>alert('The product and all references to it have been successfully removed from the database.'); location = 'Products.aspx';</script>";
                    Response.Write(script);
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    string script = "<script language='javascript' type='text/javascript'>alert('Problems with the server are preventing your request from completing. Please try again. If you keep getting this message, please contact the system administrator and describe the problem. Thank you.'); location = 'Products.aspx';</script>";
                    Response.Write(script);
                }
                finally
                {
                    conn.Close();
                }

I will update this thread tomorrow to include deletion of the images in images/thumbs

Surely running a SQL script manually would suffice?  Why would you want to put this in the control panel?  what happens if you accidently run this after you are in production?  I would leave this kind of task to a manual operation.  My 2c.

I added links to the products page inside the data grid.  When you click a link, a window pops up and ask if you are sure you want to continue.  A DB script would suffice but I like the convenience of being able to point and click.  Don't you?

Click here to see what I did:
http://nopcommerce.deccks.com/products.aspx.jpg

The remove from inventory link does the same thing that nopCommerce does by default and the delete all records link executes the code above.

I think the images in the images/thumbs directory are stored like this:

PictureID_ProductID.extension

Right?

So if I select the appropriate PictureID, ProductID, and the extension then I can access the file to delete from disk.