How to: Implement 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

CMP_ID	int	Auto Number
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)

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;
     }
}

Now from the Presentation layer you can call the Add() and Edit()methods of Business Logic Layer for Insert and/or Update of the Company Data.

Note: This Article also gives you an idea "How to Implement Auto Identity in your Table and Get the last Inserted value by C# Code". See the DAL, you can easily understand the process.