Click here to Skip to main content
15,867,330 members
Articles / Web Development / ASP.NET
Article

ASP.NET 2.0 Custom SQL Server ResourceProvider

Rate me:
Please Sign up or sign in to vote.
4.79/5 (21 votes)
22 May 20065 min read 209.5K   1.8K   105   64
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.

C#
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.

C#
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.

C#
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.

SQL
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.

C#
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.

XML
<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


Written By
Web Developer
United States United States
Technical Architect
Sungard HE

Comments and Discussions

 
QuestionHow to clear cache Pin
muralikrishna143143-Oct-18 3:24
professionalmuralikrishna143143-Oct-18 3:24 
QuestionWinforms Pin
Cool Smith17-Apr-18 12:13
Cool Smith17-Apr-18 12:13 
QuestionASP.NET Webforms Pin
Member 121215812-Sep-16 4:06
Member 121215812-Sep-16 4:06 
SuggestionReady Library with Database Resource Provider Pin
kryszal3-Jan-16 12:43
kryszal3-Jan-16 12:43 
Questionhow to use Custom Global ResourceProvider in javascript Pin
Nazneen Zahid13-Jun-14 2:12
Nazneen Zahid13-Jun-14 2:12 
Hi
I have created and able to use in asp.net controls but i have to show some message using javascript.
could you please let me know How i can do?
Javascript not support "$Resources:resourcetype,resourcekey " syntax.

Please help. its very urgent.

Thanks in advance
QuestionHow to Consume Custom SQL Server ResourceProvider in asp.net? Pin
Member 1000370222-Apr-13 23:56
Member 1000370222-Apr-13 23:56 
QuestionHow to Consume Custom SQL Server ResourceProvider in asp.net? Pin
HARI KRISHNA A17-Oct-12 20:22
HARI KRISHNA A17-Oct-12 20:22 
QuestionSample Pin
Benjie Fallar III10-Sep-12 22:37
Benjie Fallar III10-Sep-12 22:37 
GeneralCache reset Pin
maxtin20001-Aug-12 13:01
maxtin20001-Aug-12 13:01 
GeneralRe: Cache reset Pin
bsnu25-Jan-17 22:47
bsnu25-Jan-17 22:47 
QuestionHow to modify the code to use different sql table structure Pin
Gültekin KAYA17-Dec-10 21:27
Gültekin KAYA17-Dec-10 21:27 
GeneralAlways get error Pin
thx101022-Apr-10 23:52
thx101022-Apr-10 23:52 
GeneralRe: Always get error Pin
Gültekin KAYA30-Dec-10 3:11
Gültekin KAYA30-Dec-10 3:11 
GeneralVery nice, thank you. Works great with a very little work. If anybody needs a working sample for visual studio 2008 just ask Pin
andrea_scurci24-Nov-09 3:55
andrea_scurci24-Nov-09 3: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 Pin
reskatze14-Dec-09 21:08
reskatze14-Dec-09 21: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 Pin
Melvin Odell Tucker1-Sep-10 8:09
Melvin Odell Tucker1-Sep-10 8: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 Pin
co253-Sep-10 15:36
co253-Sep-10 15: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 Pin
drproject8-Jun-11 4:32
drproject8-Jun-11 4: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 Pin
PrathimaSindhu1-Nov-11 23:47
PrathimaSindhu1-Nov-11 23: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 Pin
Member 86253008-Mar-12 9:47
Member 86253008-Mar-12 9: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 Pin
Jain_sandeep16-Jul-12 0:53
Jain_sandeep16-Jul-12 0: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 Pin
CubicBlake8-Oct-12 23:16
CubicBlake8-Oct-12 23: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 Pin
mad17max20-Nov-12 8:22
mad17max20-Nov-12 8: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 Pin
Stickboysoup2-Mar-13 15:54
Stickboysoup2-Mar-13 15: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 Pin
alsayani30-Mar-14 23:04
alsayani30-Mar-14 23:04 

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

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