Friday, February 8, 2008

Linq, LinqDataSource and Connection Strings

Today I figured something out that might be obvious to some but caused me confusion for a good amount of time. What I figured out is where the default constructor for a DataContext class gets the connection string it uses to connect to a database. You're probably already aware that when you create a new LINQ To SQL item in a Visual Studio 2008 project a custom DataContext derived class is generated by the SqlMetal tool (this happens invisibly in VS.) This DataContext has numerous constructors. One for example takes a connection string as an argument. A DataContext object created in this way will use the connection string supplied when it needs to access the database. In fact since I started using LINQ I have been relying heavily on this very constructor. I was using it despite the fact that I knew that there was a default constructor on the generated DataContext as well. The reason for this was that I wasn't really clear on where exactly the default constructor was getting its connection string value from. I did see that if I used the default constructor the data context would just automatically connect to the database I used to create the Linq To Sql file but that was all I knew. And since I do this for a living and most of the applications I develop are developed against a devel db and then deployed against a producton db I just sort of figured that the proper way to handle things would be to always use the previously mentioned DataContext constructor that takes a connection string argument. I then created a little utility method called GetDataContext that would read a connection string out of the app or web.config file and use that as the argument to the DataContext's constructor, passing back the resulting object. This method served me pretty well for a couple of months. But this morning it went pear-shaped on me. I was deploying a new LINQ-based web application to our production server. Once up there I tried to load the first page in my browser. To my surprise instead of the page I expected I got an error message that the SQL Server couldn't be reached. I checked and double checked my connection string value in my web.config to make sure it was pointing at the right place. It was. I verified that the same code ran fine in our test environment. It did. "What," I wondered, "could possibly be the problem?" My next step was to add some logging around that GetDB method. I told it to log the connection string it was using. I compiled and posted the new binaries and tried to access my page again. Again I got the error so I looked in the logs. Oddly enough there was no entry in the log at all for this last request. But I knew the page used the database to display a list. How was it that my GetDB method wasn't getting called? Then it dawned on me. For this particular list I'd decided to try out the new LinqDataSource object. I hadn't yet really worked with the LinqDataSource and I'd figured this would be a good opportunity. Now that I remembered using the LinqDataSource it occurred to me that I really didn't know where it was getting its connection string from. I knew that under the hood it must be creating an instance of my DataContext object. And I suspected it was using the default constructor. But how could I get it to use the proper connection string? First I looked at the properties on the LinqDataSource. There didn't appear to be anything there that would let me control the connection string. I then decided to reexamine my LINQ project and try to figure out where the default constructor's connection string was coming from. This led me to realize that the connection string was defined as a member of the Properties.Settings class and therefore the value for it was in the ConnectionStrings section of an app.config file in my LINQ project. So in order to get my application to work all I had to do was copy the connection string 'add' directive from the app.config in my LINQ project to the web.config of my web project and then update the connectionString value to the proper one for my production envirionment. Loaded up my page again and it worked like a charm. And now that I know this I'm going back and fixing all my LINQ applications to use the default contsructor. No more GetDB methods for me!