By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,017 Members | 1,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,017 IT Pros & Developers. It's quick & easy.

What is the best way to store a shopping list or order?

P: n/a
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.

Oct 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
>which is most efficient. <<

I would start by first focusing on correctness before worring about
efficiency. Does the system you are working on have such high traffic
requirements that a few insert statements would make such a big
difference? I don't know your requirements, but I find that too many
developers worry about these types of issues too much and often come up
with unnecessary hacks for no good reason. Having said that...
>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? <<
You may want to look into passing the data as XML. See this link for an
example.

http://msdn.microsoft.com/library/de...ml/sql01c5.asp

HTH


JRoughgarden wrote:
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.
Oct 17 '06 #2

P: n/a
You may want to look into passing the data as XML. See this link for an
example.

http://msdn.microsoft.com/library/de...ml/sql01c5.asp

HTH
Thanks for the URL reference. I think XML may be the way to do this;
beats writing your own code/decode routines.

Oct 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.