I have a SQL Server CLR Functions DLL that we have been using for some time. The only real problem with it is that it uses hard-coded strings when it needs to return human-readable strings (e.g. 'Yes','No'). All of the rest of our desktop application uses Resource strings and graphics to make the application language independent (e.g. allows for use of English, French, Spanish, etc.). I would like to find a way to allow resources from a language resource DLL to be looked up from within a stored procedure or function but have not found any articles referencing this (so far). This would be in the form of a function that accepts the language code and the resource name (e.g. GetResourceString('EN-US', 'ApplicationName') and returns the appropriate resource by requested type.
Does anyone have a procedure or method that shows how to load the resource assemblies into SQL server beside the CLR Functions DLL? I assume that each assembly needs to be signed but I don't know if the normal resource loading mechanism will work within SQL Server or if I can 'do-it-myself' by using reflection to load assemblies from within a CLR function?
I have not tried this specific requirement but SQL CLR assemblies can be dependent upon, and use functions from, other SQL CLR assemblies.
When you create an assembly, SQL Server also looks for any dependent assemblies of this assembly in the same location and also uploads them. It sets them to have the same owner as the main assembly that you're loading.
When you load an assembly, you can also have "is_visible" set to false, so that the assembly is only used to provide internal implementation for other assemblies in the database, instead of being cataloged directly.
There's no reason this couldn't be used to provide the equivalent of a language resource DLL, even if the standard mechanism isn't implemented directly.
You can then see the references between assemblies by querying the sys.assembly_references catalog view.