ado.net Closing Connection when using "using" statement

The using will take care of it for you. Under the hood, SqlConnection.Dispose() calls the SqlConnection.Close() method, and SqlCommand.Dispose() calls SqlCommand.Close().

As additional background, a using statement is syntactic sugar for a try ... finally that disposes the IDisposable object in the finally.


As an aside, you can make the code more concise and readable as follows:

 using (SqlConnection con = new SqlConnection(/*connection string*/))
 using (SqlCommand cmd = new SqlCommand(storedProcname, con))
 {
    //...
 }

As Phil said, the using clause will take care of it for you. When compiled down it wraps the connection create in a try .. finally and places the connection disposal call inside the finally.

For more information you can see the using statement article at msdn.


Yes your code will close the connection, however that typcally means release back to the connection pool to be truely closed later.

If you execute this snippet of code, and then do an sp_who and observe that your connection is still there, that would be why.

If you absolutely need the connection truely closed (an edge case to be sure) then use the ClearAllPools static method of ths SqlConnection


Using keyword will automatically close the connection for you so you don't need to worry about calling connection.close() at the end every time.


Comments

  1. Rudy

    • 2018/7/23

    The using will take care of it for you. Under the hood, SqlConnection.Dispose() calls the SqlConnection.Close() method, and SqlCommand.

  2. Simeon

    • 2017/11/20

    Using keyword will automatically close the connection for you so you don't need to worry about calling connection.close() at the end every time.

  3. Krasniqi

    • 2017/7/3

    Instead of using an exception handling block, you can use the C# using statement with the Connection object. This ensures that Dispose( ) will be called on the 

  4. Evans

    • 2017/3/15

    I am using ADO.NET (.NET 1.1) in a legacy app. I know that DataAdapter.Fill() opens and closes connections if the connection hasn't been opened manually before it's given to the DataAdapter. My question: Does it also close the connection if the .Fill() causes an Exception? (due to SQL Server cannot be reached, or whatever).

  5. Thomas

    • 2016/9/17

    Opening and Closing Connections You've now seen all the ingredients you need to create and use a connection. You simply create the Connection object 

  6. Jaiden

    • 2019/5/28

    The .NET Framework Data Provider for SQL Server supports a connection string format that is similar to the OLE DB (ADO) connection string format. For valid string format names and values, see the ConnectionString property of the SqlConnection object. You can also use the SqlConnectionStringBuilder class to create syntactically valid connection

  7. Jeffrey

    • 2018/4/14

    Before you can access data in Vertica through ADO.NET, you must create a connection to the database using the VerticaConnection class which is an implementation 

  8. Pedro

    • 2017/6/16

    ADO.NET SqlConnection Class. It is used to establish an open connection to the SQL Server database. It is a sealed class so that cannot be inherited. SqlConnection class uses SqlDataAdapter and SqlCommand classes together to increase performance when connecting to a Microsoft SQL Server database. Connection does not close explicitly even it

  9. Bianco

    • 2020/3/8

    Run a SQL INSERT statement to open and close a connection to Cloud SQL for MySQL by using the ADO.NET System.Data.Common package.

  10. Gatlin

    • 2016/3/12

    If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call. Only connections with the same configuration can be pooled. ADO.NET keeps several pools at the same time, one for each configuration.

  11. Cristian

    • 2020/8/18

    We all know that we should take extra care to always close our DB able to safely use DB transactions from your C# code (using ADO).

  12. Jamal

    • 2017/8/3

    In the .NET Framework, ADO.NET plays a part in this and minimizes the opening and closing process to make the program execution a bit faster by creating, what we call, a Connection Pool. This technique reduces the number of times the connection is opened, by saving the instance of the connection.

  13. Layton

    • 2015/3/15

    connection.Close();; }; }; }. A Connection String in the Web.Config file with connection 

  14. Chaim

    • 2016/11/8

    I have this code in a C# class file: { try { using (SqlConnection con = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new 

  15. Jaxon

    • 2015/10/17

    Now, imagine having an application opening and closing database ADO.NET provides connection pooling capabilities with the use of a 

Comments are closed.

Recent Posts