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.

nopCommerce SQL Query

Here is the SQL query to delete product images in bulk

WHERE [Id] in (SELECT PictureId FROM Product_Picture_Mapping WHERE ProductId = 'HERE_GOES_PRODUCT_ID');

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

nopCommerce SQL Query

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

UPDATE Product
SET IsFreeShipping='1'

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

nopCommerce SQL Query

0 = False (NO free shipping)
1 = True (free shipping)

You can also modify the SQL query like this:

UPDATE Product
SET IsFreeShipping='1'
WHERE ID IN (8, 9, 10)

(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".

nopCommerce SQL Query

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

INSERT INTO [dbo].[Customer_CustomerRole_Mapping]

nopCommerce SQL Query

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

nopCommerce SQL Query

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

Please feel free to ask any questions!

Check out PART-2 of this series - Time-saving nopCommerce SQL query snippets you wish you knew earlier

- Read Part 2: Click Here


About Author

Written By Lavish Kumar

Based out of New York, USA, Lavish Kumar is a full stack web developer by profession and founder of Striving Programmers, a trusted community for developers that offers a wealth of articles and forums to assist individuals with improving their software development skills.

Leave your comment