Solved

class returning multiple SQL 2000 rows

Posted on 2006-07-07
12
206 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

22 Experts available now in Live!

Get 1:1 Help Now