1

When having a query that returns multiple results, we are iterating among them using the NextResult() of the SqlDataReader. This way we are accessing results sequentially.

Is there any way to access the result in a random / non sequential way. For example jump first to the third result, then to the first e.t.c

I am searching if there is something like rdr.GetResult(1), or a workaround.

Since I was asked Why I want something like this,

  • First of all I have no access to the query and so I can not changes, so in my client I will have the Results in the sequence that server writes / produces them.
  • To process (build collections, entities --> business logic) the first I need the Information from both the second and the third one.
  • Again since it is not an option to modify some of the code, I can not somehow (without writing a lot of code) 'store' the connection info (eg. ids) in order to connect in a later step the two ResultSets
  • The most 'elegant' solution (for sure not the only one) is to process the result sets in non sequential way. So that is why I am asking if there is such a way.

Update 13/6

While Jeroen Mostert answer gives a thoughtful explanation on why, Think2ceCode1ce answer shows the right directions for a workaround. The content of the link in the comments how in additional dataset could be utilized to work in an async way. IMHO this would be the way to go if was going to write a general solution. However in my case, I based my solution in the nature of my data and the logic behind them. In short terms, (1) I read the data as they come sequentially using the SqlDataReader; (2) I store some of the data I need in a dictionary and a Collection, when I am reading the first in row but second in logic ResultSet; (3) When I am Reading the third in row, but first in logic ResultSet I am iterating in through the collection I built earlier and based on the dictionary data I am building my final result.

The final code seems more efficient and it is more maintainable than using the async DataAdapter. However this is a very specific solution based on my data.

Improve this question
5
  • 3
    SqlDataReader is a forward only cursor Commented Jun 9, 2016 at 14:42
  • Why do you want to do this? Are you trying to avoid the execution of one of the statements? Commented Jun 9, 2016 at 14:46
  • 1
    I misunderstood your question, so deleted my answer. But as a comment I will just mention that DataTable and DataSet are the objects you use to cache local copies of queries for random access and/or modification. Commented Jun 9, 2016 at 14:48
  • @ConradFrix I just updated the question adding the 'Why' Commented Jun 9, 2016 at 15:06
  • 1
    Given your restrictions, your only real way out is to leverage a DataSet and accept that you have to get all data locally first before you can process it. SQL Server won't do this for you. Commented Jun 9, 2016 at 15:12

2 Answers 2

Reset to default
7

Provides a way of reading a forward-only stream of rows from a SQL Server database.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx

You need to use DataAdapter for disconnected and non-sequential access. To use this you just have to change bit of your ADO.NET code.

Instead of

SqlDataReader sqlReader = sqlCmd.ExecuteReader();

You need

DataTable dt = new DataTable();
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
sqlAdapter.Fill(dt);

If your SQL returns multiple result sets, you would use DataSet instead of DataTable, and then access result sets like ds.Tables[index_or_name].

https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

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

3 Comments

DataSet seems the right way to go, however (after some reading and testing) I have some performance concerns. Moreover DataAdapter does not (yet) support 'Async' methods, which is one of the features I am considering to use in the future.
Are you expecting some thing like this? See reply from Fred at social.msdn.microsoft.com/Forums/en-US/…
Yes I already look at it and I plan to test it. it is a very nice approach indeed and tackles the DataAdapter 's, Async 'drawback'.
2

No, this is not possible. The reason why is quite elementary: if a batch returns multiple results, it must return them in order -- the statement that returns result set #2 does not run before the one that returns result set #1, nor does the client have any way of saying "please just skip that first statement entirely" (as that could have dire consequences for the batch as a whole). Indeed, there's not even any way in general to tell how many result sets a batch will produce -- all of this is done at runtime, the server doesn't know in advance what will happen.

Since there's no way, server-side, to skip or index result sets, there's no meaningful way to do it client-side either. You're free to ignore the result sets streamed back to you, but you must still process them in order before you can move on -- and once you've moved on, you can't go back.

There are two possible global workarounds:

  • If you process all data and cache it locally (with a DataAdapter, for example) you can go back and forth in the data as you please, but this requires keeping all data in memory.
  • If you enable MARS (Multiple Active Result Sets) you can execute another query even as the first one is still processing. This does require splitting up your existing single batch code into individual statements (which, if you really can't change anything about the SQL at all, is not an option), but you could go through result sets at will (without caching). It would still not be possible for you to "go back" within a single result set, though.
Improve this answer

Comments

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.