LINQ to SQL DataContext and Connection Problems

Question

I am using LINQ to SQL for my website to access two databases. The first database is the website (which i'll call WEBSITE) data, the second is a history of transactions (which i'll call WEBSITE_HISOTRY). When I added a table from the WEBSITE_HISOTRY to my datacontext not so long ago i recieved some sort of alert that I clicked OK on (probably not the best idea). I do recall it was something about Visual Studio complaining that the connection for the database differed from my config or something along those lines. Everything worked fine until I published to my server. I kept getting a database not found error and when I logged the connection strings I found this.

WEBSITE_HISTORY

Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=WebSiteHistory;Integrated Security=TRUE

WEBSITE

Data Source=my.dyndns.net; Network Library=DBMSSOCN; Initial Catalog=Website; User ID=WebsiteUser;Password=*;

I also found that the constructor for the WEBSITE_HISTORY datacontext required a conneciton string (unlike the WEBSITE which has a parameterless constructor). I altered the constructor but everytime a I add a table to the datacontext, it changes back. I had read in another question about setting the datacontext connection properties to Application = true. I have tried this but I cannot set the "Settings Property Name" to the correct connection.

Im not sure what I did do incite this behavior. Any help would be greatly appreciated.

Answer 1

What you did is not going to work. A database context is a connection to a single database. You should not attempt to add tables from other databases to a particular context. In fact, this probably simply won't work. If you find yourself dealing with multiple databases, you are going to need to have multiple database context objects, one for each database.

BTW, I think you'll find you will get more answers to your questions, if you accept more of the responses to your questions as answers. Your acceptance rate is only about 25%. This might discourage people from responding to you.

Answer 2

The data context should have multiple overloaded constructors available to you; one of them asks for a connection string. I would recommend using this one all the time; the reason is this allows you to change to point to a new database that has the same structure, and it will work that way. I tend to create a static class to create my data context classes to make this easier:

public static class DCCreate
{
   public static DC Create()
   {
     return new DC(ConfigurationManager.ConnectionStrings["CName"].ConnectionString);
   }
}

Answer 3

I gave up and stopped fighting with the designer. I am now passing the connection string as Brian had suggested (i.e. DataAccess.Properties.Settings.Default.WEBSITEHISTORY_ConnectionString) and it works. When i examined the Properties file i found that the WEBSITE_HISTORY property was missing this

[global::System.Configuration.SpecialSettingAttribute(global::System.Configuration.SpecialSetting.ConnectionString)]

I havent looked into it yet but I am betting that this was causing it to use the default connection in my DataAccess properties and not the web config.

Per Brian's comments, the scenario that he described is what i believe to have caused this to begin with.

Thank you for all of the help!