How to manage multiple ado.net database connections from asmx web service

Question

Since IIS assigns a workflow for each request, I intend to create a new object to serve each request. I have 2 questions:

• Is it efficient to create a new object to serve each request? (is there even an alternative?)

• It is thread safe, efficient, and best practice for creating a new connection and open and close it for each request, as shown 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 am using oracle db.

Thanks: Matti

Answer 1

When you execute new SomeSqlConnection(), you are not really creating a new connection every time. Since database connections are expensive to create ADO.NET, it maintains a connection pool and pulls connections from there. You can also remove the method call Close. Here is a snippet that you can safely use in every 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

, . - "", .

, , , . , .