good practice for saving objects to database

Question

Lately we've been wondering what people find works well for saving your business objects / entities to a database. For purpose of discussion, consider the example of an order with a few items in it (customer details, names, quantities, prices etc.). We're using SQL Server and C# and have some legacy ASP Classic/Jscript for older projects, and no chance to move to NoSQL type solutions etc.

There's roughly three ways I know of

1. create adhoc SQL queries on the server side (seems slower + more brittle, if you change your server side language, you have to recreate all this code)

2. use a stored procedure that accepts an XML representation of the order, and then the stored procedure will read the document and do whatever needs to be done. (this is the method we've used till now)

3. use some kind of ORM technology (I know some people swear by it, but we would prefer to avoid it as we haven't got enough experience with it to accurately gauge whether it's a path we want to go down, and there's enough chatter around performance issues and complexity that we're a bit nervous in committing to it given everything else going on, adopting a KISS philosophy)

What I want to know is:

1. whether anyone has any other/better approaches not in the above list that we can investigate

2. whether the XML method is standard/good practice. We like it because most languages support serializing to XML, it's web friendly, easy to read during debugging and pretty flexible (easy to extend, can choose how strong or weak via schemas etc.) but want to take a chance to consider other ideas.

Note: Using OPENXML in SQL Server 2008 stored proc - INSERT order differs from XML document seems to be relevant in some ways; till now we've used openxml() so I guess the question to ask would be if the XML method is a reasonable way to go, is XQuery a better approach ? Is there a handy reference/post that someone could point to on the really best way of doing things in this manner in terms of flexibility, readability etc.

Edit: Thanks for all for replying. I'll make my response even though this has been closed (still not clear why discussion is verboten on StackOverflow). Apologies for taking a while.

I should probably clarify; where we have found XML useful is when we have a "complex" object that is composed of several smaller business objects. For example an order with "items" ; there may be one or more number of items. If we load an order and then the user adds an item to their order, we just tack on another item, serialize to XML and then pass it to the stored procedure which can figure out whether it's an insert or update, whereas I'm not sure how you would do it otherwise (apart from a lot of little stored procedures/adhoc SQL which I'd avoid. ORMs: see comment below ). If we're dealing with some flat structure that amounts to an insert/update of a row, then a regular stored procedure with strongly typed parameters works well.

As for the other comments regarding ORMs; I found the links to the piece by Martin Fowler interesting. I have read the original http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx a while back but always appreciate more information. My main concern with ORM at the moment is just the complexity of learning to use it, and using it well (avoiding the pitfalls etc.) which given the current environment we're working with looks like a bit much to handle.

But thanks for the replies; just wanted to see if there were other approaches that we might have missed. Looks like ORMs are something worth investing some time in on the side.

Thanks again.

-Marcin

Answer 1

It sounds like you may want to keep your business logic and quite a bit of your app in the database (i.e. stored procedures) so you can reuse it from multiple programming environments? If that is the case, I guess I can get onboard with sending XML to a stored procedure.

My normal bias is completely the opposite, preferring to use NHibernate on any project using .NET 2.0 or greater. There is a learning curve for using an ORM, but you get massive increases in developer productivity and quite a lot of strong typing, plus in more recent .NET releases you also get Linq.

I do think NoSQL (RavenDB, etc.) is worth considering for your scenario.

As far as performance, dynamic SQL and ORMs are very likely to get you very close to stored procedure performance and there is generally a way to get closer to the metal when necessary. I would be a lot more concerned with the performance of parsing XML, which is generally slow. Parsing XML is also brittle.

In your situation, I would likely be advocating to move any app talking to the database up to a recent version of .NET and moving business logic out of the database layer. In the long run, that likely has a lower total maintenance cost than continuing on with the fragmented environment you are dealing with. I understand how difficult it can be to convince management of this though.

Answer 2

We used the following with two projects quite successfully:

Each business object gets one or more stored procedures in the database that creates/updates/deletes it. (Sometimes known as "CRUD") The procedure completely isolates the Frontend/Webserver from the database structure.

Yes, you have to write these for every object (There are tools to generate a basic version of them, e.g. SSMS-Tools for Management Studio), and yes you have to extend these for every new field. But you also have to change your current XML-parsing procedure if you are using XML to transfer the data. With XML you avoid changing signature of the procedure, but that's a bit like cheating because the structure of the data you tranfer DID change. You are just hiding it inside the XML.

On the Frontend/Webserver side, you have to provide all business objects with a method to save them, which in turn just calls the relevant procedure and passes all the fields to it. If you use named parameters you are also independent from the order of parameters and can omit optional parameters, which enables you to extend the procedure without having to change each call to it.

The CRUD method is really fast, because you don't have to do any XML creation or parsing and the procedures can deal with exactly the case at hand. Databases like that. It also makes it easy to find bugs because the procedures tend to be small.

I see no use in serializing and deserializing XML in your case. You need the native DB-Client/Driver anyway and that can deal with passing multiple fields of different types quite well. Your Webserver code end up with clean calls to the DB, and the DB need not deal with parsing XML, which is not it's primary job.

Answer 3

I'd need more clarification on exactly what you are saving to SQL Server in terms of an object, but if you are talking about simply saving XML to a database as a representation of a business object, then I'd suggest you aren't making the best use of a relational database.

A well designed database can help enforce business rules via constraints, referential integrity via foreign keys and provide many other benefits that you might be missing out on if you are simply storing XML in a given table.

With regards to using XML as a parameter to a stored proc, then Erland Sommarskog has some great material on options of how to handle this including XML. He also has the following to say on OPENXML

Already in SQL 2000 you could use XML thanks to the OPENXML function. OPENXML is still around in SQL 2005 and SQL 2008, but about the only reason to use it would be that you need to support SQL 2000 as well. OPENXML is bulkier to use, and performance is nowhere near nodes. In fact, in my tests it's 30-50 % slower than the iterative method. One possible advantage with OPENXML, though, is that the query plan is simpler. Query plans for XQuery mixes XML operators with relational operators, whereas OPENXML results in once single Remote Scan. This reduces the risk for the query-plan disasters you sometimes get with XQuery.

Martin Fowler also has a great article on OrmHate, that provides a really nice discussion on some of the reasons behind peoples reluctance to use ORM frameworks. He ends his article by suggesting;

So ORMs help us deal with a very real problem for most enterprise applications. It's true they are often misused, and sometimes the underlying problem can be avoided. They aren't pretty tools, but then the problem they tackle isn't exactly cuddly either. I think they deserve a little more respect and a lot more understanding.

I think in your case, this is quite appropriate as they do take quite a bit of time to master.

Therefore, I'd be more inclined to lean towards option 2 (XML) and look at either incorporating your desired order in the way the XML is constructed in the first place, or maybe looking at what is possible with a combination of nodes and the ROW_NUMBER function of SQL Server.