You might want to convert a .NET DataTable to a multidimensional array (object[,]) for many reasons, the most common one is, to use it within Microsoft Office Suite add-ins, for example: showing the data of a DataTable in an Excel Range or a ListObject.
Many articles on the internet suggest that the best way to do this is looping trough all DataRows and columns in the DataTable and manually adding them to a multidimensional array, by pointing to the column index inside a DataRow.
That is wrong!
And one should always avoid doing that, because of the negative effect it has on the performance.
The correct thing to do would be to loop through all DataRows and columns inside the DataTable and use the built-in .NET method CopyTo() on the ItemArray of a DataRow, in order to copy column and row cells to a separate array and then in the end, merge the column and row arrays to a multidimensional array (object[,]).
When testing the conversion of a DataTable with 2088 rows and 96 columns, it only took 21 milliseconds using the CopyTo method, compared to 1200++ milliseconds if I was not using it!
The performance increase may be slightly noticeable when converting small DataTables,but imagine the effect of using the method below when converting DataTables that contain huge amounts of data...
Enjoy! :)
private static object[,] GetRawData(DataTable dt)
{
//Initialize a multidimensional array that will contain our columns and rows.
var rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
//Initialize an array that we will copy DataTable columns to.
var columnsArray = new object[dt.Columns.Count];
//Initialize an array that will copy DataTable rows to.
var rowsArray = new object[dt.Rows.Count * columnsArray.Length];
//Copy columns from the DataTable to the columns array.
for (var columnIndex = 0; columnIndex < columnsArray.Length; columnIndex++)
columnsArray[columnIndex] = dt.Columns[columnIndex].ColumnName;
//Copy rows from the DataTable to the rows array.
for (var rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
dt.Rows[rowIndex].ItemArray.CopyTo(rowsArray, rowIndex * columnsArray.Length);
//Merge DataTable columns and rows into a 2D array.
for (int colIndex = 0; colIndex < columnsArray.Length; colIndex++)
{
//Merge columns.
rawData[0, colIndex] = columnsArray[colIndex];
//Merge rows.
for (var i = 0; i < rowsArray.Length / columnsArray.Length; i++)
rawData[i + 1, colIndex] = rowsArray[i * columnsArray.Length + colIndex];
}
//Done.
return rawData;
}
