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 (+)
- No need to trap exception if message validated because will match with accessor method (+) Note there is still the need to trap for bad data. -->
- Maintains independence of base business object from the xml-parsing business object (+)
- Both parse xml input (=)
- Flexible, able to submit subsets of data (=)
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
<!-- h1>how to show this, ala OMPersistor, OM, etc existing business objects.
Blocks of code should be wrapped in <pre> tags like this:
// generated business object
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.
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.