Satellite assemblies in SQL Server 2005

This is a sample on how to register satellite assemblies in SQL Server 2005.

Based on the CultureInfo on the executing thread, CLR will try to load the respective resource assembly. It should not be a difference between satellite assemblies inside and outside of the SQL CLR other than location: if for a normal application the satellite assemblies need to be located in a special name subdirectory or in GAC for SQL CLR, the satellite assemblies should be registered inside of the database using Create Assembly command. Remember that the SQL Server will only load the assemblies registered in the database.

The naming convention is mandatory: for assembly A, resources assembly file should be named as A.resource.dll. The sql name given at the registration time is not important.

Also I observed from my tests that it is mandatory to have a match between the versions of the root assembly and resources assembly (but this is not specific to SQL CLR).

In my bellow test I am changing the CultureInfo of the current thread in order to check that the right resource assembly is loaded.

I have already created 2 resource files Test.resources and Test.fr.resource that contain an entry 'test', with values ‘default’ and 'fr' with the following code.

IResourceWriter rw = new ResourceWriter(strFileName);

rw.AddResource(strResName, strResValue);

rw.Close();

This is the assembly code that tries to consume the resource and change the CultureInfo. My assembly is strong named using TestKeyPair.key so you will need to use your own key pair there.

using System.Reflection;

using System.Threading;

using System.Globalization;

using System.Resources;

[assembly:AssemblyVersion("1.3.0.0")]

public class cTest

{

        public static string MainMethod(string strCul)

        {

                 if (strCul!="" && strCul!="def")

                 {

                         Thread.CurrentThread.CurrentUICulture = new CultureInfo (strCul);

                 }

                 ResourceManager rm = new ResourceManager ("Test", Assembly.GetExecutingAssembly());

                 return rm.GetString("test");

        }

}

This is the command to compile assembly:

csc.exe /target:library /out:Test.dll Test.cs /r:system.dll /res:Test.resources /keyfile:TestKeyPair.key

This is the command that I used to create the resource assembly (note the version, culture and key used for signing):

al.exe /out:Test.resources.dll /v:1.3.0.0 /c:fr /embed:Test.fr.resources /t:lib /keyf:TestKeyPair.key

This is the TSQL code used to register assemblies and clr user defined function in the database:

CREATE ASSEMBLY Test FROM 'C:\temp\meta\Test.dll' WITH PERMISSION_SET=UNSAFE

go

CREATE FUNCTION dbo.f_SatTest (@p_culture nvarchar(400))

RETURNS nvarchar(400)

AS

EXTERNAL NAME Test.cTest.MainMethod

go

CREATE ASSEMBLY Test_FR FROM 'C:\temp\meta\Test.resources.dll'

go

SELECT name FROM sys.assemblies WHERE name LIKE 'Test%'

go

SELECT dbo.f_SatTest('')

go

SELECT dbo.f_SatTest('fr')

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm

Cross Posted from https://blogs.microsoft.com/sqlclr