Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 12 Oct 2004
Rate this:
Please Sign up or sign in to vote.
An article on mapping XML elements as input parameters into stored procedures

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 (+) <!--
  • 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 (=)

Background

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. <!-- h1>how to show this, ala OMPersistor, OM, etc existing business objects. <!--

Blocks of code should be wrapped in <pre> tags like this:

//
// generate
//
//
// compile
//
//
// zSP
//
//
// 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.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

rmlc

United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberHemlata165-Jan-11 20:22 
GeneralXML data into database tables PinmemberMember #385537315-Mar-07 10:36 
GeneralopenXML Pinmemberrmlc14-Oct-04 12:36 
GeneralDB Script PinmemberAtlant14-Oct-04 4:39 
GeneralRe: DB Script Pinmemberrmlc14-Oct-04 12:32 
GeneralMinor issues PinmemberMarc Brooks13-Oct-04 16:25 
GeneralRe: Minor issues Pinmemberalmander9-Sep-06 21:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 13 Oct 2004
Article Copyright 2004 by rmlc
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid