class returning multiple SQL 2000 rows

I have written a class that uses a sqlDataReader to read a row or rows from a table.  The calling program passes a parameter indicating how many rows to return.

But, I don't know how to actually return more than one row to the calling program.

For example, I have the property 'loc' which will return location

so the calling program wants the value
        myclass.loc

of course that only returns one value for the topmost record


How can I get the class to return something for each row returned in the data reader?  Would there be a way to return, for example,

       myclass.loc(0) = value from first record
       myclass.loc(1) = value from second record
      myclass.loc(2) = value from third record

etc.?

Thanks
LVL 4
g_johnsonAsked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
We can expand on the previous sample by creating a class to hold the details of the record and then store objects of our new class in the ArrayList and return them instead:

Public Class MyRecord
    Public FirstName As String
    Public LastName As String
    Public Age As Integer
End Class

Public Class GetData

    Private _curIndex As Integer
    Private _data As ArrayList

    Public Sub New()

        Dim cn As New SqlConnection("Connection string")
        Dim cmd As SqlCommand = cn.CreateCommand()
        Dim rec As MyRecord

        cmd.CommandText = "SELECT FirstName FROM Customers"

        cn.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        _data = New ArrayList()

        While rdr.Read()
            rec = New MyRecord
            rec.FirstName = rdr("FirstName")
            rec.LastName = rdr("LastName")
            rec.Age = Integer.Parse(rdr("Age"))
            _data.Add(rec)
        End While

        rdr.Close()
        cn.Close()

        _curIndex = 0

    End Sub

    Public Function GetRecord() As MyRecord
        If _curIndex <> _data.Count Then
            Return CType(_data(_curIndex), MyRecord)
            _curIndex = _curIndex + 1
        Else
            Return Nothing
        End If
    End Function

End Class

Module Module1

    Sub Main()

        Dim obj As New GetData()
        Dim record As MyRecord = obj.GetRecord()

        While Not record Is Nothing
            Console.WriteLine(record.FirstName & ", " & record.LastName)
            record = obj.GetRecord()
        End While

    End Sub

End Module

0
 
Carl TawnSystems and Integration DeveloperCommented:
You have a few options:

1) Load the results from your DataReader into a collection (Array, ArrayList, etc) and return that.
2) Load your data into a DataSet/DataTable instead and return that.
3) Hold the data in a DataTabe and provide an indexer on your class to allow access to each row.
4) Return your data in some sort of delimited list.
0
 
g_johnsonAuthor Commented:
Hi carl,

I like #1, but don't know how to do it.
If I declare a variable, e.g.,

dim marrloc() as string

how would I set up the readonly property?  I got syntax errors everytime I tried

#2, too new to this, don't know anything about how to do that

#3, same as #2, wouldn't know how to do that either

#4, i could figure out how to do it in a delmited list, but that's probably not very elegant.

Thanks!

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Carl TawnSystems and Integration DeveloperCommented:
Do you actually want to return the data to the calling object, or hold the data in your class and allow the calling object to retrieve it a bit at a time ?
0
 
g_johnsonAuthor Commented:
oh, that's an interesting question

I would think it should be held in the class and retrieved as needed
0
 
Carl TawnSystems and Integration DeveloperCommented:
There are a multitude of ways to do this which would take a long time to explain. So heres a sample of one possible method.

First, a simple class that retrieves a list of FirstNames from the database and stores them internally an ArrayList. The GetFirstName method will continue to return the FirstNames one by one each time it is called until it reaches the end:

Public Class GetData

    Private _curIndex As Integer
    Private _data As ArrayList

    Public Sub New()

        Dim cn As New SqlConnection("Connection string")
        Dim cmd As SqlCommand = cn.CreateCommand()

        cmd.CommandText = "SELECT FirstName FROM Customers"

        cn.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        _data = New ArrayList()

        While rdr.Read()
            _data.Add(rdr("FirstName").ToString())
        End While

        rdr.Close()
        cn.Close()

        _curIndex = 0

    End Sub

    Public Function GetFirstName() As String
        If _curIndex <> _data.Count Then
            Return _data(_curIndex)
            _curIndex = _curIndex + 1
        Else
            Return ""
        End If
    End Function

End Class

Next, a small sample console app showing how you might use it:

Module Module1

    Sub Main()

        Dim obj As New GetData()

        Dim fName As String = obj.GetFirstName()

        While fName <> ""
            Console.WriteLine(fName)
            fName = obj.GetFirstName()
        WEnd

    End Sub

End Module
0
 
g_johnsonAuthor Commented:
Question:  what "calls" Public Sub New?
0
 
Carl TawnSystems and Integration DeveloperCommented:
Nothing, its the constructor for the class it is run automatically when an instance of the class is created.

I only used that as an example of how you could do it. If you want to explicitly call a method to retrieve the data then you can do that instead.
0
 
g_johnsonAuthor Commented:
thanks for all your help

right now all of the data is generated in a public function.  i started down the path of creating an array list for each of the variables (there are 6 strings and 2 integers) within that function.  but then i couldn't figure out how the calling program would retrieve them.
0
 
g_johnsonAuthor Commented:
Thanks for all you help.  I'm not quite getting this, but I think I have made some progress.  I'm tyring to implement your ideas without having to re-write everything I've already done.  My class is also used to update and insert records (I don't know if this is pertinent or not, but thought I'd throw it in here).  But, that's why I don't think I can use the "Public Sub New" construct for reading records.

BTW, you have been very helpful and I will award points now if you're tired of working on this!  I know you're answer is correct and complete, I just don't fully understand it yet.

Anyhow, I created a class to store the results of the "read" and that runs without error so I assume it's working.  So in my function that reads the records, instead of:

                        mbatch_id = CType(rdr1("bid"), String)

I now have

                        rec.rBatch_id = CType(rdr1("bid"), String)

with "rec" being a reference to my new class

So I don't know what to do in my calling program.  It still calls the function that actually reads the records and runs the above code.

Do I need to completely re-write this?

0
 
Carl TawnSystems and Integration DeveloperCommented:
No you don't need to re-write everything. I only used the constructor as a sample of how you might do it, but the exact implementation will vary based on the requirements. If calling a method to retrieve the records works better for you then stick with it.

OO is a big topic an there are numerous possible ways of doing things. Basically the sample I posted creates a class that basically represents a record in the database and stores a collection of them in an ArrayList. The GetRecord() method returns each instance of that class in turn to the calling program, but that could be changed to give access based on an index. That way you can provide read/write access to the records and use the ArrayList for updating/deleting records as well.

Unfortunately trying to explain all this stuff in a post and without graphics is a bit of a nightmare as it could easily take several pages to explain properly. Hopefully you have got the jist and it sounds like you have managed to incorporate at least some of it into your app.
0
 
g_johnsonAuthor Commented:
[Unfortunately trying to explain all this stuff in a post and without graphics is a bit of a nightmare as it could easily take several pages to explain properly. Hopefully you have got the jist and it sounds like you have managed to incorporate at least some of it into your app.]

Ain't that the truth!  Thanks for your help.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.