TableAdapter with multiple related DataTables

In my ongoing VB6-to-VS2005 project, we have started using .NET 2.0 TableAdapters in the data access layer instead of SqlDataReaders to get strongly typed parameters and data when using our stored procedures. The main reason for this is that the customer in the existing solution has change management problems due to the untyped nature of DataReaders and the "late bound" style of accessing column values using .GetString[columnIdx].

When the customer has to change the result set of a stored procedure, this type of data access provides no compile time verification when getting a column value, and introduced bugs will not show up until run-time. Using TableAdapters "can dramatically increase your development and stabilization phase productivity by leveraging compile time verification for column changes or additions" [VB.NET team]. Compile time catching of bugs is way better than run-time exceptions, logging and debugging efforts.

The downside is of course lesser performance as a TableAdapter uses the DataSet/DataTable classes, but the size of our result sets are generally small and should thus not incure too much overhead. In addition, the number of required stored procedure calls might go down when retrieving 1:M related data from several related tables (e.g. master-details, multi-select picklists, etc). More on this below.

Read more about using TableAdapters in the VB.NET team blog and at MSDN. What is not covered in details in these articles, however, is how to work with hierarchical data; i.e. how to use TableAdapters to work with a DataSet that contains several related DataTables (e.g. order+order lines, person+favorite colors+favorite cars, etc).

Before you start to working with TableAdapters, you should recognize that a TableAdapter is not a new class in the .NET 2.0 framework. A TableAdapter is just a 'code generated' class that internally uses the classic DataAdapter, DataSet and DataTable classes. The generated code provides you with a typed dataset based on the SQL query / stored procedure you specify in the TableAdapter wizard. The generated class may also provide you with insert, update, and delete methods (see details at MSDN2). Note that datatable must have a defined key column before the insert, update, and delete SQL statements can be generated.

There are two main ways to create a set of TableAdapters. Start by selecting 'Data-Data Sources' from the VS2005 main menu, this opens the 'Data Sources' explorer. Then either create a new data source and select multiple stored procedures in the checkbox list, this will generate one TableAdapter for each selected "sproc"; or open a dataset in designer mode, right click and select 'Add-TableAdapter', this will add another TableAdapter to your dataset. Both ways will create multiple DataTables within a DataSet, however without any DataRelations.

Use the dataset designer to add the relations you need to be able to navigate between the DataTables at run-time. Adding relations allows you e.g. to use .GetChildRows(relationName)to read data related to the current master record.

Note that the relations are added to the .Relations collection of the DataSet, not as .ChildRelations of the "parent" DataTable. You must use the .Fill() method of each TableAdapter to populate all the DataTables and their DataRelations at run-time. Note that the .GetData() method returns a single DataTable without populating any relations.

Using TableAdapters have allowed us to reduce the number of stored procedure calls (round-trips to SQL Server) dramatically. The SqlDataReader code first read all the master records, then did three extra stored procedure calls for each of the master records to get the 1:M related data. E.g. with 20 master records, 61 round-trips to the SQL Server was needed. With TableAdapters, only 4 stored procedure calls are needed to fill the four related DataTables. Reading data for all master records is now as simple as doing a foreach() on the root DataTable and using .GetChildRows(relationName)to get the related data. This reduction should gain most of the raw performance difference between DataReaders and TableAdapters, or even perform better.

How to update related DataTables using TableAdapters is documented here at MSDN2.

Rebuild VSTO-O add-in on .NET2.0 RC1

Last week our VSTO-O add-in would not load when installed on new client PCs. This was caused by the automatic download and installment of .NET2.0 RC1 performed by the VSTO setup project, while the add-in was built with .NET2.0 beta 2. A rebuild of the add-in and the setup project was needed to make things work. Thus, I had to uninstall all beta 2 stuff, including .NET, Visual Studio 2005, VSTO runtime, SQL Express, etc. After that, I installed VS2005 RC1 on my PC, which now includes the VSTO project templates by default.

The existing VSTO-O project compiled without problems in VS2005 RC1, so I didn't need to create a new project and move the code manually as I had to when going from VSTO alpha to VSTO beta. You might not be that lucky (see the article referenced below).

Some modifications was needed for the setup project. In beta 2, you had to manually add several VSTO assemblies (DLLs) and install them to your target folder. I removed all those VSTO assemblies, and did a 'refresh dependencies', which in RC1 is able to correctly detect and add the required VSTO assemblies to the setup kit.

The add-in would, however, still not load in Outlook. As I know the details on how to get a VSTO-O add-in to load, I quickly turned to the registry to check the CLSID InprocServer32 setting for my add-in. In VSTO-O beta 2, the add-in loader was named VSTAddin.DLL, while it in RC1 has got the documented, correct name AddinLoader.DLL. Use 'View-Registry' in the VS2005 setup project, navigate to HKCU\Software\Classes\CLSID\{guid}\InprocServer32\ key and change the name of the VSTO loader stored in the (default) setting.

With these changes to my setup project, the add-in now installs, loads and works correctly on RC1.

Note that modifying the setup project is not needed if you choose to start with a new VSTO-O project and move the code manually from the old beta 2 project.

Mads Nissen has posted an article that describes how to get your VSTO-O add-in to work with .NET2.0 RC1 and details about making a fully automated setup kit using a custom prerequisite with the setup project. He has also updated the CAS custom installer action to support uninstall.

.NET2.0 ObjectDataSource and BindingList<T> VS typed datasets

I am currently at a project where some VB6 and .NET1.1 WinForms applications are to be ported and upgraded to .NET2.0. The current architecture is layered and employs (custom) business entity objects modelled on the domain of the customer. The solution is distributed over several tiers, and the communications technology of the architecure (.NET remoting) is also up for review. In addition, it is a design goal to enable the solution to provide services and entity data to external systems, both within the company and to e.g. partners.

Yesterday some of the developers started to argue for replacing the existing business entity objects with typed datasets. Their main reason for this was to get "maximum" developer productivity through two-way WinForms data binding in the user controls. This is a typical way of thinking for developers that have worked mainly with databases and ASP.NET solutions. In addition, scrapping the domain entity objects would be a step in the wrong direction from 'domain driven design' (DDD) and from 'service orientation' enabling the solution. This discussion is not a new one, refer to this MSDN article by Dino Esposito for pros & cons of datasets vs business entity objects. I also recommend reading the articles referenced at the end of the 'Cutting Edge' article. Note how they all agree on that exposing datasets in a service is a bad idea.

.NET2.0 provides several new mechanisms aimed at bringing the ease of dataset binding to business entity objects, allowing the developers to use design time tools and wizards to bind their GUI to entity objects. The ObjectDataSource (BindingSource) and the generic BindingList<T> are the main enabling object binding mechanisms in .NET2.0. Note that it is possible to provide object binding also in .NET1.1 as shown in this MDSN article by Paul Ballard, but it will require more coding and will not give full design time support for setting up the binding.

What you will find out is that the BindingList<T> has some 'last mile' problems when compared to the binding mechanisms provided by the DataView/DataSet combination. In our prototype/spike the lack of built-in implementation for sorting and filtering immediately surfaced. These methods are defined in the IBindingList interface, but is not implemented by BindingList<T>. A bit of googling led me to this blog entry and this GotDotNet workspace. Andrew Davey's BindingListView<T> provides a data bindable view for a BindingList, in the same way that a DataView provides a bindable view of a DataTable. This component implements much of the stuff described in Paul Ballard's article. One less argument for the typed dataset clan :-)

Typed datasets still has the upper hand when it comes to batch updates, optimistic concurrency, etc, when compared to custom business entity objects and collections. What the solution might need is an ORM framework that handles all the object-to-database stuff (the DDD repository pattern). I might be in for a classic turf war with the dataset-all-the-way clansmen!

VS2005 and VSTO-O reinstall blues

Due to some problems with my Visual Studio 2005 installation, I had to reinstall VSTS on my PC. This caused my VSTO Outlook projects to stop working in VSTS with the error message "Unable to load project file <file name>". No further details were given. I then reinstalled VSTO-O beta 2, the VSTO beta 2 run-time (VSTOR.EXE), and the Office 2003 PIAs. Still no luck. I then reinstalled the Windows Script 5.6 as this had solved a VS.NET problem for me before. My project would still not load.

I then tried to create a new VSTO-O project, and this time I got the famous "class not registered" error. As usual, no details about which class.

It was time for RegMon from System Internals, a company that provides high quality freeware tools for "under the hood" monitoring during deployment and troubleshooting. I opened VSTS, selected 'File-New-Project' and browsed to the "Outlook Add-in" project template. Then I captured all registry access done while I clicked OK in the VSTS 'New Project' dialog. RegMon captured a looong list of trace, but by filtering on process name "devenv*" the list was shortened to only a few hundred entries.

Starting from the bottom of the list, I soon found two "OpenKey" events that resulted in "NOT FOUND", with no further attempts to read info about the CLSID. Double-clicking the entry in RegMon opened the standard Registry Editor, in which I myself could see that the specified CLSID was nowhere to be found in the registry.

I then used another PC with a working copy of VSTS and used the standard Registry Editor to search for the CLSID (GUID), and it turned out to be MSXML6.DLL that was missing on my PC. You will find this DLL in the System32 folder.

I think that all this was caused by uninstalling SQL Express from my PC. It would not be the first time Microsoft installers do not mark shared DLLs as "permanent".

Will they ever learn ?

I have this last week been involved in deploying a InfoPath+SharePoint (WSS) solution developed by my colleagues Mads Nissen and Rolf Inge Kirkeng. Things have gone quite well, except for annoyances caused by the language versions of WSS being different. That is, the WSS version used to develop and host the InfoPath forms in a form library, is the English version, while this customer had the Norwegian version.

I had saved the WSS team-site, including the form libraries, as a template (1033), and needed to convert it into language code 1044. Normally, you can rename the .STP file into .CAB, extract the files, perform a 'edit-replace all' on the LCID instances, build a new .CAB file, and finally rename the file to .STP. Unfortunately, this did not work this time, so I had to set up the WSS team-site manually, and then publish the InfoPath forms to the site, which automatically creates the form libraries with all the applicable metadata columns from the schema. End of my involvement.

Today, Rolf Inge finshed the deployment of the InfoPath solution, and ran into some problems with the scripts of the forms. The forms use several secondary data connections to retrieve pick list data, etc. from SharePoint lists. The problem turned out to be the naming of the standard columns of a form library. E.g. the forms expected 'Title', but in the Norwegian version this column has the name 'Tittel'. Such a low level of abstraction (internal name = display name) makes a solution really hard to standardize and deploy.

Has Microsoft not learned anything from the infamous experiences with localized WordBasic and Office VBA in the late 1990s ? It was a nightmare to deploy solutions (templates and macros), as each customer would most likely have a different object model "language", language literally meaning a national language. "ActiveWindow" would be "AktivtVindu" in the Norwegian Office versions. Even within a company you most likely would find several different installations of Office. I worked a lot for Det Norske Veritas that has offices around the world, and it was impossible to make a single, standard set of Office templates. Microsoft did in fact realize that this was not very productive, and these days the Office object model is all English.

I wish Microsoft soon would make every product use English internal names for programming, and separate, localized display names for the user interface. The same goes for the SharePoint central admin tools; please let us have the option to run e.g. the English admin tools, even on a Norwegian installation. The localized admin tools makes it hard to move between customers and quickly find the stuff you need to configure SharePoint. The strange terms sometimes used in the translations does not make it any easier.