ArrayList and sql Database - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

ArrayList and sql Database

yooyoo Posts: 7Member

Hello!

Q: how to add the data from database to the arraylist?
(like arraylist.add(New data(data1, data2),and i m using sql server 2000. and the data1 is represent a column of data (like sales column has a record= 1,000,......) in a certain row in the datatable.)

thanks!

Comments

  • iwilld0itiwilld0it Posts: 1,134Member
    [b][red]This message was edited by iwilld0it at 2005-5-19 7:10:39[/red][/b][hr]
    :
    : Hello!
    :
    : Q: how to add the data from database to the arraylist?
    : (like arraylist.add(New data(data1, data2),and i m using sql server 2000. and the data1 is represent a column of data (like sales column has a record= 1,000,......) in a certain row in the datatable.)
    :
    : thanks!
    :

    Well you could you a DataReader and a custom class to accomplish this ...

    Say you have a database table called Person with a id, FirstName, and LastName field. First you would create a class to represent a database record like so ....

    [code]
    Public Class Person
    Private mID As Integer
    Private mFirstName As String
    Private mLastName As String

    Sub New(ByRef reader As IDataReader)
    Try
    mID = CInt(reader("PersonID"))
    mFirstName = CStr(reader("FirstName"))
    mLastName = CStr(reader("LastName"))
    Catch
    Throw New ArgumentException("Could not load data from the record")
    End Try
    End Sub

    Public Property ID() As Integer
    Get
    Return mID
    End Get
    Set(ByVal Value As Integer)
    mID = Value
    End Set
    End Property

    Public Property FirstName() As String
    Get
    Return mFirstName
    End Get
    Set(ByVal Value As String)
    FirstName = Value
    End Set
    End Property

    Public Property LastName() As String
    Get
    Return mLastName
    End Get
    Set(ByVal Value As String)
    mLastName = Value
    End Set
    End Property
    End Class
    [/code]

    The constructor accepts a generic Data Reader, so this class can be used for SQL Server 2000 and any other database provider.

    Next, here is a function that uses the class above and returns an Arraylist ...

    [code]
    Public Function GetPersonData() As ArrayList
    ' Create ADO objects
    Dim connect As New SqlConnection("your connection string here ....")
    Dim command As New SqlCommand("select * from Person", connect)
    Dim reader As SqlDataReader

    ' Create new Array list
    Dim data As New ArrayList

    Try
    Call connect.Open()
    reader = command.ExecuteReader

    ' Loop through data reader, contruct Person object, and add it to ...
    ' array list.
    Do While reader.Read
    Call data.Add(New Person(reader))
    Loop

    Return data
    Catch
    ' Place your exception logic here
    Finally

    ' Clean-up
    If Not Reader Is Nothing _
    AndAlso reader.IsClosed = False Then
    Call reader.Close()
    End If
    If connect.State = ConnectionState.Open Then
    Call connect.Close()
    End If
    End Try

    ' Return array list regardless.
    Return data
    End Function
    [/code]

    Here is an example of using the function ...

    [code]
    Dim data As ArrayList = GetPersonData()

    For Each p As Person In data
    System.Diagnostics.Debug.WriteLine(p.LastName & ", " & p.FirstName)
    Next
    [/code]

    Basically, this is looping through the arraylist of Person objects and printing the LastName / FirstName.

    Hope this helps.


Sign In or Register to comment.