Where should I place my database connection string and how do I handle connection pooling?

Question

I am developing an asp.net application which I have hosted on an IIS server. To open a connection I use:

SqlConnection con = new SqlConnection("Server = INLD50045747A\\SQLEXPRESS; 
Database = MyDatabase;User ID = sa; Password = Welcome1; Trusted_Connection = False;");
con.Open();

Is it possible to store this connection string somewhere so that I don't need to write in every aspx.cs file? I am using MSSQL database.

I'm facing an issue which says:

The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

I read somewhere which asks me to increase the maximum connection pool to 100. Will it affect the performance of my application?

Answer 1

You probably aren't closing your open connections propertly.

Increasing the "pool size" is like putting a bigger bucket under a waterfall - it will help, but barely.

Try and locate areas where something like this is happening:

con.Open();

Ensure that if it's not in a try/catch, that it is in one, and that it includes a finally statement.

try {
   con.Open();
   //several other data base releated
   //activities
} catch (Exception ex) {
  // do something
} finally {
  con.Close();
}

Also, to avoid having to use the finally block, you can just wrap the SqlConnection in a using statement.

  using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["yourKey"].ConnectionString))
        {

            // write your code here 

        }

In regards to your question about connection string, yes store it in your web.config

<connectionStrings>
<add name="name" connectionString="Data Source=;Initial Catalog=;User ID=sa;password=;Persist Security Info=True;Connection TimeOut=20; Pooling=true;Max Pool Size=500;Min Pool Size=1" providerName="System.Data.SqlClient"/>
</connectionStrings>

Answer 2

Store it in the web.config file, in the connectionStrings section:

<connectionStrings>
    <add name="name"
        connectionString="Data Source=;Initial Catalog=;User ID=sa;password=;Persist Security Info=True;Connection TimeOut=20; Pooling=true;Max Pool Size=500;Min Pool Size=1"
        providerName="System.Data.SqlClient"/>
</connectionStrings>

And then you will be able to access this in your code...

ConfigurationManager.ConnectionStrings["name"].ConnectionString

Answer 3

you can do using also

it will automatically disposes the object

if you use "using" there is no need of con.close and all

  using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["yourKey"].ConnectionString))
        {

     // write your code here 

        }

Answer 4

Store the connection string in the web.config files. you can find numerous examples. Check this for the properties. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.71%29.aspx

Thanks Shankar

Answer 5

Use the Help of Both

try
{
 con.Open();
}
catch(Exception ex)
{
if(con.State== ConnectionState.Open)
con.Close();
}
finally
{
con.Close();
}

and also add the Connection String in Web.Config, under Configuration. This will help you.

Answer 6

Yes, store it in the web.config file but make sure that if there is an error it doesn't display the content of the web.config file to the user (thus showing the world your password.)

If you use that same connection string in a lot of applications you could consider writing a service to provide the connection strings, that way you only have to change them in one place.

Answer 7

The best option is to use typed settings.

Open your project properties.
Go to Settings tab.
Add new setting, for example MainConnectionString, select setting type (ConnectionString). In the value insert your connection string or hit '...' button to bring a dialog to build connection string.

Now you can reference your connection string in the code like this:

Settings.Default.MainConnectionString

If you open your config file you will see

  <configuration>
    <connectionStrings>
      <add name="WebApplication1.Properties.Settings.MainConnectionString"
           connectionString="Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True"
           providerName="System.Data.SqlClient" />
    </connectionStrings>

You can have this connection string specified:

• for one web site in its own web.config.
• for a group of web sites
• for all web sites on a box: in machine scope web.config.
• for all application on a box: in the machine.config.

This can be convenient if you have a lot of applications that connect to the same db and are installed on one box. If db location changes you update just one file machine.config, instead of going to each application's config file.