Trouble loading SQL Data Reader data into DataTable

Question

string query = "select * from cfo_daily_trans_hist";
            try
            {
                using (SqlConnection connection = new SqlConnection(
                       cnnString))
                {
                    SqlCommand command = new SqlCommand(query);
                    command.Connection = connection;
                    connection.Open();

                    var result = command.ExecuteReader();
                    DataTable datatable = new DataTable();
                    datatable.Load(result);
                    connection.Close();
                }
            }

So the var result is created through the ExecuteReader(); and HasRows is true, and it shows the correct amount of fields. However, the DataTable that I create from it is empty.

What am I doing wrong? I'm 99% sure it's getting data, but I don't know how to find it through the SqlDataReader object to make sure.

Thanks.

user596075 · Accepted Answer · 2011-10-18 14:58:13Z

6

Instead of a SqlDataReader, use a SqlDataAdapter.

SqlDataAdapter myAdapter = new SqlDataAdapter(command);
myAdapter.Fill(datatable);

With a SqlDataAdapter, you don't need to explicitly call SqlConnection.Open() and SqlConnection.Close(). It is handled in the Fill() method.

answered Oct 18, 2011 at 14:58

user596075

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

Comments

user16860642 · Accepted Answer · 2021-09-08 12:25:06Z

0

You can try to add "COALESCE" in your sql command statement ... if you have some NULL value in your query result, you will have problem with dataTable

answered Sep 8, 2021 at 12:25

user16860642

1

1 Comment

Community Over a year ago

Please add further details to expand on your answer, such as working code or documentation citations.

Collectives™ on Stack Overflow

Trouble loading SQL Data Reader data into DataTable

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related