CODEWITHSUNDEEP
asp.netsql-servercrud
user1945782
user1945782

Reputation:

To CRUD or not to CRUD

I know the benefits of using CRUD, and that there are also some disadvantages, but I'd like to get some more expert feedback and advice on the process below for writing data to a database, particularly regarding best practice and possible pro's and con's.

I've come across two basic methods of creating records in my time as a developer. The first (and usually least helpful in most of the works I've seen) is to create a stub and use the various populated fields (including the PK) wherever it is needed. This usually leads to a raft of disowned records floating around the database with no real purpose.

The second way is to only hold a stub in memory, giving (what would be) the object's PK field a default value of, for instance, -1 to represent a new record. This keeps database access to a minimum, especially if the record is not needed later.

Personally, I've found the second way a lot more forgiving and straightforward than the first. The question I'd like to pose, though, is whether to rule out CRUD in favour of a stored procedure that carries out both the INSERT and UPDATE aspects of the CRUD process based on the afore mentioned default value, something like...

BEGIN
    IF @record_id = -1 
        INSERT ....
    ELSE
        UPDATE ....
END

Any feedback would be appreciated.

Upvotes: 2

Views: 693

Answers (1)

granadaCoder
granadaCoder

Reputation: 27852

As a rule of thumb, I tend to write Upsert procedures.......but I based the "match" on the unique_constraint, not the surrogate key.

For example.

dbo.Employee EmployeeUUID is the PK, Surrogate Key SSN is a unique constraint.

dbo.uspEmployeeUpsert would look something like this:


Insert into dbo.Employee (EmployeeUUID , LastName , FirstName, SSN )
Select NEWID() , LastName , FirstName , SSN 
from @SomeHolderTable holder
where not exists (select null from dbo.Employee innerRealTable where 
innerRealTable.SSN = holder.SSN )

Update dbo.Employee 
Set EmployeeUUID = holder.EmployeeUUID
, LastName = ISNULL ( holder.LastName , e.LastName ) /* or COALESCE */
, FirstName = COALESCE ( holder.FirstName , e.FirstName )
from dbo.Employee e , @SomeHolderTable holder
Where e.SSN = holder.SSN

You can also use the MERGE function.

You can also replace the SSN with the SurrogateKey (EmployeeUUID in this case)

What is @SomeHolderTable you ask?

I like to pass xml to the stored procedure, shred it into a @Variable or #Temp table, then write the logic for CU. D(elete) is possible as well, but I usually isolate to a separate procedure.

Why do I do it this way?

Because I can update 1 or 100 or 1000 or N records with one db hit. My logic seldom changes, and is isolated to one place.

Now, there is a small performance hit for shredding the Xml. But I find it acceptable 99% of the time. Every once in a while, I write a non "set based" Upsert routine. But that is for heavy hitter procedures for heavy hitting usage.

That's my take.

You can see the "set based" part of this approach (with the older OPENXML syntax) at this article:

http://msdn.microsoft.com/en-us/library/ff647768.aspx

Find the phrase : "Perform bulk updates and inserts by using OpenXML"

Here is the "more code" version of what the above URL talks about:

http://support.microsoft.com/kb/315968

EDIT

if exists ( select 1 from dbo.Employee e where e.SSN = holder.SSN )
BEGIN

    Insert into dbo.Employee (EmployeeUUID , LastName , FirstName, SSN )
    Select NEWID() , LastName , FirstName , SSN 
    from @SomeHolderTable holder
    where not exists (select null from dbo.Employee innerRealTable where 
    innerRealTable.SSN = holder.SSN )
END

I wouldn't necessarily do this. But its an option if you want a "boolean check".

So, with my uniqueidentifier setup, I will pass down an "Empty Guid" (00000000-0000-0000-0000-000000000000) (Guid.Empty in C#) to the procedure, when I know I have a new item. That would be my "-1" check in your scenario.

That's one method, that you could check for an "if exists".

It kinda depends on how many hands you have in the pot.

Also, I didn't mention that when I have lot of hands in the pot, I'll shred the xml.....then I'll do a BEGIN TRAN and COMMIT TRAN around my CU statements (with ROLLBACK in there as well). That way my CU is atomic, all or nothing.

The MERGE function will do this as well. But the pros and cons of MERGE is a different topic.

Upvotes: 0

Related Questions