A particular bug-bear of mine.
-
Quite often, I have had to work (read fix here) with other people's database developments where they implement separate insert/update methods in the business layer. A lot of the time, this doesn't actually make sense, and it is nicer just to provide a Save method where the save itself works out whether or not it needs to insert or update the record. As an example, consider the following Stored Procedure:
CREATE PROCEDURE [MyUser].[SaveUser] @ID INT OUT, @UserName NVARCHAR(30) AS SET NOCOUNT ON IF @ID = 0 BEGIN INSERT INTO UserDetails(ID, Name) VALUES (@ID, @UserName) SET @ID = SCOPE_IDENTITY END ELSE BEGIN UPDATE UserDetails SET Name = @UserName WHERE ID = @ID ENDThis means that the datalayer just needs to call the SaveUser procedure and the SP will work out whether it needs to do a save or an insert. Now, there are some arguments as to whether or not this is the responsibility of the datalayer, but look at how this simplifies procedure development. Instead of having Insert/Update/Delete, you end up having Save/Delete, thus simplifying your datalayer. The downside here is that you have to check the @ID field first, which is a (minor) performance hit that you may want to forego. Alternatively, you could have the datalayer implement the same logic and determine whether or not this is an insert by checking the ID field beforehand.public void Save() { if (_id == 0) { // This is an insert. } else { // This is an update. } }All I am asking, is that you remove the decision as to whether to insert or update from the business layer. Please...:)the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
Quite often, I have had to work (read fix here) with other people's database developments where they implement separate insert/update methods in the business layer. A lot of the time, this doesn't actually make sense, and it is nicer just to provide a Save method where the save itself works out whether or not it needs to insert or update the record. As an example, consider the following Stored Procedure:
CREATE PROCEDURE [MyUser].[SaveUser] @ID INT OUT, @UserName NVARCHAR(30) AS SET NOCOUNT ON IF @ID = 0 BEGIN INSERT INTO UserDetails(ID, Name) VALUES (@ID, @UserName) SET @ID = SCOPE_IDENTITY END ELSE BEGIN UPDATE UserDetails SET Name = @UserName WHERE ID = @ID ENDThis means that the datalayer just needs to call the SaveUser procedure and the SP will work out whether it needs to do a save or an insert. Now, there are some arguments as to whether or not this is the responsibility of the datalayer, but look at how this simplifies procedure development. Instead of having Insert/Update/Delete, you end up having Save/Delete, thus simplifying your datalayer. The downside here is that you have to check the @ID field first, which is a (minor) performance hit that you may want to forego. Alternatively, you could have the datalayer implement the same logic and determine whether or not this is an insert by checking the ID field beforehand.public void Save() { if (_id == 0) { // This is an insert. } else { // This is an update. } }All I am asking, is that you remove the decision as to whether to insert or update from the business layer. Please...:)the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.Pete O`Hanlon wrote:
Alternatively, you could have the datalayer implement the same logic and determine whether or not this is an insert by checking the ID field beforehand.
Or Alternatively you could let the data adapter do its job and make exactly the same decision for you. Notice that the Data Adapter has only 2 main methods: Fill - Executes your Select Update - Executes your Insert,Update or Delete depending on the current state of the row being saved. Sorry to say I 100% disagree with your oppinion that proper CRUD stored procedures are not a good thing.
-
Pete O`Hanlon wrote:
Alternatively, you could have the datalayer implement the same logic and determine whether or not this is an insert by checking the ID field beforehand.
Or Alternatively you could let the data adapter do its job and make exactly the same decision for you. Notice that the Data Adapter has only 2 main methods: Fill - Executes your Select Update - Executes your Insert,Update or Delete depending on the current state of the row being saved. Sorry to say I 100% disagree with your oppinion that proper CRUD stored procedures are not a good thing.
J4amieC wrote:
Or Alternatively you could let the data adapter do its job and make exactly the same decision for you. Notice that the Data Adapter has only 2 main methods: Fill - Executes your Select Update - Executes your Insert,Update or Delete depending on the current state of the row being saved.
This only works if you are using a Data Adapter. In most cases, I have been dealing with more traditional business objects that are mapped into the database. For instance, having a Customer class and a Customers collection with the Customers collection being responsible for returning a number of populated Customer classes.
J4amieC wrote:
Sorry to say I 100% disagree with your oppinion that proper CRUD stored procedures are not a good thing.
Where in my original post did I state that CRUD procedures are not a good thing? I present a couple of alternatives for saving data - one which uses a composite INSERT/UPDATE procedure, and the other which uses C# code to accomplish the same thing. What I did state was that it shouldn't be the responsibility of the business layer to work out whether to do an insert or an update. My last line was the important one:
All I am asking, is that you remove the decision as to whether to insert or update from the business layer. Please...
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
Quite often, I have had to work (read fix here) with other people's database developments where they implement separate insert/update methods in the business layer. A lot of the time, this doesn't actually make sense, and it is nicer just to provide a Save method where the save itself works out whether or not it needs to insert or update the record. As an example, consider the following Stored Procedure:
CREATE PROCEDURE [MyUser].[SaveUser] @ID INT OUT, @UserName NVARCHAR(30) AS SET NOCOUNT ON IF @ID = 0 BEGIN INSERT INTO UserDetails(ID, Name) VALUES (@ID, @UserName) SET @ID = SCOPE_IDENTITY END ELSE BEGIN UPDATE UserDetails SET Name = @UserName WHERE ID = @ID ENDThis means that the datalayer just needs to call the SaveUser procedure and the SP will work out whether it needs to do a save or an insert. Now, there are some arguments as to whether or not this is the responsibility of the datalayer, but look at how this simplifies procedure development. Instead of having Insert/Update/Delete, you end up having Save/Delete, thus simplifying your datalayer. The downside here is that you have to check the @ID field first, which is a (minor) performance hit that you may want to forego. Alternatively, you could have the datalayer implement the same logic and determine whether or not this is an insert by checking the ID field beforehand.public void Save() { if (_id == 0) { // This is an insert. } else { // This is an update. } }All I am asking, is that you remove the decision as to whether to insert or update from the business layer. Please...:)the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.Pete O`Hanlon wrote:
All I am asking, is that you remove the decision as to whether to insert or update from the business layer.
I agree and infact when I do use SPs I try and do this, makes the client code simpler and that's more likely to change so why make more work?
I have no idea what I just said. But my intentions were sincere.
-
Pete O`Hanlon wrote:
All I am asking, is that you remove the decision as to whether to insert or update from the business layer.
I agree and infact when I do use SPs I try and do this, makes the client code simpler and that's more likely to change so why make more work?
I have no idea what I just said. But my intentions were sincere.
Ed.Poore wrote:
when I do use SPs I try and do this, makes the client code simpler and that's more likely to change so why make more work?
And that's the best approach that you can take.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
Ed.Poore wrote:
when I do use SPs I try and do this, makes the client code simpler and that's more likely to change so why make more work?
And that's the best approach that you can take.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before. -
Quite often, I have had to work (read fix here) with other people's database developments where they implement separate insert/update methods in the business layer. A lot of the time, this doesn't actually make sense, and it is nicer just to provide a Save method where the save itself works out whether or not it needs to insert or update the record. As an example, consider the following Stored Procedure:
CREATE PROCEDURE [MyUser].[SaveUser] @ID INT OUT, @UserName NVARCHAR(30) AS SET NOCOUNT ON IF @ID = 0 BEGIN INSERT INTO UserDetails(ID, Name) VALUES (@ID, @UserName) SET @ID = SCOPE_IDENTITY END ELSE BEGIN UPDATE UserDetails SET Name = @UserName WHERE ID = @ID ENDThis means that the datalayer just needs to call the SaveUser procedure and the SP will work out whether it needs to do a save or an insert. Now, there are some arguments as to whether or not this is the responsibility of the datalayer, but look at how this simplifies procedure development. Instead of having Insert/Update/Delete, you end up having Save/Delete, thus simplifying your datalayer. The downside here is that you have to check the @ID field first, which is a (minor) performance hit that you may want to forego. Alternatively, you could have the datalayer implement the same logic and determine whether or not this is an insert by checking the ID field beforehand.public void Save() { if (_id == 0) { // This is an insert. } else { // This is an update. } }All I am asking, is that you remove the decision as to whether to insert or update from the business layer. Please...:)the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.I've worked on projects that supported this approach in the past. I learned (the hard way) that I had to modify the business object to ensure that it had a valid ID. So, I had to track state of the object. When loaded from the database, I set a hidden variable. If the object was created from the database load, then an internal flag would hold that fact. If the object was not instantiated from the database, then the flag would never be set. So maintaining state in the object allowed me to implement this technique. However, it is much easier to have CRUD (Create, Read, Update and Delete) implemented on any single object representing a single record from the database. Which should map to the corresponding (Insert, Select, Update, Delete) procedures. ~ CodeDoctor ~