web 2.0

ASP.NET MVC 5 Internationalization · How to Store Strings in a Database or Xml


In the previous post, ASP.NET MVC 5 Internationalization, I showed how to store the localization strings in ResX files. There is no technical reason why you cannot store the localization strings in a database or an XML file, or even in a text file located over a network.

In this post, I will provide a simple yet flexible architecture that allows for storing localization strings in any source (Database, Xml, or any). The provider will also support data annotations and generate a strongly typed resource class.

Note that you can see a summary in the tooltip box when you highlight a resource. This allows you to preview a resource without going to the data source (Database, xml, etc)

IResourceProvier simply has one method called GetResource.

    public interface IResourceProvider
    {
        object GetResource(string name, string culture);                
    }
    

This is the most basic requirement to have resources stored in a different data source. BaseResourceProvider implements this interface and provides some benefits such as caching (which is recommended for performance). BaseResourceProvider has two methods to be implemented: ReadResource and ReadResources. If you plan to have caching always turned on, then you only need to implement ReadResources.

The demo in this post is based on the solution in the previous post ASP.NET MVC 5 Internationalization

To add these classes to your project, open Package Manager Console, select your resources project and type

Install-Package i18n.ResourceProvider

This will also install two providers for you: DbResourceProvider and XmlResourceProvider:

How to store resources in a Database

First, we will use the database provider. The first thing to do is add the resource strings into our database.

Open Visual Studio Server Explorer, Right click on Create New SQL Server database.

Create a new table called Resources.

The primary key is a combination of two columns: culture and name. The data type of Value is nvarchar, which allows to store any language text. In this example, I stored all cultures in the same table, but you are not limited to this physical structure. You can change the table layout as you want by removing or adding more fields. You can even have a separate table per culture. It is flexible!

If you change the structure, you will need to update the provider DbResourceProvider.

Populate the table with all the resource strings (You can find them in the attachment in an SQL file)

Now delete the .resx resource files as they are not needed anymore.

The database provider code looks like the following:

using Resources.Abstract;
using Resources.Entities;

namespace Resources.Concrete
{
    public class DbResourceProvider : BaseResourceProvider
    {
        // Database connection string        
        private static string connectionString = null;
        public DbResourceProvider(){
            connectionString = ConfigurationManager.ConnectionStrings["MvcInternationalization"].ConnectionString;
        }
        public DbResourceProvider(string connection)
        {
            connectionString = connection;
        }
        protected override IList<resourceentry> ReadResources()
        {
            var resources = new List<resourceentry>();
            const string sql = "select Culture, Name, Value from dbo.Resources;";
            using (var con = new SqlConnection(connectionString)) {
                var cmd = new SqlCommand(sql, con);
                con.Open();
                using (var reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        resources.Add(new ResourceEntry { 
                            Name = reader["Name"].ToString(),
                            Value = reader["Value"].ToString(),
                            Culture = reader["Culture"].ToString()
                        });
                    }
                    if (!reader.HasRows) throw new Exception("No resources were found");
                }
            }
            return resources;
            
        }
        protected override ResourceEntry ReadResource(string name, string culture)
        {
            ResourceEntry resource = null;
            const string sql = "select Culture, Name, Value from dbo.Resources where culture = @culture and name = @name;";
            using (var con = new SqlConnection(connectionString)) {
                var cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@culture", culture);
                cmd.Parameters.AddWithValue("@name", name);
                con.Open();
                using (var reader = cmd.ExecuteReader()) {
                    if (reader.Read()) {
                        resource = new ResourceEntry {
                            Name = reader["Name"].ToString(),
                            Value = reader["Value"].ToString(),
                            Culture = reader["Culture"].ToString()
                        };
                    }
                    if (!reader.HasRows) throw new Exception(string.Format("Resource {0} for culture {1} was not found", name, culture));
                }
            }
            return resource;            
           
        }
       
       
    }
}
    

I used plain ADO.NET to access the resources, but you can use Entity Framework or any other tool you want.

Nothing needs to be changed in the Model class Person unless you have changed the namespace or the resources assembly. However, you will need to add or change the database connection string in the main Web.config file.

Now the final step is to generate a strongly typed resource class that will expose each resource name (ie key) as a static property. Since the resources are dynamic, I decided to write a helper tool (ResourceBuilder) to generate a C# class based on the resource provider you are implementing.

I prefer to create a new Console Application project and add it to that solution. This way it is separate from your main project, and every time you remove or add new resources in the future, you can just run this project and get a fresh copy.

using Resources.Utility;
using Resources.Concrete;
namespace ResourceBuilder 
{
    class Program
    {
        static void Main(string[] args)
        {
            var builder = new Resources.Utility.ResourceBuilder();
            string filePath = builder.Create(new DbResourceProvider(@"Data Source=(localdb)\Projects;Initial Catalog=MvcInternationalization;Integrated Security=True;Pooling=False"), 
                summaryCulture: "en-us");
            Console.WriteLine("Created file {0}", filePath);            
        }
    }
}

The ResourceBuilder.Create method accepts several parameters. The only required one is the resource provider instance from which the resources will be pulled. The summaryCulture parameter is optional and is intended to help you see the resource value summary while you type them (without going to the database to check the value):

summaryCulture accepts one of the cultures that you already implemented, and it displays the value for each resource in a summary tooltip. The other method parameters are documented in the source file.

Running the program generates a C# source file:

Copy the generated file into the Resource project. The file looks like this:

namespace Resources {
public class Resources {
    private static IResourceProvider resourceProvider = new DbResourceProvider(); 
    /// <summary>Add person</summary>
    public static string AddPerson
    {
        get
        {
            return (string) resourceProvider.GetResource("AddPerson", CultureInfo.CurrentUICulture.Name);
        }
    }
    /// <summary>Age</summary>
    public static string Age
    {
        get
        {
            return (string) resourceProvider.GetResource("Age", CultureInfo.CurrentUICulture.Name);
        }
    }
// and so on
  }
}

Try It Out

How to store resources in Xml

Create a new XML file called Resoucres.xml under the Resources project. Again, the file structure can be different from the one used in this example. However, if you change the XML file layout, you will need to update XmlResourceProvider.

Now you can either re-generate the resource class or simply update the provider property like below:

private static IResourceProvider resourceProvider = new  XmlResourceProvider(
        Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"bin\Resources.xml")
    ); // assume Resources.xml is in the bin folder

The Xml provider looks like this:

using Resources.Abstract;
using Resources.Entities;
namespace Resources.Concrete
{
    public class XmlResourceProvider: BaseResourceProvider
    {
        // File path
        private static string filePath = null;
        
        public XmlResourceProvider(){}
        public XmlResourceProvider(string filePath)
        {           
            XmlResourceProvider.filePath = filePath;
            if (!File.Exists(filePath)) throw new FileNotFoundException(
                string.Format("XML Resource file {0} was not found", filePath)
            );
        }
        protected override IList<ResourceEntry> ReadResources()
        {
           
            // Parse the XML file
            return XDocument.Parse(File.ReadAllText(filePath))
                .Element("resources")
                .Elements("resource")
                .Select(e => new ResourceEntry {
                    Name = e.Attribute("name").Value,
                    Value = e.Attribute("value").Value,
                    Culture = e.Attribute("culture").Value                    
                }).ToList();
        }
        protected override ResourceEntry ReadResource(string name, string culture)
        {
            // Parse the XML file
            return XDocument.Parse(File.ReadAllText(filePath))
                .Element("resources")
                .Elements("resource")
                .Where(e => e.Attribute("name").Value == name && e.Attribute("culture").Value == culture)
                .Select(e => new ResourceEntry {
                    Name = e.Attribute("name").Value,
                    Value = e.Attribute("value").Value,
                    Culture = e.Attribute("culture").Value
                }).FirstOrDefault();
        }
    }
}

I used LINQ to XML to access the resources, but you can use any method you like!

I hope this helps!
Any questions or comments are welcome!

Tags:

Comments

Joe United Kingdom, on 11/3/2013 9:58:16 PM Said:

Joe

Very useful tutorial. I've been doing internationalisation using XML but am considering moving it to the database. Didn't realise it was so easy!

Scott Hanselman United States, on 11/4/2013 8:42:47 AM Said:

Scott Hanselman

Really amazing resource. Thanks for taking the time to do this.

Raghav India, on 11/5/2013 8:31:01 PM Said:

Raghav

Ah.. amazing. But is there a way we can get rid of culture for each xml element, so that we can logically group the text per culture something like

<culture name="ar">
<resource name="something" value="somevalue"></resource>
</culture>

<culture name="en">
<resource name="en_something" value="en_somevalue"></resource>
</culture>

Raghav India, on 11/5/2013 8:34:42 PM Said:

Raghav

Also, if we need to add a new resource key,think this would involve generating new cs file again and compile again to generate fresh dll's,  think that would be expensive especially in production ?

Nadeem United States, on 11/6/2013 5:50:38 AM Said:

Nadeem

@Raghav,
Xml is so flexible that you can store cultures in almost infinite ways.

Yes, and that's why the Resources project is separate from the main MVC one.

sean Canada, on 11/12/2013 5:35:15 PM Said:

sean

The only suggestion or enhancement i would recommend is investigate using T4 Templates. Substitute T4 to generate the Resources.CS (C# code file) instead of the Console Application. A good resource is: msdn.microsoft.com/.../dd820620.aspx

Nadeem United States, on 11/12/2013 6:31:38 PM Said:

Nadeem

@sean,
Thanks. I already looked at that. There are some limitations to using T4 in this case. For example, you have to compile the assembly and then reference it in order to read the resources. Also, the lack of parameters at design time makes it harder to edit/update for developers.  

Sam Belgium, on 11/12/2013 7:51:27 PM Said:

Sam

I was not aware that it was that easy to change my resx files for a database.

Thanks for this article !

Mohamed RAHIM Morocco, on 11/13/2013 3:35:24 AM Said:

Mohamed RAHIM

Clean & Clear! Thanks

Non Intanon Thailand, on 11/26/2013 8:04:01 AM Said:

Non Intanon

awesome post, thanks!!

Daniel Netherlands, on 1/8/2014 12:13:27 AM Said:

Daniel

A very good approach, Nadeem - have you considered how this can be extended to work with JavaScript localization / internationalization, with the same backend framework...?

That would offer a very nice end to end solution...

Nadeem United States, on 1/12/2014 4:08:05 PM Said:

Nadeem

@Daniel,
Yes, Internationalization is definitely a very large topic. I still have a few more posts on my to do list and javascript is one of them.

sonu India, on 1/29/2014 9:45:11 PM Said:

sonu

Hey, at first i would like to thanks for your post.Right now i have to developed custom resource manager like database,xml.The main objective of this component is add,update and delete and retrieve resources from database at run time so that we don't compile the application.So tell me what i need to do....

Dimitar Yordanov Bulgaria, on 1/30/2014 10:41:11 PM Said:

Dimitar Yordanov

Nice!!! Thank you for this great post

ragil Indonesia, on 4/15/2014 5:38:18 PM Said:

ragil

great post!
is it possible to split xml file into each language?
ex: resources.en.xml etx

Nadeem United States, on 4/19/2014 2:12:39 PM Said:

Nadeem

@ragil,
Yes, of course. You need to update the Xml Resource provider to handle that.

Patrick United States, on 4/23/2014 8:38:01 AM Said:

Patrick

Hi Nadeem,

Is the source for this available at Github or some other location?

Thanks,
Patrick

Mesut Turkey, on 4/27/2014 3:02:42 AM Said:

Mesut

Hi Nadeem; thanks for your post, It was really great job; will we able to Add or edit new string resource records to the database; isn't that needed to generate cs file again and place it in bin folder at runtime??? is it secure?
The problem with asp.net internationalization is you cannot add languages or edit records dynamically.

Nadeem United States, on 4/29/2014 3:12:31 PM Said:

Nadeem

@Patrick,
the source code is available via the link "Download Code" at the top of this page.

Nadeem United States, on 4/29/2014 3:14:18 PM Said:

Nadeem

@Mesut,
You still need to update your project code to use the newly added resource record, so you have to compile your project again anyway.

Danilo Vulović Serbia, on 7/7/2014 3:41:15 AM Said:

Danilo Vulović

Your article is amazing. I'm just wondering, how I can use your provider(s) with Ninject?

Also, your example shows usage in single project(layer) application. What about multi-layer application?

Thanks one more time of this helpful article.

Nadeem United States, on 7/7/2014 5:19:43 AM Said:

Nadeem

@Danilo Vulović,
The resources project is a separate layer by itself. You can use Ninject in the same way you would with any other static class.

Add comment


(Will show your Gravatar icon)

  Country flag

Click to change captcha
biuquote
  • Comment
  • Preview
Loading



Google+