Scott Forsyth's Blog

Postings on IIS, ASP.NET, SQL Server, Webfarms and general system admin.

Cloud Resources

IIS Resources

Using connection strings from web.config in ASP.NET v2.0

ASP.NET v2.0 has a couple new ways to reference connection strings stored in the web.config or machine.config file.

A typical web.config file in v2.0 could have the following section which is placed directly under the root <configuration> section.

<connectionStrings>
    <
remove name="LocalSqlServer"
/>
    <
add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"
/>
    <add name="MainConnStr" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|main.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

connectionStrings>
    <
remove name="LocalSqlServer"
/>
    <
add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"
/>
    <add name="MainConnStr" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|main.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

You can reference this directly from code using:

[C#]
string connStr = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;

[VB]
Dim connStr As String = ConfigurationManager.ConnectionStrings("MainConnStr").ConnectionString

Note that the namespace for this is System.Configuration so for a console application the full namespace is required.


Or you can reference this declaratively within the ConnectionString property of a SqlDataSource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:MainConnStr %>"
  SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors]"
/>

Posted: Aug 26 2005, 11:18 AM by OWScott | with 24 comment(s) |
Filed under:

Comments

Dave said:

"Or you can reference this declaratively within the ConnectionString property of a SqlDataSource"

Are you sure? I get the error: The AccessDataSource ConnectionString property cannot be set, it is automatically generated.

# January 13, 2011 1:20 PM

kirti said:

<connectionStrings>

   <add name="example" connectionString="Data source=localhost; initial catalog= example1; user id= password=" providerName="System.data.sqlclient" />

 </connectionStrings>

# January 24, 2011 2:17 AM

usman_sodon said:

if i upload a folder name "testweb" which has by default configurations of "web.config"  and in that i have a simple page "abc.aspx" then have gridview  name "grid"  bounded with access db naming "db.mdf" via acccess datasource name  "src"

will the above situation needs some changes in web.config?  if after making access db on server my server gives an ODBC connection string ,then where should i deploy this ODBC  thing ?

Please help me i am upset with it , i am good at ASP and C# coding but the above problem of deploying the ASP web with access database is making me frustrated  

# June 1, 2011 1:20 AM

OWScott said:

Hi Usman.  Rather than using an ODCB connection, use just a direct connection to the access database (www.connectionstrings.com/access-2007).  If you use a relative path then it will work when you deploy to the web server without needing to transform the web.config doc.  

# June 1, 2011 11:02 AM

l said:

if i upload a folder name "testweb" which has by default configurations of "web.config"  and in that i have a simple page "abc.aspx" then have gridview  name "grid"  bounded with access db naming "db.mdf" via acccess datasource name  "src"

will the above situation needs some changes in web.config?  if after making access db on server my server gives an ODBC connection string ,then where should i deploy this ODBC  thing ?

Please help me i am upset with it , i am good at ASP and C# coding but the above problem of deploying the ASP web with access database is making me frustrated

# July 19, 2011 4:28 AM

OWScott said:

Hi I,

The trick is to not use ODBC.  Instead you can use OLEDB which is a lot better and is xcopyable.  Find the connection string that you need from www.connectionstrings.com and you can use that in your web.config file.  

You mentioned a mdf file.  That assumes that you're using sql user instancing, which isn't installed by default.  The best way to do this (usually) is to actually create the database on sql server itself and then use a connection string to that instance.  Using the path on disk is only supported with some installs of sql express.

# July 19, 2011 1:07 PM

M-Nimal said:

Is there an easy way to encrypt connection strings in a web config file?

Thanks in advance

# July 20, 2011 5:27 PM

M-Nimal said:

Is there an easy way to encrypt connection strings in a web config file?

Thanks in advance

# July 20, 2011 5:28 PM

OWScott said:

Hi M-Nimal,

Depends on how you define easy.  :)  Actually it's not too bad.  The kicker is that the encrypted key depends on a windows machine key, so you have 2 options.  A) save the web.config file to the server first and then encrypt it while on the server.  It's only useful on that server.  B) export the machinekey to another location and then it will work between servers (necessary on a webfarm).

The tool to do this is aspnet_regiis.  I don't have a good proven doc to point you to but you can start with this: www.google.com/search

My own blog post from years ago turns up on the first page, but it's so old now that I didn't point directly to it.

# July 20, 2011 5:40 PM

thesmartchamp said:

What will be the ConnectionString for connecting a MS-Access database.?

# July 25, 2011 6:20 AM

OWScott said:

The thesmartchamp.  The best resource for that is www.connectionstrings.com

# July 25, 2011 11:54 AM

Conrad said:

Is there a way to set it dynamically, eg. ConnectionString='<%=sConStr%>'?  This will be useful for apps running on multiple dbs and change based on login.

# August 4, 2011 2:22 PM

OWScott said:

Hi Conrad,

Yes, check out this link on expression builders: www.beansoftware.com/.../Expression-Builder.aspx.  That should allow you to do what you're talking about.

# August 4, 2011 11:37 PM

SUDA said:

HAI,

THIS IS MY CONECTION STRING. BUT IT SI NOT WORKING PRPERLY.PLEASE CHECK IT OUT.

MY CONNECTION STRING IS SQLCONNECTION CON= NEW SQL CONNECTION("SEVER=ABC;SOURCE="C:\\MYDOCUMENTS\\FUN2IN.SDF";INTEGRATED SECURITY =TRUE");.

# August 22, 2011 2:51 AM

OWScott said:

Hi Suda,

Here's a good list of connection string options for SQL CE: www.connectionstrings.com/sql-server-2005-ce.  Be sure to confirm that your app pool has write access to the c:\mydocuments folders too.

If that doesn't work, please explain further what issues you're running into.

# August 22, 2011 9:08 AM

loft conversion london said:

access connection string is

rovider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;

# October 5, 2011 4:36 PM

OWScott said:

Hi Sankari,

Happy New Year to you.  Sorry for the delay.

What are the details of your 500 error?  There are multiple possible causes which the 500 error details will hopefully point in the right direction.

One guess I would have is that your login database has more than 4 columns.  If you name the query then it can handle schema changes in the future.  For example: insert into login (name, password, password2) values (...).  That way it doesn't depend on exactly 4 columns in that exact order.

Also as a side it would be worth considering parameterizing your values to prevent sql injection attacks.  This search has some good links on that: www.bing.com/search

# January 5, 2012 10:56 AM

Amit said:

Hello,

I have read the article and comments. I want to know more about connection strings for connecting with MySQL Database.. I have my Database in MySql and want to integrate/ use it in my ASP.NET v2.0 application.

Can anyone tell how to do it?

Please reply.

Regds

an23in@sify.com

# January 19, 2012 6:58 AM

OWScott said:

Hi Amit,

The connection strings for mySql can be found here: www.connectionstrings.com/mysql.  This page has good details on the .NET MySql connector: www.connectionstrings.com/.../mysql-connector-net-mysqlconnection.

# January 19, 2012 9:52 AM

deepti said:

string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

           MySqlConnection con = new MySqlConnection(constr);

its not working

# January 27, 2012 5:19 AM

OWScott said:

@deepti.  What error do you get?  I would check connectionstring.com to confirm that your mysql connection string is correct and make sure that you have any necessary mysql drivers installed on your server.

# January 27, 2012 12:25 PM

SAMUEL said:

i used the following connection string

<connectionStrings>

   <add name="ApplicationServices"

        connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=.\TESTLEGALPEDIA.mdf;User Instance=true"

        providerName="System.Data.SqlClient" />

 </connectionStrings>

I got the following eror;

Format of the initialization string does not conform to specification starting at index 0.

my code is as follows;

'Loading datagridview

       Dim cn As SqlConnection

       Dim cmdgetall As SqlCommand

       Dim adallinfo As SqlDataAdapter

       Dim STRSQL As String

       'initialise objects

       cn = New SqlConnection("ApplicationServices")

       adallinfo = New SqlDataAdapter()

       m_dswork = New DataSet()

       'set up command

       STRSQL = "SELECT * FROM BIODATA"

       cmdgetall = New SqlCommand(STRSQL, cn)

       adallinfo.SelectCommand = cmdgetall

       'set up mappings

       adallinfo.TableMappings.Add("Table", "BIODATA")

       'fill dataset

       cn.Open()

       adallinfo.Fill(m_dswork)

       cn.Close()

       'set up column objects for easy access

       Dim colstud As DataColumn

       colstud = m_dswork.Tables("BIODATA").Columns("BIODATA_Title")

       'BIND TABLE TO GRID

       GridView1.DataSource = m_dswork

       GridView1.DataMember = "BIODATA"

       GridView1.DataBind()

       GridView1.DataMember = ""

       GridView1.DataSource = Nothing

   End Sub

please what do i do to correct the error?

# March 14, 2012 10:49 AM

OWScott said:

Hi Samuel,

Try changing this line:

cn = New SqlConnection("ApplicationServices")

to:

cn = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString)

# March 14, 2012 3:42 PM

David said:

this message is very help, the information on msdn is obscure.

# May 23, 2012 5:59 AM

Maddy said:

static string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

   SqlConnection con = new SqlConnection(conStr);

# June 29, 2012 7:24 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)