Filling a DataTable or DataSet the Quick Way

In .NET 1.x filling a DataSet or a DataTable meant quite some work. You needed to create a Connection object, a DataAdapter and optionally a Command object. Then you had to call the Fill method on the DataAdapter and pass it a newly created DataSet or DataTable.

In .NET 2.0, this code model has been simplified, thanks to the Load method on the DataTable and DataSet classes that takes an IDataReader as a parameter.

The following code snippet shows you how to fill a DataTable from an SqlDataReader with only a few lines of code.

private DataTable GetDataTable()
{
  string sql = "SELECT Id, Description FROM MyTable";
  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
    {
      myConnection.Open();
      using (SqlDataReader myReader = myCommand.ExecuteReader())
      {
        DataTable myTable = new DataTable();
        myTable.Load(myReader);
        myConnection.Close();
        return myTable;
      }
    }
  }
}		
		

This code executes a DataReader (a SqlDataReader in this case, but you can also use other types, like an OleDbDataReader). It then passes this open reader into the Load method of the DataTable that takes care of copying the data from the reader into the DataTable.

A quick, and easy way to fill a DataTable....


Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Friday, October 27, 2006 2:08:32 PM Nick said:
Interesting stuff, thanks.
On Wednesday, April 18, 2007 8:38:29 AM ramesh said:
excellent article
On Monday, January 07, 2008 7:02:17 PM John Cross said:
Ok, this is great and works well for me when creating my data source for a Calendar control:  Calendar1.EventSource = EventsDB.GetTable();

However, I want to be able to call the same method from a web form and use the returned datatable.  How can I use the datatable?  I want to be able to iterate the datatable, not just assigning it as a data source.  Thanks in advance,

John
On Monday, January 07, 2008 9:34:24 PM Imar Spaanjaars said:
Hi John,

You can loop through the Rows collection:

forach (DataRow myRow in myTable.Rows)
{
  // Do something with myRow
}

Hope this helps,

Imar
On Friday, January 11, 2008 2:55:37 AM Bob Bedell said:
Hi Imar,

I think you can shorten your code even further by removing:

myConnection.Close();

The using statment automatically calls the connection object's Dispose method, which has the same effect as manually closing the object. So I believe the above line of code is unnecessary.

Best,

Bob
On Saturday, April 12, 2008 6:38:56 AM Gerhard McDonald said:
Hi Imar

Very Nice...
Is there an improved version or any new functionality in .Net 3.5?
And can you post the VB version as well...

Thanx

G
On Saturday, April 12, 2008 7:44:34 AM Imar Spaanjaars said:
Hi Gerhard,

AFAIK, there are no improvements for this code in .NET 3.5.

The VB.NET version is almost identical except for a few semi-colons. If you need help converting the code, try one of the available on-line code converters.

Cheers,

Imar
On Saturday, May 17, 2008 9:34:04 AM kumar said:
hi,
can any one help me how to compare two datatable without using for loop... because i am having huge no of records... it will slow down if "for loop" is used...

thanks in advance...
On Wednesday, December 24, 2008 9:24:28 AM lau said:
excellent article!good jod!
On Monday, November 23, 2009 3:01:54 PM Tobias said:
Are there any performance difference between using the Load method of the Dataset or the Fill method of the DataAdapter? Because there are very litte difference in the code it takes to create either one so it's always interesting to see if there is something else to gain from using a certain way of coding.

As you can see on my snippet page the two functions takes exactly the same amount of rows and objects to perform the same job.

http://www.gladh.me, go to the sixth snippet called DataSet function
On Monday, November 23, 2009 4:48:49 PM Imar Spaanjaars said:
Hi Tobias,

There could be a difference; haven't actually measured it. Thew quick in this post's title is merely refering to "quick to write"...

Imar
On Wednesday, April 28, 2010 6:47:14 PM clem said:
It appears as though this works for sql commands only and not Stored Procedures.
On Wednesday, April 28, 2010 7:57:11 PM Imar Spaanjaars said:
Hi clem,

What makes you think that? This works fine for stored procedures as well.

Cheers,

Imar
On Wednesday, October 13, 2010 5:51:26 AM Codingsense said:
Thanks mate, excellent article.
On Wednesday, October 20, 2010 2:46:42 PM jp2code said:
That is slick!

I've been stuck on the mindset that I had to use a Data Adapter to Fill a Data Table.

I like it!

A+
On Saturday, November 20, 2010 4:39:55 AM SM said:
Very nice article.

Here i have one question, kindly guide me if possible.
Which would be a faster ?
by using datatable or by using datareader.
On Saturday, November 20, 2010 9:58:03 AM Imar Spaanjaars said:
Hi there,

A reader, because in this example the DataTable gets filled by a reader under the hood.

Cheers,

Imar
On Wednesday, April 04, 2012 6:48:00 AM sutha said:
its good..... I need to compare two datatable without using for loop... because i am having huge no of records... it will slow down if "for loop" is used... plxz help me
On Wednesday, April 04, 2012 6:55:51 AM Imar Spaanjaars said:
Hi sutha,

I guess it depends on the data you're holding, and the comparison you need to do. Why not filter / compare in the database?

Otherwise, I think a for loop is the way to go....

Imar
On Wednesday, April 04, 2012 1:15:12 PM jp2code said:
I have a new comment to add to this that I discovered not long after implementing this:

DataSets or DataTables filled with the Load method are READ ONLY. This is not the case when using a Data Adapter to Fill one of these objects.

This may not be important to people, but it took me a long time to debug this issue after making changes to several of my database calls.

I typically will add Columns of calculated data before sending that data to the Data Grid.

Regards,
~Joe
On Wednesday, April 04, 2012 6:27:01 PM Imar Spaanjaars said:
Hi jp2code,

Interesting; hadn't realised that. It seems to make some sense though, given that a DataReader is a read-only forward only way to read data....

Cheers,

Imar
On Friday, April 11, 2014 7:05:45 AM raosir said:
Simply simple!
Good for both learning and teaching.
Keep it up and god bless

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.