How to use DataTable and DataSet in Ado.net

DataTable

DataTable is collection of fields (columns) and records (rows). It stores data in a similar fashion to a database table. A DataTable class in Ado.net consists of a DataColumn collection, a DataRow collection and a Constraint collection. We can create a DataTable independently or it can be used in conjunction with a DataSet, as a member of DataSet. We can access the collection of tables in a dataset through the Tables property of the dataset object.

In order to populate a datatable, we create an instance of Datatable class and use the Columns or Rows collection to add columns or rows respectively in the data table using the Add method.

Properties of a DataTable

• Columns : It is used to get the collection of columns that belongs to the corresponding table.
• ChildRelations : It is used to get the collection of child relations that belongs to the corresponding table.
• Constraints : It is used to get the collection of constraints that belongs to this table.
• Datatable : It is used to get the dataset to which the table belongs.
• ParentRelations : It is used to get the collection of parent relations that belongs to the corresponding table.
• PrimaryKey : it is used to get or set the array of columns that work as a primary key for the table.
• Rows : It is used to get the collection of rows that belongs to the corresponding table.
• TableName : It is used to get or set the name of the data table.

DataTable class can be used within an application. We can go through the given example to understand the usage the data tables.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;




namespace blogex1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }




        private void Form1_Load(object sender, EventArgs e)

        {

            DataTable dt = new DataTable();

            //ADDING COLUMNS

            dt.Columns.Add("Id", typeof(int));

            dt.Columns.Add("Name", typeof(string));

            dt.Columns.Add("Salary", typeof(double));

            dt.Columns.Add("Department", typeof(string));

            //ADDING ROWS

            dt.Rows.Add(102, "Rajan", 40000, "HR");

            dt.Rows.Add(101, "Ruhi", 25000, "Development");            

            dt.Rows.Add(103, "Aman", 35000, "Production Team");

            dataGridView1.DataSource = dt;

        }

    }

}

It is a simple example for creating a datatable and adding rows and columns to it.

OUTPUT

DataSet

DataSet is the centrepiece of the disconnected data driven applications. DataSet is the in memory representation of a collection of data tables. It acts as a temporary storage during the disconnection oriented data access mode. Whenever, we populate the dataset it gets stored in the primary memory. Dataset not only fetches the data but only brings the relationships, constraints and the complete schema of the tables. Dataset do not maintain a connection to the data source enabling true disconnection management. The data within the dataset can be accessed, updated, manipulated or deleted and could be finally modified at the data source.

Populating a DataSet

• We can populate a dataset using the object of DataAdapter class. Whenever we want to populate a dataset with the data from the database, we use the Fill method of the Data Adapter class which acts as a mediator.
• To populate a dataset with a new dataTable, we use the Fill() of the adapter class and pass the dataset object as an argument. The Fill() will create a Datatable in the DataSet and fill it with the results of T-SQL commands.

 In the following example, we create an instance of the dataset class. We use the fill method to populate the dataset with data.

           DataSet tableset = new DataSet();

        public DataSet getalltable(params string[] queries)

        {

            foreach(var item in queries)

            {

                adapter = new SqlDataAdapter(item, ConnectionString);

                adapter.Fill(tableset,"Table"+(tableset.Tables.Count+1));

             }

            return tableset;

        }

Now in order to view the tables in the dataset, we provide the datasource to datagridview control on the form. Since dataset contains a collection of tables, we need to pass the name of the table or the index as an argument along the dataset object integrated with the Tables property. Instead of Datagridview control, we can even use the Datagrid control. Datagrid is very much similar to the datagridview. The only difference is that it contains a navigation bar to navigate to the various datatables present in a dataset. Due to this feature, while passing the datasource to the datagrid we only use the dataset object. Therefore, we need not mention the ‘Tables’ property with the name or index. We also use the DataRelation class to represent the relationship between the two datatables.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;




namespace ado.net

{

    public partial class Form2 : Form

    {

        public Form2()

        {

            InitializeComponent();

        }

        sqlhelper sql = new sqlhelper();




        private void Form2_Load(object sender, EventArgs e)

        {

            DataSet ds = sql.getalltable("select * from Empl", "select * from Deptinfo");

            dataGridView1.DataSource = ds.Tables["Table1"];

            dataGridView2.DataSource = ds.Tables["Table2"];

            dataGrid1.DataSource = ds;

            DataRelation datarelation = new DataRelation("All child records", ds.Tables["Table2"].Columns[0], ds.Tables["Table1"].Columns[3]);




            ds.Relations.Add(datarelation);

        }

    }

}

OUTPUT