DataSets in Microsoft .NET - Part 2

In the previous part of this article we explained various features and how to create a dataset at design time. Let's go into further details. In this part I explain sorting, searching and filtering support provided by dataset and data table objects.

Search and Filter features of DataTable object

The DataTable object exposes two methods to locate data based on search criteria. One method, Find, lets you locate a row based on its primary key values. The other, Select, acts as more of a filter, returning multiple rows of data based on more flexible search criteria.

Find or Select a DataRow using Primary Key value

We can find a DataRow in a DataTable based on the row's primary key values.

Using Find

Although the Find method is designed for DataTable objects, it's actually exposed by the DataRowCollection class. The Find method accepts an object that contains the primary key value for the row you want to locate. Because primary key values are unique, the Find method can return at most one DataRow. The following code snippet attempts to locate a customer row by its primary key value and then determines whether the search located a row.

Using VB.NET

Dim strConnectionString, strQueryString As String
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strQueryString = "SELECT CustomerID, CompanyName, ContactName, Phone " & _
"FROM Customers"
Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)
Dim tblDataTable As New DataTable
daDataAdapter.Fill(tblDataTable)
tblDataTable.PrimaryKey = New DataColumn() {tblDataTable.Columns("CustomerID")}
Dim row As DataRow = tblDataTable.Rows.Find("BERGS")
If row Is Nothing Then
MessageBox.Show("Row not found!")
Else
MessageBox.Show(row("CompanyName").ToString())
End If

Using C# .NET

string strConnectionString, strQueryString;
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strQueryString = "SELECT CustomerID, CompanyName, ContactName, Phone " +
"FROM Customers";
OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);
DataTable tblDataTable = new DataTable();
daDataAdapter.Fill(tblDataTable);
tblDataTable.PrimaryKey = new DataColumn[] {tblDataTable.Columns["CustomerID"]};
DataRow row = tblDataTable.Rows.Find("BERGS");
if (row == null)
MessageBox.Show ("Row not found!");
else
MessageBox.Show (row["CompanyName"].ToString());

If the DataTable has multiple columns forming the Primary key then you can use the overloaded method of the Find as depicted below.

Using Select

We can use the DataTable object's Select method to locate rows based on similar criteria. If you have a DataTable filled with Customer Data and you want to select Customers based on certain criteria complex or simple it is better to use the Select statement.

Using VB.NET

Dim strConnectionString, strQueryString As String
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strQueryString = "SELECT CustomerID, CompanyName, ContactName, " & _
"Phone, City, Country FROM Customers"
Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)
Dim tblDataTable As New DataTable
daDataAdapter.Fill(tblDataTable)
Dim foundRows As DataRow()
Dim row As DataRow
foundRows = tblDataTable.Select("Country = 'USA' AND City <> San Francisco'")
For Each row In foundRows
Console.WriteLine(row("CompanyName").ToString() & " - " & row("City").ToString() & " - " & row("Country").ToString())Next row

Using C# .NET

string strConnectionString, strQueryString;
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strQueryString = "SELECT CustomerID, CompanyName, ContactName, " +
"Phone, City, Country FROM Customers";
OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);
DataTable tblDataTable = new DataTable();
daDataAdapter.Fill(tblDataTable);
DataRow[] foundRows = tblDataTable.Select("Country = 'USA' AND City <> San Francisco'");
foreach (DataRow row in foundRows)
MessageBox.Show(row["CustomerID"].ToString() + " - " + row["ContactName"].ToString()+" - " + row["Country"].ToString());

Various overloaded methods are also available in the Select statement; all 4 overloads are depicted in the example below:

Select also allows you to search with a wild card but it does not support wildcard searches like ? or .. That means that whatever you can place in a SQLQuery after the WHERE clause can be used as a Filter string to Filter the Data in the DataTable.
See the example below.

Search and Filter features of DataView object

The DataTable object's Select method is powerful and flexible, but it has the following two major limitations:

1. Since it accepts such dynamic search criteria, it's not terribly efficient. 
2. Secondly, neither Windows nor Web forms support binding to the Select method's return value, an array of DataRow objects.

Generally we tend to think of DataTable as the table and DataView as the view of our SQL Database. That's not exactly what DataView is but there are some similarities between DataView objects and views in a database, they are not as closely related as DataTable objects and tables in a database are.
The DataView object does not maintain its own copy of data. When you access data through a DataView, the DataView returns data stored in the corresponding DataTable. Views in a database behave the same way. When you query a view, the database returns data from the table or tables referenced in the view.

Creating a DataView

Using VB.NET

Dim strConnectionString, strQueryString As String
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strQueryString = "SELECT CustomerID, CompanyName, ContactName, " & _
"Phone, City, Country FROM Customers"
Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)
Dim tblDataTable As New DataTable("Customer")
daDataAdapter.Fill(tblDataTable)
Dim dvDataView As DataView
dvDataView = New DataView
dvDataView.Table = tblDataTable
dvDataView = New DataView(tblDataTable)

Using C#.NET

string strConnectionString, strQueryString;
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strQueryString = "SELECT CustomerID, CompanyName, ContactName, " +
"Phone, City, Country FROM Customers";
OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);
DataTable tblDataTable = new DataTable("Customer");
daDataAdapter.Fill(tblDataTable);
DataView dvDataView;
dvDataView = new DataView();
dvDataView.Table = tblDataTable;
dvDataView = new DataView(tblDataTable);

However you may notice that if you declare a table without a name then you will receive the following Error in a Windows application:

So when you want to use a DataView Table you should have the Name.
We can easily determine the row state using the enumeration DataViewRowState; it has 8 values as shown below.

Using these values we can filter the data while showing it to the user.

Using Find

Once you've set the Sort property on a DataView object, you can call its Find method to locate a row based on the columns specified in the Sort property. As with the Find method of the DataRowCollection object, you can supply a single value or an array of values.

The DataView object's Find method does not, however, return a DataRow or a DataRowView object. Instead, it returns an integer value that corresponds to the index of the desired row in the DataView. If the DataView cannot locate the desired row then the Find method returns a value of -1.

Using VB.NET

Dim strConnectionString, strQueryString As String
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strQueryString = "SELECT CustomerID, CompanyName, ContactName, " & _
"Phone, City, Country FROM Customers"
Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)
Dim tblDataTable As New DataTable("Customer")
daDataAdapter.Fill(tblDataTable)
Dim dvDataView As DataView
dvDataView = New DataView
dvDataView.Table = tblDataTable
dvDataView = New DataView(tblDataTable)
dvDataView.Sort = "CustomerID"
Dim foundOrNot As Integer = dvDataView.Find("BERGS")
If intIndex = -1 Then
MessageBox.Show("Row not Found")
Else
MessageBox.Show(dvDataView(foundOrNot)("ContactName").ToString())
End If

Using C#.NET

string strConnectionString, strQueryString;
strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strQueryString = "SELECT CustomerID, CompanyName, ContactName, " +
"Phone, City, Country FROM Customers";
OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);
DataTable tblDataTable = new DataTable("Customer");
daDataAdapter.Fill(tblDataTable);
DataView dvDataView;
dvDataView = new DataView();
dvDataView.Table = tblDataTable;
dvDataView = new DataView(tblDataTable);
dvDataView.Sort = "CustomerID";
int foundOrNot = dvDataView.Find("BERGS");
if(foundOrNot == -1)
MessageBox.Show("Row not Found");
else MessageBox.Show(dvDataView[foundOrNot]["ContactName"].ToString());

Creating a new DataView using Designer and setting its properties

You can add a new DataView object to your designer by dragging the DataView item from the Data tab of the Visual Studio .NET Toolbox and dropping the item onto the designer or the component tray. You can also simply double-click the DataView item in the Toolbox.
Once you've created your new DataView object, you'll want to set a few of its properties. Visual Studio .NET simplifies this process. As you can see in the following figure, you can use the Properties window to select an available DataTable. You can also set other available properties of the DataView object, such as RowFilter, RowStateFilter, and Sort.

int foundOrNot = dvDataView.Find("BERGS");
if(foundOrNot == -1)
MessageBox.Show("Row not Found");
else MessageBox.Show(dvDataView[foundOrNot]["ContactName"].ToString());

AllowDelete, AllowEdit, and AllowNew Properties

DataView objects are often used in conjunction with bound controls. The AllowDelete, AllowEdit, and AllowNew properties simplify the process of restricting the types of changes that the user can make using the bound controls. Rather than setting properties on each of the bound controls, you can set these properties on just the DataView.

By default, each of these properties are set to True on the DataView object.

ApplyDefaultSort Property

The ApplyDefaultSort property is set to False by default. Setting it to True will sort the contents of the DataView depending on the primary key of the DataView object's DataTable. If you set ApplyDefaultSort to True then the DataView object's Sort property will be set to the columns in the DataTable object's primary key. For example, if a DataView is bound to a DataTable that contains order detail information and its primary key is the combination of the OrderID and ProductID columns then setting ApplyDefaultSort to True will implicitly set the Sort property of the DataView to OrderID, ProductID.

Count and Item Properties

The Item property returns a DataRowView object and is parameterized. When you call the Item property, you supply an integer that represents the row you want to retrieve. You can use the Count property to specify the number of rows visible through the DataView.

DataViewManager Property

If you created your DataView using the CreateDataView method of an instance of a DataViewManager object, the DataViewManager property will return the DataViewManager object that created the DataView. Otherwise, the property will return an uninitialized DataViewManager.

RowFilter Property

The RowFilter property is similar to a WHERE clause in a SQL query. Only rows that satisfy the criteria in the property are visible through the view. The default for the RowFilter property is an empty string.

RowStateFilter Property

The RowStateFilter property affects the data visible through a DataView in two ways. It filters rows based on their RowState, and it controls the version of the row that's visible through the DataView. The RowStateFilter property accepts values and combinations of values from the DataViewRowState enumeration, as described earlier in the chapter.

You can set the RowStateFilter property using the DataView object's constructor. The default value for the RowStateFilter property is CurrentRows, that causes the view to display the current version of all rows in the DataTable that satisfy the criteria specified in the DataView object's Sort property and are not marked as deleted.

Sort Property

The Sort property controls the sort order of data visible in the DataView; it works much like the ORDER BY clause in a SQL query. You can create a sort order based on a single column or a combination of columns. By default, the rows are sorted in ascending order. To sort columns in descending order, you add the keyword DESC after the column name. Remember to delimit your column name if it contains a nonalphanumeric character (such as a space) or if the column name is a reserved word.

Table Property

You use the DataView object's Table property to set or access the DataTable to which the DataView is bound. Changing the value of the Table property resets the RowFilter and RowStateFilter properties of the DataView to their respective default values.

Methods of DataView Object

The List of methods that can be performed on a DataView object are decribed below.

AddNew and Delete Methods

You can use the AddNew and Delete methods to add rows of data to and remove rows of data from the underlying DataTable. The AddNew method returns a new DataRowView object. Once you've set the values of the desired columns, you can call the DataRowView object's EndEdit method to add the row of data to the underlying DataTable.

You can use the Delete method to delete a row if you know the index of the row within the DataView. If you have a reference to the DataRow or the DataRowView, you can call the Delete method of the DataRow or DataRowView object instead. Remember that using the Delete method of any of these objects simply marks the row as deleted. To remove the row from the DataTable, you call the AcceptChanges method (of the DataRow or of the DataTable or DataSet that contains the row) or submit the change to your database using a DataAdapter.

BeginInit and EndInit Methods

If you want to change multiple properties of the DataView object but don't want the changes to affect the data visible through the DataView until you've changed all of the desired properties then you can use the BeginInit and EndInit methods.

CopyTo Method

The DataView object exposes a CopyTo method that behaves like the CopyTo method of the Array object. You can copy the DataRowView objects available through the DataView to an array using the CopyTo method.

Find and FindRows Methods

The DataView allows you to locate one or more rows of data using its Find and FindRows methods. Both methods are overloaded to accept a single value or an array of values. The DataView uses the values specified to search its contents based on the columns specified in the Sort property

GetEnumerator Method

The GetEnumerator method offers another way to view the contents of a DataView. It returns an instance of an IEnumerator object, that resides in the System.Collections namespace.

Thus in ADO.NET and in .Net distributed application development the Datasets and all its forms play a great role in improving Native Functionality, Maintenance, Serialization and also make the task of ease coding easier.