How to Implement SQL UPSERT () Method in C#

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