Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

class returning multiple SQL 2000 rows

Posted on 2006-07-07
12
Medium Priority
?
217 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:g_johnson
  • 6
  • 6
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17060933
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
 
LVL 4

Author Comment

by:g_johnson
ID: 17060977
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17061007
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Author Comment

by:g_johnson
ID: 17061075
oh, that's an interesting question

I would think it should be held in the class and retrieved as needed
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17061381
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
 
LVL 4

Author Comment

by:g_johnson
ID: 17062142
Question:  what "calls" Public Sub New?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17062342
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
 
LVL 4

Author Comment

by:g_johnson
ID: 17062813
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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 17063107
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
 
LVL 4

Author Comment

by:g_johnson
ID: 17072531
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
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 17072861
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
 
LVL 4

Author Comment

by:g_johnson
ID: 17073699
[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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question