ASP.NET 2.0 Custom SQL Server ResourceProvider

• Download source files - 2.08 Kb

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.