Cleaning up Customer Data in nopCommerce

Going through a nopCommerce project with a 40GB database, I found the majority of the space was allotted to a very large Customer table. To fix this, I decided to delete Customer data with the following criteria:

  • No username, email, and password data
  • Not a system account
  • Doesn’t reference a Shipping and Billing address
  • Last activity recorded a month from today’s date

I considered the customers having shopping cart items as well, but found that most of the junk data had one shopping cart item – I think this comes from a robot regularly adding specific items to a cart to check price.

Here’s the SQL used:

DECLARE @customersToDelete TABLE (id int)

INSERT INTO @customersToDelete (id)
	Username IS NULL
	AND Password is NULL
	AND IsSystemAccount = 0
	AND BillingAddress_Id IS NULL
	AND ShippingAddress_Id IS NULL
	AND LastActivityDateUtc < DATEADD(week, -1, GETDATE())

DELETE FROM ShoppingCartItem WHERE CustomerId IN (SELECT * FROM @customersToDelete)

DELETE FROM GenericAttribute WHERE KeyGroup = 'Customer' AND EntityId IN (SELECT * FROM @customersToDelete)

DELETE FROM Customer_CustomerRole_Mapping WHERE Customer_Id IN (SELECT * FROM @customersToDelete)

DELETE FROM Customer WHERE Id IN (SELECT * FROM @customersToDelete)

Once this is done, make sure to shrink the database to reclaim the space gained by clearing out the data.


Setting up a SonarQube Server in Azure

To get started with using SonarQube in Azure, do the following.

Create a virtual machine with the Standard B2s (~$30/month) size or larger.

SSH into the server and download all pre-reqs:

sudo apt-get update
sudo apt install openjdk-11-jre-headless unzip -y

Create a non-root user:

sudo adduser sonarqube
sudo usermod -aG sudo sonarqube
sudo su - sonarqube

Download and unzip SonarQube (you can get the wget URL from the Downloads page):


sudo unzip -d /opt/sonarqube

sudo mv /opt/sonarqube/sonarqube-* /opt/sonarqube

sudo chown -R sonarqube /opt/sonarqube

Then start it using the following as the non-root user:

/opt/sonarqube/bin/linux-x86-64/ console

Wait for the text SonarQube is up and then verify access at IP_ADDRESS:9000 (you can log in with admin/admin).

Set Up PostgreSQL

Next step is setting up a dedicated database – we will use PostgreSQL to accomplish this.

Install PostgreSQL:

sudo apt-get install postgresql -y

And connect into the server instance and run the following commands:

sudo -u postgres psql postgres

GRANT ALL PRIVILEGES ON DATABASE sonarqube to sonarqube;

Now modify /opt/sonarqube/conf/


Add the following line to /etc/sysctl.conf:


To finish, restart the server, start up SonarQube again and verify that it can start up successfully, alongside having the “embedded DB” warning removed from the login screen.

Set to Run As A Service On VM Start

Next, we’ll set up the ability to start SonarQube on VM start by running SonarQube as a service.

Add the following file /etc/systemd/system/sonarqube.service:

Description=SonarQube service

ExecStart=/usr/bin/nohup /usr/bin/java -Xms32m -Xmx32m -jar /opt/sonarqube/lib/sonar-application-


Then enable and start the service:

sudo systemctl enable sonarqube.service
sudo systemctl start sonarqube.service

Confirm by restarting the server and checking that SonarQube is running.

Setting up Reverse Proxy and HTTPS with Let’s Encrypt

Next step is setting up HTTPS, in this case we’ll use Let’s Encrypt for the certificate. To serve the correct ports, we’ll set up Apache and serve it as a proxy to the built-in Tomcat server that SonarQube provides.

When looking to configure Apache, set up the pre-reqs:

sudo apt install apache2 -y
sudo a2enmod proxy_http proxy_ajp rewrite deflate headers proxy_balancer proxy_connect proxy_html ssl lbmethod_byrequests slotmem_shm proxy

Add these lines to /etc/apache2/sites-enabled/000-default.conf:

ProxyPass / "http://localhost:9000/"
ProxyPassReverse / "http://localhost:9000/"

Restart Apache:

sudo systemctl restart apache2

And check that you can access SonarQube using port 80 (HTTP).

Once port 80 is accessible, you can follow the Certbot steps to enable HTTPS.

Next Steps


Installing Redux Into Typescript Create React App

To install Redux into an app created with Create React App that’s using Typescript, run the following to install:

npm install --save redux react-redux @types/react-redux

Optionally, you can also install the following to allow for both:

  • Warning in case of state mutation.
  • Connecting to the app using Redux DevTools.
npm i --save-dev redux-immutable-state-invariant
npm install --save redux-devtools-extension


Enable Routing in Typescript Create React App

When working with Create React App using the Typescript configuration, there’s a slight change to be made to set up the ability to perform routing.

Install the following into your project:

npm i -S react-router-dom @types/react-router-dom

After that’s done, use the content in the Example Page in your App.tsx file. You should be able to change between pages using the URL as reference.



Formatting Dates in Create React App with MomentJS

While working with React, I found I wanted a way to work with dates similiar to the way Angular uses the DatePipe to handle displaying dates correctly. The answer is in using the momentjs library.

To do so, add the package to your project:

npm i --save moment

Then add the following code to test in one of your .js or .tsx files:

import moment from 'moment';



You should see an ISO string being displayed. To check on ways you can use the library, check the docs on the page above.


Debugging Issues With cert-manager

If you’re using cert-manager to get a Let’s Encrypt certificate for your Kubernetes cluster and running into issues, you can do the following to see what might be going on:

Check Status of Challenge

First, check the status of the certificate:

kubectl describe certificate tls-secret --namespace ingress-nginx

You’ll see something that says something like Created new CertificateRequest resource “tls-secret-1764787185”. Run the following to get more information:

kubectl describe CertificateRequest tls-secret-1764787185 --namespace ingress-nginx

This command will provide the ID of an order, so use a similiar command:

kubectl describe Order tls-secret-1764787185-845386587 --namespace ingress-nginx

Finally, you will get a Challenge to view:

kubectl describe Challenge tls-secret-1764787185-845386587-4119551803 --namespace ingress-nginx

View Logs

If the above isn’t helping, you can view the logs:

kubectl logs -n cert-manager deploy/cert-manager -f

Upgrading a NopCommerce Plugin from 3.80 To 4.00

When upgrading nopCommerce, you’ll need to also get the latest versions of plugins to go alongside the nopCommerce installation. Depending on the upgrade, you may need to make some minor changes to the plugin to get it working.

In particular, 3.80 -> 4.00 marks the transition to .NET Core, meaning there are some significant changes to make.

Upgrade the .csproj File

To start, you’ll want to upgrade the .csproj file of the project. Easiest option here is to copy an existing plugin that works with nopCommerce 4.00 and modify it accordingly. You can use the Description.txt file to get the information you need.

File Cleanup

First, create a plugin.json file.

Delete the following files:

  • web.config
  • app.config
  • Description.txt
  • Properties folder

Fix Build

Once you’ve done this, you’ll need to go through the file and make any kind of modifications to get a build to work successfully. If you’re working with VSCode, add the following file omnisharp.json to the plugin:

  "RoslynExtensionsOptions": {
    "enableAnalyzersSupport": true
  "MsBuild": {
    "UseLegacySdkResolver": true

There are a few specifics to consider:

  • When migrating GetConfigurationRoute, the route will be moved into a Infrastructure/RouteProvider.cs file (as an example).
  • BasePublicController becomes BasePluginController.
  • AdminAuthorize becomes AuthorizeAdmin.
  • Remove references to ChildActionOnly.

Validate Functionality

After getting the build to work successfully, the final step is running nopCommerce locally and installing/uninstalling the plugin. In addition, check the functionality to make sure everything is working.

Reminder: when installing/uninstalling plugins in nopCommerce locally, make sure to shut down and restart the application after installing applications to reflect plugin installation changes.


Ripping DVDs using Handbrake

To rip DVDs, first do the following:

After the above is done, open Handbrake and open the DVD as a source. It’ll take a long time to load the data in place, and then you’ll have the ability to select different chapters to rip.

Select the desired title to rip and click ‘Encode’. After the process completes, you’ll be able to view the video.


Setting up nopCommerce 4.00+ For Use With VSCode

To get started with using nopCommerce and VSCode together, you’ll need to have the following:

  1. VSCode installed.
  2. The C# extension installed alongside VSCode.
  3. .NET Core 3.0 SDK
  4. If running nopCommerce 4.00, .NET Framework v4.6.1 Developer Pack

Quick Start

  1. Download the nopCommerce source and copy the contents to a folder on your PC.
  2. Open the folder using VSCode.
  3. If you’re running nopCommerce 4.00, apply the changes from this commit.
  4. Run dotnet restore to get all dependencies required for running locally.
  5. In the directory /Presentation/Nop.Web, run dotnet run.
  6. Access http://localhost:55390 and run the installation process to get a database created.



Create An Admin in nopCommerce With Only Database Access

If you only have access to a database for nopCommerce (for example, if you’ve accidentally locked yourself out), here’s a way to create an administrator with access to the database and access to the frontend.

First, register a new user using the standard registration process. Once that’s done, run the following SQL query on the database to assign administrator rights:

INSERT INTO Customer_CustomerRole_Mapping
	(SELECT Id FROM Customer
	(SELECT Id FROM CustomerRole
	WHERE SystemName = 'Administrators')