Blog » How To Extend ASPDNSF Product Name Field

How To Extend ASPDNSF Product Name Field

On of the nice features of AspDotNetStorefront is that it supports multiple languages right out of the box. It does this by storing the multiple locale phrases as XML fragments inside the particular field. So, in the case of a product name, this means stuffing each of the multi-lingual product names into the Name field in the Product table. It also needs to be properly defined in this field with the XML code so a sample, multiple language name field could contain something similar to the following:

<ml>
    <locale name=”de-DE”>CMS-6R4</locale>
    <locale name=”en-US”>CMS-6R4</locale>
    <locale name=”fr-FR”>CMS-6R4</locale>
    <locale name=”ja-JP”>CMS-6R4</locale>
</ml>

You can see how as the number of languages grows that even with relatively short product names, the total count of characters could easily overrun a shorter field. Unfortunately this is the case with the Name field in many of the tables; primarily the Product and Category tables. These fields are defined as nvarchar(400) rather than TEXT like the majority of other fields in the tables. A better soultion (if you rule out running on SQL2000 dbs) would be to convert these to nvarchar(MAX) fields.

The challenge of course, is that this kind of change can have a ripple effect; touching many other elements of the application. Below I’ll describe the steps I had to go through for another client recently on a v8.0.1.3 website.

The first thing to do (aside from backing up the database in case things go horribly wrong) was to expand the Name field of the Products table. For the example below, I’ve used SQL Mangler (I mean manager) to make the changes. Interestingly enough, I had to use a copy of the Manager/Workbench from SQL2005 as the one that goes with SQL2008 wouldn’t allow me to make the first change described below.

Go into Design Mode for the Product Table and you’ll see the default size of the Name field is nvarchar(400).

image

We are going to change this to nvarchar(MAX). You should be prompted that doing so will cause all associated indexes to be dropped. In this case, this is the IX_Product_Name index and according to the fine folks at ASPDNSF support, the performance hit on all but the largest site shouldn’t be a problem to allow this to happen.

image

Next, we need to turn our eyes towards the stored procedures that this applicaion relies on. The first one to modify is the aspdnsf_GetProducts stored procedure

image

If you search for the above code, you can replace it with the value shown below.

image

Next we move to modify the aspdnsf_GetProductComments stored procedure

image

You can see by the circled area below that in the CREATE statement for the #tmp table, we need to change the ProductName field to nvarchar(MAX).

image

 

 

 

 

 

Lastly, we’ll need to modify the aspdnsf_SearchProductComments stored procedure

image

Again, it is a simple matter of finding ProductName field (in this case in the #tmpProductComments table) and expanding it to MAX from 400 characters.

image

Thankfully, changing the Category Name is just a matter of modifying the length of that fields in the Category table just as we did at top for the Product table. There re no stored procedures or views that need to be modified to make this change effective

I’ve slapped this little tutorial together because I couldn’t find this information out there on the Internet and thought that if this were helpful to me, it might be helpful to others. If you’ve had to tackle a similar project or think you have a better way to skin this cat please feel free to leave me an on-topic comment down below!

Michael Gibbs
Stalk Me...
Latest posts by Michael Gibbs (see all)