Enums and SQL Server 2005

Author Message
 Posted 8/1/2007 1:18:39 PM
StrataFrame User

StrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame User

Group: Forum Members
Last Login: 4/7/2012 5:51:27 PM
Posts: 386, Visits: 2,048
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

Post #10664
Add to Twitter Add to Facebook
 Posted 8/2/2007 3:22:16 AM
StrataFrame Developer

StrataFrame Developer

Group: StrataFrame Developers
Last Login: 3/21/2014 4:25:20 AM
Posts: 6,582, Visits: 6,717
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.

Public Enum MyEnum As Integer
        Pear = 0
        Apple = 1
        Orange = 2
    End Enum

    Private Sub GetEnumName()
        MsgBox(System.Enum.GetName(GetType(MyEnum), MyEnum.Apple))
    End Sub

    Private Sub GetEnumValue()
        MsgBox(CType(MyEnum.Apple, Integer).ToString())
    End Sub

Post #10674
Add to Twitter Add to Facebook
 Posted 8/2/2007 10:29:30 AM
StrataFrame User

StrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame UserStrataFrame User

Group: Forum Members
Last Login: 4/7/2012 5:51:27 PM
Posts: 386, Visits: 2,048
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

Post #10698
Add to Twitter Add to Facebook
 Posted 8/2/2007 8:16:16 PM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: Forum Members
Last Login: 10/16/2011 3:44:33 PM
Posts: 77, Visits: 787
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

Post #10700
Add to Twitter Add to Facebook
 Posted 8/3/2007 1:23:18 AM
StrataFrame Developer

StrataFrame Developer

Group: StrataFrame Developers
Last Login: 3/21/2014 4:25:20 AM
Posts: 6,582, Visits: 6,717
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

Public Enum MyEnum As Integer
    <MicroFour.StrataFrame.Tools.EnumDisplayValue("AppleLocalizationKey", True)> _
    Apple = 0
    <MicroFour.StrataFrame.Tools.EnumDisplayValue("OrangeLocalizationKey", True)> _
    Orange = 1
End Enum

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

Public Enum MyEnum As Integer
    <MicroFour.StrataFrame.Tools.EnumDisplayValue("An Apple", False)> _
    Apple = 0
    <MicroFour.StrataFrame.Tools.EnumDisplayValue("An Orange", False)> _
    Orange = 1
End Enum

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:

MicroFour.StrataFrame.Tools.Common.GetEnumDisplayValue(MyEnum.Apple)
Post #10701
Add to Twitter Add to Facebook
 Posted 8/5/2007 2:58:13 PM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: Forum Members
Last Login: 10/16/2011 3:44:33 PM
Posts: 77, Visits: 787
Trent,

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

Public Class clsEnum

    Public Enum DeviceTypes As Integer

        <MicroFour.StrataFrame.Tools.EnumDisplayValue("Scale", True)> _

        Scale = 1

        <MicroFour.StrataFrame.Tools.EnumDisplayValue("Area", True)> _

        Area = 2

        <MicroFour.StrataFrame.Tools.EnumDisplayValue("Substance", True)> _

        Substance = 3

        <MicroFour.StrataFrame.Tools.EnumDisplayValue("PLC", True)> _

        PLC = 4

    End Enum

 

    Public Shared Function GetEnumNameDeviceTypes(ByVal Value As Integer) As String

        Return MicroFour.StrataFrame.Tools.Common.GetEnumDisplayValue(CType(Value, DeviceTypes))

    End Function

End Class

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:

create assembly MyEnumAssembly from 'C:\development\UtilEnums\bin\Debug\UtilEnums.dll'

The message I get is :

Msg 10301, Level 16, State 1, Line 1

Assembly 'UtilEnums' references assembly 'microfour strataframe base, version=1.6.0.0, culture=neutral, publickeytoken=99fe9917f71608a7.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

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

Post #10725
Add to Twitter Add to Facebook
 Posted 8/5/2007 3:19:41 PM
StrataFrame Developer

StrataFrame Developer

Group: StrataFrame Developers
Last Login: 3/21/2014 4:25:20 AM
Posts: 6,582, Visits: 6,717
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 BigGrin  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. Smile

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 Smile

Post #10726
Add to Twitter Add to Facebook
 Posted 8/6/2007 8:38:45 PM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: Forum Members
Last Login: 10/16/2011 3:44:33 PM
Posts: 77, Visits: 787
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:

create assembly SystemWindowsForms from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll' with PERMISSION_SET = UNSAFE

go

create assembly SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll' with PERMISSION_SET = UNSAFE

go

create assembly MicroFourStrataFrameBase from 'C:\Program Files\MicroFour\StrataFrame\Assemblies\MicroFour StrataFrame Base.dll' with PERMISSION_SET = UNSAFE

go

create assembly MyEnumAssembly from 'C:\development\TMS Redevelopment\TMS\UtilEnums\bin\Debug\UtilEnums.dll' with PERMISSION_SET = UNSAFE

go

create Function GetEnumNameDeviceTypes(@value int) returns nvarchar(50) as external name [MyEnumAssembly].[UtilEnums.clsEnum].[GetEnumNameDeviceTypes]

go

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

SELECT [TMS201].[dbo].[GetEnumNameDeviceTypes] (1)

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

Post #10739
Add to Twitter Add to Facebook
 Posted 8/7/2007 3:25:32 AM
StrataFrame Developer

StrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame Developer

Group: StrataFrame Developers
Last Login: 1/22/2014 2:32:14 AM
Posts: 2,850, Visits: 2,191
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.

Post #10746
Add to Twitter Add to Facebook
 Posted 8/7/2007 5:28:26 PM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: Forum Members
Last Login: 10/16/2011 3:44:33 PM
Posts: 77, Visits: 787
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

Post #10756
Add to Twitter Add to Facebook


Similar Topics

Expand / Collapse

Reading This Topic

Expand / Collapse

Site Map - Home - My Account - Forum - About Us - Contact Us - Try It - Buy It

Microsoft, Visual Studio, and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.