Working with DataView Class in ADO.NET

Introduction

The DataView is Class of Disconnected Architecture of the .NET framework. It represent customized views of a Database (Table ) and provide facility for sorting, filtering, searching the records by its given properties. There is not need to write SQL statements for these operation. Here I will perform different operation using DataView's properties.

At first, you should have a database with some record. I have a database "student" and database table "student_detail" , which has some records. So create a database. I am giving below SQL commands for my database.

create database student
use student
create table student_detail
(
 id int primary key,
 F_Name varchar(20),
 L_Name varchar(20),
 Course varchar(20),
 City varchar(20),
 Age int
 )
 select * from student_detail
 insert into student_detail  values(111,'Alok','Pandey','MCA','Delhi',23)
 insert into student_detail  values(122,'Satish','Kumar','MBA','Jhansi',24)
 insert into student_detail  values(123,'Amitabh','Pandey','B.Tech','Noida',21)
 insert into student_detail  values(124,'Pramod','Sharma','MCA','Allahabad',25)
 insert into student_detail  values(125,'Durgesh','Pandey','M.Tech','Varanasi',28)

Now, we have database with some records. Open Visual Studio 2010 > take a window form project > take a dataGridView control and 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 dataviewclassinadodotnet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlDataAdapter dadptr;
        DataSet dset;
        DataView dvu;
        string connstring = "server=.;database=student;user=sa;password=aaaaaa";
        private void Form1_Load(object sender, EventArgs e)
        {
            dadptr = new SqlDataAdapter("select * from student_detail", connstring);
            dset = new DataSet();
            dadptr.Fill(dset);
            dvu=new DataView(dset.Tables[0]);
            dGVshow.DataSource = dvu;
        }
    }
}
 
Run the application.

Output



As you have seen in the above figure, I am showing record into datagridview using DataView class. It is simple operation. Now, I am going to perform sorting and searching operation using DataView's property.

Sorting Record : The sort property is used for sorting column ( Record). The column can be sorted into ascending order or descending order. If sorting order is not specified then ascending order sorting will be performed. Means default order is ascending.

Now add some more controls to your application and arrange them like as below figure.



Add the below code in your application.( Although, I am giving full code below)

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 dataviewclassinadodotnet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlDataAdapter dadptr;
        DataSet dset;
        DataView dvu;
        string connstring = "server=.;database=student;user=sa;password=aaaaaa";
        private void Form1_Load(object sender, EventArgs e)
        {
            dadptr = new SqlDataAdapter("select * from student_detail", connstring);
            dset = new DataSet();
            dadptr.Fill(dset);
            dvu=new DataView(dset.Tables[0]);
            dGVshow.DataSource = dvu;
        }
        private void btnsort_Click(object sender, EventArgs e)
        {
            try
            {
                dvu.Sort = " "+txtcolumn1.Text+" ";
                dGVshow.DataSource = dvu;           
            }
            catch(Exception)
            {
                MessageBox.Show("Invalid Column Name");
            }
        }
 
        private void btnadvancesort_Click(object sender, EventArgs e)
        {
            try
            {
                dvu.Sort = " " + txtcolumn2.Text + " "+ cmbsortingorder.SelectedItem +" ";
                dGVshow.DataSource = dvu;
            }
            catch (Exception)
            {
                MessageBox.Show("Invalid Column Name");
            }
        }
    }
}
 
Run the application.

Output

You can sort records dippending on different sorting tecnique. Like write the column name in textbox and click the "sort" button. It will sort the record in ascending order.



You can also sort the record by sorting appropriate column in ascending or descending order. Write the column name and select sorting oredr from ComboBox and click the "sort" button.



Filtering Record: The RowFilter property is used for filter the record. It filter the record on the basis of any given condition.

Add some more controls to your application and arrange them like as below figure.



Write the below 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 dataviewclassinadodotnet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlDataAdapter dadptr;
        DataSet dset;
        DataView dvu;
        string connstring = "server=.;database=student;user=sa;password=wintellect";
        private void Form1_Load(object sender, EventArgs e)
        {
            dadptr = new SqlDataAdapter("select * from student_detail", aaaaaa);
            dset = new DataSet();
            dadptr.Fill(dset);
            dvu=new DataView(dset.Tables[0]);
            dGVshow.DataSource = dvu;
        }
 
        private void btfilter_Click(object sender, EventArgs e)
        {
            try
            {
                dvu.RowFilter = "f_name like '" + txtname.Text + "%' ";
                dGVshow.DataSource = dvu;
            }
            catch (Exception)
            {
                MessageBox.Show("Invalid Column Name");
            }
        }
       
        private void btnadvancefilter_Click(object sender, EventArgs e)
        {
            try
            {
                dvu.RowFilter = " "+txtcolumn.Text+"  =  '" + txtcolumnvalue.Text + "' ";
                dGVshow.DataSource = dvu;

            }
            catch (Exception)
            {
                MessageBox.Show("Invalid Column Name or column value");
            }
        }
    }
}
 
Run the application

Output

 

Whatever you enter in name textbox, it will show the all record which match with name you entered. To show record, click the "Filter" button. I have entered "a" in name textbox . After clicking "Filter" button it will all record which first name start with "a".



Now enter column name and column value and click the "Filter" button. It will filter the record on the basis of given column and value. I have entered "course" as column and "mca" as value.

Here are some related resources:

• DataView Class in FSharp

• Working with DataView and DataViewManager Events in ADO.NET

• Working with the SqlTransaction Class in ADO.NET

• Working with DataTable and its Methods in ADO.NET

• DataView in ADO.NET