C# DataReader: Sql Batch Of Commands And Return Results

When I have multiple select statements that are seperated with an insert statement or some other statement that doesn't return a dataset the last datasets are not returned by the DataReader when calling NextResult.

For instance i have the following sql statement that is to be executed via SqlCommand to the DataReader

select * from testing;
insert into testing (test) values ('testing here');
select * from testing;

And i execute the sql:

IDbCommand command = MyConnection.CreateCommand();
command.CommandText = statement;
var reader = (System.Data.Common.DbDataReader)command.ExecuteReader();

I would like to get back:

resultset for first select statement
records affected for insert statement
result set for second select statement

Instead I receive the first resultset and then when i execute NextResult() the return is false.

If I run two subsequent selects however the resultsets are both returned ie.

select * from testing
select * from testing2

I've tried just parsing on the ; and excuting the commands seperately. However, this will not work in the long run because eventually I will have the use case to submit an anonymous query or create a stored procedure that would have semicolons in the command.

How can iterate through a DataReader that has mixed results of data and queries that do not have a return?

Solution

I ended up resorting to using a DbDataAdapter and loading the corresponding data into memory, instead of using a DbDataReader. For my application this worked okay.

The DataAdapter handles obtaining both DataTables and also runs the inserts, etc.

Here is a code snippet similar to what i ended up doing:

var command = Connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = statement;
DataSet set = new DataSet();
var da = CreateDataAdapter(connection);
da.SelectCommand = command;
var recordsEffected = da.Fill(set);
foreach (DataTable newTable in set.Tables){
    //do stuff with data returned
}