DataSet in ADO.Net

DataSet in ADO.Net

• The DataSet class is a very important class of ADO.NET.
• It is used in disconnected architecture.
• It does not require a open or active connection to the database.
• It stores record of one or more data tables.
• It is a collection of data tables that contain the data. 
• DataSet is tabular representation of data.
• Tabular representation means it represents data into row and column format.
• It represent records in the form of Database table (Row and Column) format.
• We can use Dataset in combination with DataAdapterClass
• The Dataset contains the copy of the data we requested.
• The Dataset contains more than one Table at a time.
• A DataSet is a local copy of your Database Table that gets populated in client PC.
• It is independent of Data Source and because it exists in the local system, it makes application fast and reliable
• We can set up Data Relations between these tables within the DataSet.
• The DataAdapter Object allows us to populate DataTables in a DataSet.
• We can use Fill method of the DataAdapter for populating data in a Dataset.
• The ADO.NET DataSet class belongs to the System.Data namespace.
• DataSet is an in-memory representation of a collection of database objects including related tables, constraints and relationships among the tables.
• We can say that the DataSet is a small database because it stores the schema and data in the application memory area.

SOURCE CODE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataSet_ADO
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
                SqlConnection con = new SqlConnection(cs);
                string emp_query = "select * from employee_tbl";
                string std_query = "select * from student_tbl";
                SqlDataAdapter sda1 = new SqlDataAdapter(emp_query, con);
                SqlDataAdapter sda2 = new SqlDataAdapter(std_query, con);
                DataTable employees = new DataTable();
                DataTable students = new DataTable();
                sda1.Fill(employees);
                sda2.Fill(students);
                DataSet ds = new DataSet();

                ds.Tables.Add(employees);
                ds.Tables.Add(students);


                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Console.WriteLine(row[0] + " " + row[1] + " " + row[2] + " " + row[3] + " " + row[4] + " " + row[5]);
                }

                Console.WriteLine("------------------------------------");

                foreach (DataRow row in ds.Tables[1].Rows)
                {
                    Console.WriteLine(row[0] + " " + row[1] + " " + row[2] + " " + row[3] + " " + row[4]);
                }


                //string query = "spGetEmployees";
                //SqlDataAdapter sda = new SqlDataAdapter(query,con);
                //sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                //DataSet ds = new DataSet();
                //sda.Fill(ds);

                //ds.Tables[0].TableName = "employee_tbl";
                //ds.Tables[1].TableName = "student_tbl";

                //foreach (DataRow row in ds.Tables["employee_tbl"].Rows)
                //{
                //    Console.WriteLine(row[0] + " " + row[1] + " " + row[2] + " " + row[3] + " " + row[4] + " " + row[5]);
                //}

                //Console.WriteLine("------------------------------------");

                //foreach (DataRow row in ds.Tables["student_tbl"].Rows)
                //{
                //    Console.WriteLine(row[0] + " " + row[1] + " " + row[2] + " " + row[3] + " " + row[4]);
                //}

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
    }
}