Troubleshoot SQL Data Reader in 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, it var resultis created through ExecuteReader();and HasRowsis true, and it shows the correct number of fields. However DataTable, the one I create from it is empty.

What am I doing wrong? I am 99% sure that I am receiving data, but I do not know how to find it through an object SqlDataReaderto make sure.

Thank.

Answer 1

SqlDataReaderUse instead SqlDataAdapter.

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

With the help of SqlDataAdapteryou do not need to explicitly call SqlConnection.Open()and SqlConnection.Close(). It is processed by the method Fill().