Post
Ask Question

DataSets in Microsoft .NET - Part 2

Introduction  

 
In the previous part of this article, we discussed different features and how to create a dataset in design time lets go further in detail. In this part, I will discuss sort, search, and filtering support provided by dataset and data table object.
 
Search and Filter features of DataTable object
 
The DataTable object exposes two methods that you can use 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
  1. Dim strConnectionString, strQueryString As String  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;"  
  3. strQueryString = "SELECT CustomerID, CompanyName, ContactName, Phone " & _ "FROM Customers"  
  4. Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)  
  5. Dim tblDataTable As New DataTable  
  6. daDataAdapter.Fill(tblDataTable)  
  7. tblDataTable.PrimaryKey = New DataColumn() { tblDataTable.Columns("CustomerID") }  
  8. Dim row As DataRow = tblDataTable.Rows.Find("BERGS")  
  9. If row Is Nothing Then  
  10. MessageBox.Show("Row not found!")  
  11. Else  
  12. MessageBox.Show(row("CompanyName").ToString())  
  13. End If 
Using C# .NET
  1. string strConnectionString, strQueryString;  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +  
  3.      "Initial Catalog=Northwind;Trusted_Connection=Yes;";  
  4. strQueryString = "SELECT CustomerID, CompanyName, ContactName, Phone " +  
  5.      "FROM Customers";  
  6. OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);  
  7. DataTable tblDataTable = new DataTable();  
  8. daDataAdapter.Fill(tblDataTable);  
  9. tblDataTable.PrimaryKey = new DataColumn[] { tblDataTable.Columns["CustomerID"] };  
  10. DataRow row = tblDataTable.Rows.Find("BERGS");  
  11. if (row == null)  
  12.      MessageBox.Show("Row not found!");  
  13. else  
  14.      MessageBox.Show(row["CompanyName"].ToString()); 
If the DataTable has multiple columns forming the Primary key you can use the overloaded method of the Find as depicted below.
 
datasets1.gif
 

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
  1. Dim strConnectionString, strQueryString As String  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;"  
  3. strQueryString = "SELECT CustomerID, CompanyName, ContactName, " & _ "Phone, City, Country FROM Customers"  
  4. Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)  
  5. Dim tblDataTable As New DataTable  
  6. daDataAdapter.Fill(tblDataTable)  
  7. Dim foundRows As DataRow()  
  8. Dim row As DataRow  
  9. foundRows = tblDataTable.Select("Country = 'USA' AND City <> San Francisco'")  
  10. For Each row In foundRows  
  11. Console.WriteLine(row("CompanyName").ToString() & " - " & row("City").ToString() & " - " & row("Country").ToString()) Next row 
Using C# .NET
  1. string strConnectionString, strQueryString;  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +  
  3.      "Initial Catalog=Northwind;Trusted_Connection=Yes;";  
  4. strQueryString = "SELECT CustomerID, CompanyName, ContactName, " +  
  5.      "Phone, City, Country FROM Customers";  
  6. OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);  
  7. DataTable tblDataTable = new DataTable();  
  8. daDataAdapter.Fill(tblDataTable);  
  9. DataRow[] foundRows = tblDataTable.Select("Country = 'USA' AND City <> San Francisco'");  
  10. foreach(DataRow row in foundRows)  
  11. MessageBox.Show(row["CustomerID"].ToString() + " - " + row["ContactName"].ToString() + " - " + row["Country"].ToString()); 
Different overloaded methods are also available in the Select statement all 4 overloads are depicted in the example below:
 
datasets2.gif
 
Select also allows you to search on a wild card basis but however, it does not support wildcard searches like? or. That means whatever you can place in an SQLQuery after the WHERE clause can be used as a Filter string to Filter the Data in the DataTable.
 
See the example below.
datasets3.gif
 
Search and Filter features of DataView object
 
The DataTable object's Select method is powerful and flexible, but it has two major limitations.
  1. As 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
  1. Dim strConnectionString, strQueryString As String  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;"  
  3. strQueryString = "SELECT CustomerID, CompanyName, ContactName, " & _ "Phone, City, Country FROM Customers"  
  4. Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)  
  5. Dim tblDataTable As New DataTable("Customer")  
  6. daDataAdapter.Fill(tblDataTable)  
  7. Dim dvDataView As DataView  
  8. dvDataView = New DataView  
  9. dvDataView.Table = tblDataTable  
  10. dvDataView = New DataView(tblDataTable) 
Using C#.NET
  1. string strConnectionString, strQueryString;  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +  
  3.      "Initial Catalog=Northwind;Trusted_Connection=Yes;";  
  4. strQueryString = "SELECT CustomerID, CompanyName, ContactName, " +  
  5.      "Phone, City, Country FROM Customers";  
  6. OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);  
  7. DataTable tblDataTable = new DataTable("Customer");  
  8. daDataAdapter.Fill(tblDataTable);  
  9. DataView dvDataView;  
  10. dvDataView = new DataView();  
  11. dvDataView.Table = tblDataTable;  
  12. dvDataView = new DataView(tblDataTable); 
However you may notice that you declare a table without name then you will receive following Error in a windows application :
 
datasets4.gif
 
So when you want to use a DataView Table should have the Name.
 
We can easily find out the row state by using the enumeration DataViewRowState it has 8 values as shown below.
 
datasets5.gif
 
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, the Find method returns a value of -1.
 
Using VB.NET
  1. Dim strConnectionString, strQueryString As String  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" & _ "Initial Catalog=Northwind;Trusted_Connection=Yes;"  
  3. strQueryString = "SELECT CustomerID, CompanyName, ContactName, " & _ "Phone, City, Country FROM Customers"  
  4. Dim daDataAdapter As New OleDbDataAdapter(strQueryString, strConnectionString)  
  5. Dim tblDataTable As New DataTable("Customer")  
  6. daDataAdapter.Fill(tblDataTable)  
  7. Dim dvDataView As DataView  
  8. dvDataView = New DataView  
  9. dvDataView.Table = tblDataTable  
  10. dvDataView = New DataView(tblDataTable)  
  11. dvDataView.Sort = "CustomerID"  
  12. Dim foundOrNot As Integer = dvDataView.Find("BERGS")  
  13. If intIndex = -1 Then  
  14. MessageBox.Show("Row not Found")  
  15. Else  
  16. MessageBox.Show(dvDataView(foundOrNot)("ContactName").ToString())  
  17. End If 
Using C#.NET
  1. string strConnectionString, strQueryString;  
  2. strConnectionString = "Provider=SQLOLEDB;Data Source=local;" +  
  3.      "Initial Catalog=Northwind;Trusted_Connection=Yes;";  
  4. strQueryString = "SELECT CustomerID, CompanyName, ContactName, " +  
  5.      "Phone, City, Country FROM Customers";  
  6. OleDbDataAdapter daDataAdapter = new OleDbDataAdapter(strQueryString, strConnectionString);  
  7. DataTable tblDataTable = new DataTable("Customer");  
  8. daDataAdapter.Fill(tblDataTable);  
  9. DataView dvDataView;  
  10. dvDataView = new DataView();  
  11. dvDataView.Table = tblDataTable;  
  12. dvDataView = new DataView(tblDataTable);  
  13. dvDataView.Sort = "CustomerID";  
  14. int foundOrNot = dvDataView.Find("BERGS");  
  15. if (foundOrNot == -1)  
  16.      MessageBox.Show("Row not Found");  
  17. 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 below 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.
  1. int foundOrNot = dvDataView.Find("BERGS");  
  2. if (foundOrNot == -1)  
  3.      MessageBox.Show("Row not Found");  
  4. else MessageBox.Show(dvDataView[foundOrNot]["ContactName"].ToString()); 
datasets6.gif
 

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 is 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 according to the primary key of the DataView object's DataTable. If you set ApplyDefaultSort to True, 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 whose primary key is the combination of the OrderID and ProductID columns, 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, which 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 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 Listed 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, 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, which 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 also easing the Coding.


Similar Articles