Click here to Skip to main content
14,362,722 members

Convert Data Reader To Datatable

Rate this:
4.86 (4 votes)
Please Sign up or sign in to vote.
4.86 (4 votes)
18 Nov 2015CPOL
How to convert data reader to Datatable

In this article, we will learn how we can convert Microsoft ADOMD data reader to datatable. As you all know, a data reader is the most efficient way for looping through the data. Performance wise, a data reader is faster than any of the other ways like data adapter and cell set in MDX result. So in few situations, you may need to convert this data reader to a data table. Here in this post, we will discuss how we can convert it. I hope you will like it.

Background

For the past few months, I have been working with Microsoft ADOMD data sources. And I have written some articles as well that will describe the problems I have encountered so far. If you are new to ADOMD, I strongly recommend that you read my previous articles that you may find useful when you work with ADOMD data sources. You can find those article links here: ADOMD Data Sources

Why

You might think, why I am not using other two ways (data adapter and cell set). I will answer it here. I am handling large set of data, so when I use data adapter and cell set, it was a bit slow to get the output. So I was just checking the performance by using data reader. It was fast enough when I use data reader. So we can order these three as following by performance.

Data reader >> Data Adapter >> Cell Set

Using the Code

The following is the function that does what was explained above.

#region Convert Datareader toDatatable
       /// <summary>
       /// Convert Datareader toDatatable
       /// </summary>
       /// <param name="query"></param>
       /// <param name="myConnection"></param>
       public DataTable ConvertDataReaderToDataTable(string query, string myConnection)
       {
           AdomdConnection conn = new AdomdConnection(myConnection);
           try
           {
               try
               {
                   conn.Open();
               }
               catch (Exception)
               {
               }
               using (AdomdCommand cmd = new AdomdCommand(query, conn))
               {
                   AdomdDataReader rdr;
                   cmd.CommandTimeout = connectionTimeout;
                   using (AdomdDataAdapter ad = new AdomdDataAdapter(cmd))
                   {
                       DataTable dtData = new DataTable("Data");
                       DataTable dtSchema = new DataTable("Schema");
                       rdr = cmd.ExecuteReader();
                       if (rdr != null)
                       {
                           dtSchema = rdr.GetSchemaTable();
                           foreach (DataRow schemarow in dtSchema.Rows)
                           {
                               dtData.Columns.Add(schemarow.ItemArray[0].ToString(),
                               System.Type.GetType(schemarow.ItemArray[5].ToString()));
                           }
                           while (rdr.Read())
                           {
                               object[] ColArray = new object[rdr.FieldCount];
                               for (int i = 0; i < rdr.FieldCount; i++)
                               {
                                   if (rdr[i] != null) ColArray[i] = rdr[i];
                               }
                               dtData.LoadDataRow(ColArray, true);
                           }
                           rdr.Close();
                       }
                       return dtData;
                   }
               }
           }

           catch (Exception)
           {
               throw;
           }
           finally
           {
               conn.Close(false);
           }
       }
       #endregion;

Here, we are creating two data tables, dtData and dtSchema where dtData is for binding the data and dtSchema is for binding the schema.

Creating Data Tables

DataTable dtData = new DataTable("Data");
                       DataTable dtSchema = new DataTable("Schema");

Execute Data Reader

Now we will execute the reader as follows:

rdr = cmd.ExecuteReader();

Get the Schema

To generate the schema, we can call the function GetSchemaTable() which is a part of your data reader.

To use this function, you must include Microsoft.AnalysisServices.AdomdClient.dll.

Adding the Columns Headers to the dtData

The next thing is to add the header names to the data table dtData.

foreach (DataRow schemarow in dtSchema.Rows)
                            {
                                dtData.Columns.Add(schemarow.ItemArray[0].ToString(), 
                                System.Type.GetType(schemarow.ItemArray[5].ToString()));
                            }

Load the Data to dtData

To load the data, we are using a function LoadDataRow() which expects an object as parameter. This will find and update a specific row, if the matching row is not found, it will create another row with the given values.

Convert data reader to data table

Convert data reader to data table
object[] ColArray = new object[rdr.FieldCount];
                                for (int i = 0; i < rdr.FieldCount; i++)
                                {
                                    if (rdr[i] != null) ColArray[i] = rdr[i];
                                }
                                dtData.LoadDataRow(ColArray, true);

Conclusion

Have you ever gone through this kind of requirement? Did I miss anything that you may think is needed?. I hope you liked this article. Please share your valuable suggestions and feedback.

Your Turn. What Do You Think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C-Sharp Corner, Stack Overflow, ASP.NET Forums or Code Project instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I am able to.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sibeesh Passion
Software Developer
Germany Germany
I am Sibeesh Venu, an engineer by profession and writer by passion. I’m neither an expert nor a guru. I have been awarded Microsoft MVP 3 times, C# Corner MVP 5 times, DZone MVB. I always love to learn new technologies, and I strongly believe that the one who stops learning is old.

My Blog: Sibeesh Passion
My Website: Sibeesh Venu

Comments and Discussions

 
QuestionMany items to improve Pin
Paulo Zemek18-Nov-15 13:59
professionalPaulo Zemek18-Nov-15 13:59 
AnswerRe: Many items to improve Pin
Sibeesh Passion18-Nov-15 18:41
professionalSibeesh Passion18-Nov-15 18:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Technical Blog
Posted 18 Nov 2015

Tagged as

Stats

17.5K views
1 bookmarked