5

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?

Improve this question
3
  • Which database is this? Which provider software are you using? And have you tried wrapping your statements in a stored procedure block of code? It may actually work better than you expect. Commented Jul 7, 2015 at 1:41
  • @sstan i've been testing against a postgres database. I've tried executing with the npgsql.net and the odbc driver with the same result. The utility this is for will let the user type in the query so putting it into a stored procedure wouldn't work unforunately. Commented Jul 7, 2015 at 1:46
  • I'm using the agnostic Common namespace to hopefully support multiple servers (mysql, sqlserver) as well. Commented Jul 7, 2015 at 1:47

3 Answers 3

Reset to default
3

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
}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

this does not work for me. i had 2 statements, 1st if SELECT, 2nd is UPDATE. only the SELECT is in the dataset. im using MySqlDataAdapter
2

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
Improve this answer

6 Comments

this would work for the specific case stated above. However the tool is a sql utility and would support basically unknown input of the the sql. i'd like a c# solution if it can be found.
Oh, I see what you mean. I was assuming that the insert was invalidating the previous SELECT as a result to be returned and so it needed to be stored in memory. I'm not sure of a c# specific way.
i still think this a good solution in general although not for my specific use case. i'm sure this solution will work for others stumbling into this post later. thanks!
Problem with this, is that I'm not certain your 'Temp' table is transient outside of a stored procedure, so I believe it is retained in the "TempDB." Since you cannot drop the temp table at the end of this statement because it will muck up your results, I think a different solution is in order. Possibly a Stored Proc.
@JustinRusso good point - #temp would exist as long as the session does. I've updated.
|
0

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
}
Improve this answer

1 Comment

this is what i am using to iterate, however, it seems if there is a nonquery statement in between (for instance an insert statement) then the second query doesn't return. For instance the following three queries: select, insert, select. The last select doesn't come back with NextResult it returns false on the insert

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.