DataTable not loading SqlDataReader

Question

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(sqlCommand, connection))
        {
            command.CommandType = CommandType.Text;
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                DataTable datatable = new DataTable();
                datatable.Load(reader);

                return datatable;
            }
        }
    }

Running this code returns an empty DataTable. However, looping through reader.Read() and printing to the debug console shows that the reader has data and it prints the expected data. Also, When I expand the reader object during debugging, hasRows is true and the field count is right for the number of columns returned.

There was a similar post here Trouble loading SQL Data Reader data into DataTable but the answer essentially was, just don't use it, use a SqlDataAdapter. I would prefer to use it and the DataTable has a load method that takes an IDataReader DataTable.Load(IDataReader). I just don't know why the reader is working when I print it to the debug window but not when I load it into the DataTable. Am I just overlooking something?

@SonerGönül I don't see a Fill method on the SqlDataReader https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx — asmithdev, Dec 17 '15 at 15:29
How about using `SqlDataAdapter` and using it's `Fill` method? — Soner Gönül, Dec 17 '15 at 15:32
@SonerGönül I can use the `SqlDataAdapter` but I am just curious why the appropriate method on the DataTable isn't working... SqlDataReader is much faster and I would prefer to use it. Here is some info on the differences http://stackoverflow.com/a/1676809/3866019 — asmithdev, Dec 17 '15 at 15:35
I doubt there's a difference - `DataTable.Load` uses a data adapter internally anyway :) All those "benefits" of using `SqlDataReader` that are in the answer you linked only apply when you don't use the reader to fill a `DataTable` (which shouldn't be unexpected, since data adapter simply fills a data table using the data reader; if you do that manually using a data reader instead, where would you expect any benefit? You're doing the exact same thing. The cost is in the data table.). Be careful about accepting advice like this without understanding what actually happens - it's going to hurt. — Luaan, Dec 17 '15 at 15:39
http://www.dotnetcurry.com/aspnet/143/convert-data-reader-to-data-table there are only small differences between your code and this article, but give it a try. — terbubbs, Dec 17 '15 at 15:42
In any case, `DataTable.Load` works just fine for me. If you keep everything as is and use a data adapter to fill the table instead, does it actually work? Do you have multiple result sets? What does the command do? — Luaan, Dec 17 '15 at 15:43
@Luaan you make a good point about the performance. I still don't like stepping away from it without knowing why it won't work. — asmithdev, Dec 17 '15 at 15:52

asmithdev · Accepted Answer · 2015-12-17T17:14:20.910

13

It turns out I was just overlooking something and this is actually not an issue at all. The original code actually works fine. The preview of the DataTable object when debugging shows {} and looked empty to me.

Then there was a property on the object called ExtendedProperties that had Count = 0, which obviously isn't a row count but I just glossed over and got Count = 0 stuck in my head.

If you find yourself in the same situation, scroll down when hovering the object and expand the Rows and you should see your row count in there.

I got duped on this one... sorry for the stupidity on my part and thanks for the help everyone.

edited Dec 17 '15 at 17:14

answered Dec 17 '15 at 16:39

asmithdev

326
2
12

2

So how did you fix the error, you didnt mention this just what you noticed – Smith Mar 26 '18 at 12:39
Gossh...thats horrible – hungryMind Jul 15 '21 at 16:07

score 0 · Answer 2 · answered Dec 17 '15 at 15:37

0

Try doing this see if it works...

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter dap = new SqlDataAdapter(sqlCommand,connection);
    DataTable datatable = new DataTable();
    dap.Fill(datatable);
    return datatable;
}

answered Dec 17 '15 at 15:37

khaled4vokalz

876
9
13

1

http://stackoverflow.com/questions/34338188/datatable-not-loading-sqldatareader?noredirect=1#comment56418080_34338188 – Soner Gönül Dec 17 '15 at 15:39
why have you shared this link? @SonerGönül – khaled4vokalz Dec 17 '15 at 15:40
As @SonerGönül said, I can go this route, but I would still like to know why the `DataTable.Load()` method doesn't work as expected or if I am just doing it wrong. – asmithdev Dec 17 '15 at 15:41

score 0 · Answer 3 · edited Mar 21 '17 at 10:56

0

Use SqlDataReader or DataAdapter, both will work. Write the return statement after closing the connection. Try if it works.

edited Mar 21 '17 at 10:56

Ɖiamond ǤeezeƦ

3,223
3
28
40

answered Mar 21 '17 at 10:40

akkipedia

11
2

DataTable not loading SqlDataReader

3 Answers3