0

I have a Web API which contains database insert logic (ado.net) in C#. When multiple users (e.g. 100 users) call the Web API, every time a SQL Server connection is opened and closed for multiple requests. It slows down performance.

How can I keep a single SQL Server connection live for multiple requests? I have to keep SQL connection open only once and close after some time so that during that time it should consider multiple request and insert records in database.

Please suggest.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SSD
  • 1,041
  • 3
  • 19
  • 39
  • 6
    How have you *confirmed* that this is affecting performance. SQL connection pooling is *highly optimized*. And trying to manage connection pooling yourself is a *famously bad idea*. Why do you assume that you can optimize it better than it already is? Where are the performance metrics that you've measured which indicate that is the bottleneck? – David Jan 11 '18 at 15:14
  • 6
    .Net uses connection pooling to optimize this scenario, open/close/dispose connections as and when they are needed, this is best practice. – Alex K. Jan 11 '18 at 15:14
  • How do you know that the connection opening and closing is what takes time? Did you do a performance test? Where's the code for that test? And where's the code you use? – Camilo Terevinto Jan 11 '18 at 15:14
  • 1
    When you close a connection it just releases it for use in the pool, it doesn't actually close it on the database server, it is internal to ADO.Net. – Crowcoder Jan 11 '18 at 15:17
  • I mean to say when multiples calls to web api (>100) increases. it shouldn't slow down performance. I want web api should process 500-1000 request in 1 second. currently it takes 1.5 seconds for 100 request. I read connection pooling but it only applies to current process so when it will only open sql connection once based on pool size. how sql connection pool will work for multiple request (>500 call means 500 db insertion). please correct me if I am wrong. – SSD Jan 11 '18 at 15:31
  • 2
    An ASP.Net request-response does not happen within its own isolated process rather it happens on a thread within a worker process (application pool) meaning the connection pool is available to all sessions in your application. – Alex K. Jan 11 '18 at 15:36
  • did you get any solution for this? – Priya Nov 19 '18 at 09:23

2 Answers2

4

ADO.NET's SqlConnection is implementing a connection pool. This means that when you close or dispose an instance of SqlConnection, the underlying connection simply returns to the pool. When another instance of SqlConnection is opened, and a connection is available in the connection pool, that connection will be used.
In fact, Microsoft docs page on SQL Server Connection Pooling clearly states:

Caution
We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

This means that the best practice way of using SqlConnection is this:

using(var con = new SqlConnection(connectionString))
{
    // your sql stuff goes here...
}

BTW, SqlCommand, SqlDataReader and SqlDataAdapter also implements the IDisposable interface, so they too needs to be used in the context of the using statement:

using(var con = new SqlConnection(connectionString))
{
    using(var cmd = new SqlCommand(sql, con))
    {
        // prepare command here - parameters and stuff like that

        // either
        using(var reader = cmd.ExecuteReader())
        {

        }

        // or 
        using(var adapter = new SqlDataAdapter(cmd))
        {

        }

    }
}
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Thanks Zohar. but it will work only for current process which may contain multiple sql connection open/close scenario. I want single sql connection to work for multiple request(>100 times call to web api means everytime different process) in c#. please correct if I am wrong. – SSD Jan 11 '18 at 15:40
  • @user2638841 See [this comment](https://stackoverflow.com/questions/48210258/how-to-keep-single-sql-server-connection-instance-open-for-multiple-request-in-c#comment83402483_48210258) to your question by Alex K. – Zohar Peled Jan 11 '18 at 17:23
0

Well it's easy you just have to keep the connection open and if any readers opened they are closed.

var con = new SqlConnection("Your connection String");
   con.open();
   //your code
con.close()//after you have done your executions

Have you tried Linq. It does the same thing you want, it keeps the connection alive and i think it'll be easier for you

Lahiru Mahagamage
  • 97
  • 2
  • 12