How to Update an array with Dapper Extension?

Question

I get from DB my entities' list, change some properties and try to Update in in DB.

using (var cn = new SqlConnection(ConnectionString))
{
cn.Open();
var dataPredicate = Predicates.Field<Data>(f => f.Id, Operator.Eq, new [] {1, 2, 3}); // of course it's for an example 
var data = cn.GetList<Data>(dataPredicate);

foreach (var element in data)
{
     element.Status = StatusEnum.Pending;
     element.LastChange = DateTime.Now;
}

foreach (var activeRequest in data)
{
     cn.Update(activeRequest);
}
cn.Close();
}

I tried also:

var updated = data.Select(s => new Data
{
     Id = s.Id,
     CreateDate = s.CreateDate,
     ForeignId = s.ForeignId,
     LastChange = DateTime.Now,
     Status = RequestStatus.Pending
 });

And I get InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

I don't have any problem with another operations.

How can I Update it correctly?

Unrelated but no need to `cn.Close();` inside a using statement. — v.shashenko, Feb 25 '14 at 20:53
@v.shashenko I thought the same way, but they say the opposite: [Dapper-Extensions on github](https://github.com/tmsmith/Dapper-Extensions) — lyha, Feb 26 '14 at 08:02
@Anri , it's helpful, thank you. I thought first foreach did the same. — lyha, Feb 26 '14 at 08:19
@lyha Added answer with detailed explanations, you can accept it if it's helpful — Anri, Feb 26 '14 at 08:41

score 1 · Accepted Answer · answered Feb 26 '14 at 08:41

cn.GetList<Data>(dataPredicate) returns IEnumerable and every time you use it with foreach actual sql query gets executed and DataReader is used to provide IEnumerable with data.

So in your case you are executing DataReader twice, and second time you are trying to do Update query on the same connection with DataReader still open.

If this is desirable behaviour (too much data and you do want to load it one by one with DataReader) you should use separate connection for Update queries.

Another option will be to load all the data to list and then iterate trough it, like this:

var data = cn.GetList<Data>(dataPredicate).ToList();

How to Update an array with Dapper Extension?

1 Answers1