While understanding how yield keyword works, I came across link1 and link2 on StackOverflow which advocates the use of yield return while iterating over the DataReader and it suits my need as well. But It makes me wonder as what happens, if I use yield return as shown below and if I don't iterate through entire DataReader, will the DB connection stay open forever? IEnumerable<IDataRecord> GetRecords(){    SqlConnection myConnection = new SqlConnection(@...);    SqlCommand myCommand = new SqlCommand(@..., myConnection);    myCommand.CommandType = System.Data.CommandType.Text;    myConnection.Open();    myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);    try       {                         while (myReader.Read())          {            yield return myReader;                    }        }    finally        {            myReader.Close();        }}void AnotherMethod(){    foreach(var rec in GetRecords())    {       i++;       System.Console.WriteLine(rec.GetString(1));       if (i == 5)         break;  }}I tried the same example in a sample Console App and noticed while debugging that the finally block of GetRecords() is not executed. How can I ensure then the closure of DB Connection? Is there a better way than using yield keyword? I am trying to design a custom class which will be responsible for executing select SQLs and stored procedures on DB and will return the result. But I don't want to return the DataReader to the caller. Also I want to make sure that the connection will be closed in all scenarios.Edit Changed the answer to Ben's answer as it is incorrect to expect method callers to use the method correctly and with respect to DB connection it will be more expensive if the method is called multiple times for no reason.Thanks Jakob and Ben for detailed explanation.
Will the Database connection be closed if we yield the datareader row and not read all the records?
c#;database;.net;resources
Yes, you will face the problem you describe: until you finish iterating over the result, you will keep the connection open. There's two general approaches I can think of to deal with this:Push, don't pullCurrently you're returning an IEnumerable<IDataRecord>, a data structure you can pull from. Instead, you could switch your method to push its results out. The simplest way would be to pass in an Action<IDataRecord> which is called on each iteration:void GetRecords(Action<IDataRecord> callback){    // ...      while (myReader.Read())      {        callback(myReader);      }}Note that given you're dealing with a collection of items, IObservable/IObserver might be a slightly more appropriate data structure, but unless you need it, a simple Action is much more straightforward.Eagerly EvaluateAn alternative is just to make sure the iteration entirely completes before returning. You can normally do this by just putting the results in a list then returning that, but in this case there's the additional complication of each item being the same reference to the reader. So you need something to extract the result you need from the reader:IEnumerable<T> GetRecords<T>(Func<IDataRecord,T> extractor){    // ...     var result = new List<T>();     try     {       while (myReader.Read())       {         result.Add(extractor(myReader));                }     }     finally     {         myReader.Close();     }     return result;}