Working with DataView Class in ADO.NET

  • A K
  • Updated date Dec 29, 2011

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


dataview class in ado.net

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.

dataview in .net

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.

dataview in ado.net

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.

dataview class in .net

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.

rowfilter in ado.net

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

 sorting in ado.net

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".

dataview in dotnet

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.

working with dataview in .net

Here are some related resources: