Solved

class returning multiple SQL 2000 rows

Posted on 2006-07-07
12
207 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now