Click here to Skip to main content
Click here to Skip to main content

ASP.NET 2.0 Custom SQL Server ResourceProvider

, 22 May 2006
Rate this:
Please Sign up or sign in to vote.
How to create your own ASP.NET 2.0 custom resource provider to replace resource files (resx) with SQL Server.

Introduction

I was working on a medium-sized ASP.NET 2.0 web application that had a requirement for internationalization/globalization. The default method for internationalization in ASP.NET 2.0 uses XML .resx resource files to store language specific resources. Generally speaking, there is a one-to-many relationship between .aspx files and .resx files. Every new .aspx file will require one or more .resx files. The development and maintenance of the .resx files will become an issue as a web application grows. Wouldn't it be great to store the resources in a database, like SQL Server? Luckily, ASP.NET 2.0 features are extensible, and you can roll-your-own resource provider.

I couldn't find much "official" documentation on how to write my own resource provider (if you find any, let me know!). But I found a few good examples on various blogs and went from there. There is a very good example of a Microsoft Access Provider here, which I used as basis for my own. My example may not be a 100% perfect fit for your particular situation, but you can certainly use mine as a starting point.

Assumptions

This article assumes a couple things. First, you have some understanding of ASP.NET and how .resx resource files are implemented by default. If you need a refresher on ASP.NET 2.0 globalization, check out the ASP.NET 2.0 QuickStart Tutorial. Second, you are proficient in C#. Finally, you have a basic understanding of SQL and SQL Server.

Let's start coding...

You start by creating a class the inherits from System.Web.Compilation.ResourceProviderFactory.

public sealed class SqlResourceProviderFactory : ResourceProviderFactory
{ 
    public SqlResourceProviderFactory()
    {
    }

    public override IResourceProvider 
           CreateGlobalResourceProvider(string classKey)
    {
        return new SqlResourceProvider(null, classKey);
    }
    public override IResourceProvider 
           CreateLocalResourceProvider(string virtualPath)
    {
        virtualPath = System.IO.Path.GetFileName(virtualPath);
        return new SqlResourceProvider(virtualPath, null);
    }
}

ASP.NET will call this object's methods. There is one method for local resources and one method for global resources. Pretty simple so far. Next, we need to create a SqlResourceProvider class that implements System.Web.Compilation.IResourceProvider.

private sealed class SqlResourceProvider : IResourceProvider
{
   private string _virtualPath;
   private string _className;
   private IDictionary _resourceCache; 
   private static object CultureNeutralKey = new object(); 

   public SqlResourceProvider(string virtualPath, string className)
   {
       _virtualPath = virtualPath;
       _className = className;
   } 

   private IDictionary GetResourceCache(string cultureName)
   {
      object cultureKey; 
      if (cultureName != null)
      {
          cultureKey = cultureName;
      }
      else
      {
          cultureKey = CultureNeutralKey;
      }

      if (_resourceCache == null)
      {
         _resourceCache = new ListDictionary();
      }
      IDictionary resourceDict = _resourceCache[cultureKey] as IDictionary; 
      if (resourceDict == null)
      {
          resourceDict = SqlResourceHelper.GetResources(_virtualPath, 
                        _className, cultureName, false, null);
          _resourceCache[cultureKey] = resourceDict;
      }
      return resourceDict;
   }
   object IResourceProvider.GetObject(string resourceKey, CultureInfo culture)
   {
      string cultureName = null;
      if (culture != null)
      {
         cultureName = culture.Name;
      }
      else
      {
         cultureName = CultureInfo.CurrentUICulture.Name;
      }

      object value = GetResourceCache(cultureName)[resourceKey];
      if (value == null)
      {
          // resource is missing for current culture, use default

          SqlResourceHelper.AddResource(resourceKey, 
                  _virtualPath, _className, cultureName);
          value = GetResourceCache(null)[resourceKey];
      }
      if (value == null)
      { 
          // the resource is really missing, no default exists

          SqlResourceHelper.AddResource(resourceKey, 
               _virtualPath, _className, string.Empty);
      }
      return value;
   }
   IResourceReader IResourceProvider.ResourceReader
   {
       get
       {
           return new SqlResourceReader(GetResourceCache(null)); 
       }
   }
}

OK, we're getting to the nuts and bolts of the provider. The most important method here is the GetObject() method because this is what ASP.NET calls to get a resource for a particular culture (language). We have to create one more class, that implements System.Resources.IResourceReader. I'll be honest, I'm not 100% sure why this is required, but ASP.NET must call it at some point during a web application's lifetime.

private sealed class SqlResourceReader : IResourceReader 
{
    private IDictionary _resources; 
    public SqlResourceReader(IDictionary resources) 
    {
        _resources = resources;
    } 
    IDictionaryEnumerator IResourceReader.GetEnumerator() 
    {
        return _resources.GetEnumerator();
    } 
    void IResourceReader.Close() 
    {
    } 
    IEnumerator IEnumerable.GetEnumerator() 
    {
        return _resources.GetEnumerator();
    } 
    void IDisposable.Dispose() 
    {
    }
}

So far, we've just created the plumbing code that hooks into ASP.NET. We still have to implement the class that reads the resources from SQL Server. But before we do that, let's create the SQL Server table that we will use to hold the resource data. I've kept this simple so I didn't include the primary key and indexing information on this table. The columns RESOURCE_OBJECT, RESOURCE_NAME, and CULTURE_NAME should probably be included in a primary key or unique index because we only want one resource per ASP page per culture.

CREATE TABLE ASPNET_GLOBALIZATION_RESOURCES
(
    RESOURCE_OBJECT     NVARCHAR(255), -- VIRTUAL PATH OR CLASS NAME

    RESOURCE_NAME       NVARCHAR(128), 
    RESOURCE_VALUE      NVARCHAR(1000),
    CULTURE_NAME        NVARCHAR(50)
)

I have chosen to store all my resources, both local and global, in one table. This table could very easily be broken in two. For my purposes, I'd rather have all the resource data in one table for ease of maintenance. The column RESOURCE_OBJECT holds either the .aspx file (for local resources) or the class name (for global resources). The CULTURE_NAME column holds a string identifying the culture/language like en-US, fr-CA, or es-MX. The columns RESOURCE_NAME and RESOURCE_VALUE hold the name/value pair for the specific culture and resource object. It's also worth noting that my implementation only stores string data. If you need to store binary data (like images files), you'll have to modify this example accordingly.

Database access code

Here is the class that does the actual SQL Server database access. You will notice that the GetObject() method from the SqlResourceProvider class uses this static class.

internal static class SqlResourceHelper
{
    public static IDictionary GetResources(string virtualPath, 
           string className, string cultureName, 
           bool designMode, IServiceProvider serviceProvider)
    {
        SqlConnection con = new SqlConnection(
          System.Configuration.ConfigurationManager.
          ConnectionStrings["your_connection_string"].ToString());
        SqlCommand com = new SqlCommand(); 
        //

        // Build correct select statement to get resource values

        //

        if (!String.IsNullOrEmpty(virtualPath))
        {
            //

            // Get Local resources

            //

            if (string.IsNullOrEmpty(cultureName))
            { 
                // default resource values (no culture defined)

                com.CommandType = CommandType.Text;
                com.CommandText = "select resource_name, resource_value" + 
                                  " from ASPNET_GLOBALIZATION_RESOURCES" + 
                                  " where resource_object = @virtual_path" + 
                                  " and culture_name is null";
                com.Parameters.AddWithValue("@virtual_path",virtualPath);
            }
            else
            {
                com.CommandType = CommandType.Text;
                com.CommandText = "select resource_name, resource_value" + 
                                  " from ASPNET_GLOBALIZATION_RESOURCES " + 
                                  "where resource_object = @virtual_path " + 
                                  "and culture_name = @culture_name ";
                com.Parameters.AddWithValue("@virtual_path", virtualPath);
                com.Parameters.AddWithValue("@culture_name", cultureName);
            }
        }
        else if (!String.IsNullOrEmpty(className))
        { 
            //

            // Get Global resources

            // 

            if (string.IsNullOrEmpty(cultureName))
            {
                // default resource values (no culture defined)

                com.CommandType = CommandType.Text;
                com.CommandText = "select resource_name, resource_value" + 
                                  " from ASPNET_GLOBALIZATION_RESOURCES " + 
                                  "where resource_object = @class_name" + 
                                  " and culture_name is null";
                com.Parameters.AddWithValue("@class_name", className);
            }
            else
            {
                com.CommandType = CommandType.Text;
                com.CommandText = "select resource_name, resource_value " + 
                                  "from ASPNET_GLOBALIZATION_RESOURCES where " + 
                                  "resource_object = @class_name and" + 
                                  " culture_name = @culture_name ";
                com.Parameters.AddWithValue("@class_name", className);
                com.Parameters.AddWithValue("@culture_name", cultureName);
            }
        }
        else 
        {
            //

            // Neither virtualPath or className provided,

            // unknown if Local or Global resource

            //

            throw new Exception("SqlResourceHelper.GetResources()" + 
                  " - virtualPath or className missing from parameters."); 
        } 
        ListDictionary resources = new ListDictionary();
        try
        {
            com.Connection = con;
            con.Open();
            SqlDataReader sdr = com.ExecuteReader(CommandBehavior.CloseConnection);
  
            while (sdr.Read())
            {
                string rn = sdr.GetString(sdr.GetOrdinal("resource_name"));
                string rv = sdr.GetString(sdr.GetOrdinal("resource_value"));
                resources.Add(rn, rv);
            }
        }
        catch (Exception e)
        {
            throw new Exception(e.Message, e); 
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
        
        return resources;
    }

    public static void AddResource(string resource_name, 
           string virtualPath, string className, string cultureName)
    { 
        SqlConnection con = 
          new SqlConnection(System.Configuration.ConfigurationManager.
          ConnectionStrings["your_connection_string"].ToString());
        SqlCommand com = new SqlCommand(); 
        StringBuilder sb = new StringBuilder();
        sb.Append("insert into ASPNET_GLOBALIZATION_RESOURCES " + 
                  "(resource_name ,resource_value," + 
                  "resource_object,culture_name ) ");
        sb.Append(" values (@resource_name ,@resource_value," + 
                  "@resource_object,@culture_name) ");
        com.CommandText = sb.ToString();
        com.Parameters.AddWithValue("@resource_name",resource_name);
        com.Parameters.AddWithValue("@resource_value", resource_name + 
                                    " * DEFAULT * " + 
                                    (String.IsNullOrEmpty( cultureName) ? 
                                    string.Empty : cultureName ));
        com.Parameters.AddWithValue("@culture_name", 
            (String.IsNullOrEmpty(cultureName) ? SqlString.Null : cultureName));   
   
        string resource_object = "UNKNOWN **ERROR**";
        if (!String.IsNullOrEmpty(virtualPath))
        {
            resource_object = virtualPath;
        }
        else if (!String.IsNullOrEmpty(className))
        {
            resource_object = className;
        }
        com.Parameters.AddWithValue("@resource_object", resource_object);
   
        try 
        {
            com.Connection = con;
            con.Open();
            com.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            throw new Exception(e.ToString());
        }
        finally 
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        } 
    }

The GetResources() method in this class does the actual SQL Server database access. The code is straightforward - build a SELECT statement, execute it, and load a ListDictionary object with the name/value pairs. You will notice two parameters that are not used - designMode and serviceProvider. You can write your SQL Server provider in such a way that Visual Studio 2005 can call it to pre-populate your database. I did not do this. If this is of interest to you, check out the link I mentioned in the beginning of this article.

The AddResource() method will create a resource in the database. I call this from the SqlResourceProvider class when a resource is missing. This is just an automatic way to discover any missing resources and populate the ASPNET_GLOBALIZATION_RESOURCES table.

Finally, to get ASP.NET to use your new classes, you have to modify your web.config.

<system.web>
  <globalization resourceProviderFactoryType=
       "YourNameSpace.SqlResourceProviderFactory" />
</system.web>

Conclusion

The C# code is implemented in its own namespace with sealed and internal classes. Besides being good object oriented programming, there are theoretical performance benefits for writing sealed classes. I say "theoretical" because I can't say that I've noticed any performance benefits (or done any benchmarking), but it makes sense. The .NET runtime can make optimizations because it knows that the sealed class will never be inherited.

I designed my classes and database structure such that I can populate the ASPNET_GLOBALIZATION_RESOURCES table with data but leave the CULTURE_NAME null and that becomes my default value. In the GetObject() method of SqlResourceProvider, an attempt is made to get a resource for a specific culture. If that comes back null, then an attempt is made to get a resource with a NULL culture. After that, there is no "fallback" mechanism, which is just a fancy way of saying the calling ASP.NET code will throw an error when it attempts to retrieve a resource and the ResourceProvider can't find the resource.

Since I couldn't find any documentation about the resource provider model, I'm not clear as to how and when the provider is called. I've noticed that I have to close my browser and re-launch for the provider to load new data from the SQL Server. It appears that after ASP.NET calls for resources, it saves them until the browser is killed.

I hope you find this code useful. I know that it can be expanded (and improved) to handle a variety of situations.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Jeff Modzel
Web Developer
United States United States
Technical Architect
Sungard HE

Comments and Discussions

 
Questionhow to use Custom Global ResourceProvider in javascript PinmemberNazneen.Insignia13-Jun-14 3:12 
QuestionHow to Consume Custom SQL Server ResourceProvider in asp.net? PinmemberMember 1000370223-Apr-13 0:56 
QuestionHow to Consume Custom SQL Server ResourceProvider in asp.net? PinmemberANASURI HARI KRISHNA17-Oct-12 21:22 
QuestionSample PinmemberBenjie Fallar III10-Sep-12 23:37 
GeneralCache reset [modified] Pinmembermaxtin20001-Aug-12 14:01 
QuestionHow to modify the code to use different sql table structure PinmemberGültekin KAYA17-Dec-10 22:27 
GeneralAlways get error Pinmemberthx101023-Apr-10 0:52 
GeneralRe: Always get error PinmemberGültekin KAYA30-Dec-10 4:11 
GeneralVery nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pinmemberandrea_scurci24-Nov-09 4:55 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pinmemberreskatze14-Dec-09 22:08 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask PinmemberMelvin Odell Tucker1-Sep-10 9:09 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pinmemberco253-Sep-10 16:36 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pinmemberdrproject8-Jun-11 5:32 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask PinmemberPrathimaSindhu2-Nov-11 0:47 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask PinmemberMember 86253008-Mar-12 10:47 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask PinmemberJain_sandeep16-Jul-12 1:53 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask PinmemberCubicBlake9-Oct-12 0:16 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pinmembermad17max20-Nov-12 9:22 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask PinmemberStickboysoup2-Mar-13 16:54 
GeneralRe: Very nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pinmemberalsayani31-Mar-14 0:04 
Generalhaving an issue in production Pinmemberpriyavvraj9-Apr-09 13:52 
QuestionWhen is it executed in the page life cycle? Pinmembercooldude956-Feb-09 1:38 
AnswerRe: When is it executed in the page life cycle? PinmemberGültekin KAYA30-Dec-10 4:23 
Generalcustom resource provider compile time errors PinmemberMember 285544918-Feb-08 19:56 
GeneralXmlResourceReader Pinmembergdycus17-Sep-07 13:06 
Newsan opt refactoring Pinmembergpolevoy28-Jun-07 0:58 
GeneralCaching Culture Info PinmemberTim McCurdy20-Jun-07 3:33 
GeneralExtending the custom provider for other repository types and adding Visual Studio Design Time support PinmemberRobThijssen14-May-07 23:23 
GeneralRe: Extending the custom provider for other repository types and adding Visual Studio Design Time support PinmemberJeff Modzel17-May-07 3:36 
QuestionHow to deploy resource file as xml file Pinmemberpgindia19-Apr-07 23:21 
GeneralAlso consider this article for review PinmemberShanPlourde27-Feb-07 18:56 
GeneralYou people rock PinmemberRob Sitter31-Jan-07 2:15 
GeneralRe: You people rock PinmemberJeff Modzel31-Jan-07 3:09 
General"I'll be honest, I'm not 100% sure why this is required..." PinmemberKamWaf19-Dec-06 6:52 
GeneralGreat work Pinmemberksmithers14-Dec-06 21:53 
QuestionImplementation Pinmemberadwooley219-Nov-06 19:16 
QuestionI like to know the answer as well (Jeff?) PinmemberKamWaf19-Dec-06 4:53 
AnswerRe: I like to know the answer as well (Jeff?) PinmemberJeff Modzel31-Jan-07 4:44 
QuestionRe: I like to know the answer as well (Jeff?) Pinmemberajdennis782-Feb-07 11:55 
QuestionResources namespace PinmemberMichelsen28-Sep-06 23:45 
AnswerRe: Resources namespace PinmemberJeff Modzel29-Sep-06 10:31 
AnswerRe: Resources namespace PinmemberSergio Pereira1-Aug-07 5:53 
QuestionHow can i create a WinForm ResourceProvider Pinmemberdarcand18-Sep-06 12:05 
AnswerRe: How can i create a WinForm ResourceProvider PinmemberJeff Modzel25-Sep-06 9:33 
AnswerRe: How can i create a WinForm ResourceProvider PinmemberTim McCurdy20-Jun-07 3:29 
GeneralModification for Local Resource Provider PinmemberHomam Hosseini9-Aug-06 23:22 
GeneralRe: Modification for Local Resource Provider PinmemberJeff Modzel10-Aug-06 4:06 
Both good points.
NewsRe: Modification for Local Resource Provider PinmemberMichelsen4-Oct-06 12:52 
GeneralRe: Modification for Local Resource Provider PinmemberSergio Pereira1-Aug-07 5:59 
GeneralRe: Modification for Local Resource Provider Pinmemberrkanzeg26-Feb-07 5:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 22 May 2006
Article Copyright 2006 by Jeff Modzel
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid