DataViews Programming Tutorial with C# ADO.Net

What is DataViews?

DataView gives option to display DataTable’s data in various style, views or format. With DataView you can display data with various sorting order, customization and filtration. In this tutorial you will learn everything about DataView in C# ADO.Net.

Content of this Tutorial

1. Creating and Displaying a DataView
2. Sorting and Filtering DataView
3. Adding Row in DataView
4. Editing or Updating Row in DataView
5. Deleting Row from DataView
6. Complete Program

Creating and Displaying a DataView

private void btnDisplay_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");
 DataView dv = set.Tables["Items"].DefaultView;
 dataGridView1.DataSource = dv;
}

Output

Sorting and Filtering DataView

private void btnSort_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");
 DataView dv = set.Tables["Items"].DefaultView;
 dv.Sort = "Name ASC";
 dataGridView1.DataSource = dv;
}

Output

Adding Row in DataView

private void btnAdd_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");
 DataView dv = set.Tables["Items"].DefaultView;
 dv.AllowNew = true;
 DataRowView newRow = dv.AddNew();
 newRow.BeginEdit();
 newRow["Name"] = "Router";
 newRow["Price"] = "$130";
 newRow["Date"] = "26 August 2016";
 newRow.EndEdit();
 dataGridView1.DataSource = dv;
}

Output

Editing or Updating Row in DataView

private void btnEdit_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");
 DataView dv = set.Tables["Items"].DefaultView;
 dv.AllowEdit = true;
 dv[1].BeginEdit();
 dv[1]["Name"] = "WireLess Keyboard";
 dv[1]["Price"] = "$88";
 dv[1].EndEdit();

 dataGridView1.DataSource = dv;
}

Output

Deleting Row from DataView

private void btnDelete_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");
 DataView dv = set.Tables["Items"].DefaultView;
 dv.AllowDelete = true;
 dv.Table.Rows[2].Delete();

 dataGridView1.DataSource = dv;
}

Output

Complete Program

 

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

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

        private void btnDisplay_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");
            DataView dv = set.Tables["Items"].DefaultView;
            dataGridView1.DataSource = dv;

        }

        private void btnSort_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");
            DataView dv = set.Tables["Items"].DefaultView;
            dv.Sort = "Name ASC";
            dataGridView1.DataSource = dv;
        }

        private void btnAdd_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");
            DataView dv = set.Tables["Items"].DefaultView;
            dv.AllowNew = true;
            DataRowView newRow = dv.AddNew();
            newRow.BeginEdit();
            newRow["Name"] = "Router";
            newRow["Price"] = "$130";
            newRow["Date"] = "26 August 2016";
            newRow.EndEdit();
            dataGridView1.DataSource = dv;
        }

        private void btnEdit_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");
            DataView dv = set.Tables["Items"].DefaultView;
            dv.AllowEdit = true;
            dv[1].BeginEdit();
            dv[1]["Name"] = "WireLess Keyboard";
            dv[1]["Price"] = "$88";
            dv[1].EndEdit();
                        
            dataGridView1.DataSource = dv;
        }

        private void btnDelete_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");
            DataView dv = set.Tables["Items"].DefaultView;            
            dv.AllowDelete = true;
            dv.Table.Rows[2].Delete();

            dataGridView1.DataSource = dv;
        }
    }
}

Summary:

In this chapter, you learned how to use DataView in C# ADO.Net with complete programming example. In the next tutorial, you will learn Entity Framework 6.