Late in March I released (started using a lot) a small C#/SQL Server project. It utilises the new User-Defined Table Type in SQL Server 2008 and the “structured” parameter type in ADO.NET. This allows a table originated on the client to be passed in to a stored procedure as a parameter. Oracle has had table data types for a while of course, and there are still some limitations to the SQL Server version, but it’s certainly very easy to use.

The application is an uploader of OCR’d bank statement data to the database, performing validations on the way in so that data in the final table is up to standard.

I’ve found that the structured type makes a very neat design possible The essentials of the design are as follows. The target table is tblBankStatement and I have defined a table type which mirrors its columns, datatypes and primary key.

Using system tables I created a stored procedure providing a dataset of column names, types and sizes, plus whether each column is nullable or not. Apart from the primary key, most of them are nullable. I don’t think the table type should have too many business rules otherwise it could get out of sync with any changes in the base tables.

The application has to upload data from a variety of bank statements. They all differ as to number and order of columns supplied. Some have debit and credit columns, others a single plus/minus column etc.. I created another table in the database to store layout details for each source document. By left outer joining this layout to the list of columns in the datatype it’s possible to see which columns are supplied and in what order – and which data will have to be created artificially.

The client code starts by bringing back this metadata which it uses to build a DataTable to supply as the value for the ADO.NET structured-type parameter.

I created my own C# UploadDataTable and UploadDataColumn types by subclassing DataTable and DataColumn. The DataColumn base class has an Ordinal property – I added SourceOrdinal, which, where not Null, indicates the position of the column in the source data. I also added a SourceColumns property to the DataTable providing the collection of columns expected in the source data. Once the UploadDataTable is built, the client code can iterate through the SourceColumns collection and test that there are no gaps or type mismatches in the data.

Any columns in the table type not found in SourceColumns will have to be created in code.
For example, the primary key of tblBankStatement includes [LineNum], a column which doesn’t occur in all source files. In that case the AutoIncrement property of the DataColumn is used to generate the numbers. [StatDate] is another column in the primary key, but may not be in the file. If the DefaultValue property is set to the Statement Date, the column will be assigned correctly. These were the only two methods I needed to use.

Once the data has been assembled and passed to the data loader stored procedure it’s very easy to pass the table on to other procedures and functions which can do specialised validation such as checking that the balance brought forward matches the closing balance of the previous statement.

This lightens the work done by the data loading procedure and makes it more readable since it’s now a controller. One criticism could be that most of the business rules are now in the database, but that’s the way my system works. I tend to think that the best place for business logic is one place, and many of the banking systems I’ve worked on have started with a tiered design but gradually the business logic has leaked across to the database. Since it’s so common to find some business logic in the database I decided to put all of it there.