Upsert - Efficient Update or Insert in VB.Net, SQL Server

Question

I'm trying to understand how to streamline the process of inserting a record if none exists or updating a record if it already exists. I'm not using stored procedures, although maybe that would be the most efficient way of doing this.

The actual scenario in which this is necessary is saving a user preference/setting to my SettingsUser table.

In MS Access I would typically pull a DAO recordset looking for the specified setting. If the recordset comes back empty then I know I need to add a new record which I can do with the same recordset object. On the other hand, if it isn't empty, I can just update the setting's value right away. In theory, this is only two database operations.

What is the recommended way of doing this in .NET?

Typically you would have logic in a stored procedure that would take the data for that row and determine if the key is already present. UPDATE WHERE ... if @@ROWCOUNT = 0 INSERT. In a transaction with suitable isolation level and with error handling of course. Pulling a recordset to the application to search for a duplicate is just about the most inefficient way I could imagine to do such a thing. Unless you serialized it to JSON, converted to XML, and then searched. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 19, 2012 at 17:06

Guffa · Accepted Answer · 2012-10-19 22:23:35Z

2

A stored procedure is certainly an easy way to do that. There you can try to update the record, and if no record changes, you add it. Example:

create procedure UpateUserSetting
  @UserId int,
  @Setting int
as

set nocount on

update UserSetting
set Setting = @Setting
where UserId = @UserId

if (@@rowcount = 0) begin

  insert into UserSetting (
    UserId, Setting
  ) values (
    @UserId, @Setting
  )

end

You can do the same without a stored procedure. Then you would first run the update, and check the number of affected rows, which you get returned from the ExecuteNonQuery method, and do the insert in another query if needed.

edited Oct 19, 2012 at 22:23

answered Oct 19, 2012 at 17:06

Guffa

703k112 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Upsert - Efficient Update or Insert in VB.Net, SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related