PK Insert Error

[jtucholski]

This may belong in a Web Application Forum but it also relates to this category as well.

I would like for anyone who consumes our web data provider layer to only have to call UpdateX and if the object did not exist in the database it would do an INSERT. If the object does exist then I think an UPDATE should be done instead.

Now for me to be able to get around the fact that if I attempt to do an UPDATE and the object does not yet exist, or I try to do an INSERT and the object does exist, should I do a REMOVE first and then do the INSERT?

the other option could be for me to

have the ID property in all of our objects as a READONLY property that can only be defined internally. When the enduser calls the DataProvider Update method we check to see if the ID is there and if it is then we call an UPDATE otherwise we do an INSERT.

[jtucholski]

Thinking about it more I don't understand why there should be a setter for your ID attribute. That adds to the risk that a rogue developer could come along create the object with an ID already in the DB and change all of its properties to these crazy values. Right?

[r937]

do the INSERT first, and if the return code is successful, you're done, otherwise do the UPDATE

[jtucholski]

Is that returned in the ExecuteNonQuery method?

[jtucholski]

I'm finding that if the Primary Key is broken on the insert that the ExecuteNonQuery throws an exception instead of returning a response value

[r937]

sorry, man, i have no idea what you're talking about

objects? properties? methods? these are all part of the application language that interfaces with the database, they have nothing to do with the database itself

perhaps you would like to have your thread moved to a specific language forum?

[longneck]

then catch the exception and handle it.

[wwb_99]

I also vote to move this to the ASP.NET forum or maybe the general app development forum. This really is an application-layer issue--the sql bit is simple with stored procedures.

Insofar as what to do, it really depends on requirements. Inside the data access layer, I tend to have the objects figure out if they need to be inserted based on null or default id values.

The real trick you have here is how to figure out if those remote clients accessing your web service have the necessary permissions to update the records they wish to update . . .

Off Topic:

Pfft . . . pfft . . . catching exceptions is NOT a way to handle business logic. They are very, very expensive items to throw and catch

[r937]

[exceptions] are very, very expensive items to throw and catch

how does that particular expense compare with a SELECT?

lots of people do a SELECT, and based on whether it returns a row, then they decide whether to do the INSERT or the UPDATE

that's 2 separate database calls, right? and note: on every single instance

whereas doing the INSERT first, followed by the UPDATE if necessary, that would be 1 database call, or 2 in some cases, with the net result being that it's faster, on average

[jtucholski]

This last post is exactly what I WANTED to say.... thanks r937.

Application Layer or not... How do you deal with the fact that your Database Connection in the Application Layer throws an exception when you attempt to INSERT an object that already uniquely exists (e.g one that will be breaking PK Constraint)? If it already exists then I want to UPDATE it.

[longneck]

Application Layer or not... How do you deal with the fact that your Database Connection in the Application Layer throws an exception when you attempt to INSERT an object that already uniquely exists (e.g one that will be breaking PK Constraint)? If it already exists then I want to UPDATE it.

switch to mysql which has the INSERT ... ON DUPLICATE KEY UPDATE syntax.

[longneck]

Off Topic:

Pfft . . . pfft . . . catching exceptions is NOT a way to handle business logic. They are very, very expensive items to throw and catch

i hear these two things a lot (exceptions are not for business logic, exceptions are expensive) but no one has ever explained them. can you tell why, or point a previous discussion?

[jtucholski]

longneck good call on the INSERT....ON DUPLICATE KEY UPDATE...thats what we need.

[wwb_99]

@r937: actual expense of database calls depends on systems and some local topography. But I suspect the expense of the exception is at least equal, especially considering the application has to make the call to generate the exception. I also suspect database-level errors make for a more expensive call on the DBMS end of things, as error handling tends to be expensive. Furthermore, if your DBMS supports stored procedures, this can easily be wrapped in one, getting the incident down to one trip to the DB.

@longneck: Good question. I did some quick searches and could not find the wonderful articles I base my statements on. But basically, exceptions require stopping execution and walking back up to the stack. And stack walks are expensive. You are much better off dealing with "expected" errors with conditional statements rather than catching "expected" exceptions and dealing with things in a catch block.

Short anecdotal evidence: a friend of mine was working on an app, and buried deep within was a method which was throwing and catching an exception 30% of the time. They tracked it down and fixed it, causing something like a 100% performance gain overall.

@jtucholski: You can handle this in a number of ways. Easiest might be to wrap the save calls in a stored procedure that UPDATEs when it needs UPDATing and INSERTS when it needs INSERTing. Something like:

CREATE PROC Object_Save
(
    @ID int,
    @Value varchar(50),
    @InsertId int OUTPUT
)
AS
IF EXISTS(SELECT ID FROM Objects WHERE ID=@ID)
BEGIN
   UPDATE Objects
   SET (Value=@Value)
   WHERE ID=@ID
   SET @InsertId=-1
END
ELSE
BEGIN
   INSERT INTO Objects (Value)
   VALUES (@Value)
   SET @InsertId=SCOPE_IDENTITY()
END

(NB: that is Ms-Sql specific, as well as typed on the go, so your milage may vary)

Another option would be to make your business object be able to devine on its own if the primary key exists and if it should call an UPDATE or INSERT method. This works best if your primary keys are auto-incriments or GUIDs, as you can just have a default value--eg: Int.MinValue or GUID.NewGuid.

Bottom line is that you need to have conditional logic somewhere to prevent this from creating an error. Moreover, if this is a publically accessible service API, you need to make sure there are proper checks in place to make certain that people are not updating (or reading) inappropriate bits of data.

[jtucholski]

wwb_99,

For the cost of exception handling, does the cost only factor in if the exception is actually thrown? If you have the try-catch block wrapped around and the exception rarely gets thrown is it any different than not having it all in those instances?

[wwb_99]

From what I have read, they made the try bit of exception handling very cheap at the cost of making the catch bit very expensive.

Still, you should not be using a try-catch for business logic here but that should rather be modeled somewhere in the application.

[r937]

Still, you should not be using a try-catch for business logic here but that should rather be modeled somewhere in the application.

this is diametrically opposite and in complete contradiction with your other advice to bury this logic inside the database in a stored proc

in my opinion

[wwb_99]

Depends on where one sees the database here. I handle these things in code, mainly because there tend to be other rules surrounding UPDATE vs INSERT and they should be centralized, and I try and keep significant logic out of the database for maintainability's and testability's sake.

OTOH, one can make the argument that the stored procedure is a part of the application and it is valid to have the logic of the mechanics of saving data there. Especially if there is little difference in the business rules of INSERTing vs UPDATing a record.

[r937]

OTOH, one can make the argument that the stored procedure is a part of the application and it is valid to have the logic of the mechanics of saving data there.

yes, that's what i do

and i try real hard never to use the word layer in this context