Working With DataSet in ADO.NET

A K
12y
182.3k
0
0

Article

Introduction

DataSet is tabular representation of data. Tabular representation means it represents data into row and column format. This class is counted in a disconnected architecture in .NET Framework. Which means it is found in the "System.Data" namespace. The Dataset can hold records of more than one Database tables or DataTables.

Now we try to use it into our application. Generally DataAdapter object is used with DataSet to fill DataSet with value. Let's look a simple example to using DataSet. In this example, I am using "student" Database ( Database name ) which has a table "student_detail". Take a Windows Form Application -> a DataGridView control and write the following code.

using System.Windows.Forms;
using System.Data.SqlClient;

namespace workingwithdataset
{
    public partial class frmdataset : Form
    {
        public frmdataset()
        {
            InitializeComponent();
        }
        private void frmdataset_Load(object sender, EventArgs e)
        {
            SqlDataAdapter dadapter = new SqlDataAdapter(" select * from student_detail ", " database=student;user=sa;password=wintellect ");
            DataSet dset = new DataSet(); //Creating instance of DataSet
            dadapter.Fill(dset,"student_detail"); // Filling the DataSet with the records returned by SQL statemetns written in sqldataadapter
            dataGridView1.DataSource = dset.Tables["student_detail"]; // Binding the datagridview
        }
    }
}

Run the application. The code is in the load event of the form. So when you run the application, the output will look like the following figure.

Output

Now we fill the dataset with a record from the DataTable. In the example shown above I have filled the dataset with records from the SqlDataAdapter. Write the following code for the form load event.

using System.Windows.Forms;
using System.Data.SqlClient;

namespace workingwithdataset
{
    public partial class frmdataset : Form
    {
        public frmdataset()
        {
            InitializeComponent();
        }
        private void frmdataset_Load(object sender, EventArgs e)
        {
            DataTable table1 = new DataTable();
            DataTable table2 = new DataTable();

            DataColumn dc11 = new DataColumn("ID", typeof(Int32));
            DataColumn dc12 = new DataColumn("Name", typeof(string));
            DataColumn dc13 = new DataColumn("City", typeof(string));

            table1.Columns.Add(dc11);
            table1.Columns.Add(dc12);
            table1.Columns.Add(dc13);

            table1.Rows.Add(111,"Amit Kumar", "Jhansi");
            table1.Rows.Add(222, "Rajesh Tripathi", "Delhi");
            table1.Rows.Add(333, "Vineet Saini", "Patna");
            table1.Rows.Add(444, "Deepak Dwij", "Noida");

            DataSet dset = new DataSet();
            dset.Tables.Add(table1);
            dataGridView1.DataSource = dset.Tables[0];
        }
    }
}

In the above example, I am creating a DataTable and filling the DataSet with the record of DataTable. Run the application.

Output

DataSet can hold the record of more than one table. Now we fill the DataSet with more records from the DataTable. You have to add a DataGrid control. Take the DataGrid control and write the following code for the form load event.

            DataColumn dc13 = new DataColumn("City", typeof(string));

            EMPLOYEE.Columns.Add(dc11);
            EMPLOYEE.Columns.Add(dc12);
            EMPLOYEE.Columns.Add(dc13);

            EMPLOYEE.Rows.Add(111, "Amit Kumar", "Jhansi");
            EMPLOYEE.Rows.Add(222, "Rajesh Tripathi", "Delhi");

            DataColumn dc21 = new DataColumn("ID", typeof(Int32));
            DataColumn dc22 = new DataColumn("Name", typeof(string));
            DataColumn dc23 = new DataColumn("Subject", typeof(string));

            // Craeting TEACHER table
            DataTable TEACHER = new DataTable();

            TEACHER.Columns.Add(dc21);
            TEACHER.Columns.Add(dc22);
            TEACHER.Columns.Add(dc23);

            TEACHER.Rows.Add(123, "Anil Kumar", "Java");
            TEACHER.Rows.Add(234, "Amar Sharma", "C++");

            DataColumn dc31 = new DataColumn("ID", typeof(Int32));
            DataColumn dc32 = new DataColumn("Name", typeof(string));
            DataColumn dc33 = new DataColumn("Course", typeof(string));
            DataColumn dc34 = new DataColumn("Year", typeof(Int32));

            // Craeting STUDENT table

            DataTable STUDENT = new DataTable();

            STUDENT.Columns.Add(dc31);
            STUDENT.Columns.Add(dc32);
            STUDENT.Columns.Add(dc33);
            STUDENT.Columns.Add(dc34);

            STUDENT.Rows.Add(1, "Manoj Singh", "M.Tech", 2);
            STUDENT.Rows.Add(2, "Sanjay Kumar", "MCA", 3);
            STUDENT.Rows.Add(5, "Pramod Sharma", "MBA", 2);
            STUDENT.Rows.Add(18, "Satish jain", "B.Tech", 4);

            DataSet dset = new DataSet();

            dset.Tables.Add(EMPLOYEE);
            dset.Tables.Add(TEACHER);
            dset.Tables.Add(STUDENT);

            dataGrid1.DataSource = dset;
        }

    }
}

Run the application.

Output

Click at plus (+) sign to explore all tables.

You can show records of Tables by clicking the Tables links. Like I am clicking at Table1.

Now we try to show records into TextBox from DataSet. As we know DataSet holds records in a row - column format. So its data can be retrieved by specifying rows and column index. Look at the following code where I am getting the Data of the first row in the TextBox. At first take one DatagridView, three Labels, three TextBox and one Button control. Arrange them into the following format.

Write the following code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace workingwithdataset
{
    public partial class frmdataset : Form
    {
        public frmdataset()
        {
            InitializeComponent();
        }
        DataTable EMPLOYEE;
        DataSet dset;
        private void frmdataset_Load(object sender, EventArgs e)
        {
            // Craeting EMPLOYEE table
            EMPLOYEE= new DataTable();

            DataColumn dc11 = new DataColumn("ID", typeof(Int32));
            DataColumn dc12 = new DataColumn("Name", typeof(string));
            DataColumn dc13 = new DataColumn("City", typeof(string));

            EMPLOYEE.Columns.Add(dc11);
            EMPLOYEE.Columns.Add(dc12);
            EMPLOYEE.Columns.Add(dc13);

            EMPLOYEE.Rows.Add(111, "Amit Kumar", "Jhansi");
            EMPLOYEE.Rows.Add(222, "Rajesh Tripathi", "Delhi");

           dset = new DataSet();
            dset.Tables.Add(EMPLOYEE);

            dataGridView1.DataSource = dset.Tables[0];

        }

      private void btnshow_Click(object sender, EventArgs e)
        {
            txtid.Text = dset.Tables[0].Rows[0][0].ToString();
            txtname.Text = dset.Tables[0].Rows[0][1].ToString();
            txtcity.Text = dset.Tables[0].Rows[0][2].ToString();
        }

    }
}
Run the application.

Output

Click at "show" button to show record into Textbox.