Storing Database Connection String in Web.Config

Today I found, that storing connection strings were little bit changed between .NET 2.0 and .NET 3.5.

Here is an example of how to store a database connection string in the application’s Web.Config file for .NET 2.0. The code for accessing the connection string is located in the C# code below.

.NET 2.0

<appSettings>
<add key="ConnectionString" value="server=localhost;database=TestDB;uid=sa;password=secret;" />
</appSettings>

And here is the C# code how to get it.

using System.Configuration;

public class TestGetConnection
{
  public TestGetConnection()
  {
    try
    {
      // Get connection string from Web.Config
      string strConnection = ConfigurationSettings.AppSettings("ConnectionString");
    }
  }
}

.NET 3.5

ConfigurationSettings.AppSettings is now deprecated.

<connectionStrings>
  <add name="YourConnectionString" connectionString="server=localhost;database=TestDB;uid=sa;password=secret;" providerName="System.Data.SqlClient" />
  <add name="NewString" connectionString="server=localhost;database=TestDB;uid=sa;password=secret;" providerName="System.Data.SqlClient" />
</connectionStrings>

And here is the C# code how to get it.

using System.Configuration;

public class TestGetConnection
{
  public TestGetConnection()
  {
    try
    {
      // Get connection string from Web.Config
      string strConnection = ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;
    }
  }
}

You can use <remove /> to delete connection setting from web.config.

<connectionStrings>
  <remove name="YourConnectionString"/>
  <add name="YourConnectionString" connectionString="server=localhost;database=TestDB;uid=sa;password=secret;" providerName="System.Data.SqlClient" />
</connectionStrings>

Connection String Syntax Notes

  • Values may be delimited by single or double quotes, (for example, name=’value’ or name=”value”). Either single or double quotes may be used within a connection string by using the other delimiter, for example, name=”value’s” or name=’value”s’,but not name=’value’s’ or name=””value””. The value type is irrelevant.
  • All blank characters, except those placed within a value or within quotes, are ignored.
  • Keyword value pairs must be separated by a semicolon ( ; ). If a semicolon is part of a value, it also must be delimited by quotes.
  • Names are not case sensitive. If a given name occurs more than once in the connection string, the value associated with the last occurence is used.
  • No escape sequences are supported.

If you need to connect to SQL Express, you shold use connection string with specifying the file you want to attach.

connectionString="Server=.\SQLExpress;AttachDbFilename=c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SpamTestimonial.mdf;Database=SpamTestimonial;Trusted_Connection=Yes;"

If you need to find all possible connection string, go to web page dedicated to them for more databases than you can image. http://www.connectionstrings.com/

5 thoughts on “Storing Database Connection String in Web.Config

  1. Martin M?lek Post author

    @KB Hunter
    ProviderName attribute is optional. The default value is “System.Data.SqlClient”.
    With providerName you tell what type of connection string you have provided. The ProviderName property can have several different values, depending on which data provider you want to use.

    Some standard data providers:

    System.Data.OdbcAny data source that is accessed through an ODBC driver
    System.Data.OleDbAny data source that is accessed through an OLE DB driver
    System.Data.OracleClientProvides native access to Oracle databases
    System.Data.SqlClientProvides native access to SQL Server databases
    System.Data.SqlServerCeProvides native access to SQL Server CE databases (.NET Compact Framework)

  2. Khaine

    If i dont want to save my sql connection in web.config is this the right way to do it?

    var strInsert = “Insert Into KontaktMe(FirstName, LastName, Email, Question) values (“+kontaktfirstname.Text+”, “+kontaktlastname.Text+”, “+kontaktemail.Text+”, “+kontaktquestion.Text+”)”;
    var connectionString = new System.Data.SqlClient.SqlConnection(“Data Source=.\\SQLEXPRESS;AttachDbFilename=\”C:\\Users\\jgattringer\\Documents\\Visual Studio 2010\\WebSites\\WebSite1\\App_Data\\ASPNETDB.MDF\”;Integrated Security=True;User Instance=True;Asynchronous Processing=true”);

    var cmdInsertKontaktMe = new System.Data.SqlClient.SqlCommand(strInsert, connection:connectionString);

    cmdInsertKontaktMe.Connection.Open();
    cmdInsertKontaktMe.ExecuteNonQuery();
    cmdInsertKontaktMe.Connection.Close();

    i just get an error “invalid columns” and he shows me the values of the input in my form.
    I dont understand whats going on here…if i try it with cmdInsertKontaktMe.BeginExcecuteNonQery there isnt any error but i also get no entry in my database.

  3. vidya mohan

    I have a question regarding web.config file usage for multiple versions assemblies & sites.

    We have a sharepoint ASP.net web site and a sub site in it

    Example: http://portal — Main Site (c:\inetpub\wwwroot\portal) — for production
    http://portal/Stage – sub site (c:\inetpub\wwwroot\portal\Stage) — for stage – physical server & web application is same

    Currently we are following release process as below

    — First we will deploy new web pages in http://portal/Stage and later will move to http://portal, currently as we have same web.config on root folder (c:\inetpub\wwwroot) it reflecting new version assembly on both sites.

    Do you have any inputs how we can use multiple web.configs (in parent site and child site) or single web.config to support different version assemblies for root and child sites ?

Leave a Reply

Your email address will not be published. Required fields are marked *