|
|
Posted by JRoughgarden on 10/17/06 17:36
After a customer decides to buy a shopping list, there is generally a
need to store/insert one master record and a variable number of child
detail records, preferably all wrapped in a transaction. There are lots
of ways to do this and I am wondering if anyone knows which is most
efficient.
One approach is to use ADO.NET's transaction capabilities, define
single-record insert procs for the master and detail tables, and call
the detail insert in a loop from the web page. This has N+1 trips to
the server, which is not too attractive.
Another approach is to concatenate all the data into a big
string/varchar variable and pass it to a decoder proc that would then
call the single record insert procs via a loop inside the decoder proc.
This second approach would use T-SQL's transaction capability and have
only one trip to the server, but it is more effort to code on the web
page and in the decoder proc.
Surely this is a common problem. Are there any more elegant/efficient
methods that anyone can suggest? Can one pass an array to a proc? Is
this a place for a user defined data type?
Any advice is much appreciated.
Navigation:
[Reply to this message]
|