CODEWITHSUNDEEP
c#sqldatareader
weeksdev
weeksdev

Reputation: 4355

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:

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?

Upvotes: 5

Views: 2429

Answers (3)

weeksdev
weeksdev

Reputation: 4355

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
}

Upvotes: 3

Justin Russo
Justin Russo

Reputation: 2214

Take a look here, at the SqlDataReader.NextResult method...

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult.aspx

IDbCommand command = MyConnection.CreateCommand();
command.CommandText = statement;
var reader = command.ExecuteReader();

while(reader.Read()){
    //Logic for first result
}

reader.NextResult();

while(reader.Read()){
    //Logic for second result
}

Upvotes: 0

Chris Schubert
Chris Schubert

Reputation: 1298

You should temporarily store your results from the first query before inserting your testing values.

SQL Server:

DECLARE @temp TABLE
([test] VARCHAR(20)) -- change type depending on your needs.

INSERT INTO @temp
SELECT *
FROM testing

SELECT * FROM @temp  -- first result
SELECT * FROM testing

PostgreSQL:

CREATE TEMP TABLE temp1 ON COMMIT DROP AS
SELECT *
FROM testing

INSERT INTO testing (test) VALUES ('testing here');

SELECT * FROM temp1  -- first result
SELECT * FROM testing

Upvotes: 2

Related Questions