how to manage multiple ado.net database connections from asmx Web Service

Question

Since IIS assigns a worker thread for each request I've intention to create new object to serve each request. I have 2 questions:

1. Is it efficient to create new object to serve each request? (is there even alternatice?)

2. Is it thread safe, efficient and best practice to create new connection, and open&close it for each request like below:

using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString))
{ 
    conn.Open();
    MySqlCommand cmd = new MySqlCommand("SELECT password FROM Admin WHERE username='" + username + "'", conn);
    object dbp = cmd.ExecuteScalar();
    conn.Close();
}

PS. this example is taken from this site. I use oracle db.

Thanks: Matti

Answer 1

When you do new SomeSqlConnection() you actually don't create a new connection every time. As database connections are expensive to create ADO.NET keeps a connection pool and draws connections from there. You could also remove the call to the Close method. Here's a snippet you can safely use in each request:

var connectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (var conn = new MySqlConnection(connectionString))
using (var cmd = conn.CreateCommand())
{ 
    conn.Open();
    cmd.CommandText = "SELECT count(*) from some_table";
    object result = cmd.ExecuteScalar();
}

Answer 2

As Darin correctly states in his answer, you should allow the connection pooling mechanism to do its job. Don't try and build something 'clever', it won't be as good.

The golden rule with expensive resources such as database connections is to acquire late and release early. As long as you abide by that then you'll do just fine.