ADO.NET DataView Class in C# with Examples
Posted by Superadmin on November 15 2023 11:07:48

ADO.NET DataView Class in C# with Examples

In this article, I am going to discuss ADO.NET DataView Class in C# with Examples. Please read our previous article where we discussed how to call a stored procedure and store the result in ADO.NET DataSet in C# using Stored Procedures with Examples. DataView in C# is similar to Views in SQL Server. So, let’s first understand what are Views in SQL Server, and then we will understand ADO.NET DataView Class in C# with multiple examples.

 

Views in SQL Server:

The Views in SQL Server are nothing but compiled SQL queries. We can also consider the Views as virtual tables. As a virtual table, the Views do not store any data physically by default. But if you want then you can change this default behavior. So, when we query a view, it actually gets the data from the underlying database tables as shown in the below image.

 

Views in SQL Server

Simply we can say that the views in SQL Server act as an interface between the Table(s) and the user. And for a single table, we can create any number of views by filtering the table data, sorting the table data, hiding column data, etc. For a better understanding of SQL Server Views, please click on the below URL.

  

https://dotnettutorials.net/lesson/sql-server-views/

 

ADO.NET DataView Class in C#:

The DataView Class in C# is very much similar to Views in SQL Server. So, using DataView, we can display or represent the DataTable’s data in various styles, or formats. For example, using DataView we can display the DataTable data in various sorting orders, and we can also filter the DataTable’s data. And the most important point is that we can create any number of DataViews for a single DataTable to respreset the data in different formats. 

According to MSDN, DataView represents a data-bindable, customized view of a DataTable for sorting, filtering, searching, editing, and navigation. The DataView does not store data but instead represents a connected view of its corresponding Data Table. Changes to the DataView’s data will affect the original Data Table. Changes to the DataTable’s data will also affect all DataViews associated with the Datatable.

  

Example to Understand ADO.NET DataView Class in C#:

Let us understand ADO.NET DataView in C# with examples. We are going to use the following Employee table to understand this concept.

 

Example to Understand ADO.NET DataView in C#

Please use the below SQL Script to Create the EmployeeDB database and Employee table and Populate the Employee table with the required sample data in SQL Server Database.

 

CREATE DATABASE EmployeeDB;
GO
 
USE EmployeeDB;
GO
 
CREATE TABLE Employee( 
Id INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(50),
Mobile VARCHAR(50),
Age INT,
Salary INT,
Department VARCHAR(50) 
 ) 
GO
 
INSERT INTO Employee VALUES (100, 'Anurag','Anurag@dotnettutorial.net','1234567890', 25, 10000, 'IT') 
INSERT INTO Employee VALUES (101, 'Priyanka','Priyanka@dotnettutorial.net','2233445566', 26, 15000, 'HR') 
INSERT INTO Employee VALUES (102, 'Preety','Preety@dotnettutorial.net','6655443322', 27, 20000, 'IT') 
INSERT INTO Employee VALUES (103, 'Sambit','Sambit@dotnettutorial.net','9876543210', 28, 25000, 'HR') 
INSERT INTO Employee VALUES (104, 'Pranaya','Pranaya@dotnettutorial.net','1234567890', 25, 10000, 'IT') 
INSERT INTO Employee VALUES (105, 'Rakesh','Rakesh@dotnettutorial.net','2233445566', 26, 15000, 'HR') 
INSERT INTO Employee VALUES (106, 'Santosh','Santosh@dotnettutorial.net','6655443322', 27, 20000, 'IT') 
INSERT INTO Employee VALUES (107, 'Tarun','Tarun@dotnettutorial.net','9876543210', 28, 25000, 'HR') 
GO
Creating ADO.NET Dataview Instance in C#:

We can create a DataView Instance in C# in two different ways. They are as follows:
Using DataView Constructor: The constructor of the DataView class initializes a new instance of the DataView class and accepts the DataTable as an argument.
Syntax: DataView dataView = new DataView(dataTable);

 

Using DefaultView Property of the DataTable: We can also create a DataView by using the DefaultView property of the DataTable.
Syntax: DataView dataView = dataTable.DefaultView;

Displaying a Dataview in C#:

Using a for loop and using the integer index position, we can access the elements of a data view as follows:
Using For Loop to access the DataView
for(int i = 0; i<dataView.Count;i++)
{
      Console.WriteLine(dataView[i][“DataTableColumnName”]);
}

  

Using Foreach Loop to access the DataView
foreach (DataRowView rowView in dataView1)
{
      DataRow = rowView.Row;
      Console.WriteLine(row[“DataTableColumnName”]);
}

 

Example to Create and Display the Data of a DataView in C#:

In the below example, I am fetching the Employee table data and store in a data table. Then I am creating two DataView based on the Data Table. And finally, we are displaying the data from the data view. While fetching the data from the data view, it is actually fetching the data from the data table. The following example code is self-explained, so please go through the comment lines.

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace DataViewClassDemo
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Create the SqlDataAdapter instance by specifying the command text and connection object 
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Id, Name, Email, Mobile, Age, Salary, Department FROM EMPLOYEE", connection);
 
//Creating DataTable Object 
DataTable EmployeeDataTable = new DataTable();
 
//Filling the DataTable using the Fill Method of SqlDataAdapter object 
dataAdapter.Fill(EmployeeDataTable);
 
//Creating DataView instance using DataView Constructor 
//Initializes a new instance of the DataView class with the specified DataTable. 
DataView dataView1 = new DataView(EmployeeDataTable);
Console.WriteLine("Accessing DataView using For Loop:");
for (int i = 0; i < dataView1.Count; i++) 
{ 
Console.WriteLine($"Id: {dataView1[i]["Id"]}, Name: {dataView1[i]["Name"]}, Email: {dataView1[i]["Email"]}, Mobile: {dataView1[i]["Mobile"]}");
} 
 
//Creating DataView instance using DefaultView property of Data Table 
DataView dataView2 = EmployeeDataTable.DefaultView;
Console.WriteLine("\nAccessing DataView using Foreach Loop:");
foreach (DataRowView rowView in dataView2) 
{ 
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Age: {row["Age"]}, Salary: {row["Salary"]}, Department: {row["Department"]}");
} 
} 
} 
catch (Exception ex) 
{ 
Console.WriteLine($"Exception Occurred: {ex.Message}");
} 
Console.ReadKey();
} 
} 
 }
Output:

Sorting ADO.NET DataView in C#:

If you want to Sort the Data in a DataView, then you need to use the following Sort property of the DataView object.

  

  1. Sort: This property is used to get or set the sort column or columns, and sort order for the DataView. It returns a string that contains the column name followed by “ASC” (ascending) or “DESC” (descending). Columns are sorted ascending by default. Multiple columns can be separated by commas.

Let us understand this with an example. In the below example, we have created two data views from the data table. Then we sort the first data view elements by Name in Ascending order and we sort the second data view using multiple columns, first sorting the employees by Department in Descending order and then sorting the employees by Name in Ascending order. By default, it is going to sort the data in ascending order, so using the ASC is optional. But if you want to sort the data in descending order, then it is mandatory to use DESC after the column name. Again, we can specify multiple columns by separating them with a comma.

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace DataViewClassDemo
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
//Create the SqlDataAdapter instance by specifying the command text and connection object 
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Id, Name, Email, Mobile, Age, Salary, Department FROM EMPLOYEE", connection);
 
//Creating DataTable Object 
DataTable EmployeeDataTable = new DataTable();
 
//Filling the DataTable using the Fill Method of SqlDataAdapter object 
dataAdapter.Fill(EmployeeDataTable);
 
//Creating DataView instance using DataView Constructor 
//Initializes a new instance of the DataView class with the specified DataTable 
DataView dataView1 = new DataView(EmployeeDataTable);
dataView1.Sort = "Name ASC";
Console.WriteLine("DataView Sorted By: Name ASC");
for (int i = 0; i < dataView1.Count; i++) 
{ 
Console.WriteLine($"Id: {dataView1[i]["Id"]}, Name: {dataView1[i]["Name"]}, Department: {dataView1[i]["Department"]}");
} 
 
//Creating DataView instance using DataView Constructor 
//Sorting Based on Multiple Columns Separated by comma 
DataView dataView2 = new DataView(EmployeeDataTable);
dataView2.Sort = "Department DESC, Name ASC";
//The above statement is similar to the below statement 
//dataView2.Sort = "Department DESC, Name"; 
Console.WriteLine("\nDataView Sorted By: Department DESC and Name ASC");
foreach (DataRowView rowView in dataView2) 
{ 
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Department: {row["Department"]}, Name: {row["Name"]}");
} 
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

Sorting ADO.NET DataView in C# with Examples

Filtering ADO.NET DataView in C# with Examples:

If you want to filter the Data in a DataView, then you need to use the following RowFilter property of the DataView object.

  1. RowFilter: This property is used to get or get the expression used to filter which rows are viewed in the DataView. It returns a string that specifies how rows are to be filtered.

Let us understand this with an example. In the below example, we have created two data views from the data table. In the first data view, we are applying a single filter and in the second data view, we are applying multiple filters.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlDataAdapter instance by specifying the command text and connection object
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Id, Name, Email, Mobile, Age, Salary, Department FROM EMPLOYEE", connection);
//Creating DataTable Object
DataTable EmployeeDataTable = new DataTable();
//Filling the DataTable using the Fill Method of SqlDataAdapter object
dataAdapter.Fill(EmployeeDataTable);
//Creating DataView instance using DefaultView property of Data Table
DataView dataView1 = EmployeeDataTable.DefaultView;
//Applying Single Filter
dataView1.RowFilter = "Age > 25";
Console.WriteLine($"DataView with Filter: {dataView1.RowFilter}");
foreach (DataRowView rowView in dataView1)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Age: {row["Age"]}, Age: {row["Salary"]}, Department: {row["Department"]}");
}
//Creating DataView instance using DataView constructor
DataView dataView2 = new DataView(EmployeeDataTable);
//Applying Multiple Filter
dataView2.RowFilter = "Age > 25 AND Department = 'HR'";
Console.WriteLine($"\nDataView with Filter: {dataView2.RowFilter}");
foreach (DataRowView rowView in dataView2)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Age: {row["Age"]}, Department: {row["Department"]}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

Filtering ADO.NET DataView in C#

Adding Row in ADO.NET DataView using C#:

If we want to create a new DataRowView, then we need to call the AddNew Method of the data view object which will create a new DataRowView. By default, the AllowNew property is set to true which allows us to add a new row to the data view. If you want to restrict the addition of a new row on the data view then set the AllowNew property to false. Whenever we add a new row to the data view, then you can see that new row inside the data view, but you cannot access that new row using the data table. For a better understanding, please have a look at the following example.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlDataAdapter instance by specifying the command text and connection object
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Id, Name, Email, Mobile, Age, Salary, Department FROM EMPLOYEE", connection);
//Creating DataTable Object
DataTable EmployeeDataTable = new DataTable();
//Filling the DataTable using the Fill Method of SqlDataAdapter object
dataAdapter.Fill(EmployeeDataTable);
//Creating DataView instance using DataView constructor
DataView dataView1 = new DataView(EmployeeDataTable);
//Create a new DataRowView by calling the AddNew Method of the dataview object
//If you want restrict new row on the data view then set AllowNew property to false
dataView1.AllowNew = true;
DataRowView newRow = dataView1.AddNew();
//Set the newRow column values
newRow["Id"] = 108;
newRow["Name"] = "New Name";
newRow["Mobile"] = "New Mobile";
newRow["Age"] = 27;
newRow["Salary"] = 20000;
newRow["Department"] = "IT";
//You can see the new row in the Data View
Console.WriteLine($"\nDataView Data");
foreach (DataRowView rowView in dataView1)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Mobile: {row["Mobile"]}, Department: {row["Department"]}");
}
//You cannot see the new row in the DataTable
Console.WriteLine($"\nDataTable Data:");
foreach (DataRow row in EmployeeDataTable.Rows)
{
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Mobile: {row["Mobile"]}, Department: {row["Department"]}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

Adding Row in ADO.NET DataView using C#

Updating Row in ADO.NET DataView with Examples:

Now, if you want to edit the row in a DataView, then first you need to fetch the DataView row and then by using the column name as the key you can update the column data. By default, the AllowEdit property value is set to true which allows us to edit or update the DataView. If you want to restrict the DataView not to be updated, then you need to set the AllowEdit property value to false. For a better understanding, please have a look at the below example. Here, we are increasing the salary of IT department employees by 1000 within the DataView.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlDataAdapter instance by specifying the command text and connection object
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Id, Name, Email, Mobile, Age, Salary, Department FROM EMPLOYEE", connection);
//Creating DataTable Object
DataTable EmployeeDataTable = new DataTable();
//Filling the DataTable using the Fill Method of SqlDataAdapter object
dataAdapter.Fill(EmployeeDataTable);
//Creating DataView instance using DataView constructor
DataView dataView1 = new DataView(EmployeeDataTable);
//You can see the new row in the Data View
Console.WriteLine($"DataView Data Before Update");
foreach (DataRowView rowView in dataView1)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Salary: {row["Salary"]}, Department: {row["Department"]}");
}
//If you want restrict the data view to be Updated then set AllowEdit property to false
dataView1.AllowEdit = true;
//Updating the Salary of IT Department employees by adding 1000 to their current Salary
foreach (DataRowView rowView in dataView1)
{
if(Convert.ToString(rowView["Department"]) == "IT")
{
//You can access the column by using string column name
rowView["Salary"] = Convert.ToInt32(rowView["Salary"]) + 1000;
//You can also access the column by using Integer Index Position
//rowView[5] = Convert.ToInt32(rowView[5]) + 1000;
}
}
//You can also access Individual DataView Row as follow
//Here, Index position 0 specify the first row
//dataView1[0]["Salary"] = Convert.ToInt32(dataView1[0]["Salary"]) + 1000;
//dataView1[0][5] = Convert.ToInt32(dataView1[0][5]) + 1000;
Console.WriteLine($"\nDataView Data After Update");
foreach (DataRowView rowView in dataView1)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Salary: {row["Salary"]}, Department: {row["Department"]}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

Updating Row in ADO.NET DataView with Examples

Deleting Row from ADO.NET DataView using C#:

Now, if you want to delete a DataRow in the DataView, then first you need to fetch the particular DataView row and then you need to call the Delete method. By default, the AllowDelete property value is set to true which allows us to delete a DataRow from the DataView. If you want to restrict that the DataRow in a DataView should not be deleted, then you need to set the AllowDelete property value to false. For a better understanding, please have a look at the below example. Here, we are deleting the IT department employees from DataView.

 

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataViewClassDemo
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=EmployeeDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlDataAdapter instance by specifying the command text and connection object
SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT Id, Name, Email, Mobile, Age, Salary, Department FROM EMPLOYEE", connection);
//Creating DataTable Object
DataTable EmployeeDataTable = new DataTable();
//Filling the DataTable using the Fill Method of SqlDataAdapter object
dataAdapter.Fill(EmployeeDataTable);
//Creating DataView instance using DataView constructor
DataView dataView1 = new DataView(EmployeeDataTable);
//You can see the new row in the Data View
Console.WriteLine($"DataView Data Before Delete:");
foreach (DataRowView rowView in dataView1)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Salary: {row["Salary"]}, Department: {row["Department"]}");
}
//If you want restrict the delete operation on a dataview then set AllowDelete property to false
dataView1.AllowDelete = true;
//Deleting IT Department employees from the Data View
foreach (DataRowView rowView in dataView1)
{
if (Convert.ToString(rowView["Department"]) == "IT")
{
rowView.Delete();
}
}
//You can also delete Individual DataView Row as follow
//Here, Index position 0 specify the first row
//dataView1[0].Delete();
Console.WriteLine($"\nDataView Data After Delete:");
foreach (DataRowView rowView in dataView1)
{
DataRow row = rowView.Row;
Console.WriteLine($"Id: {row["Id"]}, Name: {row["Name"]}, Salary: {row["Salary"]}, Department: {row["Department"]}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}
Output:

ADO.NET DataView in C# with Examples

Note: Whenever we are adding a new row in a data view, then those are not going to be added to the data table. But if we are updating the data view, then those updates are going to be reflected in the data table provided that row exists in the data table. If you are deleting a row from the data views, then you cannot access those deleted rows from the data table, in that case, either you need to call the AcceptChanges method or RejectChanges method on the data table object.

In the next article, I am going to discuss the Architecture of DataSet in .NET with Examples. Here, in this article, I try to explain ADO.NET DataView Class in C# with Examples. I hope you enjoy this ADO.NET DataView Class in C# with Examples article.