Post
Ask Question

How to Implement SQL UPSERT () Method in C#

Introduction

In this article, we will learn the UPSERT () Method in C# and how to implement it in C#.

What is UPSERT () Method in C#?

Upsert is a Composite word; that combines Insert and Update. As per the name itself, Upsert defines that, using this single method, we can either Insert a record into a Table or 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 a single method.
  2. No need to create separate Stored Procedures for Insert and Update.
  3. Easy to Debug and Maintain as Upsert is the single point entry for both functionality.
  4. Single point Validation.
  5. Less Coding.

Here is the step-by-step for implementing the Upsert Method.

Step 1

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

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 your 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 the Business Logic Layer to Insert and Update the Company Data.

Conclusion

This article also explains "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.


Similar Articles