I’ve been with my current company for a little over a year now. I was hired to manage the conversion of one of their main websites to a new Content Management System (CMS). While this may not seem like a huge challenge, when you factor in the current site supports 10+ languages and 20+ countries, I definitely had some learning to do.

Kentico CMS for ASP.NET was our selected platform and version 6 was the current version when we started building the site. If you’ve not used Kentico, certain template values are contain and translated using something called UI Culture Strings. So, on the English site, the key “srch.dialog.AllWords” would display “All Words” and the Dutch – Netherlands site would display “Alle woorden.”

Managing and entering these string values is a tedious process that must be done one by one. The interface for managing these strings in Kentico requires several click to enter just one translated value. Our site has almost 300 custom strings that needed translating and some cultures (Country and Language combinations) would eventually use the same strings.

For instance, our French-Canadian site would use the same string values as our French-France site. Not wanting to clicked and edit almost 300 terms that were already in the database for another culture, I decided to devise a quicker way to do it. All it took was a little digging in the Kentico database and a little bit of SQL to get things to work.

Here are the steps you can use to copy existing UI Culture Strings in Kentico to a New Culture. The example code below shows how I copied the Dutch values for our Netherlands site to our Dutch – Belguim site.

Be aware, we will be working on and modifying your Kentico database. You should backup your database before attempting any of the code below. Also, UI Culture IDs will be unique to each site, so don’t just copy and paste the code below.

Open your database in Microsoft SQL Server Management Console and we’ll get started.

List Your Cultures

The first thing you’ll want to do is get a list of the cultures (country and language combinations) currently used on your site. The following SQL:

SELECT UICultureID, UICultureName, UICultureCode	

FROM CMS_UICulture

ORDER BY UICultureName

Will generate a list similar to this:

The important part to note is the UICultureID. This will be necessary to determine the culture you will copy and the culture you will populate. In my example, I want to copy Dutch-The Netherlands (88) to Dutch-Belgium(87).

Count Your Translation Terms

Next, you’ll want to check your default culture (in my case English-US) and see how many custom strings I have. This is just to ensure that your default terms and second culture terms are both completely translated.

SELECT TranslationID, TranslationStringID, TranslationUICultureID, TranslationText

FROM [CMS_ResourceTranslation] AS a

JOIN CMS_ResourceString AS b

ON a.TranslationStringID = b.StringID

WHERE StringIsCustom = 1 AND TranslationUICultureID = 11

We want to pass the StringIsCustom with a value of 1, since we’re not worried about the default Kentico strings. I can see that I’ve got 277 terms. Next I’ll check to see if my Dutch-The Netherlands terms are complete by changing my query to TranslationUICultureID = 88.

SELECT TranslationID, TranslationStringID, TranslationUICultureID, TranslationText

FROM [CMS_ResourceTranslation] AS a

JOIN CMS_ResourceString AS b

ON a.TranslationStringID = b.StringID

WHERE StringIsCustom = 1 AND TranslationUICultureID = 88

This returns 278 as well, so we’re ready to create new translations strings for Dutch-Belgium.

Insert New Strings for the New Culture

We want to insert new values for Dutch-The Netherlands (with a TranslationUICultureID = ’88’) for every value that is populated and that is a custom string. The code I’ll use is below:

INSERT INTO CMS_ResourceTranslation (TranslationStringID,TranslationUICultureID,TranslationText)	
	
	SELECT TranslationStringID, '87', TranslationText
	
	FROM [CMS_ResourceTranslation] AS a

	JOIN CMS_ResourceString AS b

	ON a.TranslationStringID = b.StringID

	WHERE StringIsCustom = 1 AND TranslationUICultureID = '88'

That may look a little confusing, so I’ll break it down for you.

INSERT INTO CMS_ResourceTranslation (TranslationStringID,TranslationUICultureID,TranslationText)

The first line is our insert statement for the CMS_ResourceTranslation table. There are 4 columns in this table: TranslationID, TranslationStringID, TranslationUICultureID, TranslationText. The TranslationID is the auto-increment identity field for the table, so we don’t have to worry about populating data for that column. The three columns we want to populate are: TranslationStringID, TranslationUICultureID, TranslationText.

SELECT TranslationStringID, '87', TranslationText

We use a SELECT to create the new records in the CMS_ResourceTranslation table, but we want to designate them as Dutch-Belgium, which from our first step, we know has a TranslationUICultureID of 87. Our SELECT statement (above) shows that we’ll use the current record value for TranslationStringID and TranslationText, but we will manually set the TranslationUICultureID to 87.

	FROM [CMS_ResourceTranslation] AS a

	JOIN CMS_ResourceString AS b

	ON a.TranslationStringID = b.StringID

This SQL joins the two Translation tables and allows us to select only the custom strings we’ve created.

WHERE StringIsCustom = 1 AND TranslationUICultureID = '88'

Finally, the WHERE clause determines that we only want the custom strings we’ve created (StringIsCustom = 1). The most important part of the WHERE clause is the TranslationUICultureID = ’88’ that creates the new records based on the terms we’ve entered in Kentico for Dutch-Netherlands.

Once you’ve double checked your values and ensured you’re creating records for your new culture–Dutch-Belgium(87)–based on the records you’ve already translated for your existing culture–Dutch-The Netherlands(88)–you can execute your SQL INSERT statement.

If you’ve done everything correctly, you should get a confirmation message that shows the correct number of records (in my case 277) were created in the database.

And there you have it. We’ve eliminated hours of clicking, copying and pasting with a few simple SQL commands.

If you have any questions or comments, just leave a comment on the post and I’ll do my best to answer them.