CODEWITHSUNDEEP
sql-servervb.netado.net
HK1
HK1

Reputation: 12210

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

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?

Upvotes: 0

Views: 1701

Answers (1)

Guffa
Guffa

Reputation: 700322

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.

Upvotes: 2

Related Questions