public void insertOrCreateNoUpdate(thingInDB toInsert, string tableName, string[] searchFields)
{
using (SqlCommand cmd = _dbConn.CreateCommand())
{
Dictionary <string, object> .KeyCollection columnNameList = toInsert.columnNames.Keys;
string columnNames = string.Join(",", columnNameList);
string columnValues = string.Join(",", columnNameList.Select(x => "@" + x));
cmd.CommandText =
$"DECLARE @insertedID int " +
$" MERGE {tableName} WITH (SERIALIZABLE) AS T " +
$" USING(VALUES({columnValues})) AS U({columnNames}) " +
$" ON (" +
String.Join(" AND ", searchFields.Select(x => $"T.{x} = U.{x}")) +
$") WHEN MATCHED THEN " +
$" UPDATE SET " +
// String.Join(", ", columnNameList.Select(x => $"T.{x} = U.{x}")) +
$" @insertedID = T.id" +
$" WHEN NOT MATCHED THEN " +
$" INSERT({columnNames}) " +
$" VALUES({columnValues});";
cmd.CommandText += "; SELECT CAST(ISNULL(@insertedID, SCOPE_IDENTITY()) AS INT)";
//Debug.WriteLine(cmd.CommandText);
foreach (KeyValuePair <string, object> col in toInsert.columnNames)
{
addParamWithValue(cmd, "@" + col.Key, col.Value);
//Debug.WriteLine(col.Key + " is " + col.Value);
}
toInsert.dbID = (int)cmd.ExecuteScalar();
}
}
private void insertOrCreate(thingInDB toInsert, string tableName, string searchFields)
{
insertOrCreate(toInsert, tableName, new[] { searchFields });
}