3 Time-saving nopCommerce SQL query snippets you wish you knew earlier

nopCommerce is driven by Microsoft SQL database and every single scrap of information is stored in that SQL database. Information like products, orders, customers, plugins, blog posts, comments, product reviews, pages etc; everything is stored in that SQL database. 

Sometimes you may need a quick fix for a specific problem or need to change some information (or data) across the board in nopCommerce. When you are dealing with a ton of information (or data), it is not feasible to go through each and every record because it can be a very tedious process. By running a few SQL queries against your nopCommerce database, you can easily make necessary changes without any kind of manual effort.

nopCommerce administration section is quite user-friendly and allows any store owner (or admin) to manage or edit anything with ease. But, when it comes to making global changes to thousands of records, running a SQL query is the way to go.

Note: Always backup your nopCommerce database before making any kind of mass changes!

In this article, we will go over 3 time-saving nopCommerce SQL query snippets.


1) Deleting product images in bulk

If you have added (or uploaded) a lot of pictures to each product on your nopCommerce store site. In case, you come across any situation where you have to delete those images, it can be a very time consuming process to delete the images linked to any product.

This task can be done very quickly and efficiently by a SQL script.

- In nopCommerce database, all the products are stored in "Product" table
- In nopCommerce database, the  the mapping of images and products is stored in "Product_Picture_Mapping" table


Below is a screenshot of "Product_Picture_Mapping" table in which 5 images are mapped with the product ID 46.





Here is the SQL query to delete product images in bulk

Run this SQL query against your nopCommerce database and all the images for product with ID 46 will be deleted.






2) Configuring free shipping for products in bulk

nopCommerce does offer an import feature (in administration section) that allow store owners to import a CSV file of products with "IsFreeShipping" set to TRUE. This is certainly helpful in marking "IsFreeShipping" true or false for a list of products in bulk.

In case, you come across any situation where you have to configure free shipping of products for ID great than 10 (or any number). In that case, you cannot use  the default import feature and SQL script is the way to go.

Here is the SQL query to configure free shipping for product in bulk

Run this SQL query against your nopCommerce database and all product greater than ID 10 will be configured to free shipping like  this:

You can also modify the SQL query like this:

(Here: 8,9 & 10 are the product IDs)




3) Assigning administrator role to any user

Sometimes while testing or using nopCommerce first time, some users accidentally remove the administrator role from the default admin account and hence get locked out from the administration section. 

If you have access to SQL database of your nopCommerce site, you can easily assign yourself administrator role via SQL query.

All you need to know is the ID of your old admin account. If you do not know the ID, you can simply create a new account by registration page on public store and use the ID of that account.

Let's take an example of customer account with ID = 6 whole current role is only "Registered".





Here is the SQL query to assign administrator role to this account

Now, if we check the role of this account, we should be able to see the "Administrator" role assigned to it.




I hope many nopCommerce users / developers will find this article useful.

Please feel free to ask any questions!