Share Database connection between objects

Question

I am going back and forth with this changing my design multiple times.

I have Two classes Shop & Source,
each Shop has IList<Product> (approx. 1000 products),
and Source has IList<Item> (approx 200),
All Classes needs to perform some actions on the same Database.

At first, I shared an open DbConnection with all objects:
And when building my WebService it became problematic,
I cached the open static DbConnection (bad as it sounds) and used it.
but it started making trouble when testing multiple requests, when the connection closed sporadically and ExecuteReader() threw (wasn't using MARS).

After reading This and many other sources suggesting to rely on connection pooling.
I replaced my code to open a connection just before I query, and dispose of it when finished.
but I notice a degredation in performance.
Can I check how the pool is functioning (or isn't it?)

Question: How can I monitor my SqlConnection pool?

Question: Is there any other design to share this connection between these many objects.

Thanks a lot.

William Xifaras · Accepted Answer · 2016-12-12 17:11:40Z

1

The performance degradation may not be related to how you manage the database connection. Generally, re-creating connections isn't a costly operation and .NET will pool / manage them for you. You should be able to create as many connections as you want, just ensure you are properly disposing them. There could be issues with the underlying queries or other areas of your application that you may need to consider.

How can I monitor my SqlConnection pool?

There are Performance Counters you can look at.
Take a look at using perfmon

Is there any other design to share this connection between these many objects.

How are you reading the connection string? That itself could be static, but I would still rely on connection pooling (as you noted from your research). Are you properly disposing the connections?

Example (static connection string):

    public static readonly string ConnectionString connString = ConfigurationManager.ConnectionStrings["YOURCONNECTION"].ConnectionString.ToString();

    private void TestMethod()
    {
        using (var dbConn = new SqlConnection(connString)) {
            string query = "select ....";
            using (var dbCommand = new SqlCommand(query, dbConn)) {
                ...
            }
        }
    }

answered Dec 12, 2016 at 17:11

William Xifaras

5,3622 gold badges22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tomer W Over a year ago

The ConnectionString is a member of each class, though same string. I Am using using block for all Connection, Command & Reader. I'll take a look at my statistics, Everything points to the conclusion I might be slipping something else.

William Xifaras Over a year ago

@TomerW I would create a utility class that you can put this in. No reason to duplicate this in all your classes.

Tomer W Over a year ago

Thanks, i don't know exactly what i changed, but after messing around with the code, it is running lightning speed constantly :)

Collectives™ on Stack Overflow

Share Database connection between objects

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related