Using Pooling or SqlConnection object variable is better ?

MrKBA

Hi, I need to know which is better for work and performance please : I have to do many operation on database so I am hesitating between : 1. create object "SqlConnection" and let connection opened during service (webservice) works 2. using the instruction below every time I want to do a modification in dtatabase

using (SqlConnection sqlconnection = GetSqlConnection(connectionString, false))
{
sqlconnection.Open();
.....

}

Where

public static SqlConnection GetSqlConnection(string connectionString, bool disablePooling = true, bool forceMasterDB = false)
{
if (disablePooling || forceMasterDB)
{
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder(connectionString);

            if (disablePooling)
                sqlConnBuilder.Pooling = false;

            if (forceMasterDB)
                sqlConnBuilder.InitialCatalog = "master";

            connectionString = sqlConnBuilder.ConnectionString;
        }
        return new SqlConnection(connectionString);
    }

Thank you

Tim Carmichael

If you have the code, why not write a simple app to process a number of statements and check the execution time. Ensure the statements executed are the same for each.

Richard Deeming

Create the connection as late as possible, and wrap it in a using block to ensure that it's always disposed of properly. Don't disable connection pooling, since the underlying network connections can be expensive to set up and tear down. The connection pool is there to improve the performance of your code! Trying to share a single connection object across multiple threads will only lead to errors. To avoid the errors, you'd have to synchronise access to the shared connection, which will significantly degrade the performance of your code.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

MrKBA

thank you for response Yes what I did is that every time I need the connection I use it in using block. Also I use pooling always because I'm using the same connection string. so you see this safe ?

MrKBA

thank you for response but as I said I know what you suggest but I ask what is more safe also for me.

Richard Deeming

Yes, it sounds like you're doing the right thing. :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

MrKBA

thank you for help