How do you map a CultureInfo name to the CurrentLanguage of a SqlConnectionString?

I am attempting to dynamically specify the CurrentLanguage of the SQL connection strings used by an application at runtime based on the current culture information of the application.

My initial attempt was the following:

foreach (ConnectionStringSettings item in ConfigurationManager.ConnectionStrings)
{
    if (!this.ConnectionStrings.ContainsKey(item.Name))
    {
  开发者_StackOverflow社区      SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(item.ConnectionString);
        if (String.IsNullOrEmpty(builder.CurrentLanguage))
        {
            var culture = Thread.CurrentThread.CurrentUICulture ?? Thread.CurrentThread.CurrentCulture;
            builder.CurrentLanguage = culture.Name;
        }

        this.ConnectionStrings.Add(item.Name, builder.ToString());
    }
}

This however fails, as the Name of the CultureInfo does not map the the name in the sys.syslanguages of SQL server.

Is there a way to map one of the CultureInfo name properties to a SQL server language name, or am I stuck calling a user defined function that accepts an LCID and maps that to the appropriate name to pass to SET LANGUAGE?

After reviewing the sys.syslanguages documentation, it appears that there are 33 supported languages. Given such a small subset of languages, the final solution I ended up using was to create a SqlConnectionLanguage class that is used by a factory class to dynamically specify the SQL Server language name based on the LCID of the current culture.

Given the difficulty in mapping a CultureInfo to the SQL Server language name or alias, creating a utility class that encapsulated the 33 supported languages appears to be the simplest approach.

Factory class specifies connection language by doing the following:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(this.ConnectionStrings[name]);
if (String.IsNullOrEmpty(builder.CurrentLanguage))
{
    var culture = Thread.CurrentThread.CurrentUICulture ?? Thread.CurrentThread.CurrentCulture;

    if (culture != null)
    {
        var language = SqlConnectionLanguage.Create(culture);

        if (language != null)
        {
            builder.CurrentLanguage = language.Name;
        }
    }
}

return new SqlConnection(builder.ToString());

Implementation of the SqlConnectionLanguage class:

/// <summary>
/// Represents a language supported by SQL Server.
/// </summary>
/// <remarks>
/// See <a href="http://msdn.microsoft.com/en-us/library/ms190303.aspx">sys.syslanguages (Transact-SQL)</a> 
/// for more information about culture support in SQL Server.
/// </remarks>
[Serializable()]
public class SqlConnectionLanguage : INotifyPropertyChanged, INotifyPropertyChanging
{
    //=======================================================================================================
    //  Constructors
    //=======================================================================================================
    #region SqlConnectionLanguage()
    /// <summary>
    /// Initializes a new instance of the <see cref="SqlConnectionLanguage"/> class.
    /// </summary>
    public SqlConnectionLanguage()
    {

    }
    #endregion

    #region SqlConnectionLanguage(int id, int localeId, string name, string alias)
    /// <summary>
    /// Initializes a new instance of the <see cref="SqlConnectionLanguage"/> class 
    /// using the specified unique identifier, locale identifier, name, and alias.
    /// </summary>
    /// <param name="id">The unique identifier for the language.</param>
    /// <param name="localeId">The  Microsoft Windows locale ID for the language.</param>
    /// <param name="name">The official name for the language.</param>
    /// <param name="alias">The alternative name for the language.</param>
    public SqlConnectionLanguage(int id, int localeId, string name, string alias) : this()
    {
        this.Id         = id;
        this.LocaleId   = localeId;
        this.Name       = name;
        this.Alias      = alias;
    }
    #endregion

    //=======================================================================================================
    //  Public Properties
    //=======================================================================================================
    #region Alias
    /// <summary>
    /// Gets or sets the alternative name for this language.
    /// </summary>
    /// <value>
    /// The alternative name for this language. 
    /// The default value is an <see cref="String.Empty"/> string.
    /// </value>
    public string Alias
    {
        get
        {
            return _languageAlias;
        }

        set
        {
            if (PropertyChangeNotifier.AreNotEqual(_languageAlias, value))
            {
                using (new PropertyChangeNotifier(OnPropertyChanging, OnPropertyChanged))
                {
                    _languageAlias = !String.IsNullOrEmpty(value) ? value : String.Empty;
                }
            }
        }
    }
    private string _languageAlias = String.Empty;
    #endregion

    #region Id
    /// <summary>
    /// Gets or sets the unique identifier for this language.
    /// </summary>
    /// <value>
    /// A <see cref="Int32"/> value that represents the  unique identifier for this language. 
    /// The default value is <i>zero</i>.
    /// </value>
    public int Id
    {
        get
        {
            return _languageId;
        }

        set
        {
            if (PropertyChangeNotifier.AreNotEqual(value, _languageId))
            {
                using (new PropertyChangeNotifier(OnPropertyChanging, OnPropertyChanged))
                {
                    _languageId = value;
                }
            }
        }
    }
    private int _languageId;
    #endregion

    #region InstalledLanguages
    /// <summary>
    /// Gets the installed languages for the SQL Server database engine.
    /// </summary>
    /// <value>
    /// A <see cref="ConcurrentDictionary{Int32, SqlConnectionLanguage}"/> collection that contains 
    /// the installed languages for the SQL Server database engine.
    /// </value>
    /// <remarks>
    /// The collection is keyed by the <see cref="SqlConnectionLanguage.Id"/> of the 
    /// contained <see cref="SqlConnectionLanguage"/> elements.
    /// </remarks>
    public static ConcurrentDictionary<int, SqlConnectionLanguage> InstalledLanguages
    {
        get
        {
            if (_languageInstalledLanguages == null)
            {
                _languageInstalledLanguages = new ConcurrentDictionary<int, SqlConnectionLanguage>();

                _languageInstalledLanguages.TryAdd(0, new SqlConnectionLanguage(0, 1033, "us_english", "English"));
                _languageInstalledLanguages.TryAdd(1, new SqlConnectionLanguage(1, 1031, "Deutsch", "German"));
                _languageInstalledLanguages.TryAdd(2, new SqlConnectionLanguage(2, 1036, "Français", "French"));
                _languageInstalledLanguages.TryAdd(3, new SqlConnectionLanguage(3, 1041, "日本語", "Japanese"));
                _languageInstalledLanguages.TryAdd(4, new SqlConnectionLanguage(4, 1030, "Dansk", "Danish"));
                _languageInstalledLanguages.TryAdd(5, new SqlConnectionLanguage(5, 3082, "Español", "Spanish"));
                _languageInstalledLanguages.TryAdd(6, new SqlConnectionLanguage(6, 1040, "Italiano", "Italian"));
                _languageInstalledLanguages.TryAdd(7, new SqlConnectionLanguage(7, 1043, "Nederlands", "Dutch"));
                _languageInstalledLanguages.TryAdd(8, new SqlConnectionLanguage(8, 2068, "Norsk", "Norwegian"));
                _languageInstalledLanguages.TryAdd(9, new SqlConnectionLanguage(9, 2070, "Português", "Portuguese"));
                _languageInstalledLanguages.TryAdd(10, new SqlConnectionLanguage(10, 1035, "Suomi", "Finnish"));
                _languageInstalledLanguages.TryAdd(11, new SqlConnectionLanguage(11, 1053, "Svenska", "Swedish"));
                _languageInstalledLanguages.TryAdd(12, new SqlConnectionLanguage(12, 1029, "čeština", "Czech"));
                _languageInstalledLanguages.TryAdd(13, new SqlConnectionLanguage(13, 1038, "magyar", "Hungarian"));
                _languageInstalledLanguages.TryAdd(14, new SqlConnectionLanguage(14, 1045, "polski", "Polish"));
                _languageInstalledLanguages.TryAdd(15, new SqlConnectionLanguage(15, 1048, "română", "Romanian"));
                _languageInstalledLanguages.TryAdd(16, new SqlConnectionLanguage(16, 1050, "hrvatski", "Croatian"));
                _languageInstalledLanguages.TryAdd(17, new SqlConnectionLanguage(17, 1051, "slovenčina", "Slovak"));
                _languageInstalledLanguages.TryAdd(18, new SqlConnectionLanguage(18, 1060, "slovenski", "Slovenian"));
                _languageInstalledLanguages.TryAdd(19, new SqlConnectionLanguage(19, 1032, "ελληνικά", "Greek"));
                _languageInstalledLanguages.TryAdd(20, new SqlConnectionLanguage(20, 1026, "български", "Bulgarian"));
                _languageInstalledLanguages.TryAdd(21, new SqlConnectionLanguage(21, 1049, "русский", "Russian"));
                _languageInstalledLanguages.TryAdd(22, new SqlConnectionLanguage(22, 1055, "Türkçe", "Turkish"));
                _languageInstalledLanguages.TryAdd(23, new SqlConnectionLanguage(23, 2057, "British", "British English"));
                _languageInstalledLanguages.TryAdd(24, new SqlConnectionLanguage(24, 1061, "eesti", "Estonian"));
                _languageInstalledLanguages.TryAdd(25, new SqlConnectionLanguage(25, 1062, "latviešu", "Latvian"));
                _languageInstalledLanguages.TryAdd(26, new SqlConnectionLanguage(26, 1063, "lietuvių", "Lithuanian"));
                _languageInstalledLanguages.TryAdd(27, new SqlConnectionLanguage(27, 1046, "Português (Brasil)", "Brazilian"));
                _languageInstalledLanguages.TryAdd(28, new SqlConnectionLanguage(28, 1028, "繁體中文", "Traditional Chinese"));
                _languageInstalledLanguages.TryAdd(29, new SqlConnectionLanguage(29, 1042, "한국어", "Korean"));
                _languageInstalledLanguages.TryAdd(30, new SqlConnectionLanguage(30, 2052, "简体中文", "Simplified Chinese"));
                _languageInstalledLanguages.TryAdd(31, new SqlConnectionLanguage(31, 1025, "Arabic", "Arabic"));
                _languageInstalledLanguages.TryAdd(32, new SqlConnectionLanguage(32, 1054, "ไทย", "Thai"));
            }
            return _languageInstalledLanguages;
        }
    }
    private static ConcurrentDictionary<int, SqlConnectionLanguage> _languageInstalledLanguages;
    #endregion

    #region LocaleId
    /// <summary>
    /// Gets or sets the Microsoft Windows locale ID for this language.
    /// </summary>
    /// <value>
    /// A <see cref="Int32"/> value that represents the Microsoft Windows locale ID for this language. 
    /// The default value is <i>zero</i>.
    /// </value>
    public int LocaleId
    {
        get
        {
            return _languageLocaleId;
        }

        set
        {
            if (PropertyChangeNotifier.AreNotEqual(value, _languageLocaleId))
            {
                using (new PropertyChangeNotifier(OnPropertyChanging, OnPropertyChanged))
                {
                    _languageLocaleId = value;
                }
            }
        }
    }
    private int _languageLocaleId;
    #endregion

    #region Name
    /// <summary>
    /// Gets or sets the official name for this language.
    /// </summary>
    /// <value>
    /// The official name for this language. 
    /// The default value is an <see cref="String.Empty"/> string.
    /// </value>
    public string Name
    {
        get
        {
            return _languageName;
        }

        set
        {
            if (PropertyChangeNotifier.AreNotEqual(_languageName, value))
            {
                using (new PropertyChangeNotifier(OnPropertyChanging, OnPropertyChanged))
                {
                    _languageName = !String.IsNullOrEmpty(value) ? value : String.Empty;
                }
            }
        }
    }
    private string _languageName = String.Empty;
    #endregion

    //=======================================================================================================
    //  Public Methods
    //=======================================================================================================
    #region Create(CultureInfo culture)
    /// <summary>
    /// Creates a new <see cref="SqlConnectionLanguage"/> using the specified <paramref name="culture"/>.
    /// </summary>
    /// <param name="culture">The <see cref="CultureInfo"/> to create</param>
    /// <returns>
    /// A new <see cref="SqlConnectionLanguage"/> using the specified <paramref name="culture"/>. 
    /// If no SQL Server language exists for the specified <paramref name="culture"/>, 
    /// returns a <see langword="null"/> reference (Nothing in Visual Basic).
    /// </returns>
    /// <exception cref="ArgumentNullException">The <paramref name="culture"/> is a <see langword="null"/> reference (Nothing in Visual Basic).</exception>
    public static SqlConnectionLanguage Create(CultureInfo culture)
    {
        Guard.AgainstNullReference(culture, "culture");

        return InstalledLanguages.Values.FirstOrDefault(language => language.LocaleId == culture.LCID);
    }
    #endregion

    #region ToString()
    /// <summary>
    /// Returns a string that represents the current <see cref="SqlConnectionLanguage"/>.
    /// </summary>
    /// <returns>
    /// A string that represents the current <see cref="SqlConnectionLanguage"/>.
    /// </returns>
    public override string ToString()
    {
        return this.Alias;
    }
    #endregion

    //=======================================================================================================
    //  INotifyPropertyChanged Implementation
    //=======================================================================================================
    #region PropertyChanged
    /// <summary>
    /// Occurs when a property value changes.
    /// </summary>
    /// <remarks>
    /// The <see cref="PropertyChanged"/> event can indicate all properties on the object have changed 
    /// by using either a <b>null</b> reference (Nothing in Visual Basic) or <see cref="String.Empty"/> 
    /// as the property name in the <see cref="PropertyChangedEventArgs"/>.
    /// </remarks>
    public event PropertyChangedEventHandler PropertyChanged;
    #endregion

    #region OnPropertyChanged(string propertyName)
    /// <summary>
    /// Raises the <see cref="PropertyChanged"/> event.
    /// </summary>
    /// <param name="propertyName">The name of the property that changed.</param>
    protected void OnPropertyChanged(string propertyName)
    {
        this.OnPropertyChanged(new PropertyChangedEventArgs(propertyName));
    }
    #endregion

    #region OnPropertyChanged(PropertyChangedEventArgs e)
    /// <summary>
    /// Raises the <see cref="PropertyChanged"/> event.
    /// </summary>
    /// <param name="e">A <see cref="PropertyChangedEventArgs"/> that contains the event data.</param>
    protected void OnPropertyChanged(PropertyChangedEventArgs e)
    {
        PropertyChangedEventHandler handler = this.PropertyChanged;

        if (handler != null)
        {
            handler(this, e);
        }
    }
    #endregion

    //=======================================================================================================
    //  INotifyPropertyChanging Implementation
    //=======================================================================================================
    #region PropertyChanging
    /// <summary>
    /// Occurs when a property value is changing.
    /// </summary>
    /// <remarks>
    /// The <see cref="PropertyChanging"/> event can indicate all properties on the object are changing 
    /// by using either a <b>null</b> reference (Nothing in Visual Basic) or <see cref="String.Empty"/> 
    /// as the property name in the <see cref="PropertyChangingEventArgs"/>.
    /// </remarks>
    public event PropertyChangingEventHandler PropertyChanging;
    #endregion

    #region OnPropertyChanging(string propertyName)
    /// <summary>
    /// Raises the <see cref="PropertyChanging"/> event.
    /// </summary>
    /// <param name="propertyName">The name of the property that is changing.</param>
    protected void OnPropertyChanging(string propertyName)
    {
        this.OnPropertyChanging(new PropertyChangingEventArgs(propertyName));
    }
    #endregion

    #region OnPropertyChanging(PropertyChangingEventArgs e)
    /// <summary>
    /// Raises the <see cref="PropertyChanging"/> event.
    /// </summary>
    /// <param name="e">A <see cref="PropertyChangingEventArgs"/> that contains the event data.</param>
    protected void OnPropertyChanging(PropertyChangingEventArgs e)
    {
        PropertyChangingEventHandler handler = this.PropertyChanging;

        if (handler != null)
        {
            handler(this, e);
        }
    }
    #endregion
}

I think that CultureInfo has a LCID property. This should map to the LCID column in the sys.syslanguages table. So you should probably query the sys.syslanguages table with this LCID to get the value from the Name column, and then pass this string to your ConnectionStringBuilder. For example: SELECT name FROM sys.syslanguages WHERE lcid = 1033 will return "us_english".

I realize you need a connection to the DB to do this and therefore a connectionstring, so I might be giving an useless answer here. But you can query the languages without specifying the CurrentLanguage.

Maybe this is very ugly... but you could to smth like this:

 builder.CurrentLanguage = culture.EnglishName.Substring(0, culture.EnglishName.IndexOf(" "))

Also, perhaps you could make use of an Windows API function like here How to convert Microsoft Locale ID (LCID) into language code or Locale object in Java

and then maybe play around with it until it suits you. Either way, good luck:)