How to fill datatable from datareader in C# without dataadapter?

Most of the developer use DataAdapter to fill the DataSet and then take the first table from the data set, which is quite slow, why can't we use DataReader to read the data and use datatable's Load method to load the reader into it, in this article we will see how we can load a table faster and easier way, even without using the DataSet and DataAdapter.

First of all set our connection string in web.config file to connect to the database.

<connectionStrings>
  <add name="dbConString" 
    connectionString="data source=localhost;initial catalog=db_name;UID=sa;PWD=xxx"  
    providerName="System.Data.SqlClient" />
</connectionStrings>

We are going to create a method GetDataTable which will use datareader to load the data into data table.

public DataTable GetDataTable()
{
   DataTable dataTable = new DataTable();
   using (SqlConnection conn = 
      new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
   {
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = "procedure_name";
      cmd.CommandType = CommandType.StoredProcedure;

      // SqlDataReader need an open conncetion, so check and open it.
      if (conn.State != ConnectionState.Open)
           conn.Open();

      // Read data by using Execute Reader
      SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

      //Use data table load method to load data from data reader
      dataTable.Load(dr);                
   }
   return dataTable;
}

Conclusion:

• No need to use DataAdapter
• No need of DataSet
• No need to read record one by one
• simply use load and data is ready
• we used "CommandBehavior.CloseConnection", which close the connection after reading
• We also used "using" so once control pass end bracket (}) it will automatically disposed

Is not it easy, fast and handy to load data from a data reader into data table, enjoy it.

Other blogs you may like

How to use linq query on a datatable in C#

Is it possible to perform a linq query on datatable, answer is no because datatable’s row collection, DataRowCollection does not implement IEnumerable<T> so we cannot perform linq query on it. But if there is any way to convert our datatable data row collection into IEnumerable, then surely we can.... By Charles Fuller   On 25 Jun 2013  Viewed: 3,973

Convert datatable to json in C#

To convert a DataTable into JSON in C# is really very easy. Is there any class or method in .Net which can help us to directly do it, then answer is No. We need to write a small utility class or method which can help to convert any datatable into json easily.What we are going to do is, create a... By Ali Adravi   On 09 Jun 2013  Viewed: 31,277

default value for different data type in C#

In C# there are different data type and they use some default value when we declare a variable. When we define a variable of type int or Int32 say int score; so what is the value of score, will it be null or zero, in the same way if we create a variable of type string/String what value it holds... By Ali Adravi   On 27 Mar 2013  Viewed: 2,623

enum in C# and conversion

An enumeration type (also known an enumeration or an enum) provides an efficient way to define a set of named integral constants that may be assigned to a variable to make programming clear, understandable and manageable. There are ways of using, like how to use enum in switch and case statement,... By Hamden   On 27 Mar 2013  Viewed: 4,586

Dynamic vs var in C# with example

When we have var which dynamically holds any type or value so why we need dynamic type, what are the differences and situations where we can use dynamic rather than var, there were some question in my mind so I explored more which I want to share here. There are two things, static type and... By Nathan Armour   On 26 Mar 2013  Viewed: 3,223