Uploading XML data into database tables using business object, code generation and stored procedures

• Download source - 28 Kb

Introduction

The requirement is to map xml elements as input parameters from clients, into corporate database tables using stored procedures.

Why it's useful

With advent of web services, clients will be sending xml messages. There will be a need to map the data in the xml messages as input parameters in stored procedures. We show how business objects can be generated for each stored procedure (not table) and then invoked each time the user uploads new data. Code generation is only needed once and every time thereafter when the stored procedure changes. Otherwise, the business object is invoked on each upload to transfer the xml message data into the database table.

The problem it solves

Mapping from xml message inputs to stored procedure input parameters using business objects (generated at development time and without using reflection at runtime).

The starting point for us are the columns in the database table. Alternately, one could start from the xml. (See background for Dan Wahlin's article on this approach) Some advantages (+) and disadvantages (-) of each approach includes:

• Need naming convention between xml element and table column (-)
• Added complexity of code generation (-)
• Difficult to debug in-memory process invocation but can be tested in nunit (-+)
• Separate xsd validating xml message and validating input in business object. (+) Allowing one to trap non-nullable columns (+) and disallowing one from passing in elements not in the table (+)
• Contract between xml and table (+) guarantees the matching of elements and columns (+)
• No need to build dynamic sql (+)
• Using stored procedures (+)
• Maintains independence of base business object from the xml-parsing business object (+)
• Both parse xml input (=)
• Flexible, able to submit subsets of data (=)

Background

• From XML to Database using the XmlTextReader

  This article takes the approach of starting from the xml message rather than the approach here, starting out from the database.

• An XSD to .NET language code class generator that adds real punch to Microsoft's XSD Tool

  The XML message can be validated given an XSD. The easiest way is to use the XSD tool to generate it based on the database table.

• HOW TO: Validate XML Fragments Against an XML Schema in Visual C#.NET

  This article show how to use the XSD to validate the incoming XML message.

• Advanced Data Mapping in .NET

  Covers data mapping in general.

• How To Programmatically Compile Code Using C#Compiler

  Used in the code to generate the business object for repeated use to transfer XML message data into stored procedure and into the database table.

• A simple beep

  Useful in debugging the code. Alternately, the generated business object can be put into an NUnit test harness.

Using the code

Given that a table has been created and an insert stored procedure written for creating new rows in the table. We can query the database for the metadata information on the stored procedure. Of interests are the input parameters to the stored procedure. With these input parameters, we can generate a matching business object that has a load method that reads in the xml message and maps by name, the value of each element into the property in the business object. These properties tie the message element with the table column.

As the business object properties represent a contract with the client that is supplying the xml message data, one can insert into the database table via the stored procedure by invoking the accessor methods directly and not use reflection at runtime. Also, clients can supply all or a subset of data. Note too that multiple rows of data can be submitted in one large xml message.

Using the system involves a 2-stage process.

First, an agreement is reached as to the elements/columns that are supplied by the client and exposed by the business (The common naming convention can be avoided by having the business object translate or map the incoming xml message element names to the corresponding in-house database table column name.)

Second, the business object is generated to facilitate this transfer of the xml message to the database table. Note that if there are existing DAO or persistent or business objects to work with the database, the system just re-uses those objects.

An ASP project that uploads client XML data file (Security is a separate issue) is provided in the project zip file.

Points of Interest

• No backup, i.e., no precautions taken against exceptions that help in facilitating recovery or error tracking.
• There is no transaction involving multiple tables
• Only the Create is shown, other CRUD actions not shown.
• There is no validating at the business object level but nothing precludes one from validating the data before persisting it.
• there is no schema/xsd validating of the xml message. Again nothing precludes including this or using another program to validate the incoming XML message.

History

This project is a literate program, Also, the first version used the open source language Ruby for code generation. The first approach, using the xml from the outset was implemented; then the second approach; and finally the second approach using c# entirely.