My recent review of passing data to a database through an xml document has prompted me to take a step back and consider what are the good practices. To be clear, passing parameters to a database as an XML fragment is not a manageable practice.
I have learned, through working with people much smarter than me, that when you start designing your application code you should start with the question, “How can I test this?” The question is always valid, even without automated testing, because it directs your application into reliable methods.
I have written tests using the information stored in the metadata of SQL Server allowing me to validate the input parameters of a stored procedure. If I test the parameters of the procedure, and test the code calling the procedure for the same contract, I can have increased confidence that the application and the procedure are synchronized, and effectively communicate data. I, however, my only input to a stored procedure is an XML fragment, this becomes impossible to validate; at least on the stored procedure side of things.
Most modern applications work with object based languages. As a result we have need of Object Relational Mapping (ORM)when persisting our data in a relational database such as SQL. You can write your own ORM or use any number of ORM code generators. Some mappers require definitions for the mappings, others use naming conventions to reduce the amount of customization needed. Others build the object and communication completely from the database structure.
The point is that all of these methods use clearly defined communication and contracts such that there is no ambiguity about what is being done, or what some attribute represents. I’m not arguing for any particular implementation such as a repository pattern, traditional multi-tiered, or some form of Onion architecture. I am arguing for separating the data used in your web application from the internal workings of your database.
There are dozens of frameworks, code generators, and patterns designed for persistence in an SQL database. Do you have a favorite? When do you chose to write your own? Share with us your preference by leaving a comment, or sending an email to btaylor@sswug.org.
Cheers,
Ben