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)
{
SqlResourceHelper.AddResource(resourceKey,
_virtualPath, _className, cultureName);
value = GetResourceCache(null)[resourceKey];
}
if (value == null)
{
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),
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();
if (!String.IsNullOrEmpty(virtualPath))
{
if (string.IsNullOrEmpty(cultureName))
{
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))
{
if (string.IsNullOrEmpty(cultureName))
{
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
{
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.