ADO - SqlDataView Class

 the details of the ADO.NET DataView class in C# and explore various operations such as creating a DataView, displaying, sorting, filtering, updating rows, and deleting rows:

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

// Assuming you have a populated DataTable named "myDataTable"
DataTable myDataTable = new DataTable();
// Creating a DataView instance
DataView dataView = new DataView(myDataTable);
// Displaying the DataView
foreach (DataRowView rowView in dataView)
{
    foreach (DataColumn column in myDataTable.Columns)
    {
        Console.WriteLine($"{column.ColumnName}: {rowView[column]}");
    }
    Console.WriteLine();
}

Explanation:

First, we assume that you have a populated DataTable named "myDataTable".

Then, we create a new instance of the DataView class by passing the DataTable as a parameter to the constructor.

To display the DataView, we iterate through each DataRowView object in the DataView using a foreach loop.

Within the loop, we iterate through each DataColumn in the DataTable and access the corresponding values using the indexer rowView[column].

We display the column name and its corresponding value for each row in the DataView.

Creating ADO.NET DataView Instance in C#:

DataTable myDataTable = new DataTable();
// Populate the DataTable...
DataView dataView = new DataView(myDataTable);

To create a DataView instance, you need to pass a populated DataTable to the DataView constructor. The DataView provides a dynamic view of the data in the DataTable, allowing you to apply sorting, filtering, and other operations.

Displaying a DataView in C#:

foreach (DataRowView rowView in dataView)
{
    foreach (DataColumn column in myDataTable.Columns)
    {
        Console.WriteLine($"{column.ColumnName}: {rowView[column]}");
    }
    Console.WriteLine();
}

To display the DataView, you can use a foreach loop to iterate through each DataRowView object in the DataView. Within the loop, you can access the column values using the indexer rowView[column] and display the data as desired.

Sorting ADO.NET DataView in C#:

dataView.Sort = "ColumnName ASC"; // Sorting in ascending order
// Or
dataView.Sort = "ColumnName DESC"; // Sorting in descending order

To sort a DataView, you can set the Sort property to a column name followed by the sorting direction keyword ("ASC" for ascending or "DESC" for descending). This will reorder the rows in the DataView based on the specified column and sorting order.

Filtering ADO.NET DataView in C# with Examples:

dataView.RowFilter = "ColumnName = 'Value'"; // Filtering with a specific value
// Or
dataView.RowFilter = "ColumnName LIKE 'Value%'"; // Filtering with a value starting with "Value"
// Or
dataView.RowFilter = "ColumnName IS NOT NULL"; // Filtering with a non-null value

To filter a DataView, you can set the RowFilter property to a filter expression. The filter expression can include column names, comparison operators, and values. You can filter based on specific values, patterns, or conditions.

Updating Row in ADO.NET DataView with Examples:

DataRowView rowView = dataView[0]; // Accessing a specific row in the DataView
rowView.BeginEdit();
rowView["ColumnName"] = "New Value";
rowView.EndEdit();

To update a row in a DataView, you can access the DataRowView object representing the row and modify the column values using the indexer rowView[column]. Before making changes, you should call the BeginEdit() method, and after making changes, you should call the EndEdit() method to save the changes.

Deleting Row from ADO.NET DataView using C#:

DataRowView rowView = dataView[0]; // Accessing a specific row in the DataView
rowView.Delete();

To delete a row from a DataView, you can access the DataRowView object representing the row and call the Delete() method. The row will be marked for deletion, and you can apply the changes to the underlying DataTable using the appropriate data adapter or by calling the Update() method.

The ADO.NET DataView class provides powerful functionality for managing and manipulating views of data from a DataTable. It enables sorting, filtering, and updating data, allowing you to work with specific subsets of data and apply changes as needed.

using System;
using System.Data;
class Program
{
    static void Main()
    {
        // Creating a DataTable and populating it with sample data
        DataTable dataTable = new DataTable("Employees");
        dataTable.Columns.Add("ID", typeof(int));
        dataTable.Columns.Add("Name", typeof(string));
        dataTable.Columns.Add("Department", typeof(string));
        
        dataTable.Rows.Add(1, "John Doe", "Sales");
        dataTable.Rows.Add(2, "Jane Smith", "Marketing");
        dataTable.Rows.Add(3, "Bob Johnson", "Finance");
        dataTable.Rows.Add(4, "Alice Adams", "HR");
        // Creating a DataView instance
        DataView dataView = new DataView(dataTable);
        
        // Displaying the original data
        Console.WriteLine("Original Data:");
        DisplayData(dataView);
        
        // Sorting the DataView by Name in ascending order
        dataView.Sort = "Name ASC";
        Console.WriteLine("\nSorted Data (by Name in ascending order):");
        DisplayData(dataView);
        
        // Filtering the DataView to show only the employees in the Sales department
        dataView.RowFilter = "Department = 'Sales'";
        Console.WriteLine("\nFiltered Data (Sales Department Only):");
        DisplayData(dataView);
        
        // Updating the Name of the first employee in the DataView
        DataRowView firstRow = dataView[0];
        firstRow.BeginEdit();
        firstRow["Name"] = "John Smith";
        firstRow.EndEdit();
        
        Console.WriteLine("\nData after Updating the Name:");
        DisplayData(dataView);
        
        // Deleting the second employee from the DataView
        DataRowView secondRow = dataView[1];
        secondRow.Delete();
        
        Console.WriteLine("\nData after Deleting a Row:");
        DisplayData(dataView);
    }
    static void DisplayData(DataView dataView)
    {
        foreach (DataRowView rowView in dataView)
        {
            Console.WriteLine($"ID: {rowView["ID"]}, Name: {rowView["Name"]}, Department: {rowView["Department"]}");
        }
        Console.WriteLine();
    }
}

Explanation:

We start by creating a DataTable named "Employees" and adding three columns: ID, Name, and Department.

We populate the DataTable with sample data, representing employee records.

Next, we create a DataView instance by passing the DataTable to the DataView constructor. This allows us to work with a dynamic view of the data in the DataTable.

We display the original data in the DataView by calling the DisplayData() method, which iterates over each DataRowView object in the DataView and prints the values of the ID, Name, and Department columns.

After that, we sort the DataView by the Name column in ascending order using the Sort property.

We display the sorted data by calling the DisplayData() method again.

Then, we filter the DataView to show only the employees in the Sales department. We set the RowFilter property to the filter expression "Department = 'Sales'".

We display the filtered data using the DisplayData() method.

Next, we update the Name of the first employee in the DataView. We access the DataRowView object representing the first row, call the BeginEdit() method to start editing, modify the value of the Name column, and then call the EndEdit() method to save the changes.

We display the data after the update using the DisplayData() method.

Finally, we delete the second employee from the DataView by accessing the DataRowView object representing the second row and calling the Delete() method. The row is marked for deletion, and the changes can be applied to the underlying DataTable using the appropriate data adapter or by calling the Update() method.

We display the data after the deletion using the DisplayData() method.