tech.sunnyw .Net

SQL - How to do a CHARINDEX in reverse

I am a little surprise when SQL 2012 have introduced functions like TRY_CONVERT, but there are still no built-in functions to perform a CHARINDEX in reverse.

Code

Select UrlRecord.Slug
, LEN(UrlRecord.Slug) [Length]
, CHARINDEX('-', UrlRecord.Slug, 0) [Normal CHARINDEX]
, LEN(UrlRecord.Slug) - CHARINDEX('-', Reverse(UrlRecord.Slug), 0) + 1 [Reverse CHARINDEX with bug]
, IIF(CHARINDEX('-', UrlRecord.Slug, 0) = 0, 0, LEN(UrlRecord.Slug) - CHARINDEX('-', Reverse(UrlRecord.Slug), 0) + 1) [Desired CHARINDEX]
from UrlRecord
where EntityName = 'Category'
Order by Slug

In the above code, we are trying to find the last position (a charindex in reverse order) of a dash '-' from a string (UrlRecord.Slug).

Row 68, for a string that have only one matching character '-', either direction will return the same result.
Row 72, a Reverse CharIndex will return 10 for the last occurrence as opposed to a CharIndex that will return 5 for the first occurrence.
Row 77, there is a bug when we calculate the reverse but no matching string, it will return the full length. We will do a IIF check to return 0 when there is no match.

The final code became this.

Declare @matchChar varchar(1) = '-'
Declare @sourceString varchar(max) = 'test-test-test'

Select IIF(CHARINDEX(@matchChar, @sourceString, 0) = 0, 0, LEN(@sourceString) - CHARINDEX(@matchChar, Reverse(@sourceString), 0) + 1)

Performance

I did not pay attention to performance at all. I am sure there are rooms for improvement, but I am thinking a built-in function for such a simple operation is probably overdue by Microsoft anyway.

nopCommerce - Multi Store / Multi Currency / Multi Price for each product

Scope

We have had a few sites went live with nopCommerce. I am in the process to replicate our existing Australia sites to New Zealands sites.

Both sites to sell the same products, with stocks coming from the same location.
Products dispatch from the same warehouse.
Customizable Price, Old Price and Special Price per product per store and not using any currency converter.
etc.

What I am trying to achieve is also described here and here by other people. Or as a workitem in codeplex. No viable solution at time of writing this article.

This is base on nopCommerce v3.3.

Business Case

nopCommerce out of the box supports multiple currencies by using a currency converter.

However, this is only practical if your company is 1 entity and want to accept orders from around the world. Currency converter provides an estimate for users on how much they will be spending, but the transaction is still processed in my primary currency - AU dollars in our case.

Example of Currency Converter from my previous project - Bardot

In our business case, we own both of the AU and NZ companies as separate business entities. Our NZ company have their own price and we would like to accept local NZ credit cards in NZ currency. In this scenario, currency converter is not a feasible solution.

Technical Overview

Create a new ProductPrices table to store prices for different stores
Create an admin screen to handle Product Prices
Create a GetCustomPrice() to retrieve price from ProductPrices if store id match
Replace any existing controllers or services that use Product.Price to use GetCPrice()

Prerequisite

Code

ProductCustomPrice

We created a new SQL table to store our custom price.

In our code, we need to create a new domain object for ProductCustomPrice and a mapping file. This is already demonstrated in previous posts in prerequisite.

PriceCalculationService

We need to override the PriceCalculationService.GetFinalPrice() in the Product Price region to use our custom price. We will need a small refactor work in core.

// decimal result = product.Price;
decimal result = GetProductInitPrice(product);

We will write a method to retrieve the custom price from our database.

public virtual ProductCustomPrice GetProductCustomPriceByProductId(int productId)
{
    if (productId == 0)
        return null;
            
    var currentStoreId = _storeContext.CurrentStore.Id;
    var query = _productCustomPriceRepository.Table
                    .Where(p => p.StoreId == currentStoreId 
                            && p.ProductId == productId);

    return query.FirstOrDefault();
}

We will override the method in our new CustomPriceCalculationService class. It will pick up custom price if available, otherwise default.

protected override decimal GetProductInitPrice(Product product)
{
    var customPrice = GetProductCustomPriceByProductId(product.Id);

    if (customPrice != null)
        return customPrice.Price;

    return base.GetProductInitPrice(product);
}

CatalogController

We then need to do the same for OldPrice, SpecialPrice, SpecialPriceStartDateTimeUtc and SpecialPriceEndDateTimeUtc. I will leave this fun exercise for my readers. As a hint, a good starting point is to refactor the CatalogController like what we did for Price.

// decimal oldPriceBase = _taxService.GetProductPrice(product, product.OldPrice, out taxRate);
decimal oldPriceBase = _taxService.GetProductPrice(product, _priceCalculationService.GetProductInitOldPrice(product), out taxRate);

We now able show different Price and SpecialPrice for multi stores.

I built an admin screen base on the TierPrice screen and ProductCustomPrice() CRUD base on Nop.Admin.Controllers.ProductController.TierPriceList().

For example, the Reebok Core Price is $169.99 and Special Price is $118.99.

By introducing Per Store Custom Price, the Reebok NZ Local store would have a customized price of $200, an old price of $180 and a special price of $130.

Conclusion

This is not the best solution but the one of the possible solution. If I was a core nopCommerce developer, I would have changed the domain object to work with the prices.

I am very lucky that Australia and New Zealand use the same currency symbol. For wider audience sake, I think the solution needs to be extended to cover other area as well.

Setting up nopCommerce in Azure

I am preparing a migration plan for our nopCommerce sites from our data center to cloud. Since nopCommerce is available in the Azure Gallery, I thought I would just spin one up and see how it goes.

Installation

Gallery

From gallery, I chose nopCommerce. Note that they only have the latest version.

Database

After the instance is up, I have to go thru the standard installation guide for nopCommerce. Fairly straight forward non-cloud stuff.

So far so good, in fact I don't anticipate any issues at this stage.

Store Front

And we have an e-commerce store setup on Azure comfortably easy.

Setup

Plugins

I thought there will be some resistance with the plugins folder permissions, but in fact not. I installed a commercial pack of plugins and everything work straight out of the box. ;)

Email

Unfortunately email services wouldn't work just out of the box.

Since there is no SMTP server setup in the cloud, we will need to use 3rd party service to help us sending out emails. One of the popular one you can find on the Azure Marketplace is SendGrid.

I have my connection string set as follow in nopCommerce, and email sent out and received fine.

Payment Gateway

For our test, I have setup the Payments.PayPalDirect plugin.

After ordering some sample products from our website, the fund would go thru to PayPal Sandbox.

XML Drop

In some of the old fashion system, we are still relying on an older technology that I called Xml Drop or Xml Dump.

Basically what it does is in our site, we will generate an xml file that contains some information and the 3rd party integration system will have a collector to pick up those xml files and perform their other actions. These xml files are not exposed publicly via web, but in a form of secure ftp or a share drive via vpn with authentication and authorization.

Unfortunately I cannot find such products on Azure would do that. The closest item that I found is the Azure Storage but I cannot replace Xml Drop directly.

A better solution is to replace the Xml Drop by using Web Api. If the 3rd party system is not capable to call an api, we will then need to use Queue or ServiceBus.

Conclusion

At first, I anticipated I would get much resistance at the integration points or folder permission as it is a managed IIS instance, but it turns out it was pretty straight forward.

Using Message Queue with nopCommerce

Introduction

We have a few issues in our IPC process that some of our IPC calls are dropped out. I am looking for a more robust way to handle the calls. One thing I am looking at is the Microsoft Message Queue.

Installing

In Windows 8, MSMQ is part of the Windows Features. Installing is fairly straight forward.

Setup

By using the Management Console, I have created a new private queue called Test.

We will give IIS_IUSRS permissions to access the queue.

nopCommerce

I have skipped some of the steps on about extending the class. They were previously discussed in nopCommerce - Custom Service inherit from Core Service by Dependency Injection and new interface.

MessageQueueService

We will create a very simple class to handle MessageQueue operations. I would recommend something more robust and reliable for real life application.

using System.Messaging;

public interface IMessageQueueService
{
    void SendQueue(QueuedEmail queuedEmail);
}

public class MessageQueueService : IMessageQueueService
{
    public override void SendQueue(QueuedEmail queuedEmail)
    {
        if (queuedEmail == null)
            throw new ArgumentNullException("queuedEmail");

        using (MessageQueue messageQueue = new MessageQueue(@".\private$\Test"))
        {
            using (var transaction = new MessageQueueTransaction())
            {
                transaction.Begin();

                messageQueue.Send(queuedEmail, queuedEmail.Subject, transaction);

                transaction.Commit();
            }
        }
    }
}

DependencyRegistrar.cs

builder.RegisterType<MessageQueueService>().As<IMessageQueueService>();

WorkflowMessageService.cs

To use MessageQueue when sending out emails, we will need to extend the WorkflowMessageService class to consume our MessageQueueService. We need create a property for IoC, change our ctor, and override the SendNotification Method.

// Properties
protected readonly IMessageQueueService _messageQueueService;

Inside our ctor

_messageQueueService = messageQueueService;

In our override SendNotification(), we will call our queue service instead

// _queuedEmailService.InsertQueuedEmail(email);
_messageQueueService.SendQueue(email);

Queue

Do something on the site that will trigger an email. If everything is setup correctly, you should see a message added to the queue.

Conclusion

Although we have only done it for emails, but we can easily use it for any IPC calls. The sky is the limit.

By using a mean of MSMQ as a middle layer, we decouple our system from 3rd party integration points. Thus, conform with the ultimate rule of loose couple and high cohesion in OO design.

This concludes the most basic setup for using MSMQ in nopCommerce. The next part is for our 3rd party system to read from the queue - which is their problem :)

nopCommerce - Multi Tenancy - Round 3

Multi Tenancy in nopCommerce by itself seems like an on going project for me now. Previously, I talked about how I was able to create brand specific dll in one share code base. (No splitting / branching in Source Control, one code base only). I used MS Build to separate the dlls during compilation which is neat but clumsy, as most developers would overlook these places. These can be found in here.

http://tech.sunnyw.net/2014/07/nopcommerce-multi-tenancy-2.html
http://tech.sunnyw.net/2014/05/nopcommerce-multi-tenancy.html

Scope

Previously I mentioned,

nopCommerce uses App_Data\Settings.txt to store database connection string... it became a hassle to swap connection string for local development as web config transformation does not help...

The problem arise not only with development environment that I have to constantly swap connection string, but in the event of multi tenancy with same code base and separated database, the code needs to connect to the correct database automatically.

Also, this cannot be simply done by a plugin or a check of store id in nop, as we would need to connect to the right database to get the store context.

Last but not least. This article is base on nopCommerce 3.3.

Objective

During developments, the correct database will be picked for multiple bindings without changing Settings.txt.
During deployment, the correct connection string applied to the websites without manually changing the Settings.txt in production environment.
Conform with the nopCommerce coding standard, so we are making the least changes. Best coding practice comes after this.

Setup

For our development environment, we will add multiple domain bindings to one website.

Note we only need to bind once for all https traffic. Https binding always work on IP base not domain base, as domain information is stored in HTTP Header that is encrypted as part of the https protocol.

Although nopCommerce doesn't have multi-tenancy supported out of the box, but its multi-store functionality will help assign different stores base on the url. I have previously demonstrated that.

Note that we would not do multi binding for live environment as we still want to have separate app pool for different websites for now,

Code

Settings.txt

We need to create a bunch of Settings_{site}.txt, one for each site.

Settings_site1.txt
Settings_site2.txt
...

We will have different connection strings in each Settings.txt files.

DataSettingsManager.cs

Ideally I would like to extend this class and override the method in my own class. Unfortunately, this class does not implement any interface and no IoC involved. Plus, some vendor plugins also hard-coded the way how to instantiate this object, usually find in plugins that create their own ObjectContext.

In the LoadSettings and SaveSettings method, we will change the way how filename is picked by default to our private function.

filePath = Path.Combine(MapPath("~/App_Data/"), GetSettingFileName());

private string GetSettingFileName()
{
    var dbSettingName =  ConfigurationManager.AppSettings["DBSettingName"];
    var filePath = Path.Combine(MapPath("~/App_Data/"), dbSettingName);
            
    if (File.Exists(filePath))
    {
        return dbSettingName;
    }

    return filename;
}

Web.config Transformation

We added a new key in the web config.

<add key="DBSettingName" value="Settings_site1.txt" />

We also need to provide transformation for each build config.

<add key="DBSettingName" value="Settings_site2.txt" xdt:Transform="SetAttributes" xdt:Locator="Match(key)" />

Switch

To switch between sites during developments, we will use SlowCheetah to help transform our web.config. The correct connecting string will be picked via web config.

Conclusion

This is a step forward towards turning our sites to true multi-tenants environment. During development, I am now able to connect to the corresponding database just by transforming the webconfig.

In the future, I am hoping a particular store will always connect to its own db even if I bind multiple sites to only one set of dlls (one IIS website). Then I will be able to scale out the websites and provide high availability.

Brand specific dependencies need to be removed from web.config to another persistent storage. Maybe a share database or some sort.
Need to change how DataSettingsManager is instantiated. It needs to be done via IoC so it can be extended.
Find a balanced approach for scalibility.

How to exclude files and folders in .Net for deployment

Scope

We want to exclude some files or folders when we deploy / publish a project. They are mostly development only or internal folders that is not needed in productions.

Code

Create a new XML file in the project folder (the same folder that holds the .csproj or .vbproj file) and name it <projectname>.wpp.targets.
Use the following as the base, and modify to include what folders / files to exclude.

<?xml version="1.0" encoding="utf-8" ?>
<project toolsversion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <itemgroup>
    <excludefrompackagefolders include="[semi-colon-separated-folder-list]">
    </excludefrompackagefolders>
    <excludefrompackagefiles include="[semi-colon-separated-files-list]">
    </excludefrompackagefiles>
  </itemgroup>
</project>

Task

Today I have a task to strip off characters from a string that are not alphabets or numbers. I implemented it as extension method so that it is reusable.

Code

public static string ToAlphaNumericString(this string inString)
{
    if (string.IsNullOrEmpty(inString))
        return string.Empty;

    return new string(inString.Where(c => char.IsLetterOrDigit(c)).ToArray());
}

With linq, the code is short and simple. There are 2 interesting points in this.

char.IsLetterOrDigit()

One of the magic part of our magic line is the char.IsLetterOrDigit(). It checks if a character is letter or digit, then it will return as part of the list via linq.

ToArray()

The Where clause from linq returns IEnumerable. Normally we use ToList() to write the result to the memory for later use. However, because string is an array of chars in C#, we try to convert the linq result to an array, we then pass char[] to the constructor of string to instantiate a new object.

Thoughts

You probably already noticed by now. What I have done was checking up the characters one by one and instantiate a new string object with the characters. This has a potential performance issue as create an object is an expensive operation, at least for the purpose of stripping illegal characters. I have not done my benchmark yet, but I think StringBuilder will be faster.

EDIT: It turns out Linq is actually pretty good in performance according to some benchmarks.
Ref: http://stackoverflow.com/questions/11395775/clean-the-string-is-there-any-better-way-of-doing-it/11396039#11396039

Pages - Menu

Code

Performance

Scope

Business Case

Technical Overview

Prerequisite

Code

ProductCustomPrice

PriceCalculationService

CatalogController

Other Solutions that didn't work

Tier Price

Conclusion

Installation

Gallery

Database

Store Front

Setup

Plugins

Email

Payment Gateway

XML Drop

Conclusion

Introduction

Installing

Setup

nopCommerce

MessageQueueService

DependencyRegistrar.cs

WorkflowMessageService.cs

Queue

Conclusion

Scope

Objective

Setup

Code

Settings.txt

DataSettingsManager.cs

Web.config Transformation

Switch

Conclusion

Scope

Code

Further Reading

Task

Code

char.IsLetterOrDigit()

ToArray()

Thoughts