What are the differences between DataRows, DataSets and DataTables in C#?

rwheeler23
rwheeler23 used Ask the Experts™
on
In C#, what are the differences between using a DataRow, a DataSet or a DataTable? Are these interchangeable? Are there advantages or disadvantages to using one over another? In the scenario I am working with right now I have a dataadapter tied to a command that queries a SQL table. I need to loop through each record and populate an Excel spreadsheet. Right now I have this and it works fine but is there a better, more efficient, way?

MyDataTable dt = new DataTable();                           /* Create the data table to hold the data */
MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter(); /* Create the SQL Data Adapter to contain the returned data */
MyDataAdapter.SelectCommand = MyDataCommand;                /* Assign the SQL data command to the SQL data adapter */
MyDataAdapter.Fill(dt);                                     /* Populate the data table with the results of the query */

foreach (DataRow row in dt.Rows)
{
    Write record to Exel (row["DATA"].ToString())
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Freelance programmer / Consultant
Commented:
A DataSet contains none, one or many DataTables.
A DataTable contains none, one or many DataRows.
A DataRow contains fields (columns) with the data.

Each is different than the other.
Commented:
Just another way of presenting what Andy said:

DataSet
-   DataTable
    -   DataRow
    -   DataRow
    -   DataRow
    -   DataRow
-   DataTable
    -   DataRow
-   DataTable
    -   DataRow
    -   DataRow

It's also worth noting that these are the normal, POSSIBLE relationships, but you don't always need each piece. For example, you can have a DataTable that doesn't belong to a DataSet. DataSets are most valuable when you have multiple tables that are related in some way.

To answer your other question, your code looks fine to me (assuming there aren't significant problems in the "Write record to Excel" portion that doesn't expose any code.
rwheeler23President

Author

Commented:
Thanks for this excellent advise. Still learning and trying to relate all of this to my SQL scripts.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
If your SQL script is returning one resultset, you can get :
-datarows for each row of your SQL resultset
-one datatable -containing all the datarows
-maybe one dataset depending on your code (dataset is not mandatory)

If your SQL script is returning more then one resultset, you can get :
-one datatable for each resultset
-datarows for each row of your SQL resultset
-one dataset containing the X datatables

a dataset will give you additional features like relations between your datatables (ie foreign keys)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial