How to best clean up a SQLDataReader when using linq

Question

I have a method that returns a data reader. Normally I would wrap a using around the data reader so that it gets disposed nicely after I iterate through it. THe problem is that I've chosen to consume the data reader using Linq, which means that the defered execution causes the reader to get disposed early. Is there a proper pattern to consume and dispose of the data reader using Linq without having to built a complete collection of it's contents?

using (System.Data.SqlClient.SqlDataReader reader = CallStoredProcedure())
{
    return reader.Cast<System.Data.Common.DbDataRecord>().Select(rec => new ObjectModel.CollectorSummaryItem()
    {
        CollectorID = (int)rec[0],
        Name = rec.IsDBNull(1) ? null : rec.GetString(1),
        Information = rec.IsDBNull(2) ? null : rec.GetString(2)
    });
}

Have you tried simply delegating the read? Create a method that accepts IDataRecord and builds your object. Build a list off of it and return that list? — Sinaesthetic
– Sinaesthetic, Commented Apr 24, 2013 at 3:22

Ian Warburton · Accepted Answer · 2017-11-08 00:49:52Z

8

You need to actually read from the reader inside the using block:

using (System.Data.SqlClient.SqlDataReader reader = CallStoredProcedure())
{
    while (reader.Read())
    {
        yield return new ObjectModel.CollectorSummaryItem()
        {
            CollectorID = (int)reader[0],
            Name = reader.IsDBNull(1) ? null : reader.GetString(1),
            Information = reader.IsDBNull(2) ? null : reader.GetString(2)
        };
    }
}

This will evaluate to code with the same or consistent return type with what you had before, but doesn't close the reader until after you're done reading from it.

edited Nov 8, 2017 at 0:49

Ian Warburton

15.8k25 gold badges120 silver badges211 bronze badges

answered Apr 23, 2013 at 22:11

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sinaesthetic · Accepted Answer · 2013-12-05 01:22:04Z

0

Shooting from the hip here. This should help you decouple the reader logic as well.

public IEnumerable<MyObject> ExecuteNonQuery(...)
{
  ...
  using(var reader = comm.ExecuteReader())
  {
    var results = new List<MyObject>();
    return reader
            .Cast<System.Data.Common.DbDataRecord>()
            .Select(rec => GetFromReader(rec))
            .ToList();
  }
}

public MyObject GetFromReader(IDataRecord rdr)
{
   return new MyObject { Prop1 = rdr["prop1"], Prop2 = rdr["prop2"] };
}

edited Dec 5, 2013 at 1:22

answered Apr 24, 2013 at 3:30

Sinaesthetic

12.4k30 gold badges115 silver badges186 bronze badges

1 Comment

Sir Intellegence-BrainStormexe Over a year ago

There is a slight problem with that code. LINQ is lazy and won't evaluate the lambda expression until you enumerate over the IEnumerable, which will likely be outside of the method given. By that time, the using statement would have disposed of reader. If you used a method like ToList() after the Select statement to force the enumeration of the IEnumerable inside the using block, that might work.

Collectives™ on Stack Overflow

How to best clean up a SQLDataReader when using linq

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related