IDbProvider needs a CreateOrUpdate method so that we don't have to make multiple calls.
SQLIte will be simple
INSERT OR REPLACE INTO Employee (id, name, role)
VALUES (1, 'John Foo', 'CEO');
SQL Server will be a little trickier. We could "merge" if it's easy to do in the dialect, or we have to do it the old way with an if exists call.
# merge example
MERGE dbo.ClientData AS [Target]
USING (SELECT 12345 AS clientId, 'Some' AS data) AS [Source] ON [Target].clientId = [Source].clientId
WHEN MATCHED THEN UPDATE SET [Target].data = [Source].data, [Target].updatedDateUtc = GetUtcDate()
WHEN NOT MATCHED THEN INSERT (clientId, data) VALUES ([Source].clientId, [Source].data);
# if exists examlpe
IF NOT EXISTS (SELECT * FROM dbo.Employee WHERE ID = @SomeID)
INSERT INTO dbo.Employee(Col1, ..., ColN)
VALUES(Val1, .., ValN)
ELSE
UPDATE dbo.Employee
SET Col1 = Val1, Col2 = Val2, ...., ColN = ValN
WHERE ID = @SomeID
IDbProvider needs a CreateOrUpdate method so that we don't have to make multiple calls.
SQLIte will be simple
SQL Server will be a little trickier. We could "merge" if it's easy to do in the dialect, or we have to do it the old way with an
if exists
call.Methods to add to IDbProvider