Efficient ADO - Part III by William Ryan

In the First and Second segments of this article, I walked you through doing many tasks with Cached data that used to be in the sphere of hitting the database.  This allows increased efficiency on both the client and server as well as the network.  In this segment, I'm going to walk through a few more such techniques.  Since every time I start writing I think of more to cover, I'm probably going to include a Part IV.

Keys

If you've worked with Relational Database technology for any amount of time, you've undoubtedly heard the adage "The Key, The Whole Key and Nothing but the Key".  It can be summarized like this "Make sure every table has a Key on it, and please spare me the nonsense about not needing a key...you are only displaying your lack on knowledge".

Ok, now that I've shown my partisan roots, how does that relate to ADO.NET.  Good Question.  When we are working with DataTable class or the DataSet Class, we are working with cached data.  The ultimate objective here is to do as much as possible without going back to the database.  If you are coming from previous versions of ADO, the best way to think of ADO.NET is that the more trips you have to make back to the database, the lamer you are.  I know, it sounds rough, but it's too easy to get lazy and rely on the database and I think I defended my position on this in my last two articles.  So, we have a local set of data but we want to keep it real with Dr. Codd.  How do we do it?

The first way is to manipulate the DataColumn class.   Your Datatable has a collection of Datacolumns and one (or more) of them corresponds to the Primary Key of your database table (assuming you obey Relational Database theory).  So, assuming it was a Column 0 of your table, you could set the Unique property to true.  This will ensure that only one instance of a value will occur in that column in a given datatable.  Now, before I continue, let me emphasize something.  If you don't use  a Key client side, you are going to have to write a LOT more code and at best, you'll come up with an inferior solution, so let's not even go there.  So, you can set the Unique property to true and you've just made yourself a de facto primary key:

DataTable dt = new DataTable(); //Pretend we filled it with Data //And Column 0 corresponded to the postion //or the Primary Key in the Database table. dt.Columns[0].Unique = true;

This approach works great for many situations unless you have a real database and use Composite Key.
You can't set unique to true on all three columns because that's not what a composite key does.  Fortunately, there's a PrimaryKey  property of the datatable we can set.  Ok, so let's say that Columns 0, 1 and 2 are the PrimaryKey of the DataBase table.  Heres' how you handle it:

DataTable dt = new DataTable(); DataColumn[] dcs = new DataColumn[5]; DataColumn dc0 = new DataColumn(); DataColumn dc1 = new DataColumn(); DataColumn dc2 = new DataColumn(); DataColumn dc3 = new DataColumn(); DataColumn dc4 = new DataColumn(); dcs[0].DataType = System.Type.GetType("System.String"); dcs[1].DataType = System.Type.GetType("System.String"); dcs[2].DataType = System.Type.GetType("System.String"); dcs[3].DataType = System.Type.GetType("System.String"); dcs[4].DataType = System.Type.GetType("System.String"); dcs[0] = dc0; dcs[1] = dc1; dcs[2] = dc2; dcs[3] = dc3; dcs[4] = dc4; dt.PrimaryKey = dcs;

Now, the combination or each of those fields comprises the composite key.

The only 'Gotcha' in the process is this, the PrimaryKey Property is an Array of DataColumns, even if that array only has one value in it.  You can't use this:

dt.PrimaryKey = dc0;

because dc0 is a DataColumn, not an array.

Errata

Ok, by now you should see that this is pretty easy to implement.  You also understand why. But there's a few things I haven't mentioned and I wasn't sure where they fit in.  Hence my errata section.

If you define a PrimaryKey, you can use the Find method of the DataTable's Rows collection.  This is just one of the many ways you can search for data, but it's cool.  Note that Find isn't a method of the DataTable either, it's a method of the DataRowCollection class.  The main thing that Find gives you is the ability to Find based on multiple columns.  So, assume the example above we have a gazillion rows, one of which has the following five values comprising the key:  

object[] searchValues = new object[5]; searchValues[0] = "ADO.NET"; searchValues[1] = "Is"; searchValues[2] = "Really"; searchValues[3] = "Reallly"; searchValues[4] = "Cool"; DataRow target; //Since a PrimaryKey value can only exist once in a DataTable, the Return                 //value can be just a DataRow target = dt.Rows.Find(searchValues);

If target is Null, then you didn't find the value, otherwise you did.  If you did the same with say a DataView's rowfilter, think of how much more verbose your code would be:

DataView.RowFilter = "Column1 = 'ADO.NET' AND Column2 = 'Is' AND Column3 = 'Really' AND Column4 = 'Really' and Column5 = 'Cool'";

Another thing worth mentioning is that just as the DataSet/DataTable exists locally and may or may not bear any resemblance to your actual backend database, the same holds PrimaryKey. I can make any Column(s) on the DataTable the PrimaryKey. So, if Column1 was the 'real' PrimaryKey in the backend db, I could still define the local key just as I did above. In most cases, it makes a lot of sense to model your key after the backend schema because you'll know about violations immediately as opposed to sending nonconforming data back to the db, only to have it reject it.

Another issue is the ForeignKeyConstraint class.  Just as you can define a PrimaryKey, you can define a ForeignKey.  This may or may not be necessary.  Why?  Well, it's hard to see a scenario where you'd have a PK/FK in place without a DataRelation object.  And if you set a DataRelation, this will add  a ForeignKeyConstraint to the Constraits collection for you.  

Yet another issue is Nulls.  Just as PrimaryKeys and ForeignKeys can't contain null values (remember, Null doesn't equal anything not even itself, so it'd be impossible to test if a null was unique) in a RDBMS, the same holds for a DataTable.  If you define a PK, then you have to set a value before adding the row.  You can use the AutoIncrement Property for instance, set a Default value, or just be careful and make sure you set the value before adding the row.  Note that in most instances, you won't get a PK violation if you don't specify a value.  Instead you'll get a NoNullExceptionAllowed.  This makes sense because by default, a PK column has its AllowDbNull property set to false.  Accordingly, this exception will be raised before you can violate the PK constraint because it's checked first.

Also remember that PK's, FK's and many others are members of the Constraints collection of the DataTable.  Since it's a collection, it should come as no surprise that you can have Multiple contraints in a given table.

Off of the top of my head, I can't think of anything that I've left out, but that doesn't mean a whole lot. I'd encourage you to start experimenting with these properties if you haven't already b/c they'll certianly help you write some more robust code.