A connection string problem that perplexed me for a bit, and almost made me swear off of using the LinqDataSource has been solved, and here is the solution.

Problem:

It seems that when you use a LinqDataSource, you are kind of ‘stuck’ with the connection string that you used to generate your dbml file. This happens not to be the case, and like most problems with ASP.NET and its related languages, a little digging around in the MSDN library will allow you to find or create an elegant solution to a problem which seemingly rests in inflexibility.

Solution

The solution is to handle the ContextCreating event. Its event argument will be

an instance of LinqDataSourceContextEventArgs class. 

   1: <asp:LinqDataSource ID="LinqDataSource1" runat="server" 
   2:    OnContextCreating="LinqDataSource_ContextCreating_SamDC"
   3:     EnableInsert="True" TableName="Reviewers" />

The key here is to set the ObjectInstance property to the data context that you would like it to use. 

   1: public static void LinqDataSource_ContextCreating_SamDC(object sender, LinqDataSourceContextEventArgs e) 
   2: { 
   3:     e.ObjectInstance = new PSAMModel.SamDataContext(dbhelper.ConnectionString); 
   4: }

At this stage, I take this a little further for my projects. As you can see, I have a class called dbhelper, which has a static string property that contains my connection string. I use this because the environment that I code in, I have a possibility of building the project for up to four different web servers, each with a distinct SQL Server instance. Inside of dbhelper I have logic which determines whether to use a local connection string for debugging on a local instance of IIS, or to use another set of connection strings designed for our live servers. This is very handy, because promotions require virtually no thought, and who wants to think about long connection strings when you have just run through a bug list, writing tests, fixing code, and  refactoring? This is handy for any kind of scripted build environment you use, whether it is a Web Deployment Project, Nant, or another choice which allows you to do string replacement in config and/or source files.

Another thing that I have done with dbhelper is contained my event handlers inside of here, so that when I instantiate a new instance of a LinqDataSource, I can just handle the event, and then pass the event to a centralized handler. This way, I can change all LinqDataSource entities quickly and from one file.

Hope this helps someone. Till next time…

.w