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 msdn.microsoft.com/en-us/library/… — asmithdev
– asmithdev, Commented Dec 17, 2015 at 15:29
How about using SqlDataAdapter and using it's Fill method? — Soner Gönül
– Soner Gönül, Commented Dec 17, 2015 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 stackoverflow.com/a/1676809/3866019 — asmithdev
– asmithdev, Commented Dec 17, 2015 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
– Luaan, Commented Dec 17, 2015 at 15:39
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
– Luaan, Commented Dec 17, 2015 at 15:43

asmithdev · Accepted Answer · 2015-12-17 17:14:20Z

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, 2015 at 17:14

answered Dec 17, 2015 at 16:39

asmithdev

3262 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oghenero E. Over a year ago

So how did you fix the error, you didnt mention this just what you noticed

hungryMind Over a year ago

Gossh...thats horrible

khaled4vokalz · Accepted Answer · 2015-12-17 15:37:36Z

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, 2015 at 15:37

khaled4vokalz

97810 silver badges15 bronze badges

3 Comments

Soner Gönül Over a year ago

stackoverflow.com/questions/34338188/…

khaled4vokalz Over a year ago

why have you shared this link? @SonerGönül

asmithdev Over a year ago

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.

Ɖiamond ǤeezeƦ · Accepted Answer · 2017-03-21 10:56:49Z

0

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

edited Mar 21, 2017 at 10:56

Ɖiamond ǤeezeƦ

3,3413 gold badges32 silver badges42 bronze badges

answered Mar 21, 2017 at 10:40

akkipedia

112 bronze badges

Collectives™ on Stack Overflow

DataTable not loading SqlDataReader

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related