I feel kinda silly writing this answer, because I think that you should be able to put the pieces together a complete answer from other posts - but this is not an exact duplicate of either of the questions I have in mind.
There already are questions and answers in Stackoverflow dealing with this issue - however in my search I only found answers that are not thread safe, and most of them are using merge.
There are different questions and answers I can refer you to such as
my answer to Adding multiple parameterized variables to a database in c#
where you can see how to work with table valued parameters on c#, and to Aaron Bertrand's answer to Using a if condition in an insert SQL Server where you can see how to create a safe upsert - however I didn't find any answer that covers this completely - so here you go:
First you need to create a user defined table type in your database:
CERATE TYPE MyTableType AS TABLE
(
Column1 int NOT NULL,
Column2 int NOT NULL,
Column3 int NOT NULL,
-- rest of the columns in your table goes here
PRIMARY KEY (Column1, Column2, Column3)
)
Then, you create the stored procedure:
CREATE stp_UpsertMyTable
(
@MyTableType dbo.MyTableType readonly -- table valued parameters must be readonly
)
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE t
SET t.column4 = tvp.column4,
t.column5 = tvp.column5 -- and so on for all columns that are not part of the key
FROM dbo.MyTable AS t
INNER JOIN @MyTableType AS tvp
ON t.Column1 = tvp.Column1
AND t.Column2 = tvp.Column2
AND t.Column3 = tvp.Column3;
-- Note: <ColumnsList> should be replaced with the actual columns in the table
INSERT dbo.MyTable(<ColumnsList>)
SELECT <ColumnsList>
FROM @MyTableType AS tvp
WHERE NOT EXISTS
(
SELECT 1 FROM dbo.MyTable t
WHERE t.Column1 = tvp.Column1
AND t.Column2 = tvp.Column2
AND t.Column3 = tvp.Column3
);
COMMIT TRANSACTION;
GO
Then, the c# part is simple:
DataTable dt = new DataTable();
dt.Columns.Add("Column1", typeof(int));
dt.Columns.Add("Column2", typeof(int));
dt.Columns.Add("Column3", typeof(int));
dt.Columns.Add("Column4", typeof(string));
dt.Columns.Add("Column5", typeof(string));
// Fill your data table here
using (var con = new SqlConnection("ConnectionString"))
{
using(var cmd = new SqlCommand("stp_UpsertMyTable", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
con.Open();
cmd.ExecuteNonQuery();
}
}
Now you have a complete and safe upsert using a table valued parameter with only one round trip between c# and sql server.
