How can I populate two DataTables from one SqlDataReader

Question

I have two DataTables, and I need to populate them from one SqlDataReader.

Reason for this is that I created join in my sql query and I want to populate two tables from reader that contains them.

I used Load method from DataTable but that only works for populating first table because reader's cursor is set to the end after first Load method.

I tried to find the way to copy SqlDataReader, but that didn't work.

Any ides?

Code that I used:

var reader = comm.ExecuteReader();
DataTable1 table1=new DataTable1();
DataTable2 table2=new DataTable2();
table1.Load(reader);
table2.Load(reader);
// table2 is empty because of the first load

And what about doing reader = comm.ExecuteReader(); again after DataTable1 is populated? — varocarbas, Jul 30 '13 at 09:14
that would mean executing query twice, which is acceptable, but if there is a way to do it with one execution that would be great — stanke, Jul 30 '13 at 09:19
But this is precisely what you want: you want to copy different information into different positions of differently-structure tables. You have to read the input data from the start as many times as required (copying it to another location wouldn't change that). This is the same than reading a file line by line; if you want to store each line into two different sets, you would have to start the reading process (from the first line) twice. — varocarbas, Jul 30 '13 at 09:29
@varocarbas That's the one way to do it. If I have col1 col2 col3 in one row and I want to take col1 and col2 for one table and col3 for other table, I don't see why should I go through all rows twice — stanke, Jul 30 '13 at 09:37
If the dataset is big enough and the variability between both tables (or any additional table) is also big enough. You can store all the information in an initial table (or array or list or anything) and then access the positions you want to populate each DataTable. But in the most logical scenario (two datatables and a not too big input dataset), I would read it twice because the required code is really small and the process will be really quick anyway. — varocarbas, Jul 30 '13 at 09:44

Robert Fricke · Answer 1 · 2013-07-30T09:21:51.067

0

Use DataTable.Copy to copy both structure and data of table1 to table2.

var reader = comm.ExecuteReader();
DataTable table1 = new DataTable();
table1.Load(reader);
DataTable table2 = table1.Copy();

Msdn: DataTable.Copy

Also, you should use using:

DataTable table1, table2;
using (reader = comm.ExecuteReader())
{
    table1 = new DataTable();
    table1.Load(reader);
    table2 = table1.Copy();
}

edited Jul 30 '13 at 09:21

answered Jul 30 '13 at 09:16

Robert Fricke

3,637
21
34

I agree about `using`, but about copy, these are two different tables with different columns and that is why I cannot use `Copy()` – stanke Jul 30 '13 at 09:25
Is `ExecuteReader` executing sql that has multiple selects? – Robert Fricke Jul 30 '13 at 09:55
Read your question again: if you have one select with a join, you will only get one result table. That's how joins work. – Robert Fricke Jul 30 '13 at 10:04
I know that, I get one big table which I want to split to two separate, that's the whole point – stanke Jul 30 '13 at 12:27
I don't know how you thought the reader/DataTable.Load would split this up automatically. I'll say the best way, if not to split the tables in sql, is to load the big table into table1, then to create two more tables where you put the columns/rows you want in each. – Robert Fricke Jul 30 '13 at 12:31

score 0 · Answer 2 · answered May 14 '14 at 02:56

It can put each select table into dtList

List<DataTable> dtList = new List<DataTable>();
using (SqlDataReader reader = comm.ExecuteReader())
{
    while (!reader.IsClosed && reader.Read())
    {
        var dt = new DataTable();
        dt.Load(reader);
        dtList.Add(dt);
    }
}

How can I populate two DataTables from one SqlDataReader

2 Answers2