I18n Tip: Deploying .NET Resx Resources w/User Defined Functions
Posted on Thu, Oct 22, 2009
Today's guest Internationalization Blogger is Carl Uhrik, a veteran I18n Consultant at Lionbridge. He has 10 years of i18n experience helping large and small companies solve their complex software i18n challenges. A special thanks to Carl for his contribution today!
Often with internationalization one needs to systematically localize text literal strings found in SQL code for a database. This might represent SQL scripts to initialize a database with initial text field values or perhaps perform some systematic maintenance operations on the database to update one set of text values to a different set of text values, or possible it corresponds to error message text returned from stored procedures or user defined functions in the database that will surface to an end-user. In any case, there are a great number of ways to deal with localized database strings. Typically this involves somewhat ad hoc mechanisms that are specific to the exact schema of the database at hand - using some combination of the following elements:
- Preparing special translated versions of the original SQL code
- Special tables to hold translated text values
- Special SQL scripts or code to INSERT/UPDATE text values
- XML-load or text-load Utilities
Carefully engineering such approaches, one might even get close to using standard Localization (L10n) resource files - especially exploiting XML (or XLIFF in particular), but there will be a need for an upstream/downstream transformational mechanism (i.e., some external coding or scripting) which again introduces ad hoc aspects to the process. When a large number of SQL scripts or DB tables are concerned, such ad hoc approaches can become a troublesome maintenance burden for ongoing support, both for a localization team and the development team for the end product.
The combination of Microsoft SQL Server 2005 and Visual Studio 2005 provide a surprisingly simple alternative. Basically, it allows a standard I18n approach of externalizing the translatable strings in SQL code to a standard .NET RESX resource file, replacing the strings with calls to new SQL User Defined Functions (UDFs). These UDF functions simply invoke standard underlying .NET code for the purpose of accessing the RESX resource. This is facilitated by 2 critical features of SQL Server 2005 and Visual Studio 2005:
- SQL Server 2005 contains its own .NET CLR hosting environment into which externally defined UDFs can be deployed from VS 2005
- Visual Studio 2005 has the concept of a "SQL Server Project" which provides a ready-made template for creating such an external .NET assembly in which the localized RESX resources and accessor method can be built and deployed to a SQL Server instance corresponding to the database in question
A Sample Walkthrough
Setting this up is really much easier than it sounds. It is assumed that you have SQL Server 2005 installed with at least a single database configured (it can be empty or contain just a single table if you like). Furthermore, it will assume you have Visual Studio 2005 installed although it is nearly the same for Visual Studio 2008.
Step 1
In Visual Studio there is a Database project type template (either for VB or C#) labeled "SQL Server Project" that lets you create a project to manage a set of user defined functions (among other things) associated to a database.
Once the project based on that template is created, if you have SQL Server running with the database of interest, one can simply choose the desired database connection via a popup as illustrated below:
Note in particular the database name (i18ntest) is used again below when testing.
If one skips over this configuration, later, right-click on the project node in the Solution Explorer, select Properties and under "database" project properties, one can "browse" for the appropriate database and thereby set the necessary connection string for the required SQL server database.
Step 2
Right-click on the project node in the Solution Explorer and select "Add", and then "User Defined Function...". Give it a filename (for example "L10nUdfFunctions.cs"), and click the "Add" button.
This gives you a sample function that looks like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString L10nUdfFunctions()
{
// Put your code here
return new SqlString("Hello");
}
};
Now test it by right-clicking on the project node in the Solution Explorer and choose "Deploy". The code will be compiled, and the function will be deployed to SQL Server database specified in the database connection property for the project. Open SQL Server Management Studio and write some script to execute the sample function in a query such as:
use i18ntestSEL
ECT dbo.L10nUdfFunctions()
This should return "Hello".
Disclaimer: Actually, this might not work as advertised due to default security configuration.
You may get the follow error:
Msg 6263, Level 16, State 1, Line 4Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
In order to enable execution of .NET Code in SQL 2005, you can use something like the following:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
It just needs to happen once - don't make it part of every SQL script you write! Consider the security issues carefully, and if you decide to undo it, it can be disabled again:
EXEC sp_configure 'clr enabled', 0;
RECONFIGURE WITH OVERRIDE;
GO
Alternatively, you can enable/disable it using Surface Area Configuration.
Step 3
Once the project above is working, it is easy to expand the set of functions to include the one(s) really desired.
First set up some resource files in the project in the usual way. For example, add a default resource such as Resource1.resx and a corresponding Resource1.it-IT.resx (or whatever locale you like) with a default set of keys String1, String2, String3 ... and whatever text strings as values. Note that strongly typed resources are not essential in this context - it will be a lot of extra bother for such a simple example. In any case, at this point, there is a default resource manager class and getstring method which can be used to do what is needed.
So, now we expand the functions in L10nUdfFunctions.cs with the code below for accessing the .NET resources which will be reflected as an expanded set of UDFs in the SQL Server environment:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using SqlServerProject2005_1.Properties;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString L10nUdfFunctions()
{
// Put your code here
return new SqlString("Hello");
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString getStr1(SqlString keystr)
{
CultureInfo ci = Resource1.Culture ;
String x = Resource1.ResourceManager.GetString(keystr.Value, ci);
return (x.Length < 1) ? new SqlString(keystr.Value) : new SqlString(x);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString getStr2(SqlString keystr, SqlString localestr)
{
string in_locale_str = localestr.Value;
CultureInfo ci ;
ci = new CultureInfo(in_locale_str);
if (ci == null) ci = Resource1.Culture;
String x = Resource1.ResourceManager.GetString(keystr.Value, ci);
return ((x==null) || (x.Length < 1)) ? new SqlString(keystr.Value) : new SqlString(x);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString getLoc()
{
CultureInfo ci = Resource1.Culture;
String x = (ci == null) ? CultureInfo.CurrentUICulture.Name : ci.Name;
return new SqlString(x);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString setLoc(SqlString localestr)
{ CultureInfo ci = Resource1.Culture ;
String x = (ci == null) ? CultureInfo.CurrentUICulture.Name : ci.Name;
SqlString last_locale = new SqlString(x);
CultureInfo new_ci = new CultureInfo(localestr.Value);
Resource1.Culture = (new_ci == null) ? Resource1.Culture : new_ci;
return last_locale;
}
};
Then Save, Rebuild and Deploy.
One can easily verify the state of this deployment from within SQL Server Management Studio. Just check under the DB in question for Programmability -> Function -> Scalar Function, which gives the list of UDFs that return strings (and other scalar types) such as getstr1 which have been deployed from Visual Studio 2005. Note one needs to "refresh" SQL Server Management Studio after each deploy to get current state, but no need to restart.
Below is a sample script to test success of the deployed UDF functions. It could be run either in SQL Server Management Studio as a new query or the database project test script (default=Test.sql) in Visual Studio 2005.
-- select i18ntest.dbo.setLoc('it-IT') ;
-- select i18ntest.dbo.setLoc('en-US') ;
select i18ntest.dbo.getStr1('String1')+' ~ '+i18ntest.dbo.getStr2('String1', 'it-IT');
select i18ntest.dbo.getStr1('String2')+' ~ '+i18ntest.dbo.getStr2('String2', 'it-IT');
select i18ntest.dbo.getStr1('String3')+' ~ '+i18ntest.dbo.getStr2('String3', 'it-IT');
select i18ntest.dbo.getStr1('String4')+' ~ '+i18ntest.dbo.getStr2('String4', 'it-IT');
-- select i18ntest.dbo.setLoc('it-IT') ;
-- select i18ntest.dbo.getLoc() ;
Note that some invocation of alternate locales is provided as comments above. Feel free to experiment with the various functions defined - setLoc, getLoc, getStr1 and getStr2 to get/set locale and retrieve strings for a specific locale.
Conclusion
Here we have presented an approach to externalizing text strings in SQL code, resourcing the translatable text as standard .NET RESX files. It involves replacing the strings with calls to UDFS to access RESX resources, using Visual Studio to deploy such UDFs from a "database project" into the SQL Server environment. This approach could also be used to augment other I18n SQL Server locale functionality as well in cases where one may want to supplement I18n features already present in SQL Server. However, we should remember that this approach is specific to SQL Server and Visual Studio. Moreover, there is the issue of the security implications associated with enabling the CLR execution in SQL Server (which may not be appropriate in some situations).