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.
- Instead of writing two methods separately we can aggregate the code into single method.
- No need to create to separate Stored Procedures for Insert and Update.
- Easy to Debug and Maintain as Upsert is the single point entry for both the functionality.
- Single point Validation.
- 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.