How can I do a "Join" between two DataTables?

It's tempting to think that with objects like “DataView” and methods like “Select” that DataSets would support this option, but the bottom line is that they don't. A DataView is a sorted and/or filtered list of rows from 1 table only. Also, the Select method is on the DataTable and as such - can't look at other tables.

There are a few options to consider.

  1. Do the join in SQL. SQL is good at this kind of operation. It would require you to have a new DataTable with the composed list of columns from the two tables - and a DataAdapter with at least a SelectCommand specified to support the fill. You would perform your join in the SelectCommand.CommandText. If you are also retrieving data into 2 separate DataTables for other reasons, then of course you have some redundant data and also some synchronization issues between the 2 individual tables and the joined table. The joined table will not reflect changes in the 2 base tables until you update the database and re-execute the Fill on the joined table.
  2. Like the above solution, you could create a 3rd table with the composed list of columns in your DataSet but instead of loading from the dataset, you could copy the data from the base tables yourself using the AddRow method. If the two base tables share the same primary key, you could try to “merge” the data from the two DataTable into the 3rd DataTable.
  3. If the two tables to be joined are in a master detail relationship you can use expression columns to lookup data in a parent DataTable or aggregate records from a Child Table. For example if you have a Customer DataTable with a stateId column, which is a foreign key to a State DataTable with stateId and stateName columns, you can add a computed DataColumn to the Customer DataTable with an expression of Parent.stateName. This new column will be kept in sync if the underlying name changes in the State DataTable or if the stateId is changed on the Customer DataTable to point to a different State. Similarly you can look up values on a child DataTable from a parent DataTable but since there can be 1 or more rows you will typically need aggregates like Sum, Avg, Min, Max, etc. in you expression. The DataColumn.Expression property online help is valuable for the types of expressions you can use.

How do I create a Crystal Report from a DataSet

http://www.tek-tips.com/gfaqs.cfm/pid/796/fid/3940

How can I improve the loading of my datasets?

Datasets maintain some internal indexes to improve performs for things like finds and selects.

When you are loading more than 1 row into a DataTable - with a DataAdapter.Fill or other technique, you can turn this index maintenance off by doing a

MyTable.BeginLoadData()

Where MyTable is the reference to your DataTable, which could be

MyDataSet.Tables[0].BeginLoadData()

When you are done loading the data, don't forget to turn the index maintenance back on with.

MyDataSet.Tables[0].EndLoadData()

When loading multiple tables, you should turn on/off 1 table at a time.

Another thing that Datasets do while having rows added to tables is to validate them. Things like primary key uniqueness, foreign key referential integrity and nulls in columns with AllowDBNull = false are some examples of things that must be checked. Again, you can save some cycles by turning this off during loading of a dataset and turning it back on afterward. This can be done with:

MyDataSet.EnforceConstraints = false

And of course when you are done loading, you can perform a:

MyDataSet.EnforceConstraints = true

Of course you may get a “ConstraintException” on this last line if there are problems with your data. Otherwise, you'd get this exception as soon as the offending row is loaded. On a related note, you can check DataSet.HasErrors and each DataTable.HasErrors for any errors. For each table you can call DataTable.GetErrors() to get a list of rows with errors. Each row has a RowError property that contains any error text related to the entire row, and also a GetColumnError() method that you can use to test each column for a column specific error message.

You know you're a geek when...

The other day while sitting on the couch eating some M&M's with my 5yr old daughter she asks me what “MGM” means....and starts to try to pronounce “megum“ - she hasn't quite got the idea of acronyms yet.

I explain how “&” means “And” and it's “M and M's”. Claire comes back at me with “well why didn't they just use plus (+)”. The next 10 minutes are me explaining concatenation to her and how it differs from addition. I'm pretty sure she's going to answer her kindergarten teacher's question of  “What is 4 AND 5” with “45” next week at school....”and that if you really were looking for 9 you should have asked properly 'what is 4 PLUS 5' not AND”. I can hardly wait for parent teacher interviews.

Hmmm, maybe I should teach her what “and” means in terms of boolean logic.

New Smart Client Reference Application - IssueVision

This is a new smart client reference application from Microsoft. Actually it was created by Vertigo for Microsoft - where Susan Warren now works (former Queen of ASP.NET). This is not a rewrite of TaskVision which is a common question. It was built to show off some advanced topics for Smart Client apps in conjunction with the recent DevDays events that have been going on in the U.S. but unfortunately haven't made it up to Canada due to some overloaded efforts going into VS Live.

You can download this from Microsoft although it's not the easiest thing to find.

Some of the interesting highlights:

  • focus on security....some wrapped up DPAPI classes.
  • Application Deployment and Updating

This app wasn't built with the recently released offline application block since the timing wasn't right - but nevertheless, a good fresh reference app worth looking at.

Building Maintainable Applications with Logging and Instrumentation

I'm doing this MSDN webcast in a few weeks

10/05/2004 1:00 PM - 10/05/2004 2:00 PM (EasternTime)

In this session we'll cover the world of logging and instrumenting your application. We'll discuss the various .NET framework components as well as higher level services as provided by the Exception Management Application Block, the Enterprise Instrumentation Framework and the Logging BLock. We'll discuss the various issues with persisting information in file logs, the event log, and WMI Performance Counters. We will also compare other alternative technologies such as log4net. We'll also discuss best practices for loging and instrumenting your application and provide some considerations for when and where it makes good sense to instrument your application from experiences in the field.

Update: The slides, samples and livemeeting recording links can all be found here.

Service Autonomy?

In a previous post, I discussed the impart of the first of the Four Basic Tenets of Service Orientation on the design of a service-based application.  In this post, I consider the second tenet in the same context. 

The second tenet says that services must be autonomous.  This is probably one of the more hotly debated tenets, in part because the definition of autonomy as it applies to services does not appear to be part of the common vernacular.

If you go back to the description of this tenet as written by Don Box here, it would appear that the definition of autonomy involves independence from the client.  Specifically, Don contrasts the autonomy (that is, the independence) of services with the interdependence of objects in a OO application.  In OO, the called object is inextricably linked to the calling object through the call stack.  If the calling object goes awry (a euphemism for death by exception), the called object goes away too.  Or at least doesn't have any place to send the result back to.

A service, on the other hand, has a life outside of the calling application.  If the calling application dies while the service is doing its think, the service will continue functioning properly.  In fact, one of the guarantees of autonomy (in this sense) is the ability for a service to be called aysynchronously with no expected return value.  In this manner, it can be guaranteed that the service remains independent of the calling client.

Another view, one espoused by Rich Turner here, is that the autonomy of services defines how a service interacts with other services.  A method of Service A is invoked.  That method in turn invokes a method on Service B.  Service A is autonomous if it is not hard wired to the location or implementation details of Service B.  So Service A calls Service B.  If Service B isn't where it is expected, then Service A goes through a number of steps to discover Service B's whereabouts.  At no point does Service A require any information from Service B or any one else to try and complete the invoked method.

I have a problem with this description.  Not with the concept being described (that is, that a service, where possible, should be self-healing), but that the word 'autonomous' is used to describe it.  There is a connotation associated with antonomy of being independent.  Indeed, a number of dictionaries include 'independent' as a synonym for the word (see dictionary.com). As Rich aptly points out, no useful service is truly independent. So I don't think that autonomous really describes the state of a service.

Now the question becomes what word or words would better describe the concepts that are being conveyed here.  My own personal choice would be 'sovereign'.  While it is listed as synonomous with autonomous and independent in a number of different references, the connotation is more appropriate for both of these points.  A soverign service has an expectation of a certain level of independence. A sovereign service would be expected to survive the failure of any client.  A sovereign service would be expected to locate and negotiate communication between it and any other soverign service. Maybe I'm being picky here, but I think that considering a service to be sovereign will better prepare an architect for service-orientation. The impact that this change in viewpoint has is the topic of my next post.

MSDN Subscriber Download RSS Feeds

Perhaps I'm not the only one to just learn about this recent feed a month old now I guess:

http://msdn.microsoft.com/subscriptions/rss.xml

My favourite item in that so far has to be the MS Bob 1.0a release that came on March 31st.

Upcoming UG Meetings

Kate Gregory is starting a new UG in the east end in Oshawa. They are meeting Apr 20. The topic is an overview presentation of .NET. gtaeast.torontoug.net to register.

The regular meeting of the Canadian Technology Triangle meets next Tuesday Apr 20th as well. This special meeting is part of the MSDN User Group tour and the topic is The .NET Compact Framework. A special note that this event is not in its usual location, but rather at the Peter Benninger Theatre.  cttdnug.org to register.

Things I don't like about NUnit

Firstly - I love NUnit - and nobody has done more to increase the quality of .NET Applications than the contributors to this project - nobody.

But I know that next generation Unit testing framework authors are listening so I might as well state the things I'd like to see:

  • I'd like to be able to run a single test from the command line. Not just a single fixture, but a specific test.
  • I'd like my tests to be parameterizable. I'd like from the Command Line to run a test and provide the specific values.
  • Wouldn't it be cool to create a batch file of scenarios? What about doing this in XML? Duh - no brainer.
  • A lot of my tests in this case would simply wrap up and call business objects - so why not be able to have a virtual test in the xml file? That is just call a class/method directly with XML.I'm not saying this is the be all / end all - really the only kinds of assertions I could do realistically would be to test for certain exceptions or no exceptions. Return values? Possibly - but really - some times our methods accept and return things other than value types - but this might be a nice thing to have regardless.

With these things in place, one could conceivably:

  • tie a class modelling tool into test scenarios. Hmm, I need a class, with a method and if I pass this data, I should get these results.
  • If an end user reports a bug in my defect tracking system, I should be able to create an NUnit test that exposes this bug. Then my support people can come back from time to time, have the defect tracking system run the test to see if it's been fixed in a given patch/release/build, etc. and update the status on the defect.