Question app.config work with linq to sql

jaassie

New member
Joined
Apr 14, 2012
Messages
3
Programming Experience
1-3
In a C# 2008 application, I used linq to sql to connect to a sql server 2008 database. Now I want to obtain the connection of the database from the
app.config file instead of hardcoding the value into the application. I want to be able to change the connection string to the database since I will be moving this application from unit testing database to user acceptance database to the the prodction database.
Right now I am getting the error message, ""Object reference not set to an instance of an object." I know this error message is coming from the line of code that I listed below since it is the only line of code I changed.
The following is a snippet of the code from the *.designer.cs file (this is part of the dbml file).

using System.Configuration;
public esampleDataContext() :
base(ConfigurationManager.ConnectionStrings["sample.Properties.Settings.DEVConnectionString"].ConnectionString,mappingSource)
**Note: base(global::sample.Properties.Settings.Default.DEVConnectionString, mappingSource) is the line of code that I replaced that was generated by the
.net framework
I want to mention the following:
1. The *.dbml file is associated with a particular project file.
2. The connection string settings for this *.dbml file are the following:
a. DEVConnectionString (settings)
b. Connection String = Data Source=dev1;Initial Catalog=DEV_test;Integrated Security=True
c. application settings=True
d. settings property name is DEVConnectionString.
3. The project file that has the *.dbml file. has a reference set to the system.configuration.
**Note: This code in similar applications that I wrote, but I do not why it does not work here.
Thus can you tell me, show me in code, and/or point me to a reference that I can use so I can obtain the value in the app.config file for the database connection string I want to use. I do not want to use a hard-coded connection string to the database.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,124
Location
Sydney, Australia
Programming Experience
10+
I don't know what you did in the first place but the default for LINQ to SQL is to store its connection string in the config file anyway, just as it is for all the data tools I've ever used in VS. I just created a test app, added a SQL Server Express database and dragged a table from the Server Explorer onto the LINQ to SQL design surface. Here's the contents of my App.config file, all auto-generated:
C#:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        [COLOR="#FF0000"]<add name="LinqToSqlTest.Properties.Settings.TestConnectionString"
            connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True"
            providerName="System.Data.SqlClient" />[/COLOR]
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>
and here are the auto-generated constructors for my LINQ to SQL model:
C#:
public TestDataContext() : 
		base([COLOR="#FF0000"]global::LinqToSqlTest.Properties.Settings.Default.TestConnectionString[/COLOR], mappingSource)
{
	OnCreated();
}

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

public TestDataContext(System.Data.IDbConnection connection) : 
		base(connection, mappingSource)
{
	OnCreated();
}

public TestDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
		base(connection, mappingSource)
{
	OnCreated();
}

public TestDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
		base(connection, mappingSource)
{
	OnCreated();
}
As you can see, the parameterless constructor uses the connection string from the config file by default. There was no need for me to do anything to make that happen.

Regardless, you basically never edit designer-generated files by hand. If you want to change something about designer-generated code then you do it in the designer that generates the code. For LINQ to SQL, that means opening your model in the designer, clicking some of the empty space and then opening the Properties window. At the bottom you will see the Connection property. There you can select an existing connection string or create a new one. If you create a new one then it will be added to the config file by default. I did that with my test project and here's what the config file looked like afterwards:
C#:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="LinqToSqlTest.Properties.Settings.TestConnectionString"
            connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True"
            providerName="System.Data.SqlClient" />
[COLOR="#FF0000"]        <add name="LinqToSqlTest.Properties.Settings.ReportServerConnectionString"
            connectionString="Data Source=PICARD;Initial Catalog=ReportServer;Integrated Security=True"
            providerName="System.Data.SqlClient" />[/COLOR]
    </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>
and here's the constructors for the LINQ to SQL model:
C#:
public TestDataContext() : 
		base([COLOR="#FF0000"]global::LinqToSqlTest.Properties.Settings.Default.ReportServerConnectionString[/COLOR], mappingSource)
{
	OnCreated();
}

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

public TestDataContext(System.Data.IDbConnection connection) : 
		base(connection, mappingSource)
{
	OnCreated();
}

public TestDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
		base(connection, mappingSource)
{
	OnCreated();
}

public TestDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
		base(connection, mappingSource)
{
	OnCreated();
}
I didn't write a single jot of code in all that. Everything was designer-based.
 

jaassie

New member
Joined
Apr 14, 2012
Messages
3
Programming Experience
1-3
My original auto-generated code looks like yours, base(global::sample.Properties.Settings.Default.DE VConnectionString, mappingSource). That code worked as long as I stayed on the same database. However once I moved the application to a different database, the new values in the app.config file were not used. The original database connection string values became hard-coded in the application somehow. For me to move the application to a new database, I did a global search and replace for the data base connection strings.
I do not want to keep the hard-coded values in the application. Can you tell me what I need to do with the designer that generates the code so the values in the app.config file will be used?
I have been working with the designer that generates the auto-generated code but I have not found a way for my code to work.
What do you suggest? Should I setup new database connection strings in the app.config file for each database this application will be attached to? Basically this application will be moved to 3 different database. Does that mean I should setup 3 different database connections in the app.config file for the 3 different databases this application will be connected to?
Thus can you tell me what I can do so the application really uses the value in the app.config file?
 
Last edited:

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,124
Location
Sydney, Australia
Programming Experience
10+
Thus can you tell me what I can do so the application really uses the value in the app.config file?
That's what I did do in my previous post. The whole point of putting the connection string in the config file is so that it can be edited by hand. If you want to connect to a different database then you change the connection string in the config file. The code doesn't change so the model loads the same connection string from the config file but the value of that connections string will have changed. If you know multiple values that will be used then you can simply make multiple copies of the same setting in the config file and comment out the ones you're not currently using.
 

jaassie

New member
Joined
Apr 14, 2012
Messages
3
Programming Experience
1-3
Since my application is not using the app.config file, what could I be doing wrong with the designer tool that autogenerates the code? There must be something I am setting incorrectly.
To see the properties, I first select the visiual designer and right click in a white space section of the designer and I see ther properties. I then select the connection settings. What should the connection settings be?
 
Top Bottom