What is the best way to write code that first checks if a row exists and updates or inserts it accordingly?

Question

I am rather new to the more modern ways of coding database persistence, and my intuition is telling me I probably am missing something because my code seems "inelegant" but I do not see another way to do it.

Note: I understand maybe Linq to SQL is considered "dead". I'm more in a "learning the concepts" mode assuming that I can probably apply this to entity framework or nhibernate later.

Background

I'm getting records from one database and putting them in another. For each record retrieved there are two cases:

1. It is a brand new record.
2. It is a previously inserted record that has updated values.

How I Did this in my "old code"

I write a stored procedure called UpsertRecord that checked for the existence of a row and handled both insert and update cases accordingly. This was a pain to type out and part of the reason I started looking at a more modern approach.

USE [dtsynch]
GO

/****** Object:  StoredProcedure [dbo].[InsertStat_test1]    Script Date: 10/27/2010 15:04:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertStat_test1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertStat_test1]
GO

USE [dtsynch]
GO

/****** Object:  StoredProcedure [dbo].[InsertStat_test1]    Script Date: 10/27/2010 15:04:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertStat_test1]
 @Uri nvarchar(255),
 @clicks decimal(18,0),
 @clickthru decimal(18, 0),
 @contextualImpressions decimal(18, 0),
 @currency nvarchar(3),
 @epc decimal(18, 2),
 @impressions decimal(18,0),
 @leads decimal(18,0),
 @numSales decimal(18,0),
 @numSubSales decimal(18,0),
 @pid decimal(18,0),
 @revenue decimal(18, 2),
 @saleAmount decimal(18, 2),
 @signups decimal(18, 2),
 @subSaleAmount decimal(18, 2),
 @theyGet decimal(18, 2),
 @weGet decimal(18, 2),
 @Campaign nvarchar(255),
 @Affiliate nvarchar(255),
 @Year decimal(18,0),
 @Month decimal(18,0),
 @Day decimal(18,0)
AS
BEGIN
 SET NOCOUNT ON;
 UPDATE dtsynch.dbo.Stat_test1
 SET clicks = @clicks
       ,clickthru = @clickthru
       ,contextualImpressions = @contextualImpressions
       ,currency = @currency
       ,epc = @epc
       ,impressions = @impressions
       ,leads = @leads
       ,numSales = @numSales
       ,numSubSales = @numSubSales
       ,pid = @pid
       ,revenue = @revenue
       ,saleAmount = @saleAmount
       ,signups = @signups
       ,subSaleAmount = @subSaleAmount
       ,theyGet = @theyGet
       ,weGet = @weGet
       ,Campaign = @Campaign
       ,Affiliate = @Affiliate
       ,[Year] = @Year
       ,[Month] = @Month
       ,[Day] = @Day
 WHERE Uri = @Uri
 IF (@@ROWCOUNT = 0)
 BEGIN
  INSERT INTO dbo.Stat_test1 (
    Uri 
   ,clicks
   ,clickthru
   ,contextualImpressions
   ,currency
   ,epc
   ,impressions
   ,leads
   ,numSales
   ,numSubSales
   ,pid
   ,revenue
   ,saleAmount
   ,signups
   ,subSaleAmount
   ,theyGet
   ,weGet
   ,Campaign
   ,Affiliate
   ,[Year]
   ,[Month]
   ,[Day]
  )
  VALUES (
    @Uri 
   ,@clicks
   ,@clickthru
   ,@contextualImpressions
   ,@currency
   ,@epc
   ,@impressions
   ,@leads
   ,@numSales
   ,@numSubSales
   ,@pid
   ,@revenue
   ,@saleAmount
   ,@signups
   ,@subSaleAmount
   ,@theyGet
   ,@weGet
   ,@Campaign
   ,@Affiliate
   ,@Year
   ,@Month
   ,@Day
  )
 END
END;

GO

What I have so far using Linq to SQL:

List<Stat> stats = service.DirectTrack.GetStatsForCampaign(campaign, dateForRetreivedStats);

//service.SalesForce.UpsertStats(stats);

Syncher db = new Syncher(
 @"Data Source=AARON\SQLEXPRESS;Initial Catalog=syncher;Integrated Security=True",
 XmlMappingSource.FromXml(File.ReadAllText(@"C:\zzz4\projects\svn\repo\1\statsyncher\trunk\src\DAgents.StatSyncher\Generated Code\SqlMetal\Syncher.map")));

foreach (var i in stats)
{
 var cur = (from c in db.DTStatResource
     where c.ApiUrl == i.ExternalId
     select c).FirstOrDefault();

 if (cur == null)
 {
  DTStatResource n = new DTStatResource
  {
   ApiUrl = i.ExternalId,
   Impressions = Convert.ToInt32(i.Impressions),
   ContextualImpressions = Convert.ToInt32(i.ContextualImpressions),
   Clicks = Convert.ToInt32(i.Clicks),
   ClickThru = Convert.ToDecimal(i.ClickThru),
   Leads = Convert.ToInt32(i.Leads),
   Signups = Convert.ToDecimal(i.Signups),
   NumSales = Convert.ToInt32(i.NumSales),
   SaleAmount = Convert.ToDecimal(i.SaleAmount),
   NumSubSales = Convert.ToInt32(i.NumSubSales),
   SubSaleAmount = Convert.ToDecimal(i.SubSaleAmount),
   TheyGet = Convert.ToDecimal(i.TheyGet),
   WeGet = Convert.ToDecimal(i.WeGet),
   EPC = Convert.ToDecimal(i.EPC),
   Revenue = Convert.ToDecimal(i.Revenue),
   Currency = i.Currency,
  };
  db.GetTable<DTStatResource>().InsertOnSubmit(n);
  try
  {
   db.SubmitChanges();
  }
  catch (Exception exxx)
  {
   Console.WriteLine(
    "FAILED-breaking..." + exxx.StackTrace + exxx.Message);
   break;
  }
 }
 else
 {
   cur.ApiUrl = i.ExternalId;
   cur.Impressions = Convert.ToInt32(i.Impressions);
   cur.ContextualImpressions = Convert.ToInt32(i.ContextualImpressions);
   cur.Clicks = Convert.ToInt32(i.Clicks);
   cur.ClickThru = Convert.ToDecimal(i.ClickThru);
   cur.Leads = Convert.ToInt32(i.Leads);
   cur.Signups = Convert.ToDecimal(i.Signups);
   cur.NumSales = Convert.ToInt32(i.NumSales);
   cur.SaleAmount = Convert.ToDecimal(i.SaleAmount);
   cur.NumSubSales = Convert.ToInt32(i.NumSubSales);
   cur.SubSaleAmount = Convert.ToDecimal(i.SubSaleAmount);
   cur.TheyGet = Convert.ToDecimal(i.TheyGet);
   cur.WeGet = Convert.ToDecimal(i.WeGet);
   cur.EPC = Convert.ToDecimal(i.EPC);
   cur.Revenue = Convert.ToDecimal(i.Revenue);
   cur.Currency = i.Currency;
   try
   {
    db.SubmitChanges();
   }
   catch (Exception exxx)
   {
    Console.WriteLine(
     "FAILED-breaking..." + exxx.StackTrace + exxx.Message);
    break;
   }
 }
}

QUESTION: What surprises me is that I have to still type out everything twice. If this is indeed required I'm sure it won't surprise those more experienced and I'll be happy to just have a confirmation on that point, otherwise, is there an easier way to get the same functionality (a.k.a Upsert in Salesforce).

Answer 1

bool doInsert = (cur == null);

if (doInsert) cur = new DTStatResource();

// Do your member assignments here

if (doInsert)
{
    db.GetTable<DTStatResource>().InsertOnSubmit(n);
}

try
{
    db.SubmitChanges();
}
catch (Exception ex)
{
    // Handle exceptions
}