Load DataTable and DataSet from SqlDataReader

Sourav.Kayal
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 5251 red flag

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#.  

Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)