DataSet Tutorial with C# ADO.Net Programming Example

In this tutorial, you will learn:


1. What is DataSet in ADO.Net?
2. What is DataAdapters?
3. DataAdapters Properties and Methods.
4. Programming Example

Content of this Article

What is DataSet in ADO.NET?

In a simple word, 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. Accessing Remote Database each time for updating or retrieving details are time-consuming so datasets help you to keep local database tables on your PC.

A DataSet behaves like real Database and it represents a complete set of data that includes tables, constraints, and relationships among the tables. Using the DataAdapters you can fill DataSet and use this dataset for retrieving and storing information. When all the tasks get completed, update Real Database with datasets.

What is DataAdapters?

DataAdapters are used for controlling Datasets and it provides communication between DataSets and DataSource. DataAdapters make a connection with Data Source and then Fill Data to DataSets. It also Updates Data Source with DataSets.

Important Data Adapters Properties and Methods

Properties:

Properties Description
DeleteCommand It is used for Deleting Records from DataSource
InsertCommand It is used for adding New Record to a DataSource
SelectCommand It is used for Selecting Records from a DataSource
UpdateCommand It is used for Updating Records in a DataSource.
TableMapping It is used for mapping actual database tables and datasets.

Methods:

Method Description
Fill This method Fills Records from DataAdapters to DataSets.
Update This method update DataSource with DataSets.

Programming Example in C# Console:

  
using System;
using System.Data.SqlClient;
using System.Data;

namespace DataSet_Example
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";            
            string querystring = "Select * from Items";
            SqlDataAdapter adapter = new SqlDataAdapter(querystring, ConString);
            DataSet ds = new DataSet();
            adapter.Fill(ds, "Items");
            Console.WriteLine(ds.GetXml());           
            Console.ReadKey();
        }
    }
}

Output


  
    1
    LED Screen
    $120
    2017-01-27T00:00:00+05:30
  
  
    2
    USB Keyboard
    $20
    2017-05-25T00:00:00+05:30

DataSet Example with Grid View

Mostly DataSet is used with GridView in ASP.Net. Here, I have explained DataAdapters and DataSets with Examples.

1. Open Visual Studio 2015 and Go to File > New >
Project. Create a New Windows Forms Application Dataset_Example.

New Windows Project

2. Drag a GridView and a Button like that.

Extract Data from DataSet to GridView
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace DataSet_Exampl
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";

            SqlDataAdapter adapter = new SqlDataAdapter(Query,ConString);
            DataSet set = new DataSet();

            adapter.Fill(set, "Items");
            dataGridView1.DataSource = set.Tables["Items"];
        }
    }
}

Output


Updating, Inserting, and Deleting Records in a Dataset

After populating dataset, you can update, insert or delete a record from the dataset. Here is a full programming example.

Adding New Row in DataTable
private void btnInsert_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");
            
            //Adding New Row to DataSet           
            DataRow row = set.Tables["Items"].NewRow();
            row["Name"] = "4GB DDR3 RAM";
            row["Price"] = "$50";
            row["Date"] = "26 May 2017";
            set.Tables["Items"].Rows.Add(row);          
            
            dataGridView1.DataSource = set.Tables["Items"];
        }

Output

Insert Row in Dataset
Edit or Update Row in DataSet

If you don’t know row index or unique row number still you can update or edit row in dataset by using following method.

private void btnUpdate_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");

            set.Tables["Items"].Rows[1]["Name"] = "Graphics Card";

            dataGridView1.DataSource = set.Tables["Items"];
        }

Output

Update Row in Dataset
Delete Row in DataSet

You can delete row from dataset using Delete() Method.

private void btnDelete_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");

            set.Tables["Items"].Rows[1].Delete();

            dataGridView1.DataSource = set.Tables["Items"];
        }

Output

Delete Row from DataSet

Save Dataset Changes to Database

After Modifying Dataset, you can save Dataset changes to database.

Programming Example 

private void btnSave_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");

            //Adding New Row to DataSet and Update           
            DataRow row = set.Tables["Items"].NewRow();            
            row["Name"] = "4GB DDR3 RAM";
            row["Price"] = "$50";
            row["Date"] = "26 May 2017";
            set.Tables["Items"].Rows.Add(row);

            //Updating Database Table
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            adapter.Update(set.Tables["Items"]);

            MessageBox.Show("DataSet Saved to Database Successfully");
           
        }
 

Output

DataSet Saved to Database Successfully _

Complete Program

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

namespace DataSet_Exampl
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            //Fill DataSet
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";

            SqlDataAdapter adapter = new SqlDataAdapter(Query,ConString);
            DataSet set = new DataSet();

            adapter.Fill(set, "Items");
            dataGridView1.DataSource = set.Tables["Items"];
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");

            set.Tables["Items"].Rows[1]["Name"] = "Graphics Card";

            dataGridView1.DataSource = set.Tables["Items"];
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");
            
            //Adding New Row to DataSet           
            DataRow row = set.Tables["Items"].NewRow();
            row["ID"] = 3;
            row["Name"] = "4GB DDR3 RAM";
            row["Price"] = "$50";
            row["Date"] = "26 May 2017";
            set.Tables["Items"].Rows.Add(row);          
            
            dataGridView1.DataSource = set.Tables["Items"];
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");

            set.Tables["Items"].Rows[1].Delete();

            dataGridView1.DataSource = set.Tables["Items"];
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            //Fill Dataset
            string ConString = @"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True";
            string Query = "SELECT * FROM Items";
            SqlDataAdapter adapter = new SqlDataAdapter(Query, ConString);
            DataSet set = new DataSet();
            adapter.Fill(set, "Items");

            //Adding New Row to DataSet and Update           
            DataRow row = set.Tables["Items"].NewRow();            
            row["Name"] = "4GB DDR3 RAM";
            row["Price"] = "$50";
            row["Date"] = "26 May 2017";
            set.Tables["Items"].Rows.Add(row);

            //Updating Database Table
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            adapter.Update(set.Tables["Items"]);

            MessageBox.Show("DataSet Saved to Database Successfully");
           
        }
    }
}

Summary

In this chapter, you learned how to work with DataSet and DataTable in C# ADO.Net. You can locally work with the dataset and after completing all the modification, save back to the database. In the next chapter, you will learn about DataTables and DataView.

 

Share your thought