ADO.NET DataViews
Part II
I think what gives a lot of new ADO.NET developers so much trouble is that they don't really have a reference to work from. ADO gave us connected recordsets and was the Ultimate Black Box. More than any other technology out there, many 'gurus' of ADO had no clue what was going on behind the scenes. If you think about ADO.NET from the perspective of a DBA vs. a Developer, it is much easier to understand conceptually. Think of the System.Data.DataSet object as the rough analogy of a Database. Think of the System.Data.DataTable as the approximate equivalent of a Table in a Database. One or more System.Data.DataTable object comprises a System.Data.DataSet object. The System.Data.DataTable objects are comprised of System.Data.DataColumn objects and System.Data.DataRow objects. System.Data.DataColumn objects correspond to Fields in a typical Database table. (I think you got the idea by now where everything is derived from) DataColumn objects have all the goodies that you expect in a commercial database system like Oralce, Microsoft SQL Server, or IBM DB2. You can add autoincrement fields, you can set the initial seed and the increment value, you can define them as Unique and much more. As such, you can add DataRelations which serve as a Primary/Foreign Key construct. This in turn enables you to enforce Primary Key constraints (see the links at the end of this document for in depth discussions on why all tables should have Primary Keys and miscellaneous Database Design Concerns). There are no client side equivalents of Stored Procedures or Triggers, but there is an equivalent to the View...amazingly enough, System.Data.DataView .
Enough background, let's get into something useful. Ok, one last piece of background information. Just as Views in most commercial RDBMS systems are virual tables based on real tables, System.Data.DataView objects are virutal tables based on DataTable objects. Now, assuming that you are a ADO.NET programmer or your Database administrator is doing his job, all of your tables will have a Primary Key. This makes searching much much easier. Instead of iterating through your DataTable 's rows collection or making unnecessary trips to the Database, you can 'find' records with the DataTable 's System.Data.DataTable.Find() method. Assuming we already called out DataAdapter to fill our DataTable, this is typically how you'd .Find something easily< please note that I ALWAYS use numeric indices in my code. This is not some quirk of mine...there is a sound reason for using this that I'll get to later>:
VB.NET
myDataTable.PrimaryKey = New DataColumn() {tbl.Columns(0))} 'Where 0 is the index of the column that's your key
Dim dro as DataRow = myDataTable.Rows.Find("ADO.NET")
C#
myDataTable.PrimaryKey = new DataColumn[] {myDataTable.Columns[0]};
DataRow dro = myDataTable.Rows.Find("ADO.NET");
|
Ok, that's really easy isn't it? Yes, but it's also quite limited. Why? Because this only provides the ability to .Find by the Primary Key field. Even if you are a E.F. Codd fanatic like I am, you'll probably have more than one column in most of your tables, and you'll probably need to do some sort of .Find on one of those other columns.
Now, we could take the hard way out and iterate through the table until we find our desired condition(s). Well, that's why I'm writing this article. .NET programming, and ADO.NET in particular works best when you leverage it's power to your advantage, not when you try to recreate it.
So what do you do if you need a more advanced find, or a find on a field that isn't a Primary Key? Well, we'll get to that in a minute. For now, I want to show you another trick. Most professional grade database tables not only have Primary Keys, but often these keys are composite keys. You can leverage the .Find method on any of the columns. So let's say that we have a composite key composed of SSN, and DOB in myTable
using the same code as before...
Visual Basic .NET
Dim dro() as DataRow = myDataTable.Select("SSN = '100000000' AND DOB < '08/06/1980'")<BR>
'We may have multiple finds, so we use an array of DataRows
C#
DataRow dro[] = myDataTable.Select("SSN = '100000000' AND DOB < '08//06//1980'")<BR>
|
Now, another cool trick is that ADO.NET supports Wildcards with a syntax identical to ANSI SQL . For instance, if you wanted to find all Social Security Numbers that began with 10000 you could change your criteria to 10000% like this <pardon the pun>.
But still, DataTable objects only go so far, and to me, they aren't nearly as cool as DataView objects, although my beloved DataView wouldn't be worth much without DataTable objects.
Now, one last thing before we finally move on to DataView objects. Sorting is very easy in a DataView, but it's also pretty easy in a DataTable. The SELECT method can take a second parameter, and that parameter is the Sort Order. So in our previous examples with the SELECT method, we could simply add a comma and 'Order By Whatever ASC or Desc'.
As much as I like DataTable objects, one thing really grates at me. You can bind a grid or other control to a DataTable but you have to bind the entire table to it. So if I had a grid with a Windows.Forms.ComboBox above it that allowed the user to select a filter criteria like Last_Name, and I filter only records which match my last name 'Ryan' I can't bind my grid to this. At best, I can sort my grid and have things appear at the relative top of bottom of the grid, but let's face it, that's totally lame. But it's more than lame. It's totally lame if you are on the desktop. Sure, you have tons of RAM and fast processors not to mention a killer pipe on your intranet so you can requery the Database over and over and no one will know but your DBA. But on the Web or a PDA, this is catastrophic. Keep firing enough queries and make your user wait....don't worry, you won't have to deal with them for long. Any bad habits you have querying your database will make itself known very quickly. With that said, let's look at the DataView and what it can do for us <Hint: We can bind to it!>
The first thing you need to make a DataView is a DataTable. After you fill your DataTable, create a DataView and set it to the DataTable.
Visual Basic .NET
Dim dv as New DataView
dv = myDataTable.DefaultView
C#
DataView dv = new DataView();
dv = myDataTable.DefaultView; |
That's it!
So let's do something cool with it. First we'll look at finding (I already showed you how to filter) things. The first thing you need to do after you have your view is to specify a Sort Column. The Sort Column needs to be specified because behind the scenes, this is how the DataView to apply its .Find method to.
So first, let's say we have a column named Last_Name and we want to find really elite ADO.NET programmers. The best way to go about that, other than looking for the last names Vaugh or Sceppa would be to search for Ryan.
'Assuming the above declarations are still in place...
Visual Basic .NET
dv.Sort = "Last_Name"
Dim x as Integer = dv.Find("Ryan")
'x represents an integer and as you probably guessed, -1 means we 'didn't find anything. Any non-negative number 'indicates a hit. Just 'as IndexOf in a String object returns the first occurrence of a match, 'Find does the same. However, if you have multiple matches, you'll 'need to get a little more sophisticated.
If x = -1 Then
'Do Nothing, nothing was found
Else
SomeDataGrid.DataSource = dv
End If
|
'This will fill the grid with one row if we find the last name of Ryan
C#
dv.Sort = "Last_Name";
int x = dv.Find("Ryan");
if(x = -1){
//Do Nothing}
else{
dg.DataSource = dv;
} |
//Ditto
Now, let's say you want to find every instance of Ryan. Perhaps you were looking for great ADO.NET programmers, a second rate NFL football coach, a Governer, and a home builder. In this instance, you can't use the .Find method because you want all instances, not just one instance. So now you declare a DataRowView and use the .FindRows() method.
Visual Basic .NET
dv.Sort = "Last_Name"
Dim dRows as DataRowView() = dv.FindRows("Ryan")
C#
dv.Sort = "Last_Name";
DataRowView[] dRows = dv.FindRows("Ryan"); |
Now, you can still bind dRows to the DataGrid which means you can use advanced filtering without going back to the DataBase.
There is certianly a lot more to ADO.NET then what was discussed here. The Dataview is just one of many objects that you will
no doubt want to include in your Developers toolbox. This should serve as a basic introduction, but I encourage you to read further
and learn as much as you can.
I can be reached at dotnetguru@comcast.net or bill@knowdotnet.com
Links:
http://www.sql-server-performance.com/q&a71.asp
http://www.csus.edu/ac/acdba/sqlstds.htm
http://www.sqlmag.com
http://www.amazon.com/exec/obidos/tg/detail/-/0735614237/qid=1067011716/sr=8-1/ref=sr_8_1/102-3188683-1796953?v=glance&n=507846
http://www.amazon.com/exec/obidos/tg/detail/-/1590590120/qid=1067011779/sr=1-15/ref=sr_1_15/102-3188683-1796953?v=glance&s=books
http://www.amazon.com/exec/obidos/tg/detail/-/1930110294/qid=1067011779/sr=1-20/ref=sr_1_20/102-3188683-1796953?v=glance&s=books