dcsimg
Results 1 to 9 of 9

Thread: SQL database to multidimensional array

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    8

    SQL database to multidimensional array

    First time I've had occasion to do this, but how would you represent the data in a SQL database table in a multidimensional array? I've heard tell of multidimensional arraylists but have no idea how to use them.

    Here's what I have -

    The table is 15 columns across and a varied amount of rows depending on the query.

    rawData is an arraylist. finalData is an array.

    Code:
    Dim myReader As SqlDataReader = sqlcom.ExecuteReader
    
         While myReader.Read()
                For i = 0 To 14
                    rawData.Add(myReader(i))
                Next
            End While
    
            finalData = rawData.ToArray
    Obviously, this gives me a long list of all the data from the query. I want to break it in rows.

    Any help is appreciated!

  2. #2
    Hyperactive Member
    Join Date
    Jan 2007
    Posts
    351

    Re: SQL database to multidimensional array

    The usual procedure would be to create a structure, then make a list of that: see this thread: http://social.msdn.microsoft.com/For...-e285bfa2bf87/

    BUT, why do you need this as an array of somesort? How you have the data at the moment (datatable) is in a way a multidimensional array.
    Rico

    Using: VB.net & MS SQL

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    8

    Re: SQL database to multidimensional array

    Enrico,

    Actually, you make an excellent point. What I'm going to be doing is adding columns, but I suppose I could just step through the array and add it that way. Thanks for the idea. The obvious is never obvious.... ;-)

    Mark

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,798

    Re: SQL database to multidimensional array

    I would jsut use a datatable... but that's just me.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Hyperactive Member
    Join Date
    Jan 2007
    Posts
    351

    Re: SQL database to multidimensional array

    Enelanan, the datatable is easy to work with, and can be used to provide data to a control easily with the control.datasource. Stick with it!
    Rico

    Using: VB.net & MS SQL

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: SQL database to multidimensional array

    I would also suggest as others have using a DataTable. If you have some reason not too then here is a simple solution which stores data in a List(Of T).

    This example works from an OleDb provider which by changing the provider would do the same thing.

    Form level variable
    Code:
    Private MyList As New List(Of SomeClass)
    Get rows
    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cb As New OleDb.OleDbConnectionStringBuilder With
        {
            .DataSource = IO.Path.Combine(Application.StartupPath, "SomeDatabase.accdb"),
            .Provider = "Microsoft.ACE.OLEDB.12.0"
        }
    
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = cb.ToString
            }
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText = "SELECT * FROM SomeTable"
                }
    
                cn.Open()
                Dim Reader As OleDb.OleDbDataReader = cmd.ExecuteReader
                Dim RowData As Object() = New Object(Reader.FieldCount - 1) {}
                Dim Read As Boolean = False
    
                If Reader.Read Then
                    Do
                        Reader.GetValues(RowData)
                        MyList.Add(New SomeClass(RowData))
                        Read = Reader.Read
                    Loop While Read
                End If
            End Using
        End Using
        For Each item In MyList
            Console.WriteLine("[{0}] [{1}] [{2}]", item.Identifier, item.FirstName, item.Comment)
        Next
    End Sub
    Class for MyList where we know how many columns and data type per column there are per row.
    Code:
    Public Class SomeClass
        Public Property Identifier As Integer
        Public Property FirstName As String
        Public Property Comment As String
        Public Sub New(ByVal sender As Object())
            Identifier = CInt(sender(0))
            FirstName = sender(1).ToString
            Comment = sender(2).ToString
        End Sub
    End Class
    If you use a DataTable but only to get data.
    Code:
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cb As New OleDb.OleDbConnectionStringBuilder With
        {
            .DataSource = IO.Path.Combine(Application.StartupPath, "SomeDatabase.accdb"),
            .Provider = "Microsoft.ACE.OLEDB.12.0"
        }
    
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = cb.ToString
            }
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText = "SELECT * FROM SomeTable"
                }
    
                Dim dt As New DataTable
                cn.Open()
                dt.Load(cmd.ExecuteReader)
                For Each row As DataRow In dt.Rows
                    MyList.Add(New SomeClass(row))
                Next
            End Using
        End Using
    
        For Each item In MyList
            Console.WriteLine("[{0}] [{1}] [{2}]", item.Identifier, item.FirstName, item.Comment)
        Next
    End Sub
    Modified class
    Code:
    Public Class SomeClass
        Public Property Identifier As Integer
        Public Property FirstName As String
        Public Property Comment As String
        Public Sub New(ByVal sender As Object())
            Identifier = CInt(sender(0))
            FirstName = sender(1).ToString
            Comment = sender(2).ToString
        End Sub
        Public Sub New(ByVal sender As DataRow)
            Identifier = sender.Field(Of Integer)(0)
            FirstName = sender.Field(Of String)(1)
            Comment = sender.Field(Of String)(2)
        End Sub
    End Class
    This is what I would use, the DataTable, no class or List
    Code:
    Private myDataTable As New DataTable
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim cb As New OleDb.OleDbConnectionStringBuilder With
        {
            .DataSource = IO.Path.Combine(Application.StartupPath, "SomeDatabase.accdb"),
            .Provider = "Microsoft.ACE.OLEDB.12.0"
        }
    
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = cb.ToString
            }
            Using cmd As New OleDb.OleDbCommand With
                {
                    .Connection = cn,
                    .CommandText = "SELECT * FROM SomeTable"
                }
    
                cn.Open()
                myDataTable.Load(cmd.ExecuteReader)
            End Using
        End Using
    
        If myDataTable.Rows.Count > 0 Then
            For Each row As DataRow In myDataTable.Rows
                Console.WriteLine("{0}", String.Join(",", row.ItemArray))
            Next
        End If
    End Sub

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    8

    Re: SQL database to multidimensional array

    Thanks guys! A lot of that is WAY over my head, but I'm going to have some fun picking through it. I'm sure it'll come in hand one day. For now, I'm just a lowly librarian writing a stats app. This is what I ended up with and it works fine for my purposes which was to just to find the sums of 15 different columns with an unknown number of rows -

    Code:
       While myReader.Read()
    
                For i = 0 To 14
                    rawData.Add(myReader(i))
                Next
    
            End While
    
            For i = 0 To 14
                For j = 0 + i To rawData.Count - 1 Step 15
                    processedData(i) += rawData.Item(j)
                Next
            Next
    Thanks again for the information! I'm sure I'll learn something from it!

  8. #8
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,523

    Re: SQL database to multidimensional array

    Quote Originally Posted by enenalan View Post
    Thanks guys! A lot of that is WAY over my head, but I'm going to have some fun picking through it. I'm sure it'll come in hand one day. For now, I'm just a lowly librarian writing a stats app. This is what I ended up with and it works fine for my purposes which was to just to find the sums of 15 different columns with an unknown number of rows -

    Code:
       While myReader.Read()
    
                For i = 0 To 14
                    rawData.Add(myReader(i))
                Next
    
            End While
    
            For i = 0 To 14
                For j = 0 + i To rawData.Count - 1 Step 15
                    processedData(i) += rawData.Item(j)
                Next
            Next
    Thanks again for the information! I'm sure I'll learn something from it!
    Finding the sum of unknown rows, 15 columns... if this was known in the beginning this could have been done with a lot less code. Always indicate exactly what you want and why next time.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    34,915

    Re: SQL database to multidimensional array

    Situations where a multi-dimensional array is the best solution are very rare. This isn't one of them. It will cause you more frustration than learning to use one of the other suggested solutions. Like TG, I'd stick with a datatable.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width