Using statement

Question

I have two questions.

1) Should the using statement always be used in the connection? So, I would use it in a connection, and then another in the reader in a connection? Therefore, I would use two using statements.

2) Suppose you use a using statement in a connection, as well as a reader that returns to the connection. So you have two operators. Does it create two Try {} finally {} blocks or only one?

Thank!

Answer 1

• You should always use instruction usingwhen an object implements IDisposable. This includes connections.

• It will create two nested blocks try{}finally{}.

Answer 2

. using , IDisposable. , . , . , . , using , :

DataReader MyQuery()
{
    string sql="some query";
    using (var cn = new SqlConnection("connection string"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            return rdr;
        }
    }
}

. - , , :

DataReader MyQuery()
{
    string sql="some query";
    using (var cn = new SqlConnection("connection string"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cn.Open();
        return cmd.ExecuteReader();
    }
}

using (var rdr = MyQuery())
{
    while (rdr.Read())
    {
        //...
    }
}

, , , , .

- , . , MyQuery() Action, while (rdr.Read()), .

: IEnumerable, :

IEnumerable<IDataRecord> MyQuery()
{
    string sql="some query";
    using (var cn = new SqlConnection("connection string"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
              yield return rdr;
        }
    }
}

, , , . : linq.

, - , , , IEnumerable :

//part of the data layer
private static IEnumerable<IDataRecord> Retrieve(string sql, Action<SqlParameterCollection> addParameters)
{
    //DL.ConnectionString is a private static property in the data layer
    // depending on the project needs, it can be implementing to read from a config file or elsewhere
    using (var cn = new SqlConnection(DL.ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        addParameters(cmd.Parameters);

        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
              yield return rdr;
        }
    }
}

:

public IEnumerable<IDataRecord> GetFooChildrenByParentID(int ParentID)
{
    //I could easily use a stored procedure name instead, and provide overloads for commandtypes.
    return Retrieve(
        "SELECT c.* 
         FROM [ParentTable] p 
         INNER JOIN [ChildTable] c ON c.ParentID = f.ID 
         WHERE f.ID= @ParentID", p => 
       {
          p.Add("@ParentID", SqlDbType.Int).Value = ParentID;
       }
     );
}

Answer 3

1) ? , , ? .

, IDisposable. using :

using (DbConnection connection = GetConnection())
using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = "SELECT FOO, BAR FROM BAZ";
    connection.Open();
    using (DbDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            ....
        }
    }
}

2) , , . , . {} {} ?

using try/finally

Answer 4

1). , ADO.NET - BeginExecuteReader, , , async . , , . , " " .

Answer 5

:

1) , .

2) using()will create two blocks wrapped in each other in the same order. First, it will remove the internal object (reader), and then remove the object from the external (connection).

Answer 6

This article will probably be of interest to you: How to implement IDisposable and Finalizers: 3 simple rules