When I close a SqlConnection, will it free the resources from SqlCommand and SqlDataReader?

Question

Can I place the using() statement only on the SqlConnection? Will it also free the child SqlCommand and SqlDataReader?

Can I do this:

using(SqlConnection connection = new SqlConnection("connection string"))
{

    connection.Open();

    SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
    SqlDataReader reader = cmd.ExecuteReader();
    if (reader != null)
    {
        while (reader.Read())
        {
           //do something
        }   
    } 

}

Or does it need to be:

using(SqlConnection connection = new SqlConnection("connection string"))
{

    connection.Open();

    using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        }

    }

}

You have an answer to your question here: https://stackoverflow.com/questions/14755687/should-i-be-using-sqldatareader-inside-a-using-statement. — Jose Luis, Aug 04 '21 at 19:07
You could save yourself from even needing an answer to that - have you looked into using [Dapper](https://dapper-tutorial.net/)? It's a huge improvement over using raw ADO.NET. No need to separately declare a command and a reader, no reading raw values into DataTables or manually constructing a model from a reader. Your entire command could be: `List result = connection.Query("select * from SomeTable").AsList();` — mason, Aug 04 '21 at 19:07
You should only remember about the connection. It is the most critical resourse since in can be used not only managed but unmanaged code. Command ( and Reader) will be disposed by net common way. This resources are not critical. — Serge, Aug 04 '21 at 20:24
@Serge I really wouldn't rely on that, not closing the reader can leave stuff hanging AFAIK — Charlieface, Aug 05 '21 at 10:57
You can stack the second and third `using` without braces between. Also `if (reader != null)` is completely unnecessary — Charlieface, Aug 05 '21 at 10:59

score 0 · Answer 1 · answered Aug 06 '21 at 02:52

Well, sqlCommand has a conneciton object, and it also has a built in reader.

So, don't create a sepeate conneciton object - use the one built into the sqlCommand object. Since the sqlCommand object will be disposed then so will your connection.

So, do it this way, say to load a gridview:

  protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
        LoadGrid();
        }
    }


    void LoadGrid()
    {

        using (SqlCommand cmdSQL = new SqlCommand("SELECT TOP 10 * from tblHotels ORDER BY HotelName ",
            new SqlConnection(Properties.Settings.Default.TEST4)))
        {
            cmdSQL.Connection.Open();
            ListView1.DataSource = cmdSQL.ExecuteReader();
            ListView1.DataBind();
        }

    }

So, in above we load up a grid view.

But, note how I did not need (or want to) create a separate connection object - I used the built in one of the sqlCommand, and thus did not have to worry about separate disposing of the connection object - I used the one that is part of sqlCommand.

When I close a SqlConnection, will it free the resources from SqlCommand and SqlDataReader?

1 Answers1