Linq to SQL connection String to read from web.config file automatically

When we use Linq to SQL it creates a connection string in Settings files. When we need to create the DLL of the DataAccessLayer (DAL) for production server or any server which is not our current development server then we need to change connection string manually in Settings file, is not it would be good to set something in application so it can always read the connection string form web.config file automatically? Is it possible? yes, and it's quite easy. Let me explain how we can achieve it.

If you will look your DataContext.designer.cs file, it will look like

[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="NorthWind")]
public partial class MyDataContext : System.Data.Linq.DataContext
{    
       private static System.Data.Linq.Mapping.MappingSource mappingSource = 
                          new AttributeMappingSource();
       // close Extensibility Method Definitions region
       public MyDataContext() : 
                   base(global::MyLib.Library.DAL.Properties.Settings.Default.MyDBConnectionString1, mappingSource)
      {
            OnCreated();
      }

      public MyDataContext(string connection) : base(connection, mappingSource)
      {
        OnCreated();
      }
              ..........................................
              ..........................................
              ..........................................

All the above code is auto generated so you don't have to vary to understand each and every line here.

We know our DataContext class name, in this example it is "MyDataContext", right.

Now what we need is to create a partial class with the same name "MyDataContext" to override the OnCreate method and assign the connection string from web.config that's it, so let's create the class.

    public partial class MyDataContext
    {
        partial void OnCreated()
        {
            this.Connection.ConnectionString =       
            ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
        } 
    }

That's it, and here is the connection string in web.config file.

<connectionStrings>    
    <add name="MyDBConnectionString" 
            connectionString="data source=(local);
            initial catalog=NorthWind; 
            UID=sa;
            PWD=xxxxxxxxx" 
            providerName="System.Data.SqlClient"/>    
  </connectionStrings>

If you will run the application it will automatically read the connection string form web.config file.

Note: if more than one people are working on the same project and when they will drag and drop new tables it will show an error:

base(global::MyLib.Library.DAL.Properties.Settings.Default.MyDBConnectionString1 doesn't exist, in that case you don't have to worry, replace entire "global::MyLib.Library.DAL.Properties.Settings.Default.MyDBConnectionString1" string to blank ("") string because you are not reading connection string for setting file any more.

Montana Neyra
  • linq to sql
  • web.config
  • asp.net
By Montana Neyra On 17 Jun, 12  Viewed: 5,005

Other blogs you may like

Linq to Sql - Sort record on multiple columns in ascending or descending order

In SQL Server it's easy and we are familiar to sort orders on different columns but when it comes to Linq to SQL or Lamda expression we stuck how to sort the records, it is because we are experienced much. So let's discuss some daily use ordering of records with examples. **1**. Suppose we have... By Mike .Net   On 30 Jan 2013  Viewed: 4,736

MVC paging with linq to sql and html helper class

To use paging in MVC you need to first create structure to pass values form page to HTML Helper class so first create a class to keep and pass the page information public class PagingInfo { public int TotalItems { get; set; } public int ItemsPerPage { get; set; } ... By Ali Adravi   On 03 Jan 2013  Viewed: 3,500

AngularJS HTML5 mode reloading page not found solution

Removing # from Angularjs application is tricky but BOSS is always right, if he don't want to see the # (hash) in URL then there is no way to keep it any more. Actually it is not too much work for anyone to set it up within 5 minute by rewriting the URL. I was playing and found it is very easy and... By Ali Adravi   On 17 Aug 2015  Viewed: 15,889

Upload multiple image in multiple size with progress bar in asp.net

In asp.net there is not control to select multiple files and upload them once with progress bar, so we will use a small third party DLL to achieve this functionality. We will use Flajaxian FileUploader, you can download it from [http://www.flajaxian.com][1] We will create three different images... By Hamden   On 12 Jul 2012  Viewed: 6,546

Check/Uncheck all checkboxes in asp.net by javascript

I was searching for select/deselect all checkboxes code into a gridview. I found most of them only provide to select and deselet all the checkboxes if any of the checkbox is unselected into grid the main checkbox is not affecting. I further try to search some useful code but could not found any... By Ali Adravi   On 10 Jul 2012  Viewed: 5,376