Does a SQL connection close with “using” if the connection comes from a static class?
Am I closing my SQL connection correctly, by placing it inside a "using" block?
This is how I grab a new connection, execute a query, and return the results:
using (SqlConnection objCS = DB.LMSAdminConn())
{
objCS.Open();
SqlCommand objCommand = new SqlCommand("SELECT TOP 1 * FROM users WHERE userid = @userid", objCS);
objCommand.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
SqlDataReader reader = objCommand.ExecuteReader();
while (reader.Read())
{
//do something
}
reader.Close();
}
The connection itself comes from this call:
public static SqlConnection LMSAdminConn()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["lmsadmin"].ToString());
}
I am opening the connection inside a "using" block, and I thought that the connection would be closed as well, because it is opened inside the "using" block. But since the "new SqlConnection" object is actually generated from an outside class, is my connection still getting appropriately closed? SQL Server shows the connection as still being open, but I'm not sure if that is ADO.NET connection pool recycling / sharing, or if the connection is truly being held open. I don't explicitly call .Close() on the connection inside the "using" block.
Do I need to explicitly close the SqlCommand and SqlDataReader objects as well, or are they disposed when we leave the "using" block as well?
c# asp.net .net
sql-server ado.net edited Nov 14 '18 at 21:32
grudolf
1,10021624
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
add a comment |
Am I closing my SQL connection correctly, by placing it inside a "using" block?
This is how I grab a new connection, execute a query, and return the results:
using (SqlConnection objCS = DB.LMSAdminConn())
{
objCS.Open();
SqlCommand objCommand = new SqlCommand("SELECT TOP 1 * FROM users WHERE userid = @userid", objCS);
objCommand.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
SqlDataReader reader = objCommand.ExecuteReader();
while (reader.Read())
{
//do something
}
reader.Close();
}
The connection itself comes from this call:
public static SqlConnection LMSAdminConn()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["lmsadmin"].ToString());
}
I am opening the connection inside a "using" block, and I thought that the connection would be closed as well, because it is opened inside the "using" block. But since the "new SqlConnection" object is actually generated from an outside class, is my connection still getting appropriately closed? SQL Server shows the connection as still being open, but I'm not sure if that is ADO.NET connection pool recycling / sharing, or if the connection is truly being held open. I don't explicitly call .Close() on the connection inside the "using" block.
Do I need to explicitly close the SqlCommand and SqlDataReader objects as well, or are they disposed when we leave the "using" block as well?
c# asp.net .net
sql-server ado.net edited Nov 14 '18 at 21:32
grudolf
1,10021624
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
5
Theusingstatement does not know or care what the object "came from". It will dispose its object.
– SLaks
Nov 13 '18 at 22:43
They are closed and disposed when theusingblock terminates. You can read the documentation here.
– Rufus L
Nov 13 '18 at 22:52
Using statement will dispose your connection in the above case.
– Mohammad Alghanem
Nov 14 '18 at 7:03
1
You should useusingstatements for (almost) all objects that implementIDisposableand that you're not maintaining references to in non-local variables. So yes, put yourSqlCommandandSqlDataReaders in there too.
– Damien_The_Unbeliever
Nov 14 '18 at 7:18
add a comment |
Am I closing my SQL connection correctly, by placing it inside a "using" block?
This is how I grab a new connection, execute a query, and return the results:
using (SqlConnection objCS = DB.LMSAdminConn())
{
objCS.Open();
SqlCommand objCommand = new SqlCommand("SELECT TOP 1 * FROM users WHERE userid = @userid", objCS);
objCommand.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
SqlDataReader reader = objCommand.ExecuteReader();
while (reader.Read())
{
//do something
}
reader.Close();
}
The connection itself comes from this call:
public static SqlConnection LMSAdminConn()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["lmsadmin"].ToString());
}
I am opening the connection inside a "using" block, and I thought that the connection would be closed as well, because it is opened inside the "using" block. But since the "new SqlConnection" object is actually generated from an outside class, is my connection still getting appropriately closed? SQL Server shows the connection as still being open, but I'm not sure if that is ADO.NET connection pool recycling / sharing, or if the connection is truly being held open. I don't explicitly call .Close() on the connection inside the "using" block.
Do I need to explicitly close the SqlCommand and SqlDataReader objects as well, or are they disposed when we leave the "using" block as well?
c# asp.net .net
sql-server ado.net edited Nov 14 '18 at 21:32
grudolf
1,10021624
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
Am I closing my SQL connection correctly, by placing it inside a "using" block?
This is how I grab a new connection, execute a query, and return the results:
using (SqlConnection objCS = DB.LMSAdminConn())
{
objCS.Open();
SqlCommand objCommand = new SqlCommand("SELECT TOP 1 * FROM users WHERE userid = @userid", objCS);
objCommand.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
SqlDataReader reader = objCommand.ExecuteReader();
while (reader.Read())
{
//do something
}
reader.Close();
}
The connection itself comes from this call:
public static SqlConnection LMSAdminConn()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["lmsadmin"].ToString());
}
I am opening the connection inside a "using" block, and I thought that the connection would be closed as well, because it is opened inside the "using" block. But since the "new SqlConnection" object is actually generated from an outside class, is my connection still getting appropriately closed? SQL Server shows the connection as still being open, but I'm not sure if that is ADO.NET connection pool recycling / sharing, or if the connection is truly being held open. I don't explicitly call .Close() on the connection inside the "using" block.
Do I need to explicitly close the SqlCommand and SqlDataReader objects as well, or are they disposed when we leave the "using" block as well?
c# asp.net .net
sql-server ado.net c# asp.net .net
sql-server ado.net edited Nov 14 '18 at 21:32
grudolf
1,10021624
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
edited Nov 14 '18 at 21:32
grudolf
1,10021624
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
edited Nov 14 '18 at 21:32
grudolf
1,10021624
edited Nov 14 '18 at 21:32
grudolf
1,10021624
edited Nov 14 '18 at 21:32
grudolf
1,10021624
1,10021624
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
asked Nov 13 '18 at 22:40
Tim WestoverTim Westover
727
727
5
Theusingstatement does not know or care what the object "came from". It will dispose its object.
– SLaks
Nov 13 '18 at 22:43
They are closed and disposed when theusingblock terminates. You can read the documentation here.
– Rufus L
Nov 13 '18 at 22:52
Using statement will dispose your connection in the above case.
– Mohammad Alghanem
Nov 14 '18 at 7:03
1
You should useusingstatements for (almost) all objects that implementIDisposableand that you're not maintaining references to in non-local variables. So yes, put yourSqlCommandandSqlDataReaders in there too.
– Damien_The_Unbeliever
Nov 14 '18 at 7:18
add a comment |
5
Theusingstatement does not know or care what the object "came from". It will dispose its object.
– SLaks
Nov 13 '18 at 22:43
They are closed and disposed when theusingblock terminates. You can read the documentation here.
– Rufus L
Nov 13 '18 at 22:52
Using statement will dispose your connection in the above case.
– Mohammad Alghanem
Nov 14 '18 at 7:03
1
You should useusingstatements for (almost) all objects that implementIDisposableand that you're not maintaining references to in non-local variables. So yes, put yourSqlCommandandSqlDataReaders in there too.
– Damien_The_Unbeliever
Nov 14 '18 at 7:18
5
5
The
using statement does not know or care what the object "came from". It will dispose its object.– SLaks
Nov 13 '18 at 22:43
The
using statement does not know or care what the object "came from". It will dispose its object.– SLaks
Nov 13 '18 at 22:43
They are closed and disposed when the
using block terminates. You can read the documentation here.– Rufus L
Nov 13 '18 at 22:52
They are closed and disposed when the
using block terminates. You can read the documentation here.– Rufus L
Nov 13 '18 at 22:52
Using statement will dispose your connection in the above case.
– Mohammad Alghanem
Nov 14 '18 at 7:03
Using statement will dispose your connection in the above case.
– Mohammad Alghanem
Nov 14 '18 at 7:03
1
1
You should use
using statements for (almost) all objects that implement IDisposable and that you're not maintaining references to in non-local variables. So yes, put your SqlCommand and SqlDataReaders in there too.– Damien_The_Unbeliever
Nov 14 '18 at 7:18
You should use
using statements for (almost) all objects that implement IDisposable and that you're not maintaining references to in non-local variables. So yes, put your SqlCommand and SqlDataReaders in there too.– Damien_The_Unbeliever
Nov 14 '18 at 7:18
add a comment |
2 Answers
2
active
oldest
votes
A using block is essentially syntactic sugar for having a try/finally block that calls the Dispose method of the object it is acting on, it doesn't matter where that object was created.
For a SqlConnection object, calling Dispose will close the connection. From the docs:
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling
CloseorDispose. Close and Dispose are functionally equivalent.
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
add a comment |
Yes, it will close the connection once it loses scope.
Sample:
using (SqlConnection sqlConn = new SqlConnection("myConnectionString"))
{
sqlConn.Open();
...
}
This code will be converted into the following by the compiler :
try
{
SqlConnection sqlConn = new SqlConnection("myConnectionString");
sqlConn.Open();
...
}
finally
{
sqlConn.Close();
}
As you can see the close() is called in the finally block.
This finally block will force close the connection even if there is an exception during run-time within the using block.
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
Your translation is wrong. The declaration and instantiation of thesqlConnvariable is done before thetryblock begins, and thefinallyclause will not callClose, it will callDispose. Please refer to official documentation for a correct translation.
– Zohar Peled
Nov 15 '18 at 12:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53290589%2fdoes-a-sql-connection-close-with-using-if-the-connection-comes-from-a-static-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
A using block is essentially syntactic sugar for having a try/finally block that calls the Dispose method of the object it is acting on, it doesn't matter where that object was created.
For a SqlConnection object, calling Dispose will close the connection. From the docs:
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling
CloseorDispose. Close and Dispose are functionally equivalent.
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
add a comment |
A using block is essentially syntactic sugar for having a try/finally block that calls the Dispose method of the object it is acting on, it doesn't matter where that object was created.
For a SqlConnection object, calling Dispose will close the connection. From the docs:
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling
CloseorDispose. Close and Dispose are functionally equivalent.
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
add a comment |
A using block is essentially syntactic sugar for having a try/finally block that calls the Dispose method of the object it is acting on, it doesn't matter where that object was created.
For a SqlConnection object, calling Dispose will close the connection. From the docs:
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling
CloseorDispose. Close and Dispose are functionally equivalent.
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
A using block is essentially syntactic sugar for having a try/finally block that calls the Dispose method of the object it is acting on, it doesn't matter where that object was created.
For a SqlConnection object, calling Dispose will close the connection. From the docs:
If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling
CloseorDispose. Close and Dispose are functionally equivalent.
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
answered Nov 13 '18 at 22:46
DavidGDavidG
69.5k9111127
69.5k9111127
add a comment |
add a comment |
Yes, it will close the connection once it loses scope.
Sample:
using (SqlConnection sqlConn = new SqlConnection("myConnectionString"))
{
sqlConn.Open();
...
}
This code will be converted into the following by the compiler :
try
{
SqlConnection sqlConn = new SqlConnection("myConnectionString");
sqlConn.Open();
...
}
finally
{
sqlConn.Close();
}
As you can see the close() is called in the finally block.
This finally block will force close the connection even if there is an exception during run-time within the using block.
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
Your translation is wrong. The declaration and instantiation of thesqlConnvariable is done before thetryblock begins, and thefinallyclause will not callClose, it will callDispose. Please refer to official documentation for a correct translation.
– Zohar Peled
Nov 15 '18 at 12:21
add a comment |
Yes, it will close the connection once it loses scope.
Sample:
using (SqlConnection sqlConn = new SqlConnection("myConnectionString"))
{
sqlConn.Open();
...
}
This code will be converted into the following by the compiler :
try
{
SqlConnection sqlConn = new SqlConnection("myConnectionString");
sqlConn.Open();
...
}
finally
{
sqlConn.Close();
}
As you can see the close() is called in the finally block.
This finally block will force close the connection even if there is an exception during run-time within the using block.
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
Your translation is wrong. The declaration and instantiation of thesqlConnvariable is done before thetryblock begins, and thefinallyclause will not callClose, it will callDispose. Please refer to official documentation for a correct translation.
– Zohar Peled
Nov 15 '18 at 12:21
add a comment |
Yes, it will close the connection once it loses scope.
Sample:
using (SqlConnection sqlConn = new SqlConnection("myConnectionString"))
{
sqlConn.Open();
...
}
This code will be converted into the following by the compiler :
try
{
SqlConnection sqlConn = new SqlConnection("myConnectionString");
sqlConn.Open();
...
}
finally
{
sqlConn.Close();
}
As you can see the close() is called in the finally block.
This finally block will force close the connection even if there is an exception during run-time within the using block.
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
Yes, it will close the connection once it loses scope.
Sample:
using (SqlConnection sqlConn = new SqlConnection("myConnectionString"))
{
sqlConn.Open();
...
}
This code will be converted into the following by the compiler :
try
{
SqlConnection sqlConn = new SqlConnection("myConnectionString");
sqlConn.Open();
...
}
finally
{
sqlConn.Close();
}
As you can see the close() is called in the finally block.
This finally block will force close the connection even if there is an exception during run-time within the using block.
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
answered Nov 14 '18 at 5:10
Prabu AnandPrabu Anand
14
14
Your translation is wrong. The declaration and instantiation of thesqlConnvariable is done before thetryblock begins, and thefinallyclause will not callClose, it will callDispose. Please refer to official documentation for a correct translation.
– Zohar Peled
Nov 15 '18 at 12:21
add a comment |
Your translation is wrong. The declaration and instantiation of thesqlConnvariable is done before thetryblock begins, and thefinallyclause will not callClose, it will callDispose. Please refer to official documentation for a correct translation.
– Zohar Peled
Nov 15 '18 at 12:21
Your translation is wrong. The declaration and instantiation of the
sqlConn variable is done before the try block begins, and the finally clause will not call Close, it will call Dispose. Please refer to official documentation for a correct translation.– Zohar Peled
Nov 15 '18 at 12:21
Your translation is wrong. The declaration and instantiation of the
sqlConn variable is done before the try block begins, and the finally clause will not call Close, it will call Dispose. Please refer to official documentation for a correct translation.– Zohar Peled
Nov 15 '18 at 12:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53290589%2fdoes-a-sql-connection-close-with-using-if-the-connection-comes-from-a-static-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
5
The
usingstatement does not know or care what the object "came from". It will dispose its object.– SLaks
Nov 13 '18 at 22:43
They are closed and disposed when the
usingblock terminates. You can read the documentation here.– Rufus L
Nov 13 '18 at 22:52
Using statement will dispose your connection in the above case.
– Mohammad Alghanem
Nov 14 '18 at 7:03
1
You should use
usingstatements for (almost) all objects that implementIDisposableand that you're not maintaining references to in non-local variables. So yes, put yourSqlCommandandSqlDataReaders in there too.– Damien_The_Unbeliever
Nov 14 '18 at 7:18