25 November 2007

LINQ and Web Application Connection Strings

It's usually a good idea to seperate Biz Layer Objects and Data Access Objects into seperate assemblies, but when you do that and use LINQ to SQL (.dbml) files the DataContext object default ctor uses the Settings file that is automattically generated as the default connection string instead of the configuration file connectionString setting.

When you drag a database object to the dbml file an app.config file is created, but there isn't an option anywhere in properties to actually use it.  We really don't want to have to manage connection strings in multiple places and when dev'n a class library it's usually best to leave that up to the client of the class library especially if you own dev of both projects.  (not considering service interface here)

Here's what I recommend to simplify the connection string management,

1) Create the dbml file in the class library project and drag a database object onto the design surface.

2) Find the default parameterless ctor for the DataContext something like this and remove it.

 

public DataClasses1DataContext() :

base(global::LINQConnStringLib.Properties.Settings.Default.ConnectionString, mappingSource)

{

OnCreated();

}

 

3) Add a reference to the System.Configuration DLL.

4) Create a new CS file to hold the Data Context partial class that will contain the default ctor and the following code to it (replacing class name and conn string name for your project)

public partial class DataClasses1DataContext

{

public DataClasses1DataContext() : base (ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)

{

OnCreated();

}

}

5) Add the connection string to your application configuration file.

<configuration>

<connectionStrings>

<add name="ConnectionString"

connectionString="x"

providerName="System.Data.SqlClient" />

</connectionStrings>

</configuration>

6) Delete the settings file if you don't need it.  It will be created again if you add new objects to the dbml file so I just leave it there.

7) Right click on the DBML file design surface and chose properties.  Select "none" for the Connection property.  That way the default ctor won't be created in the generated file.

The down side to this approach is that you have to set the Connection property to none after you add new objects to the dbml file, but it's not as bad as having to manage the connection string in multiple places.

 

Hope this helps,

Jon

Filed under: , , ,
 

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# Noticias externas said:

It&#39;s usually a good idea to seperate Biz Layer Objects and Data Access Objects into seperate assemblies

25 November 07 at 8:04 PM
# Daniel said:

But what if i want to have the datacontext load its connection strings from its own app.config?

because i find this approach, adding an app.config to the datacontext project and compiling the settings into the dll, really, really, really dumb...

although it makes the build subject to automation/continuous integration (build process, modify settings, pack the files).

Thank you,

Dan

06 December 07 at 9:58 AM
# jongallant said:

Dan - An app.config file is added to Class Library projects by default when you add a new LINQ to SQL (dbml) file to the project. If you follow the steps above your app.config connectionStrings will work the same way web.config connectionStrings will.

Jon

06 December 07 at 10:13 AM
# David Klein (Oakton) said:

Hi Jon, - you don't need to delete code from the designer. There is a property that you can set on the dbml as per my blog entry that tells the designer not to generate the empty constructor. See

http://ddkonline.blogspot.com/2008/02/set-connection-string-in-linq-dbml-file.html

18 February 08 at 1:39 AM
# Sebastian Paul said:

Hi,

for those who don't want to change the generated code, here's another approach:

Create a new cs file for the partial code.

Implement OnCreated as follows:

partial class MyDataContext

{

partial void OnCreated()

{

ConnectionStringSettings s = ConfigurationManager.ConnectionStrings["MyConnection"];

if (s != null) Connection.ConnectionString = s.ConnectionString;

}

}

The data context will use the connection string, if found.

29 March 08 at 11:59 AM
# oyesil said:

Sebastian, this is a great solution, how come I couldn't think this :).. thanks alot..

22 October 08 at 4:00 PM
# Siva said:

Thanks a lot Sabastian. very helpful and saved me a lot of time. Thanks once again

05 December 08 at 1:32 AM
# Alex said:

in dbml you can specify a different  BaseClass

public class DataContextBase : System.Data.Linq.DataContext

   {

       private static string overrideConnectionString = ConfigurationManager.ConnectionStrings["CString"].ConnectionString;

       public DataContextBase() : base(overrideConnectionString) { }

       public DataContextBase(string connectionString) : base(overrideConnectionString) { }

       public DataContextBase(string connectionString, System.Data.Linq.Mapping.MappingSource mappingSource) : base(overrideConnectionString, mappingSource) { }

       public DataContextBase(IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { }

   }

16 April 09 at 12:32 PM
# Tommy Newcomb said:

Thanks that works great.  I did a search on my connstring password and it was all over the place in my code using Linq to SQL.  Using the example above puts it back in 1 place in the config file.  I love Linq to SQL, but stuff like this makes me wonder about MS.

15 October 09 at 9:29 AM

Leave a Comment

Comment Policy: No HTML allowed. URIs and line breaks are converted automatically. Your e–mail address will not show up on any public page.

(required) 
(optional)
(required) 

  
Enter Code Here: Required
Page view tracker