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:
- It is a brand new record.
- 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 m开发者_StackOverflowore 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).
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
}
精彩评论