Return data from datareader asynchronously

Question

I am new to the async stuff and am having a lot of problems trying to get this to work:

I am trying to load a large result set from SQL what I want to have happen is when I run the code below:

public async override IEnumerable<DataResult> Read()
{
    using (SqlConnection objConn = new SqlConnection(Options.GetConnectionString()))
    {
        await objConn.OpenAsync();
        SqlCommand comm = new SqlCommand(Options.SqlText, objConn);
        SqlDataReader reader = await comm.ExecuteReaderAsync();

        while (await reader.ReadAsync())
            yield return new DataResult { Reader = reader };
    }
}

Producer code:

BlockingCollection<DataResult> DataCollection = new BlockingCollection<DataResult>();

var producer = new Producer<DataResult>(() =>
{
    using (var sequenceEnum = sourceEngine.Read().GetEnumerator())
    {
        while (sequenceEnum.MoveNext())
            return sequenceEnum.Current;
    }
    return null;
}, DataCollection);
producer.Start();

That it returns the data as it reads it in record by record to a producer which will store this data into a BlockingCollection for the consumer to consume.

How can I get this code to work for what I am expecting it to do?

Can you post any code for the blocking collection or producer? — weirdev
– weirdev, Commented Aug 4, 2015 at 21:21
do you wan't to get a stream of DataResult and add it to a blocking collection ? — eran otzap
– eran otzap, Commented Aug 4, 2015 at 21:23
Yes I want when the data is read record by record to be returned to the producer to add it to the BlockingCollection. — Paul
– Paul, Commented Aug 4, 2015 at 21:24
@Pluto There is indeed a reader.ReadAsync(). See msdn.microsoft.com/en-us/library/… — Moby Disk
– Moby Disk, Commented Aug 4, 2015 at 21:28
First problem is i have a compile error: async method must be void or Task. Second issue, is I am not sure if I am doing this the correct way and looking for advice for someone if they know a better way to do this. — Paul
– Paul, Commented Aug 4, 2015 at 21:31

Stephen Cleary · Accepted Answer · 2015-08-04 21:38:01Z

3

Your Read signature is not asynchronous:

public override IEnumerable<DataResult> Read();

Any implementation of this method must be synchronous. So you could implement it just using yield and not using async/await at all.

If you want it to be asynchronous, change Read to ReadAsync:

public override Task<IEnumerable<DataResult>> ReadAsync();

which you can implement by (asynchronously) reading into a list, and then returning that list.

However, if what you really want is an asynchronous sequence (handling each piece of data as it comes in), then you should use Rx:

public override IObservable<DataResult> Read();

answered Aug 4, 2015 at 21:38

Stephen Cleary

461k78 gold badges723 silver badges859 bronze badges

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

4 Comments

eran otzap Over a year ago

that's what i was aiming for as well.

Paul Over a year ago

@Stephen Cleary I am not sure how I would set that up. Do you have a code sample?

Stephen Cleary Over a year ago

@Paul: How to set what up? You mean the Rx option?

Paul Over a year ago

Yes, I am not sure how I would use the Rx option with the return yield.

Philippe Paré · Accepted Answer · 2015-08-04 21:31:01Z

0

You unfortunately can't yield from an async method. Marking it as async, you need to return void, Task or Task<T> and that prevents any yield to be made in the method body.

You could maybe return IEnumerable<Task<T>>, returning everything that you were previously awaiting instead of using await (you won't be able to use async/await in this method anymore).

answered Aug 4, 2015 at 21:31

Philippe Paré

4,4385 gold badges39 silver badges57 bronze badges

2 Comments

Paul Over a year ago

I initially tried to not make the method async but I was having an issue trying to open a sql Connection with a Task.Factory.StartNew(() =>

Philippe Paré Over a year ago

Task.StartNew(delegate) will start a new thread, yielding from this is impossible. There is no way of doing what you are asking, in its current form. you will need to modify the return type in some way.

Collectives™ on Stack Overflow

Return data from datareader asynchronously

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related