How to Keep Single SQL Server Connection Instance Open for Multiple Request in C#

C# Using one SqlConnection for multiple queries

In short don't do it

---

Creating a new instance of the class SqlConnection does not create a new network connection to SQL Server, but leases an existing connection (or creates a new one). .NET handles the physical connection pooling for you.

When you have finished with your connection (through which you can send multiple queries) just Close() or Dispose() (or use a using{} block preferably).

There is no need, and not good practise, to cache instances of the SqlConnection class.

Update

This is a better pattern for your method, you dont have to worry about the connections state

static void SqlQuery(string cmdString)
{
    using (var connection = new SqlConnection(connString))
    using (var cmd = connection.CreateCommand(cmdString, connection))
    { 
        connection.Open();    
        // query        
        cmd.ExecuteNonQuery();
    }
}

Calling SQL Server more than one at same time and same connection

The generally accepted Best Practice for dealing with connections and commands on a "raw" ADO.NET level is to always create those objects as needed, as late as possible, and free them again as soon as possible. ADO.NET already has a very sophisticated connection pooling mechanism built-in - no point in trying to outsmart that well-proven mechanism.

In order to ensure proper disposal of the objects, it is also generally accepted to put them into using(...) { ... } blocks - something like this:

// define query as a string
string query = "SELECT ..... FROM ... WHERE ......";

// create connection and command in "using" blocks
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmd = new SqlCommand(conn, query))
{
    // set up e.g. parameters for your SqlCommand

    // open the connection, execute the query, close the connnection again
    conn.Open();

    // for a SELECT, use ExecuteReader and handle the reader (or use SqlDataAdapter to fill a DataTable)
    // for UPDATE, INSERT, DELETE just use a ExecuteNonQuery() to run the command
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
           // handle the data from the reader
        }

        // close reader
        rdr.Close();
    }

    conn.Close();
}

Is it better to execute many sql commands with one connection, or reconnect every time?

By default, SqlConnection will use connection pooling. Therefore your code does most likely not actually open many connections in either case.

You can control if SqlConnection will use pooling by enabling or disabling the pool in the connectionstring, depending on what DB your connection string is for, the syntax will vary.

See here for some info if you use MSSQLServer. Try setting Pooling=false in the connection string and see if it makes a difference.

Can I use same SQL connection string multiple times in my application?

yes you can use same by storing it inside web.config file or app.config file in case of windows form application and then reuse it

System.Configuration.ConfigurationManager.
ConnectionStrings["connectionStringName"].ConnectionString;

where connectionStringName is name of connection string stored in web.config file

Best practice for reusing SqlConnection

Creating a new instance of the class SqlConnection does not create a new network connection to SQL Server, but leases an existing connection (or creates a new one). .NET handles the physical connection pooling for you.

When you have finished with your connection (through which you can send multiple queries) just Close() or Dispose() (or use a using{} block preferably).

There is no need, and not good practise, to cache instances of the SqlConnection class.

How to avoid my HttpClient opening multiple connections for single instance of the client?

You need to Dispose of response, as per this discussion. You may wish to use using.

Additionally (from that link):

It's expected that the connections stay open. You don't want them to
stay open at all? By default connections are kept alive in a
connection pool for several minutes in order to avoid the expense of
creating and tearing them down for every request.

In other words, connections are pooled for future performance (for a short window of time).

Additionally, your:

var sp = ServicePointManager.FindServicePoint(new Uri(Config.ConnectionAPI));
sp.ConnectionLeaseTimeout = 1 * 1000;

should be run once at startup, not repeatedly. After doing this, you could thus remove the CloseConnection method.

Connecting to multiple SQL Servers dynamicly with C# Form?

you can utilize class EntityConnectionStringBuilder to build your connection string. refer more here https://msdn.microsoft.com/en-us/library/orm-9780596520281-01-16.aspx and Programmatic Connection Strings in Entity Framework 6

// Specify the provider name, server and database.
        string providerName = "System.Data.SqlClient";
        string serverName = ".";
        string databaseName = "AdventureWorks";

        // Initialize the connection string builder for the
        // underlying provider.
        SqlConnectionStringBuilder sqlBuilder =
            new SqlConnectionStringBuilder();

        // Set the properties for the data source.
        sqlBuilder.DataSource = serverName;
        sqlBuilder.InitialCatalog = databaseName;
        sqlBuilder.IntegratedSecurity = true;

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder =
            new EntityConnectionStringBuilder();

        //Set the provider name.
        entityBuilder.Provider = providerName;

        // Set the provider-specific connection string.
        entityBuilder.ProviderConnectionString = providerString;

        // Set the Metadata location.
        entityBuilder.Metadata = @"res://*/AdventureWorksModel.csdl|
                        res://*/AdventureWorksModel.ssdl|
                        res://*/AdventureWorksModel.msl";
        Console.WriteLine(entityBuilder.ToString());

        using (EntityConnection conn =
            new EntityConnection(entityBuilder.ToString()))
        {
            conn.Open();
            Console.WriteLine("Just testing the connection.");
            conn.Close();
        }

Multiple session-less requests to a page (which interacts with database) results in HTTP Error 500

It appears you are using a connection pool. By default these pools have a max of 100 connections to your SQL server and queue any additional connections. The queue has a timeout (default 15 seconds) which can be extended if you wish to queue your requests longer. This means that you might get backed up on your server. You can also increase the pool max size if your SQL server can handle it.

Here is how you increase your connection settings by adding these parameters:

• Timeout=60
• Max Pool Size=150
• etc etc

What's the best method for using SqlCommand and SqlConnection objects in an ASP.NET project

You should use new SqlCommand for each event. And close it after it's completed.

You have to use separate SqlConnection instance, because if you happen to have more than one query executed at the same moment (which is highly possible in web scenario) than you'll get an exception that previous command for this connection didn't complete yet. SqlConnection can support only one command at a time.

---

Related Topics