locked
How to fill a DataSet with Array

    Question

  • Hello,

    I'm wondering if it's possible to fill a DataSet with a two-dimensional array.  If yes I really appreciate if you can show me how.  The 2-D array is created at runtime, so I don't want to create the DataColumn or DataRow at design time.   THanks in advance.

    Michael

    Friday, 13 October, 2006 4:11 AM

Answers

  • yes, you can reference the table you want either by index or the tablename you have given it, if you know that tablename exists in the collection of datatables.

    In regards to your 2-D array, do you mean that this 2D array is basically 2 columns? If so, then you can use the Rows.Add() method and give it an object() array to add.

     

    Now of course you need to make sure that the datatable has the minimum columns, or rather, that match the number of columns in this array. So if you had 2 columns in your array, you need to make sure that the datatable has 2 columns otherwise you would get an exception thrown.

    so you can do this to add a row of data:

    Dim theRows() as string = { "Column1", "Column2" }

    theDataSet.Tables("tableName").Rows.Add(theRows)

     

    Does this help in some way?

    Friday, 13 October, 2006 8:03 AM
    Moderator

All replies

  • A dataset is a type - not specific to ADO.NET but used in ADO.NET

    A dataset consists of one or more datatable which each have one or more rows / columns. Example code to populate a dataset from an array.

    What you choose to do with the dataset after that would be up to you but you could pass this to a dataadapter to update a database - but this code is what is being used to create a dataset.

    Items can be refered by name or position index.

     


            '//Populate an Array
     Dim x(3) As String
            x(0) = "test0"
            x(1) = "test1"
            x(2) = "test2"
            x(3) = "test3"


     '//Create a dataset with a single datatable with a single column
            Dim ds As New DataSet
            ds.Tables.Add("table1")
            ds.Tables(0).Columns.Add("Column1")

     '//Add array items to new rows in datatable(0)
            For i As Integer = 0 To 3
                ds.Tables(0).Rows.Add(x(i))
            Next


     '//Show contents of datarows in datatable (0)
            For Each dr In ds.Tables(0).Rows
                Console.WriteLine(dr(0))
            Next

    Friday, 13 October, 2006 4:25 AM
  • Hi Spotty,

    Thank you for your reply.  You answered my post too fast.. :)  I review my question after I posted it and realized it was too broad.  And you answered while I was editing.  My array is a 2-D array.  How can I add one row of the array (with multiple columns) to DataRow at once?

    On side note: I have question about your comment that 'items can be referred by name or position index'.  In your example you wrote:

    ds.Tables.Add("table1")
    ds.Tables(0).Columns.Add("Column1")

    Does VB.NET automatically assign "table1" an index of 0 because it's the first table added to the DataSet?  i.e. I can use "table1" and tables(0) interchangably?  If it does, it will make adding columns at runtime so much easier.

    Thank you for your help.

    Sugento

    Friday, 13 October, 2006 4:56 AM
  • yes, you can reference the table you want either by index or the tablename you have given it, if you know that tablename exists in the collection of datatables.

    In regards to your 2-D array, do you mean that this 2D array is basically 2 columns? If so, then you can use the Rows.Add() method and give it an object() array to add.

     

    Now of course you need to make sure that the datatable has the minimum columns, or rather, that match the number of columns in this array. So if you had 2 columns in your array, you need to make sure that the datatable has 2 columns otherwise you would get an exception thrown.

    so you can do this to add a row of data:

    Dim theRows() as string = { "Column1", "Column2" }

    theDataSet.Tables("tableName").Rows.Add(theRows)

     

    Does this help in some way?

    Friday, 13 October, 2006 8:03 AM
    Moderator
  • Hi Ahmedilyas,

    Thank you very much for your response.  I tested your recommendation in two ways; one works and one does not work:  I can only make it work with jagged array and not the ordinary rectangle array.  Is jagged array the only way to make it work.  Please let me know your thought.  thank you very much in advance for your help.

    Michael

     

    1.  Source array is ordinary rectangle array (dim MyArray(11,10) as integer)   <---- DOES NOT WORK

    ''Create all the necessary DataColumns so that data in DataRow can be added to all columns at once

    For iColumnCounter = 0 To arrAllDC.GetUpperBound(1)

               dtDC2DataGrid.Columns.Add("MACHINE" & CStr(iColumnCounter), GetType(Single))

    Next

    'Create DataRow so DataSet can be filled with array data

    Dim dr As DataRow = dtDC2DataGrid.NewRow 'Declare the DataRow

    For iRowCounter = 0 To arrAllDC.GetUpperBound(0)

              For iColumnCounter = 0 To arrAllDC.GetUpperBound(1)

                            dr(iColumnCounter) = arrAllDC(iRowCounter, iColumnCounter) 'Assign value in Array to the DataRow for each column

              Next

              dtDC2DataGrid.Rows.Add(dr)         'Register/add the DataRow to the DataTable.    <-----RUNTIME ERROR! THE DATAROW HAS ALREADY BEEN ADDED TO THE DATATABLE

    Next

     

    2.  Source Array is a jagged array (Dim myArray (11)() as integer)        <------THIS WORKS

    Dim dsDC2DataGrid As New DataSet

    Dim dtDC2DataGrid As New DataTable

    Dim iColumnCounter As Integer = 0

    Dim iRowCounter As Integer = 0

    'Create all the necessary DataColumns so that data in DataRow can be added to all columns at once

    For iColumnCounter = 0 To UBound(arrAllDC(0))

               dtDC2DataGrid.Columns.Add("MACHINE" & CStr(iColumnCounter), GetType(Single))

    Next

    For iRowCounter = 0 To UBound(arrAllDC)

               dtDC2DataGrid.Rows.Add(arrAllDC(iRowCounter))      <------Passing a row in jagged array

    Next

    dsDC2DataGrid.Tables.Add(dtDC2DataGrid) 'Add the DataTable to the DataSet

    Monday, 16 October, 2006 10:26 PM