Mulitple Languages for my Objects

MADCookie posted on Thursday, October 11, 2007

Admittedly I'm a bit nervous posting here. I have read a ton of posts and there are a bunch of sharp developers here. Forgive my ignorance, please.

I have a current class library using CSLA 2.1.0-061003. My objects return data in English from my SQL Server. I now have to support Spanish.

I understand ASP.NET mechanisms regarding culture and globalization as it applies to resource files, but I need to better understand the concept as it applies to my data living in cells in my database. Could anyone give me a gentle kick in butt to point me in the right direction as to how to start this adventure? I have read Ch.4 regarding DataPortalContext and it providing my ClientUICulture, but I'm not sure how to use this knowledge.

I appreciate your patience

ajj3085 replied on Friday, October 12, 2007

Hi,

No need to feel nervous posting here.

If your database contains stuff you need to localize, you'll need to come up with a scheme there. Its similar to how you'd use a resource file in .Net. Basically, you need a list of strings which are identified by some key which is language neutral. Since you're starting in english, some english key would be fine. Then you have a table of supported locales, and another table which uses a the locale and "string id" to look up translated strings.

Something like this:
Locale
LocaleId int
Locale varchar( 5 )

String
StringId int
StringKey varchar( 10 )

LocalizedString
   LocaleId int
   StringId int
   LocalizedString nvarchar(max)

If you have a DocumentStatus table, it might look like this
DocumentStatus
   DocumentStatusId int
   StringId int

HTH
Andy

MADCookie replied on Friday, October 12, 2007

Thank you ajj3085. That is nearly the exact db scheme I was thinking. I feel good that you confirmed my idea.

When I was learning about localization in ASP.NET, I made a test web site where I played around switching my browser's language choice and also making UI on a web page so, as a user, I could change my culture (overriding page's InitializeCulture() method).

Today, I will try to apply the same concept for my CSLA objects. Do I need to code my CSLA objects differently once I have a database schema? Specifically, I'm wondering if at some point I have to get the user's culture choice and pass it in to ... I don't know what -- the dataportal_xyz methods?? Sorry. It is not connecting in my brain, hence the reason I have to just play with it. Do you have any tips or land minds to avoid?

Thanks again for your help. I appreciate your time on help me.

MADCookie replied on Friday, October 12, 2007

Hopefully, this will be my last confirmation post.

Here is my BO's dataportal fetch

        Public Shared Function GetQuestionInfo(ByVal QuestionId As Integer) As QuestionInfo
            If Not CanGetObject() Then
                Throw New System.Security.SecurityException("User not authorized to view a QuestionInfo")
            End If

            Return DataPortal.Fetch(Of QuestionInfo)(New Criteria(QuestionId))
        End Function

Going deeper into the covers, I have this method that basically sets up the SPROC and command for execution

        Private Sub ExecuteFetch(ByVal criteria As Criteria, ByVal cn As SqlConnection)
            Dim currentCultureName As String = System.Globalization.CultureInfo.CurrentUICulture.Name
            ' i.e. currentCultureName = "en"
            Dim cm As SqlCommand = cn.CreateCommand()
            cm.CommandType = CommandType.StoredProcedure
            cm.CommandText = "up_srvy_Question_Get"
            cm.Parameters.AddWithValue("@QuestionId", criteria.QuestionId)
            cm.Parameters.AddWithValue("@Culture", currentCultureName)

            Dim dr As ASH2.Data.ASHDataReader = New ASH2.Data.ASHDataReader(cm.ExecuteReader())
            If dr.Read() Then
                FetchObject(dr)
            End If
        End Sub

Would you all use System.Globalization or System.Threading or something (magic is CSLA?) to get the culture info?

Confirmation needed: Csla.Server.DataPortal Fetch() is setting the System.Threading culture info based on the client, so it is "safe" for my BO to request System.Globalization culture because it will be set with the client's desire?

Thanks again everyone for sharing your experience!

esteban404 replied on Friday, October 12, 2007

Both return a similar object, the difference is where they get it. From the Threading namespace you'll get info from within that thread's context, the Globalization namespace will get it from the system's defined/selected object. If you need to allow the user to select a language, then use the thread. Depending on your users, they may want to keep their formats eventhough they are working in a different language. You can accomodate them without too much trouble. (Note: not too much trouble, but you'll always find some lout who wants their "I"s doted differently: like a proportional dingbat heart or something. That's line in the sand time.)

MADCookie replied on Friday, October 12, 2007

I hope I'm not responding too soon-- I don't want to invoke Murphys.Law(of MyApplication). So far everything looks great! I appreciate all of your help! My changes are working beautiful.

In case anyone else tries this or needs details like me, here is some of my specific elements

Tables

Question
    QuestionId INT (PK)
    CategoryPageId INT
    Visible BIT
    DisplayText VARCHAR(400)
    DisplaySubText VARCHAR(400)

Question_Culture
    QuestionId INT
    Culture VARCHAR(15)
    DisplayText NVARCHAR(4000)
    DisplaySubText NVARCHAR(4000)

SPROC snippet

SELECT
    A.[QuestionId],
    A.[CategoryId],
    A.[Visible],

    -- Culture checking
    ISNULL(B.DisplayText, A.DisplayText) AS 'DisplayText',
    ISNULL(B.DisplaySubText, A.DisplaySubText) AS 'DisplaySubText'

FROM [dbo].[Question] A WITH (NOLOCK)
    LEFT OUTER JOIN QUestion_Culture B ON
        A.QuestionId = B.QuestionId
        AND B.Culture = @Culture
WHERE
    A.[QuestionId] = @QuestionId

My BO's command action
Dim currentCultureName As String = System.Globalization.CultureInfo.CurrentUICulture.Name
            ' i.e. currentCultureName = "en"
            Dim cm As SqlCommand = cn.CreateCommand()
            cm.CommandType = CommandType.StoredProcedure
            cm.CommandText = "up_srvy_Question_Get_Culture"
            cm.Parameters.AddWithValue("@QuestionId", criteria.QuestionId)
            cm.Parameters.AddWithValue("@Culture", currentCultureName)

            Dim dr As ASH2.Data.ASHDataReader = New ASH2.Data.ASHDataReader(cm.ExecuteReader())
            If dr.Read() Then
                FetchObject(dr)
            End If

There is composite key on the _Culture table for the Id and Culture text. I will probably create a look up table for the culture strings and use a CultureId as Andy suggested.

I also need to deal with the differences in "es" versus "es-MX". I have the elements in my database specifically set to "es-MX" because my control is setting the thread that way. I think that is fine since I need the "MX" part for ASP.NET to deal with currency and dates automatically on my static resource-file based text.

Again, THANKS!

esteban404 replied on Friday, October 12, 2007

If the data living in your cell is numeric, the globalization will affect formatting in your presentation, i.e. commas instead of decimal point. If your data is text, then that text needs to be translated and stored by language and called for based on the UI language. You can get that info from the ClientUICulture object... and more.

MADCookie replied on Friday, October 12, 2007

esteban404:
If the data living in your cell is numeric, the globalization will affect formatting in your presentation, i.e. commas instead of decimal point. If your data is text, then that text needs to be translated and stored by language and called for based on the UI language. You can get that info from the ClientUICulture object... and more.

_E

Ah, good points. I just replied to Andy asking about land minds. I'm glad you mentioned this.

Specifically, do you know if I have to write code to make the globalization of the numeric data reformat? In standard ASP.NET static text, I would not have to. I would just set the thread to use whatever CultureInfo and then the rendered HTML would pop out appropriately -- dates & currency were the easy to see.