L-2-sql in multiproject solution

I prefer to split my solution into many different projects. Though there is many benfits there is a problem with Linq-To-Sql in this setup. I want Linq-To-Sql to use the connection string in the Web.Config file but it actually places it's connection string in the Settings.setting file in the project where the datacontext lives. I will show you to get around this problem.

What you will need

I use Unity for dependency injection. You will probably need some dependency injection framework. Which one you use should not matter that much. 

The problem

Let's imagine we have the following structure

• Example(solution)
• Example.LinqToSqlData
• Example.Site

This is very simplified from what I normally have but it makes the example clearer. The problem is that DataContext in* Example.LinqToSqlData* stores it connection string inside that project. This means trouble when I for example deply my Webproject because Example*.LinqToSqlData *is then stored as a bin file with no real way to access the settings and change them.

What we want is to make the DataContext use the connection string in the Web.Config stored in *Example.Site *instead.

The solution

It turns our that the DataContext has a couple of overloads. Typically  I have used the one without parameters. This one reads the connection string from the settings file in that project.

it could be the app.config file it reads from as both contains the connection string, for this post it does not really matter which is it though.

The other overload we are interested in is the one that takes the connection string as a parameter. *DataContext(string connection) *is the signature of that one. What it does it that it allows us to say which connection string the context should use.

Reading the web.config connection string

To be able to give the DataContext the connection string in the Web.Config we need to read the We.Config. This i fairly easy though and can be done by this code:

System.Configuration.ConfigurationManager.ConnectionStrings["ExampleConnectionString"].ConnectionString;

So we can now read the connection string. But how to get this to the DataContext?

Using Unity

Because I want to keep my projects as losely coupled as possible I'm already using unity to map my repository interfaces to it's implementations. The first thing I need to is to change the contructor of the repository.

Here is the old code:

public ExampleRepository()
{
    db = new DataClassesDataContext();
}
DataClassesDataContext db;

Here is the new one:

public ExampleRepository(ConnectionStringReader reader)
{
    db = new DataClassesDataContext(reader.ConnectionString);
}
DataClassesDataContext db; 

As you can se the repository now takes an ConnectionStringReader in the constructor. I use the ConnectionStringReader reader parameter because I'm going to register this through unity. If I was passing the string around manually I would have used string connectionString.

Here is how the ConnectionStringReader looks like:

public class ConnectionStringReader
{
    public string ConnectionString { get; set; }
}

And my unity configuration looks like this:

UnityContainer container = new UnityContainer();

container.RegisterType<IExampleRepository, ExampleRepository>();

ConnectionStringReader reader = new ConnectionStringReader() { ConnectionString = ReadConnectionString() };
container.RegisterInstance<ConnectionStringReader>(reader, new ContainerControlledLifetimeManager());

The ReadConnectionString method simply read the connection string like I showed you earlier and returns that.

What I do is that I create my ConnectionStringReader and set the connection string. I then tell Unity to register that instance as the ConnectionStringReader to use whenever it needs to map an ConnectionStringReader. The second parameter tells Unity which LifeTimeManager to use. Here I don't want to read the connect string more then I have to so I choosed the ContainerControlledLifetimeManager() which means that Unity treats this object as a singleton. Whenever someone needs a ConnectStringReader this reader will be used.

Summary

This problem is likely to occur either when you deploy or if you develop your project on different machines. Now that you know about the problem and one possible solution you should plan for this right away when you start you next project. It saves a lot of pain to only have the connection string in one place.