Subscribe

Close

Thank you for visiting!

Please consider subscribing to the RSS feed or following me on Twitter.

Dealing with different website and database locales

Using different locales

When I developed this blog, I wanted to use the en-GB locale to be able to present correct formats for dates and numbers.

It is simple to set a specific locale for a whole web application - you just have to add the <globalization> tag under <system.web> in Web.Config and set the culture and uiCulture attributes, like this:

<globalization culture="en-gb" uiCulture="en-gb"/>

So far - so good. Dates and numbers have the correct format and everything looks well - until in comes the database.

I use Sweden's best web hosting provider (according to a study published in 2009) and this means the SQL Server database's collation is set to Finnish_Swedish_CI_AS - as it should be.

Having a website and a database with different locales gives you problems. Trust me.

Out-of-range datetime value

The main problem I ran into was a database insert of an incorrect date - the date I tried to insert obviously had an en-GB format while my database expected a date with sv-SE format. This generated an out-of-range datetime value error.

At first, I tried rewriting the date to a correct format, but quickly realized it was wrong to do so. I ended up with one of the two correct solutions for this problem.

Solutions

The two solutions are:

  • Update database collation to match your website locale
  • Update SQL query to use parameters

I ended up with the latter one - updating my SQL query to use paremeters instead.

This is also how you want to insert and update your database tables - by using parameters with your command object (unless you use any ORM solution like NHibernate, but that's another blog post).

Here's a SQL query where parameters are used (disregard the lousy error handling):

string strSQL = "INSERT INTO mytable(user,password) VALUES(@user,@password)";

SqlConnection objConn = new SqlConnection(connString);
SqlCommand objCmd = new SqlCommand(strSQL, objConn);

objCmd.Parameters.AddWithValue("@user", "username");
objCmd.Parameters.AddWithValue("@password", "secretpassword");

try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch { }
finally
{
objConn.Close();
}

Conclusion

When developing a database driven website, make sure you know the website's locale and the database's collation. Also, use parameters with your SQL queries, not only when dealing with dates and numbers, but always. You don't know what input might try to sneak into the database otherwise.

Comments

Good post, Martin! :o)

A thought - how would the database react to InvariantCulture formatted datetimes and etc? Like so:

myDateTime.ToString("d", CultureInfo.InvariantCulture). This will output year, day and month in a culture-insensitive format which the database should be able to interpret. I'm not sure, though! :)

To the top