Declaring DB connection in static void - will the connection pool persist?

Question

Hi, I have a multithread server that uses one MSSQL which each client should have access to. I wanted to use connection pooling also I am using this:

public static void DBconn()
        {
                SqlConnection pripojeni = new SqlConnection();
                pripojeni.ConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=XYZ;Trusted_Connection=True;Min Pool Size=20";

        }

Does the object will persist in memory (not the object itself rather than the open connections) if connection strings states "min pool"? Or will it be gone after finishing this method? Thanks

Answer 1

You should use a new SqlConnection object for each connection, not create one in the static constructor. Connection Pooling works when each connection you create uses the same connection string (you should store it in a configuration file like web.config or app.config).

Just create and open a SqlConnection object each time, remember to close/dispose it at the end, and connection pooling will ensure that it re-uses existing, open connections.

Hope that helps!

Answer 2

Since you newly declared the SqlConnection object in this method it will be gone after the method ends.

try this for a continued connection:

public static class PerstistendDB
    {
        // Readonly so it can't be destroyed!
        public static readonly System.Data.SqlClient.SqlConnection pripojeni = new System.Data.SqlClient.SqlConnection(
            "Data Source=localhost\\SQLEXPRESS;Initial Catalog=XYZ;Trusted_Connection=True;Min Pool Size=20");
    }

Note: I agree with Kieren, if you keep your connection open at all time, you will clog the database, it's (almost always) much better to open and close each time.