Load DataTable and DataSet from SqlDataReader

Load DataTable and DataSet from SqlDataReader

In this article we are going to see how to load DataTable and DataSet from SqlDataReader object.

Load DataTable from SqlDataReader

In below example we will see how to load DataTable from SqlDataReader. At first we have to read data into SqlDataReader object then we will dump all data using Load() method. Have a look on below example.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
 
            cmd.CommandText = "select * from test";
            SqlDataReader rd = cmd.ExecuteReader();
 
            DataTable dt = new DataTable();
            dt.Load(rd);
 
            Console.WriteLine("Load Data table from Reader");
 
            foreach (DataRow r in dt.Rows)
            {
                Console.Write("ID: " + 
					r["id"].ToString());
                Console.Write("Name:- "+  r["name"].ToString());
                Console.Write("Sal :- "+ r["sal"].ToString());
                Console.Write("\n");
            }
            Console.ReadLine();
        }
    }
}
 

Load DataTable from SqlDataAdapter

In same way we can load DataTable from SqlDataAdapter also. We have to use Fill() method associated with SqlDataAdapter object.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
            con.Open();
            DataTable dt = new DataTable();
            SqlDataAdapter ad = new SqlDataAdapter("select * from test", con);
            ad.Fill(dt);
 
            Console.WriteLine("Load DataTable from Adapter");
 
            foreach (DataRow r in dt.Rows)
            {
                Console.Write("ID: " +  r["id"].ToString());
                Console.Write("Name:- "+  r["name"].ToString());
                Console.Write("Sal :- "+ r["sal"].ToString());
                Console.Write("\n");
            }
 
 
            Console.ReadLine();
        }
    }
}

Load DataSet from SqlDataReader

Here we will see how to load DataSet object from SqlDataReader. The approach is very similar with DataTable . Here we will use Load() method which will take three argument.

 using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial
Catalog=test;Integrated Security=True";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
 
            cmd.CommandText = "select * from test";
            SqlDataReader rd = cmd.ExecuteReader();
 
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("name");
            dt.Columns.Add("sal");
            ds.Tables.Add(dt);
 
            ds.Load(rd, LoadOption.OverwriteChanges,dt);
 
            Console.WriteLine("Load DataSet from Reader");
           
	    foreach (DataRow r in dt.Rows)
            {
                Console.Write("ID: " +  r["id"].ToString());
                Console.Write("Name:- "+  r["name"].ToString());
                Console.Write("Sal :- "+ r["sal"].ToString());
                Console.Write("\n");
            }
            Console.ReadLine();
        }
    }
}
 

Here is sample output

Conclusion

Those are three different techniques to load DataTable and DataSet from SqlDataReader object in C#.