C# DataReader: Sql Batch Of Commands And Return Results

Question

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?

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. — sstan, Jul 07 '15 at 01: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. — weeksdev, Jul 07 '15 at 01:46
I'm using the agnostic `Common` namespace to hopefully support multiple servers (mysql, sqlserver) as well. — weeksdev, Jul 07 '15 at 01:47

score 3 · Accepted Answer · answered Jul 08 '15 at 00:39

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
}

Chris Schubert · Answer 2 · 2015-07-07T02:26:23.980

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

edited Jul 07 '15 at 02:26

answered Jul 07 '15 at 01:46

Chris Schubert

1,288
8
17

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. – weeksdev Jul 07 '15 at 01:51
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. – Chris Schubert Jul 07 '15 at 01:53
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! – weeksdev Jul 07 '15 at 01:55
1

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. – Justin Russo Jul 07 '15 at 02:21
1

@JustinRusso good point - #temp would exist as long as the session does. I've updated. – Chris Schubert Jul 07 '15 at 02:27
Ahhh, I didn't think about a virtual table. Touche!! LOL – Justin Russo Jul 07 '15 at 02:28

score 0 · Answer 3 · answered Jul 07 '15 at 02:42

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
}

answered Jul 07 '15 at 02:42

Justin Russo

2,214
1
22
26

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 – weeksdev Jul 07 '15 at 02:47

C# DataReader: Sql Batch Of Commands And Return Results

3 Answers3