Archive for August, 2009

LINQ to SQL Connection Strings

Like many developers, after reading several articles on the wonders of LINQ to SQL, I decided to incorporate it into the next project I did.  During the development phase, everything went very well.  I separated each layer of my solution into its own project and when it came time to define my business objects, I created a new .dbml file, opened a connection to my database and dragged the tables I wanted to use onto the designer screen as seen below.

dbml_file

I also added a connection string to the web.config file of my web project, but after the initial setup, I did not give a second thought to how it was that my data layer was connecting to the database.  In all the LINQ to SQL tutorials that I read, all that was necessary to connect to the database was to define a new data context and then use it like so.

public static tbl_page Page_GetByPageID(int pageID)

{

PageDataContext db = new PageDataContext();

var page = from p in db.tbl_pages

where p.PageID == pageID

select p;

What I did not realize, was that Visual Studio had taken the connection string directly from my server explorer and saved it into a file called “app.config” in my Object Model project.  When the solution was compiled, that connection string information was stored directly in the .dll and could not be changed.  As long as I was still working in development and querying the same database this was not an issue, however, when the first deployment was made to a remote production server, all of a sudden the project could not connect to the database specified in the web.config file of my web project.

To compound the problem, the error generated by SQL Server did not seem to point to an incorrect connection string, but instead indicated that there was some issue with the SQL Server installation itself.  The exact error was:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

Once the project was installed on the remote server, I actually spent a good deal of time trying to troubleshoot issues with SQL Server settings rather than looking at the source of my connection string.  When I finally did realize that my project was still trying to connect to the local database on my laptop, I spent another couple of hours trying to figure out what to do with LINQ to SQL since it, from what I could tell, had stored the local connection string in the code without any intervention from me.

The answer proved to be quite simple in the end.  I read a couple of different blog posts like this one from Rick Strahl and this one from Graham O’Neale.  Both of these posts recommended removing the connection string from the properties of the .dbml file as well as setting the “Application Settings” property to false shown here:

PreviewPane_file

This step is necessary to get the connection string out of your .dll’s and into the config file where it belongs, but their other recommendations dealt with making manual code changes to the datacontext constructor or the designer class of the .dbml file.  While both of those suggestions work, from what I can see, any such manual changes would be overwritten the next time you have to make a database change during development, which would require you to recode the manual changes each time the .dbml file changes.

Not wanting to have to have to recode any such changes every time the .dbml file changed, I kept looking for another solution and I found my answer by mistake.  After removing the connection string and setting “Application Settings” to false, I tried to compile my project and found that the data layer was giving me an error for each data context constructor that I tried to initialize.  The error said:

PageDataContext(string Connection)

Error:

‘PageDataContext’ does not contain a constructor that takes ‘0’ arguments

Clearly, now that no connection was hard coded into the .dbml file by the designer, it was wanting a connection string to be passed in when the data context was initialized.  I thought that would certainly be easy enough to do, so I defined a public variable at the top of each data layer class like so:

public static string conn = ConfigurationManager.ConnectionStrings[“myConnectionString”].ToString();

which of course points to the connection string in the web.config file of my web project and I changed each initialization of the data context object to use the new variable as follows:

PageDataContext db = new PageDataContext(conn);

Once that was done, everything compiled and worked like a charm both locally and on the remote server.  What I like about this also, is that I didn’t need to make any changes to base constructors or designer files of any of my object model classes.  (NOTE: In fairness to Rick, he mentioned doing this in the comments section of his post.  In my frustration, I just didn’t catch it the first time).

This is the type of thing that you only need to figure out once, but if you are trying to implement a LINQ to SQL project for the first time, it can be a very perplexing issue.  Most of the LINQ to SQL tutorials I read understandably focus on getting things up and running in your development environment, without a lot said about deployment.  If you are experiencing this issue, I hope this post saves you some and frustration.