A journey to other dimensions and DataTables

Thursday, 15 December 2011 12:10 animaonline
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;
        }
Comments (0)Add Comment

Write comment
smaller | bigger

busy

Latest tweets

about 8 hours ago #WhatGrindsMyGears the lock-screen on my #sgII is a f#inkg pain in the arse... so irresponsible... Argh!!
about 8 hours ago $ 370 for a wifi #PsVita model here in #Europe , I'm ordering one from US....
about 1 day ago Anyone know where "Gigabyte" , author of the "sharpei" #csharp might be now? #dev
about 1 day ago My Top 3 #lastfm Artists: Eminem (70), Dr. Dre (26) & Papa Roach (23) http://t.co/YPJlaiyy
about 2 days ago On my way to #work
about 3 days ago #DevTip When using NDK-Build don't forget to pass the 'clean' parameter sometimes ;) #dev #androidDev
about 7 days ago Heading to work... :)
about 9 days ago @YngveNilsen nice! Lag noe tilsvarende til Android også ;) btw.. Lagde du nettsiden selv? Den var fin!
about 9 days ago My Top 3 #lastfm Artists: Sum 41 (53), The Birthday Massacre (20) & Celldweller (11) http://t.co/YPJlaiyy
about 9 days ago ♥ Points Of Authority/99 Problems/One Step Closer - Amended Version by Jay-Z and Linkin Park #lastfm : http://t.co/fn7BG4zE
about 9 days ago I love my Visual Studio and #IntelliTrace @VisualStudio #devhttp://t.co/rEwxEs8b
about 13 days ago I earned the Go To Hell badge for Visual Studio Achievements: #VSAchievements #VisualStudio #dev http://t.co/lZNfYgkX
about 14 days ago @FinalMacstorm ok, did you like #ffxiii ?
about 14 days ago #ffxiii sucked so badly, so I'm not even thinking about picking up the sequel. Bet it sucks even more, #squareenix is on acid. #ffxiii2
about 14 days ago @FinalMacstorm so how do u like #ffxiii2 ?
about 15 days ago #pigeon #pigeons #wtf <3 http://t.co/kohc9rQg
about 16 days ago ♥ Fat Lip by Sum 41 #lastfm : http://t.co/US37HvEC amazon: http://t.co/oDLlmRDT
about 16 days ago Let's call it an evening

Tag Cloud