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

Question

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?

EDIT: Jon Skeet recommended creating a new connection for each query, I was wondering why?
EDIT No 2: He stated it in this answer.

score 8 · Accepted Answer · answered Jul 17 '16 at 11:35

8

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

answered Jul 17 '16 at 11:35

Hassan

1,413
1
10
12

I can't remember where I read this ,but Jon Skeet recommended that you create a new connection each time. I was wondering why? – KyloRen Jul 17 '16 at 11:38
@KyloRen because the framework will take care of the connection management for you – Hassan Jul 17 '16 at 11:46
1

@KyloRen, since connection pools are per app domain, the "open late, close early" pattern is more scalable. This reduces resource usage on the database server as well as by multiple instances of the class. – Dan Guzman Jul 17 '16 at 11:52
@DanGuzman, so what I did in my OP is considered good coding then? – KyloRen Jul 17 '16 at 11:54
1

@KyloRen, yes, except the connection member variable should be a local variable in a `using`. That would allow you to omit the Close. See http://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception. – Dan Guzman Jul 17 '16 at 12:00

score 2 · Answer 2 · answered Jul 17 '16 at 11:47

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.

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

2 Answers2