Adventures while building a Silverlight Enterprise application part #23

Today we look into how collations in Sql Server bugged me while trying to get code generation to work on a database different from what it was originally build on. We also look at how I solved the problem (which is with some cool C# and WPF code).

The problem context
Lets take a look at the big picture first, so we all are on the same page as to why I ran into this problem. In the application we are building we have multiple databases to store our LOB data in. Depending on someones authorization, it's possible he or she has access to one or more of these databases (or sometimes only subsets of these databases). Because of this, we needed some central data store to hold information on security, the complete installation and on where what LOB database is and what data is in it. We call this data store the Repository and stated that any data we need to go across LOB database boundaries, should be in the Repository.

So far, so good. In the early stages of the project we build a WCF service, based on Entity Framework, that allows us to do any data operations on the LOB databases in a generic way. To achieve this we build a code generator to generate all the business classes we needed. The code generation process we use for the service is based on the fact that we use a automatically generated EF model. In other words, the EF model is a direct depiction of the SQL Server data model. Because of this fact we can extract metadata from SQL Server to feed our code generation process. To do this, our database guys build a script that extracts the metadata from SQL Server (using the system views) and insert it into a separate database which we use to power our code generation.

Up to this point, still no problems. Now what we wanted to do, was copy all the code we've build to access the LOB databases and from that build a WCF service to access our Repository and because of that I needed to generate business classes from the model as it is inside our Repository database. Now normally we create and update our database models through a Sql Server 2005 project in Visual Studio 2008 and do schema compares.
However, data is added to this metadata by several people and in that case we tend to use a backup to distribute this data, which was exactly what I did when preparing to add the metadata from our Repository.

The probem
After restoring the metadata database is when the problems started. I needed to alter the stored procedure written to extract the metadata from a database to change the source database name to make it access the repository database. As soon as I ran the alter procedure statement to update the stored procedure, I got several collation conflict errors. It turns out that a backup I got from the Repository database was created in a different collation, from the one I normally use. To be more specific the Repository database was created with Latin1_General_CI_AS (my local default) where as the metadata database was created with SQL_Latin1_General_CP1_CI_AS.

To solve this issue I would need to go through each column one by one and change the collation. Because the only data accessed from the Repository is in system views and you can't (and don't want to) change their collations, I had to change the collations on the metadata database. I didn't feel much for doing this by hand and because I foresee this happening more often, I figured I might aswell write a small tool to handle this for me. Here is a screen shot of what it looks like:
Basically it allows you to type in a Sql Instance name, after which it retrieves the database list from that instance and also it retrieves all available collations to fill the two comboboxes. As soon as you select a database and at least a source collation, you can then click the Find Columns button to retrieve any columns with the source collation. After you've selected a target collation and unchecked whatever columns you do not want to change, you can then click the Change Collations button and it will trigger an alter table / alter column query to try and do that for you.

I guess what gives this tool it's flair is the use of some queries directly on ADO. Here is a snippet of code with these queries:

private const string GetAllDatabasesSqlCommand = "select name from sys.databases";
private const string GetAllCollationsSqlCommand = "select name from ::fn_helpcollations()";
private const string GetAllColumnNamesSqlCommand = "select o.name, c.name, t.name, c.max_length, c.is_nullable"
+ " from sys.columns c"
+ " left join sys.objects o on c.object_id=o.object_id"
+ " left join sys.types t on c.system_type_id = t.system_type_id"
+ " where c.collation_name=@collation_name";

The first query retrieves all database names. This obviously only works when connected to the master database.
The second query returns all collation names. This can be done on any database (it should always return the same list).
The final query finds all columns that use a specific collation. The result set includes the table name, the column name, the type name, the max length and whether or not a column can contain NULL. This is all the information needed to generate an alter table alter column statement which changes only the collation.

Something I never used before was the SqlConnectionStringBuilder class to dynamically create my connection string. Here is the code I used for that:

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
connectionStringBuilder.DataSource = hostnameTextBox.Text;
connectionStringBuilder.InitialCatalog = MasterDatabaseName;
connectionStringBuilder.IntegratedSecurity = true;

As you can see, using this class is really straight forward. Another thing I never used before was the ChangeDatabase method on the SqlConnection class. This came in handy as I could simply keep one connection throughout the flow of the application and switch databases quickly.

I've uploaded the code here for your viewing pleasure. Note that this tool was slammed together in a hurry so most of the code is in code behind for the main window and most of it isn't very well written, however there are some nice concepts in there as well

I hoped you enjoyed yourself again. I know I have.