How to get data from data reader to data table?

Question

Hi all! I am working on a project but I have problem. I want to get data from DataReader to DataTable. When I use Datatable.Load(Datareader); Then first record of the fetch data gone missing.

Hussain Munaf · Answer

Hello Santosh i post code use just copy this code and past on your console file i hope i will give full help: using System; using System.Text; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication1 { class Program { private static SqlConnection conn = new SqlConnection(@server=HUSSAIN-PC\HUSSAIN;database=community;Integrated Security=SSPI); public static SqlCommand cmd = new SqlCommand(select * from Student_Infoconn); public static SqlDataReader sdr; static void Main(string[] args) { try { conn.Open(); sdr= cmd.ExecuteReader(); int a = sdr.GetOrdinal(ID); int b = sdr.GetOrdinal(FirstName); int c = sdr.GetOrdinal(LastName); int d = sdr.GetOrdinal(Institute); while (sdr.Read()) { Console.WriteLine(This is IDs {0}{1}{2}{3}sdr.GetValue(a)sdr.GetValue(b)sdr.GetValue(c)sdr.GetValue(d)); } } catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); } finally { conn.Close(); } Console.ReadLine(); } } } Thanks

Pradeep Chandraker · Answer

The only reason I see is you may be reading record from datareader before loading it into datatable something like this. SqlDataReader reader = sqlCmd.ExecuteReader(); reader.Read(); dt.Load(reader); For the records you have done read operation those will not be available when you load daraReader into datatable.

Sam Hobbs · Answer

That is the type of thing that happens when we overlook something simple. Probably the only reason you don't see the cause of it is because it is so easy.

Jaganathan Bantheswaran · Answer

Hi Try like this. Its working fine. private void ConvertDateReadertoTableUsingLoad() { SqlConnection conn = null; try { string connString = ConfigurationManager.ConnectionStrings[NorthwindConn].ConnectionString; conn = new SqlConnection(connString); string query = SELECT * FROM Customers; SqlCommand cmd = new SqlCommand(query conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); DataTable dt = new DataTable(); dt.Load(dr); GridView1.DataSource = dt; GridView1.DataBind(); } catch (SqlException ex) { // handle error } catch (Exception ex) { // handle error } finally { conn.Close(); } }