Passing multiple records to one stored proc
-
In many to many relationships, I often need to add one record into a table and then insert multiple records into another table and finally connect them using a third table. All these data are related to each other and either all or non shall be saved. To do this I have to use transactions and rollback upon failure. To do transactions I can either do it inside my stored procedures(sql) or inside application(ado.net). If I do it in my application it will increase communication with database over network since for each record I call a sp remotely and transfer data. If I use transaction inside a stored procedure(and I prefer this), I have to pass all data related to one insert to that proc which includes multiple records(one to many relationship). The problem here is: What is the best way of passing multiple records to one stored procedure? using an
nVarcharparameter and splitting data inside the proc is not my favorite way, if there is any other way around? If there is a better way of doing the whole thing instead of passing one parameter or using ado.net transactions I'm eagerly waiting to hear. :) Thanks a lot in advanced.:rose:"In the end it's a little boy expressing himself." Yanni
-
In many to many relationships, I often need to add one record into a table and then insert multiple records into another table and finally connect them using a third table. All these data are related to each other and either all or non shall be saved. To do this I have to use transactions and rollback upon failure. To do transactions I can either do it inside my stored procedures(sql) or inside application(ado.net). If I do it in my application it will increase communication with database over network since for each record I call a sp remotely and transfer data. If I use transaction inside a stored procedure(and I prefer this), I have to pass all data related to one insert to that proc which includes multiple records(one to many relationship). The problem here is: What is the best way of passing multiple records to one stored procedure? using an
nVarcharparameter and splitting data inside the proc is not my favorite way, if there is any other way around? If there is a better way of doing the whole thing instead of passing one parameter or using ado.net transactions I'm eagerly waiting to hear. :) Thanks a lot in advanced.:rose:"In the end it's a little boy expressing himself." Yanni
Personally, I prefer to do it from the application. In the Data Access Layer of the application, I'd have the control of the transaction along with the creation of the parent and child rows, then the rollback or commit of the transaction. You have to get the data of what you want to insert to some type of execution on the database server, one way or another. I think passing the data for the child rows in some kind of delimited format in an nVarchar(max) (or the like) would defeat one benefit of stored procedures: performance. It doesn't make sense to me to have to 'unpack' the information for the child rows because you'll take a decent performance hit for that string manipulation. I'd prefer the database to be 'dumb' and only do small, specific tasks like 'insert a new X', 'update Y', 'delete Z', etc; with the application on top of that putting those smaller building blocks together.
Keep It Simple Stupid! (KISS)
-
Personally, I prefer to do it from the application. In the Data Access Layer of the application, I'd have the control of the transaction along with the creation of the parent and child rows, then the rollback or commit of the transaction. You have to get the data of what you want to insert to some type of execution on the database server, one way or another. I think passing the data for the child rows in some kind of delimited format in an nVarchar(max) (or the like) would defeat one benefit of stored procedures: performance. It doesn't make sense to me to have to 'unpack' the information for the child rows because you'll take a decent performance hit for that string manipulation. I'd prefer the database to be 'dumb' and only do small, specific tasks like 'insert a new X', 'update Y', 'delete Z', etc; with the application on top of that putting those smaller building blocks together.
Keep It Simple Stupid! (KISS)
Thanks for the help.
Ben Fair wrote:
It doesn't make sense to me to have to 'unpack' the information for the child rows because you'll take a decent performance hit for that string manipulation.
Exactly. That's why I don't like that. It seems there is not any third way. I don't like doing it in application but packing and unpacking is even worse, IMO. I was wishing there to be some other way that I don't know. By the way, how do you implement it in app? I know it this way:
SqlConnection cnn = new SqlConnection("") ;
cnn.Open();
SqlTransaction trans = cnn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.Transaction = trans;
try
{
// work with data
trans.Commit();
}
catch (SqlException)
{
trans.Rollback();
}I don't like this because it mostly results in a big function dealing with multiple procedures and all their parameters. It's difficult to keep it clean or automate it's code generation, or maybe I'm missing something here.:confused:
"In the end it's a little boy expressing himself." Yanni
-
In many to many relationships, I often need to add one record into a table and then insert multiple records into another table and finally connect them using a third table. All these data are related to each other and either all or non shall be saved. To do this I have to use transactions and rollback upon failure. To do transactions I can either do it inside my stored procedures(sql) or inside application(ado.net). If I do it in my application it will increase communication with database over network since for each record I call a sp remotely and transfer data. If I use transaction inside a stored procedure(and I prefer this), I have to pass all data related to one insert to that proc which includes multiple records(one to many relationship). The problem here is: What is the best way of passing multiple records to one stored procedure? using an
nVarcharparameter and splitting data inside the proc is not my favorite way, if there is any other way around? If there is a better way of doing the whole thing instead of passing one parameter or using ado.net transactions I'm eagerly waiting to hear. :) Thanks a lot in advanced.:rose:"In the end it's a little boy expressing himself." Yanni
I agree with what Ben Fair said, especially about transaction boundaries. I never get used to beginning and ending a transaction inside a database since then the client looses control over the ACID logic. However passing several record with a single call is also a performance issue so it's benefitial to use such logic. One way to do it is descibed in an article I wrote: How to pass multiple records to a stored procedure[^] Hope it helps, Mika
The need to optimize rises from a bad design.My articles[^]
-
Personally, I prefer to do it from the application. In the Data Access Layer of the application, I'd have the control of the transaction along with the creation of the parent and child rows, then the rollback or commit of the transaction. You have to get the data of what you want to insert to some type of execution on the database server, one way or another. I think passing the data for the child rows in some kind of delimited format in an nVarchar(max) (or the like) would defeat one benefit of stored procedures: performance. It doesn't make sense to me to have to 'unpack' the information for the child rows because you'll take a decent performance hit for that string manipulation. I'd prefer the database to be 'dumb' and only do small, specific tasks like 'insert a new X', 'update Y', 'delete Z', etc; with the application on top of that putting those smaller building blocks together.
Keep It Simple Stupid! (KISS)
I definitely agree with transaction boundaries, but for performance reasons it's nice to have just a single roundtrip in some cases. One way to do this is described here: How to pass multiple records to a stored procedure[^] Mika
The need to optimize rises from a bad design.My articles[^]
-
I agree with what Ben Fair said, especially about transaction boundaries. I never get used to beginning and ending a transaction inside a database since then the client looses control over the ACID logic. However passing several record with a single call is also a performance issue so it's benefitial to use such logic. One way to do it is descibed in an article I wrote: How to pass multiple records to a stored procedure[^] Hope it helps, Mika
The need to optimize rises from a bad design.My articles[^]
Great article :) Thanks for sharing. This definitely solves the issue I had. What a good coincidence to asked the question and then exactly at the same day you posted the great article. I like database related stuff(All CRUD operation) to be inside database. I use auto generated code in my application to access stored procedures. This both increase coding speed and also makes it cleaner and easier to maintain(It's easier to modify a stored procedure later instead of the application that needs recompilation.) I have this logic: A
CustomerAddstored procedure has to add a customer along with it's contact information(for instance) that might be more than one record. Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data? This is my opinion. Actually I mostly hear people advise what you also stated, but I'm still not motivated enough to do it the other way. Thanks again."In the end it's a little boy expressing himself." Yanni
-
I agree with what Ben Fair said, especially about transaction boundaries. I never get used to beginning and ending a transaction inside a database since then the client looses control over the ACID logic. However passing several record with a single call is also a performance issue so it's benefitial to use such logic. One way to do it is descibed in an article I wrote: How to pass multiple records to a stored procedure[^] Hope it helps, Mika
The need to optimize rises from a bad design.My articles[^]
-
Great article :) Thanks for sharing. This definitely solves the issue I had. What a good coincidence to asked the question and then exactly at the same day you posted the great article. I like database related stuff(All CRUD operation) to be inside database. I use auto generated code in my application to access stored procedures. This both increase coding speed and also makes it cleaner and easier to maintain(It's easier to modify a stored procedure later instead of the application that needs recompilation.) I have this logic: A
CustomerAddstored procedure has to add a customer along with it's contact information(for instance) that might be more than one record. Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data? This is my opinion. Actually I mostly hear people advise what you also stated, but I'm still not motivated enough to do it the other way. Thanks again."In the end it's a little boy expressing himself." Yanni
Hamed Mosavi wrote:
Great article Thanks for sharing
You're welcome.
Hamed Mosavi wrote:
Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data
I think this is kind of an everlasting war :) I'm not saying that starting and ending transactions at client side is absolutely the only right way. The important thing is that which ever way you choose, you know the limitations as well as the benefits. I've used to control transactions at client side for several reasons, like: - when business logic changes, it's easy to implement and add new functionality if the old logic is still valid (adding logic) - code at both middle tier and database can be kept as simple building block and it's more easily reused - if necessary transactions can be more easily propagated and distributed - business logic can be transactional (not only the data in the database) - if needed, retries can be used without having to execute the whole logic again etc. But these are still opinions :)
The need to optimize rises from a bad design.My articles[^]
-
That's an intriguing idea, I haven't really worked with SQL Server 2008 yet and using table valued parameters hadn't crossed my mind. I can see how it would easily lend itself to this situation. Thanks for the article!
Keep It Simple Stupid! (KISS)
-
Hamed Mosavi wrote:
Great article Thanks for sharing
You're welcome.
Hamed Mosavi wrote:
Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data
I think this is kind of an everlasting war :) I'm not saying that starting and ending transactions at client side is absolutely the only right way. The important thing is that which ever way you choose, you know the limitations as well as the benefits. I've used to control transactions at client side for several reasons, like: - when business logic changes, it's easy to implement and add new functionality if the old logic is still valid (adding logic) - code at both middle tier and database can be kept as simple building block and it's more easily reused - if necessary transactions can be more easily propagated and distributed - business logic can be transactional (not only the data in the database) - if needed, retries can be used without having to execute the whole logic again etc. But these are still opinions :)
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
I think this is kind of an everlasting war
:-D Very true. While some of the benefits you mentioned can be done with writing good sql queries(like reusability) and sometimes it works better, some others can't and might be needed for many projects to be done inside application. I believe in the projects that I deal with everyday, putting data related stuff on the shoulder of the database works better. Definitely it will be useful and maybe needed to have transactions in business logic specially when working on web based projects, IMHO. It looks like you are working on larger projects, so you decided to take transactions from Database or even DAL and do it in BLL. I'm guessing you are using
System.Transactionsor maybeCOM+ and SWCboth of which are too big for my kind of small projects. Maybe what we work on is playing an important role on the technology we use or the way we use it. :)"In the end it's a little boy expressing himself." Yanni
-
Mika Wendelius wrote:
I think this is kind of an everlasting war
:-D Very true. While some of the benefits you mentioned can be done with writing good sql queries(like reusability) and sometimes it works better, some others can't and might be needed for many projects to be done inside application. I believe in the projects that I deal with everyday, putting data related stuff on the shoulder of the database works better. Definitely it will be useful and maybe needed to have transactions in business logic specially when working on web based projects, IMHO. It looks like you are working on larger projects, so you decided to take transactions from Database or even DAL and do it in BLL. I'm guessing you are using
System.Transactionsor maybeCOM+ and SWCboth of which are too big for my kind of small projects. Maybe what we work on is playing an important role on the technology we use or the way we use it. :)"In the end it's a little boy expressing himself." Yanni
-
You obviously have thought these through so I believe that you have a good and well defined solution. That's the most important thing :)
The need to optimize rises from a bad design.My articles[^]
Mika Wendelius wrote:
You obviously have thought these through
Not really. Just as much as time permits and I never find enough time.
Mika Wendelius wrote:
a good and well defined solution. That's the most important thing
Yes that's certainly the important thing. I hope to be able to do my job well. Discussions in forums of CP has always been a great source of information and helped me a lot. I always learn from them. I have just had a good one. Thanks for that. :)
"In the end it's a little boy expressing himself." Yanni
-
Mika Wendelius wrote:
You obviously have thought these through
Not really. Just as much as time permits and I never find enough time.
Mika Wendelius wrote:
a good and well defined solution. That's the most important thing
Yes that's certainly the important thing. I hope to be able to do my job well. Discussions in forums of CP has always been a great source of information and helped me a lot. I always learn from them. I have just had a good one. Thanks for that. :)
"In the end it's a little boy expressing himself." Yanni