Changing Column Collations Through C# and ADO.NET

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 built on. We also look at how I solved the problem (which is with some cool C# and WPF code).

The Problem Context

Let's take a look at the big picture first, so we are all 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 someone's authorization, it's possible that 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 an 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 built 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 Problem

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) whereas 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 as well 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 its 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 straightforward. 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.