Hi,

We have a Enums class that contains all enums for the solution and some of these enum values actually make their way into the application database. In some queries it would be much better to show the 'text value' of an enum rather than it's integer value. Does anyone know if there is any way of doing such a thing in SQL Server 2005 now that CLR/managed code is supported? If there is can you point me to a reference or two.

Cheers, Peter

Well, the first thing that you will need to do is create a SQL CLR project.

Your BO will talk to the deployed SQL Project via a stored procedure.  If you use the DDT, it will deploy and create the stored procedure for you.  Just add the assembly to the DDT project and it will take care of the deployment side of things.

As for the enums, you can pull the value or text easily using the System.Enum shared methods.

Hi Trent,

Well I haven't tried this yet but I must say it seems easy - stunning!!!

Once again, thanks for the prompt response.

Cheers, Peter

Trent,

I've just had a go at implementing this idea, and it worked pretty easily. However I want to go one step further.

We have used the names of elements of the enums as the key for localisation. So I have the name, how do I localise it?

The application is likely to be used where there is more than one language used for an implementation, so although we could use a default language for the site, we would prefer to have it set on a per user basis, but how can we know the language being used by the user accessing the CLR code in the database? We are using the database to hold the Localisation data so the CLR code should be able to get at it reasonably.

Have you any thoughts about how this could be done?

Peter

Hey Peter,

Yeah, the SQL CLR projects are a really cool concept and work quite well.  We too had the need to localize enums and there is actually an attribute within the framework that allows enums to be localized...or provide a display value different than the actual character representation.

Using a Localization Key

Just providing a more readable display value that isn't localized

If you use any of the SF lists and populate using the enum, these values will be automatically pulled when populated.  To manually pull the localized or readable value you can call this:

Trent,

I gave that a go, but with no success. I have an enum class which now looks as follows:

The Keys (Scale, Area, Substance, PLC) exist in the MessageItems Table in the Database.

I've built this and tried to add it as an assembly to SQL Server as follows:

The message I get is :

When I attempt to load the Strataframe base assembly I get a similar message about system.windows.forms

Have I misunderstood what you were suggesting? Am I missing an important step?

Peter

Peter,

You are going in the right direction, but the one thing that you have to rememeber is that the SQL projects are bare-bones and have basically no references.  So you have to work a little bit more in order to get those references included and working in an SQL project.

The MOST complicated part of the SQL projects are getting them setup.  This always entails some additional work...and maybe a few cuss words   But once you get it setup, all future calls and methods are much easier.  Just for what it is worth, it took some effort for me as well the first time I setup an SQL project because of this very reason....it had no references that you are generally used to having already included in your project.....that and passing back a DataTable.

Stick with it on your references and keep adding them until the errors go away and then make sure that those references are available on the SQL Server machine.  In the end your efforts will be worth it

Trent,

I think I went off the rails a few steps back.

Because we already had a clsEnums project, I just extended that instead of starting with a new SQL CLR project. We don't use DDT, so I added the assembly and functions int SQL Server manually. The steps I used are as follows:

At this point I was able to run the following in a query

which gave the result "Scale" whereas the localised version should be "Scale." (notice period at the end to show the difference between the key and localised value).

Is this lack of localisation simply because runing the function in a Query window, there is no context that allows the function to know its language, and therefore it returns the key instead of the localisation? Would I have better luck if I called it from a stored procedure that was launched from a strataframe program?

At this point I re-read your advice and saw that I should have started with a SQL CLR project, so I started one of them and copied the code from earlier into it, and got errors about invalid references as expected. When I went to add references only the Projects Tab, which was empty, and an SQL Server Tab which had the message "The connection to the database failed for the following reason: There is no available SQL server connection. No server references can be added at this time, but you may continue to develop", were available. So how do I go about adding the references I need?

Thanks for your patience

Peter

On your question on how to retrieve the localized value, you can do so by using either the System.Globalization.CultureInfo.CurrentCulture.LCID to get the locale ID (of course, this will get the locale ID of the server so you'll need to need to make sure to set the proper region within the control panel) or pass over the locale ID when you call the SPROC.  As for the problem with adding the references, you should be able to click the button at the top of the Solution Explorer that says "Show All Files."  After that, you should see a new node under the project that says References.  Right-clicking on it will allow you to add a new reference.  Hope that helps. 

Oh, and the error about the SQL Server connection string is becuase that connection string is used for debugging the stored procedures.  If you set the conn string, VS will attempt to deploy the DLL and attach the remote debugger so that you can step into your sprocs when they're called.

Trent & Ben,

Thanks for the suggestions, but I was still not able to get it going. Although I would like to get the localisation going, it's not worth investing any more time on, so I'll leave it for now, and just stick to the unlocalised names of the enum values.

Your responsiveness has been brilliant as always.

Peter