April 17, 2002 at 10:02 am
Environment: ADO.NET and SQL2000
I've been testing using OPENXML in stored procedures to perform multi-table / multi-record inserts and updates.
While doing inserts on non-dependent tables works quite well and I like the fact that I can vary the columns of data passed without modifying the SProc, I've hit one snag and that is with getting the IDENTITY value for multi-table (PK/FK) inserts.
Normally, I would do this with passed parameters and and after the first insert 'Set @iIDent = Scope_Identity()' for the dependent tables.
Since OPENXML requires everything to be in the XML document, I have not been able to find a way to update the value for that field. While I could use a GUID instead of an Identity field, that is not my first choice, IF I can get the Identity field to work.
I would appreciate some input/opinions from anyone using ADO.NET and OPENXML.
Is this methodology (even if I must use a GUID) practical? The value of being able to have the SProc so dynamic seems to be a big benefit. Should I consider creating a shaped recordset within the middle tier and performing the inserts / updates that way vs a stored procedure or stick with the tried and true, one trip per record via SProcs?
Performance isn't a huge consideration here - ease of maintenance is the primary goal. All clients will run this WinForm application via Terminal Server so performance hits for multiple trips (and there wouldn't be that many) really is not an issue.
Any input would be greatly appreciated.
April 17, 2002 at 10:49 am
I like OpenXML as a way to avoid the round trips. I like uniqueidentifiers too! I think your options are to include a guid as an alternate pkey so you have something to join back on, or cursor through the set and do it one at a time. I think the overhead of the cursor is reasonable compared with doing multiple network trips.
Andy
April 17, 2002 at 10:55 am
Andy,
Thanks for the reply - but performance aside, what about maintainability?
Assuming using GUIDs, it would seem that having SProcs that can handle varying amounts of parameters without being edited and never having to build some ponderous parameter list for an insert that spans 5+ tables is a big benefit - but is there some downside I'm missing?
Also, what about using ADO.NET Recordsets and building the normalized recordset in the middle tier? Of course, now there is even more load on the middle tier, but aside from performance, for maintenance and expansion/modification, it still seems in the limited testing I've done, XML is still head and shoulders above this and anything else I'm aware of.
Just trying to think through the options before I get too far down the path.
April 17, 2002 at 12:29 pm
Havent spent enough time on .Net to offer an opinion. In general I think that this is a good place to use XML. I think the tradeoff is trading a strong typing for a looser one, which opens you up to the possibility of very weird results if the xml is incorrect. I see it as being fairly maintainable.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply