CODEWITHSUNDEEP
c#sqlsql-server
KyloRen
KyloRen

Reputation: 2741

Is there an advantage to creating a new SQL connection for each query?

I am trying to design an efficient application that connects to a SQL Database and I was wondering what the merits/demerits of creating a SQL connection for each SQL query is, Eg, Like the code below, making a class with the connection set once so you can call on it when ever you need it in the class.

class SqlQuery
    {
        SqlConnection Connection = new SqlConnection(@myConnectionString);
          public void fillInfoData()
          {
              SqlCommand updateCommand = new SqlCommand(
                    @"my sql query", Connection);
              Connection.Open();
              updateCommand.ExecuteNonQuery();
              Connection.Close();
          }
    }

Or should you create a new connection for each new query?

Upvotes: 4

Views: 2116

Answers (2)

Zein Makki
Zein Makki

Reputation: 30022

I think what he meant was not to keep a global connection object that is open. But you need to create an SqlConnection when you need it, execute all the queries needed at the point (Not a single connection per each query) and then close the connection.

So if you have a bunch of queries that needs to be executed, you don't need a single sql connection for each but one is only enough. But if you're executing a query each time the user clicks a button for example, it's better to open a connection on each click, because that way it is easier to manage closing and disposing the connection.

Upvotes: 2

Hassan
Hassan

Reputation: 1433

You open a connection per query and then when you close it, it goes back to the connection pool and the framework will manage the rest

So you do not have to worry about keeping connection alive, timeouts etc...

MSDN Article

https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

Upvotes: 10

Related Questions