How to Implement SQL UPSERT () Method in C#

Upsert is a Composite word; combines Insert and Update. As per the name itself Upsert defines that, using this single method we can either Insert a record in to Table or we can Update the required record by providing the new values. So using this single method we can get these advantages.

1. Instead of writing two methods separately we can aggregate the code into single method.
2. No need to create to separate Stored Procedures for Insert and Update.
3. Easy to Debug and Maintain as Upsert is the single point entry for both the functionality.
4. Single point Validation.
5. Less Coding.

Here is the step by step to implement the Upsert Method.

Step 1:

Create a Stored procedure for your Table with name like spUPSERT_EMP_MAST. Here I assumed that my table name is EMP_MAST and my Table structure is like

So the Upsert stored procedure for this table as:

CREATE PROCEDURE sp_UPSERT_CMP_MAST

@CMP_ID int output,
@CMP_NAME varchar(100),
@CMP_LOGO varchar(100),
@CMP_PNCH_LINE varchar(200),
@CMP_ADRS1 varchar(200),
@CMP_ADRS2 varchar(200),
@CMP_PHN varchar(30),
@CMP_FAX varchar(30),
@CMP_URL varchar(100),
@CMP_EMAIL varchar(100),
@FLAG bit -- Insert/Update Flag

AS
BEGIN
-- If the Insert/Update Flag is True, then it will insert a record.
IF(@FLAG = 1)
BEGIN
INSERT INTO CMP_MAST(CMP_NAME
,CMP_LOGO
,CMP_PNCH_LINE
,CMP_ADRS1
,CMP_ADRS2
,CMP_PHN
,CMP_FAX
,CMP_URL
,CMP_EMAIL)
VALUES(@CMP_NAME
,@CMP_LOGO
,@CMP_PNCH_LINE
,@CMP_ADRS1
,@CMP_ADRS2
,@CMP_PHN
,@CMP_FAX
,@CMP_URL
,@CMP_EMAIL)
-- Stores the last inserted Id from the Company Master Table to the Input-Output Variable..
SET @CMP_ID = (SELECT TOP 1 @@IDENTITY FROM CMP_MAST)
END
ELSE -- If the Flag is False then Update the Record.
BEGIN
UPDATE CMP_MAST SET CMP_NAME = @CMP_NAME
,CMP_LOGO = @CMP_LOGO
,CMP_PNCH_LINE = @CMP_PNCH_LINE
,CMP_ADRS1 = @CMP_ADRS1
,CMP_ADRS2 = @CMP_ADRS2
,CMP_PHN = @CMP_PHN
,CMP_FAX = @CMP_FAX
,CMP_URL = @CMP_URL
,CMP_EMAIL = @CMP_EMAIL
WHERE CMP_ID = @CMP_ID
END
END
RETURN

Step 2:

Now inside you Data Access Layer (DAL) add a method like

/// <summary>
/// A single method to insert/Update the Company record.
/// </summary>
/// <param name="company">A Company object.</param>
/// <param name="upsertFlag">Insert/Update Flag; set it True to Insert and False to Update.</param>
/// <returns>The last Inserted/Updated Company Id.</returns>
public int Upsert(Company company, bool upsertFlag)
{
int result = 0;
try
{
SqlCommand sqlCommand = new SqlCommand("sp_UPSERT_CMP_MAST", sqlConnection);
sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

// Input / Output SQL parameter of Company Id.
SqlParameter identity = new SqlParameter("@CMP_ID", 0);
identity.Direction = System.Data.ParameterDirection.InputOutput;

sqlCommand.Parameters.Add(identity);
sqlCommand.Parameters.Add(new SqlParameter("@CMP_NAME", company.Name));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_LOGO", company.Logo));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_PNCH_LINE", company.PunchLine));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_ADRS1", company.AddressLine1));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_ADRS2", company.AddressLine2));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_PHN", company.Phone));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_FAX", company.Fax));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_URL", company.WebURL));
sqlCommand.Parameters.Add(new SqlParameter("@CMP_EMAIL", company.Email));
sqlCommand.Parameters.Add(new SqlParameter("@FLAG", upsertFlag));

sqlConnection.Open();
sqlCommand.ExecuteNonQuery();

// Return the last inserted Id or updated Id.
result = Convert.ToInt32(sqlCommand.Parameters["@CMP_ID"].Value);
sqlConnection.Close();

sqlCommand.Dispose();
}
catch (Exception)
{
throw;
}

return result;
}

Step 3:

Now Inside your Business Logic Layer add these two methods

public int AddCompany(Company company)
{
try
{
CompanyData companyData = new CompanyData();
return companyData.Upsert(company, true);
}
catch (Exception)
{
throw;
}
}

public int EditCompany(Company company)
{
try
{
CompanyData companyData = new CompanyData();
return companyData.Upsert(company, false);
}
catch (Exception)
{
throw;
}
}