How to convert DataTable to 2D Object Array?

Article
2013-05-16

Question

_{Thursday, May 16, 2013 3:28 PM}

Hello,

I am going crazy trying to search for the answer or figure this out. All i'm trying to do is take this:

convert DataTable dTable to an object [,] testArray

Array materialArray = dTable.AsEnumerable().Select(x => x.ItemArray).ToArray();
Array myArray = testDataSet.Tables[0].Columns.OfType<DataColumn>().Select(c => c.ColumnName.ToString()).ToArray();
testArray = new object[materialArray, myArray]; // does not work. Cannot implicitly convert type 'System.Array' to 'int'

When I do a step through and look at the value of materialArray, the info from the table is there but it's 1 dimensional. I can easily do this using excel range, but obviously the procedure is different with datatables.

Can someone PLEASE help me out with this? This is the last piece of the puzzle.

Thank you in advance!

All replies (10)

_{Thursday, May 16, 2013 8:35 PM ✅Answered}

foreach (DataRow row in table.Rows)
{
...

++currentRow;
};

Alternatively do this:

for (int currentRow = 0; currentRow < table.Rows.Count; ++currentRow)
{
var row = table.Rows[currentRow];
...
};

Michael Taylor
http://msmvps.com/blogs/p3net

_{Thursday, May 16, 2013 4:44 PM}

ItemArray is the array of values (one for each column). If you want to convert a table to a table (which is all a 2D array is) then you'll have to generate an array with N columns where N is testDataSet.Tables[0].Columns.Count. The table with have M rows where M = testDataSet.Rows.Count. For each row you'll enumerate the ItemArray value and store each value into the next column in the row.

//Not tested
var table = testDataSet.Tables[0];
var numCols = table.Columns.Count;
var numRows = table.Rows.Count;

int currentRow = 0;
var array = new object[numRows][numCols];
foreach (var row in table.Rows)
{
for (int index = 0; index < numCols; ++index)
array[currentRow][index] = row[index];
};

I question the benefit of doing this though as you'll lose the column and type information. Data tables can already be serialized and whatnot so generally having a DT is preferable to a 2D array.

Michael Taylor
http://msmvps.com/blogs/p3net

_{Thursday, May 16, 2013 5:36 PM}

        public static object[,] ToArray(DataTable data) {
            var ret= Array.CreateInstance(typeof(object),data.Rows.Count, data.Columns.Count) as object[,];
            for (var i = 0; i < data.Rows.Count - 1; i++)
                for (var j = 0; j < data.Columns.Count - 1; j++)
                    ret[i, j] = data.Rows[i][j];
            return ret;
        }

Muthukrishnan Ramasamy
net4.rmkrishnan.net
Use only what you need, Reduce global warming

_{Thursday, May 16, 2013 5:59 PM}

Hi CoolD,

Thank you for your reply! Unfortunately your code does not run. I noticed you haven't tested it, but it has a couple of errors

var table = testDataSet.Tables[0];var numCols = table.Columns.Count;var numRows = table.Rows.Count;int currentRow = 0;var array = new object[numRows][numCols]; //Invalid rank specifier: expected ',' or ']'foreach (var row in table.Rows){   for (int index = 0; index < numCols; ++index)      array[currentRow][index] = row[index]; //Cannot apply indexing with [] to an expression of type 'object'};

So I modified it to this, and I get the 2D array with the right amount of rows & columns, but the values are not stored. The ItemArray of the 1st row shows the values, but on the other rows everything is null.

var table = testDataSet.Tables[0];var numCols = table.Columns.Count;var numRows = table.Rows.Count;int currentRow = 0;array = new object[numRows, numCols];foreach (var row in table.Rows){    for (int index = 0; index < numCols; ++index)               array[currentRow, index] = row;};

Any idea why the values are not being stored?

To help understand why Im doing this, to make a long story short, I export a query result to Excel and I have a program that when it loads, it stores the excel file into a 2D array. Then I run loops on that array to get the info I need. So instead of exporting to excel, I want my program to store the query results in the 2D array. That way I don't have to redo a bunch of code.

_{Thursday, May 16, 2013 6:49 PM}

row is an array of values. You need to dereference the field within the array

array[...] = row[index];

Rows is pre-generics collection so change the foreach to use DataRow (I personally use an extension method so this isn't a problem for me):

foreach (DataRow row in table.Rows)

Michael Taylor
http://msmvps.com/blogs/p3net

_{Thursday, May 16, 2013 7:19 PM}

Thanks Cool, but what about the other line that throws the error:

var array = new object[numRows][numCols]; //Invalid rank specifier: expected ',' or ']'

_{Thursday, May 16, 2013 7:41 PM}

C# uses commas between dimensions so it is:

var array = new object[numRows, numCols];

Michael Taylor
http://msmvps.com/blogs/p3net

_{Thursday, May 16, 2013 8:25 PM}

Hi CoolD,

You're very close. Unfortunately, I still get only the 1st row stored and the other rows are null. Seems like the loop does not store the data from the next row. Any idea why?

_{Thursday, May 16, 2013 9:11 PM}

You are the Coolest Dad! Thank you so much for your assistance on this annoying task that has deprived me of sleep lately. I need a lot more practice with dealing with arrays and loops seeing how powerful they can be.

Thanks again, CoolD!

_{Friday, May 17, 2013 5:08 PM}

Hi, Did you try my solution?

Muthukrishnan Ramasamy
net4.rmkrishnan.net
Use only what you need, Reduce global warming

Share via

How to convert DataTable to 2D Object Array?

Question

All replies (10)

Additional resources