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:
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 v220.127.116.11 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).
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.
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
If you search for the above code, you can replace it with the value shown below.
Next we move to modify the aspdnsf_GetProductComments stored procedure
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).
Lastly, we’ll need to modify the aspdnsf_SearchProductComments stored procedure
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.
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!
At Over The Top and Exhibit A Communications, I've programmed solutions for Google as well as at least one other company that was later acquired by Google.
I've been CTO of an Internet SaaS company and spent my time pretty evenly between guiding the future technical strategy of the company, architecting software solutions for my dev teams, designing and running a data center to service our clients world-wide as well as being a technical evangalist/sales engineer to our media clients large and small.
I've also been Chief Photographer of the Daily Sun/Post newspaper back when it was a 5-day a week daily newspaper.
I also spent a great many years as a beach lifeguardfor the City of San Clemente as well as Jr. Lifeguard instructor and then as it's program coordinator.
Private Pilot with Instrument rating and proud husband of soon to be 25 years (and counting).