24/7 Applications Dot NET Applicaiton Open close conneciton

Question

i am working on a applicaiton that will running 24/7.application life cycle is so simple. when ever new request come.its just update the record in database. aplicaiton update record in different servers and in different database. there is millions of request application entertain in an hour for each request it open and close connecition as per below code.

internal int ExecuteNonQuery(string Query)
    {
        using (SqlConnection SqlConn = new SqlConnection(this.ConnectionString))
        {
            using (SqlCommand sqlComm = new SqlCommand(Query, SqlConn))
            {
                SqlConn.Open();
                sqlComm.CommandTimeout = 60;
                sqlComm.ExecuteNonQuery();
                return 0;
            }
        }
    }

i want to optimize my code i dont want every time request come it will create a new connection for this i have read connection pooling mechanism in ado.net. remember that i have different sql connection (maximum 10). can i use connection pooling? or can i make my own logic to create sqlconection for each connection and opened them for all day. also my application oftenly generate hand shake exception.

score 0 · Answer 1 · answered Jan 19 '20 at 08:27

There are plenty of ways to enable connection pooling as described here

If you are using MsSQl with the OleDb driver for example, you have connection pooling out of the box.

Depending on your situation, you can modify the Max Number connections in your pool. To establish the number of this, check this answer here: Should I set max pool size in database connection string? What happens if I don't?

It's important to say that the connections are not destroyed, they are just returned to the pool. That makes it very efficient and it's the recommended way to go.

OleDb

The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. Connection string arguments can be used to enable or disable OLE DB services including pooling.

ODBC

Connection pooling for the .NET Framework Data Provider for ODBC is managed by the ODBC Driver Manager that is used for the connection, and is not affected by the .NET Framework Data Provider for ODBC.

OracleClient The .NET Framework Data Provider for Oracle provides connection pooling automatically for your ADO.NET client application. You can also supply several connection string modifiers to control connection pooling behavior (see "Controlling Connection Pooling with Connection String Keywords," later in this topic).

i really want to do it from my custom logic where i make list of connection an open that connection only once. do the connnection pooling do it same for me remeber my connection strings is changes but it is limited like 10 to 15 conneciton. — Usman Asif, Jan 19 '20 at 09:28
Yes it is in most cases. Depends mostly on what you are using. — Athanasios Kataras, Jan 20 '20 at 09:51

24/7 Applications Dot NET Applicaiton Open close conneciton

1 Answers1