Click here to Skip to main content
15,119,897 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I know that EOF and BOF don't exist for SqlDataReader, but I'm wondering if there's a strategy that could duplicate the idea.

I know how to loop through rows with it.

VB
rdr = cmd.ExecuteReader
If rdr.HasRows Then
    While rdr.Read

    End While
End If


But I'd like to do this instead because it's very very inconvenient to use the above code when you have to do something at the end of each group. I mean if you have a dataset like this:

Customer CarsOwned
Becky BMW
Becky Ford
Becky Honda
Fred Ford
Fred Honda
Fred Kia
George GM
George Mercedes
John Audi
John Honda
John Kia
John Mercedes
John Volvo
John VW
Sally BMW
Sally Chrysler
Sally Ford
Sally GM

When you loop through the above and need to do something at the end of each customer, it's a pain.

It's a lot easier to do this:

VB
'pseudo code: move to the first row
While 1=1    
    'pseudo code: if we're at the end of the dataset, break out of the while here    

    '... other code here

    'pseudo code: move to the next row
    'if on the next customer or at the very end, do some things here
End While


I do this often in T-SQL and in Delphi. It sure would be nice to do with vb.net

Thanks,

Avian

What I have tried:

I've tried making this work and looked for solutions. Haven't found any ideas yet.
Posted
Updated 26-Jun-18 12:38pm
Comments
[no name] 17-Jun-18 9:53am
   
Rather ugly, but I think it will do the Job (I hope format/indents will survive in the comment. Sorry, no indents gone)
// Pseudo code
string currPerson= string.Empty;
bool eof= !rdr.HasRows;
while (!eof)
{
eof= !rdr.Read();
bool newGrp= false;
if (!eof && !rdr.Item("FirstName").ToString().Equals(currPerson))
{
newGrp= currPerson != string.Empty;
currPerson= rdr.Item("FirstName").ToString();
}

if (eof || newGrp)
{
// Do something on newGrp or at the very end
}
}

Think this will do what you want?

Using conn As New SqlConnection("Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=Tester;Persist Security Info=True;Integrated Security=true")
    conn.Open()

    Using cmd As New SqlCommand("SELECT * FROM PeopleCars ORDER BY FirstName, CarType", conn)
        Using rdr = cmd.ExecuteReader()
            If rdr.HasRows Then
                Dim currPerson As String = ""
                While rdr.Read
                    Diagnostics.Debug.WriteLine(rdr.Item("FirstName").ToString())
                    If Not rdr.Item("FirstName").ToString().Equals(currPerson) Then
                        If currPerson <> "" Then
                            ' do something at the end of a group
                        End If
                        currPerson = rdr.Item("FirstName")
                    End If
                End While
                ' do something with the last group
            End If
        End Using
    End Using

End Using


HTH,
Mike
   
OK so right after I said I couldn't think of a way to avoid it, I thought of one. The appropriateness of the solution depends on what you're trying to do, of course. But you can avoid repeating the item for the change from one to another by using a dictionary and processing each one identically. It removes the need to track a 'current' item and handle the transition and therefore the need to manage the tail end when there's no transition. It also, as a side-effect, removes the need for the items to be sorted by name which will make the select run faster. There is the added storage cost in the dictionary but depending on your needs... anyway, here you go.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim peopleCarsDict As New Dictionary(Of String, List(Of String))

    Try
        Using conn As New SqlConnection("Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=Tester;Persist Security Info=True;Integrated Security=true")
            conn.Open()

            Using cmd As New SqlCommand("SELECT * FROM PeopleCars", conn)
                Using rdr = cmd.ExecuteReader()
                    If rdr.HasRows Then
                        While rdr.Read
                            AddToDict(peopleCarsDict, rdr.Item("FirstName").ToString(), rdr.Item("CarType").ToString())
                        End While
                    End If
                End Using
            End Using

        End Using
    Catch ex As Exception
        ' report error to user
    End Try

    ' now anything can be done with the dictionary
    For Each kvp As KeyValuePair(Of String, List(Of String)) In peopleCarsDict
        Diagnostics.Debug.WriteLine(kvp.Key + " has " + kvp.Value.Count + " cars")
    Next

End Sub

Private Sub AddToDict(ByRef dict As Dictionary(Of String, List(Of String)), key As String, val As String)
    Dim valArray As New List(Of String)
    ' if new person then create dict entry
    If Not dict.ContainsKey(key) Then
        dict.Add(key, New List(Of String))
    End If
    ' get the proper array and add the val
    If dict.TryGetValue(key, valArray) Then
        valArray.Add(val)
    Else
        ' something went wrong here, we couldn't get the array!!
        Throw New Exception("Could not get array")
    End If
End Sub


BTW - the dictionary could alternatively be a List(Of Person) where Person is a custom class with a name and a List(Of String) cars.

Mike
   
The simplest option would be to use LINQ.

Start with an extension method, which you only need to write once:
VB.NET
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common

Module DataReaderExtensions
    Private Shared Iterator Function AsEnumerableCore(ByVal reader As IDataReader) As IEnumerable(Of IDataRecord)
        Dim enumerator = New DbEnumerator(reader)
        While enumerator.MoveNext()
            Yield CType(enumerator.Current, IDataRecord)
        End While
    End Function

    <Extension()>
    Public Shared Function AsEnumerable(ByVal reader As IDataReader) As IEnumerable(Of IDataRecord)
        If reader Is Nothing Then Throw New ArgumentNullException(NameOf(reader))
        
        Dim dbReader = TryCast(reader, DbDataReader)
        If dbReader IsNot Nothing Then Return dbReader.Cast(Of IDataRecord)()
        Return AsEnumerableCore(reader)
    End Function
End Module

Then you can group the records returned by your data reader:
VB.NET
Using rdr = cmd.ExecuteReader()
    Dim groupedRecords = rdr.AsEnumerable().GroupBy(Function (r) r["FirstName"].ToString())
    
    For Each recordGroup As IGrouping(Of String, IDataRecord) In groupedRecords
        Dim name As String = recordGroup.Key
        
        ' ... Start of group here ...
        For Each record As IDataRecord In recordGroup
            ' ... Detail here ...
        Next
        ' ... End of group here ...
    Next
End Using
   
v3
Quote:
I know that EOF and BOF don't exist for SqlDataReader, but I'm wondering if there's a strategy that could duplicate the idea.

EOF and BOF are functions and concepts that date from about 40 years, an era where one was working directly on database file.
Everything was working with the record number in database file, the problem was that with indexes and filters, we were jumping from record to record and the was a need to know when Beginning Of File or End Of File was reached. Thus the 2 functions.
With SQL, the result of query is already filtered and ordered, thus the first row of result is BOF and last row is EOF. So you don't need the functions.
   
Hi Mike. Yes, that's what I do now. But I'm trying to avoid repeating the "end of a group" code. It's not horrible. I usually write a small routine that I call in both spots, but the other way is clearer and easier to code, IMO. - Avian :-)
   
Comments
Mike V Baker 16-Jun-18 8:57am
   
So you're only trying to avoid repeating the "do something at the end of a group" code? I can't think of any way to avoid that. Of course if it's more than one or two lines I would write it up in a function and call that function so there's not a lot of duplication. Other than that something like that wouldn't keep me up at night.
What was it that you're trying to do with this code? If you're counting the cars per person then perhaps in the sql would be better..

SELECT FirstName, COUNT(CarType) FROM PeopleCars GROUP BY FirstName

Naturally in a real world app this info wouldn't be in a single table. The 'PeopleCars' here in this sample would more likely be a three table join.

Mike
Well the cars thing was just an example. My actual problem is far more complex and not worth gumming things up in here. My issue came across clearly. The dictionary idea is a good one but I'm just going with my original solution which is to call a subroutine twice. Once at the top of the loop and again after it's done. It works fine. Not a big deal. I just don't like this method. The way I deal with this sort of thing in T-SQL is cleaner. Too bad MS didn't see fit to add a EOF to SqlDataAdapter. Kinda silly IMO. Thanks for your help confirming that my original idea is the way to go here.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900