Working with local connections instead of global

Question

In recent weeks I became very concerned that the way I've been working with database connections for the past years, is simply wrong.

I exclusively create a new connection for each database operation I have to perform. only in the more complex programs, I would stack several tasks (Sql commands) in the background and then every few seconds I would create a new single connection, and execute these with the same connection.

Here is typical sample:

     SqlConnection connection = new SqlConnection(con);
            SqlCommand command = new SqlCommand(@"
            DECLARE @Pk TABLE (Id int)
            INSERT INTO Petrol.Reports(
            [TimeStamp])
            OUTPUT INSERTED.Id INTO @Pk
            VALUES(
            @TimeStamp)

            SELECT Id FROM @Pk", connection);
            command.CommandType = CommandType.Text;

            SqlParameter parameter = command.Parameters.Add("@TimeStamp", SqlDbType.DateTime);
            parameter.Value = this.TimeStamp;

            using (connection)
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        this.Id = reader.GetInt32(0);
                        Ref.Petrol_ReportsById[Id] = this;
                    }
                }
            }

Recently I started working with PostgreSQL and I was freighted to see that it creates a new process for almost every connection I create.

The deal is that most code samples around are using very similar approach, but I'm afraid that this is just to keep things simple.

I consider re writing my objects generating tool to share a single connection between all operations (with thread safety in mind), would this be a correct approach in your opinion?

Answer 1

You should, although it is likely already being done for you

In database operations, a large part of the cost associated with the operation is setting up the connection, doing proper authentication, and so on. It makes a lot of sense to not have a new connection for every operation but simply re-use existing connections. This is a pattern called connection pooling.

The thing with connection pooling is that it is such a great improvement that a lot of times the language you are working with will do it implicitly. You will often have to explicitly tell the code in some whay that you do NOT want to use connection pooling. In your code, the call to close on the connection would not actually close the connection unless you specifically configure your connection string that way. It will simply return it to the pool, making it available for use again.

With that in mind your pattern of connection usage is very good: you get a connection (that will be created if none are available, or simply be a re-used connection that existed), use it for the shortest amount of time you need it to execute your actions, and then release it (to be either destroyed after a period of time or re-used for other actions).

Answer 2

I consider re writing my objects generating tool to share a single connection between all operations

Your current code works, and if it works fast enough, why do you want to fix something which is not broken?

Only if some code of yours has some noteable or measurable performance problems, and you know opening and closing the connections too often is the cause, you should consider to rewrite it in the described manner. Do not optimize just for the sake of optimization.