Power of the DataView

The Power of the DataView

---

Introduction

Its hard to imagine what the creators of ADO.NET were thinking as they were designing it. It has been challenge enough just understanding and mastering all of the many objects and their capabilities. Just think, to physically access a column of data you have to now maneuver through at least five objects instead of two in ADO.

As part of my effort to learn ADO.NET, I decided to first determine how to reproduce the basic functionality that I had in ADO, especially with the Recordset. That is where I discovered the new and many uses of the DataView class (although I prefer the term object to class).

Think about the following code snippet:

objRS.Sort = "CompanyName" objRS.Filter = "Country = " & strCountry If Not objRS.EOF Then Do Until objRS.EOF ... ObjRS.MoveNext Loop End If objRS.Filter = 0 objRS.Sort = ""

If the Recordset was the order detail for a customer, the sort order was specified on OrderNumber followed by ProductName. Then we used the marvelous Filter method of the Recordset to create a view of only those records we wished to work with. After we were through, we removed both the sort and the filter to perform any other tasks that were necessary.

When I first started working with ADO.NET, I just couldnt seem to figure out how to easily reproduce this functionality. Sure, you can bind a DataSet to a grid with just two lines of code. But where were examples of how to perform tasks where I needed more than just databinding to a DataTable? Thats when I stumbled across the DataView class.

I like to use the following analogy. DataSets are very much like simple, stand-alone databases. They contain tables called DataTables with rows and columns. These tables can have relationships. They also contain views called DataViews. The DataView class is very similar to a database view that you would create in Oracle or SQL Server. You can use a DataView just about anywhere you can use a DataTable. Yet they contain some additional properties and methods that a DataTable does not.

Sorting, Finding and Filtering

Three of the most useful things that you can do with a DataView are sorting, finding specific rows and filtering unwanted records. If I could do these things with a view, then I could reproduce the code snippet above using ADO.NET:

Dim dv As DataView dv = New DataView(DataSet1.Tables("Customers")) dv.Sort = "CompanyName" dv.RowFilter = "Country = " & strCountry

At this point you can either Bind the view to one of the ASP.NET server side controls or you can process each row individually:

Dim i As Integer For i = 0 To dv.count - 1 ' perform your logic here Next

In ADO.NET, there is no MoveFirst, MoveNext, MovePrior or MoveLast. Nor is there EOF or BOF. The data is all referenced as an array. To see if data is present, just check the Count property of the DataView (no more EOF runtime errors!). Remember that arrays begin at 0 and not 1.

If you want to filter rows with null values, you must first convert the null values to something such as a string:

dv.RowFilter = "Isnull(Col1,'Null') = 'Null'"

Only the Tip of the Iceberg

DataViews are also useful for other things than just the functions that we just discussed. Think about this: Every table has at least one DataView, which is a view of itself. This is called the DefaultView. That means that if you reference a table by its DefaultView, it can do anything a DataView can do as well:

DataSet1.Tables(0).DefaultView.Sort = "CompanyName" DataSet1.Tables(0).DefaultView.RowFilter = "Country = " & strCountry MyDataGrid.DataSource = DataSet1.Tables(0).DefaultView MyDataGrid.DataBind()

Another great feature of DataViews is that you can create them with the RowFilter and Sort already specified:

Dim dv As New DataView(ds.Tables("Country"), _ "Country = 'UK'", _ "CompanyName", _ DataRowViewState.CurrentRows)

DataView Differences

Of course, the DataView is not a DataTable. The most obvious difference is that DataViews are comprised of DataRowViews where as a table contains DataRows. DataRowViews do not contain a Columns collection, but an Item property to reference the underlying column. And editing data in a DataRowView is also different. But there is one trick that you can play on the DataRowView. Just like every table has a DefaultDataView property, the DataRowView has a Row property that is an actual reference to the underlying DataRow:

Dim drv As DataRowView For Each drv In dv Response.Write(drv.Rows("CompanyName") & "<br>") Next drv

Conclusion

As you can see, this is just one of the many areas where ADO.NET differs dramatically from ADO. Now that Ive discovered the DataView, Ive been able to reproduce the functionality that was in my old ASP application in my new ASP.NET applications. And with a little more practice, I hope to even overcome obstacles that were present with the Recordset.