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

ado.netasmxciis

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

Best Answer

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();
}
Related Topic