Dapper.Contrib Suggest to add InsertOrUpdate Functions

[apchenjun]

It is recommended to refer to this library to join InsertOrUpdate Functions https://github.com/olegil/SqlBulkTools I can pass in an entity for automatic comparison Insert Update Delete Operation

void ITransaction.CommitTransaction(string connectionName, SqlCredential credentials, SqlConnection connection)
        {
            if (!_list.Any())
            {
                return;
            }

            if (_disableAllIndexes && (_disableIndexList != null && _disableIndexList.Any()))
            {
                throw new InvalidOperationException("Invalid setup. If \'TmpDisableAllNonClusteredIndexes\' is invoked, you can not use the \'AddTmpDisableNonClusteredIndex\' method.");
            }

            if (_matchTargetOn.Count == 0)
            {
                throw new InvalidOperationException("MatchTargetOn list is empty when it's required for this operation. " +
                                                    "This is usually the primary key of your table but can also be more than one column depending on your business rules.");
            }
            
            DataTable dt = _helper.ToDataTable(_list, _columns, _customColumnMappings, _matchTargetOn, _outputIdentity, _outputIdentityDic);            

            // Must be after ToDataTable is called. 
            _helper.DoColumnMappings(_customColumnMappings, _columns, _matchTargetOn);

            using (SqlConnection conn = _helper.GetSqlConnection(connectionName, credentials, connection))
            {
                conn.Open();
                var dtCols = _helper.GetDatabaseSchema(conn, _schema, _tableName);
               
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    try
                    {
                        SqlCommand command = conn.CreateCommand();
                        command.Connection = conn;
                        command.Transaction = transaction;
                        command.CommandTimeout = _sqlTimeout;
                        
                        //Creating temp table on database
                        command.CommandText = _helper.BuildCreateTempTable(_columns, dtCols, _outputIdentity);
                        command.ExecuteNonQuery();

                        _helper.InsertToTmpTable(conn, transaction, dt, _bulkCopyEnableStreaming, _bulkCopyBatchSize,
                            _bulkCopyNotifyAfter, _bulkCopyTimeout, _sqlBulkCopyOptions);

                        if (_disableIndexList != null && _disableIndexList.Any())
                        {
                            command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Disable, _tableName, _disableIndexList, _disableAllIndexes);
                            command.ExecuteNonQuery();
                        }

                        //if (_outputIdentity)
                        //{
                        //    command.CommandText = _helper.GetOutputCreateTableCmd(_outputIdentity, "#TmpOutput",
                        //        OperationType.Insert);
                        //    command.ExecuteNonQuery();
                        //}

                        string comm =
                            _helper.GetOutputCreateTableCmd(_outputIdentity, "#TmpOutput", OperationType.Insert) +
                            "MERGE INTO " + _helper.GetFullQualifyingTableName(conn.Database, _schema, _tableName) +
                            " WITH (HOLDLOCK) AS Target " +
                            "USING #TmpTable AS Source " +
                            _helper.BuildJoinConditionsForUpdateOrInsert(_matchTargetOn.ToArray(),
                                _sourceAlias, _targetAlias) +
                            "WHEN MATCHED THEN " +
                            _helper.BuildUpdateSet(_columns, _sourceAlias, _targetAlias, _identityColumn) +
                            "WHEN NOT MATCHED BY TARGET THEN " +
                            _helper.BuildInsertSet(_columns, _sourceAlias, _identityColumn) +
                            (_deleteWhenNotMatchedFlag ? " WHEN NOT MATCHED BY SOURCE THEN DELETE " : " ") +
                            _helper.GetOutputIdentityCmd(_identityColumn, _outputIdentity, "#TmpOutput",
                                OperationType.Insert) +
                            "DROP TABLE #TmpTable;";
                        command.CommandText = comm;
                        command.ExecuteNonQuery();

                        if (_disableIndexList != null && _disableIndexList.Any())
                        {
                            command.CommandText = _helper.GetIndexManagementCmd(IndexOperation.Rebuild, _tableName, _disableIndexList);
                            command.ExecuteNonQuery();
                        }

                        if (_outputIdentity)
                        {
                            command.CommandText = "SELECT InternalId, Id FROM #TmpOutput;";

                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                var list = _list.ToList();

                                while (reader.Read())
                                {

                                    var test = reader[0];
                                    var test2 = reader[1];

                                    T item;

                                    if (_outputIdentityDic.TryGetValue((int)reader[0], out item))
                                    {
                                        Type type = item.GetType();

                                        PropertyInfo prop = type.GetProperty(_identityColumn);

                                        prop.SetValue(item, reader[1], null);
                                    }


                                }
                            }

                        }

                        transaction.Commit();

                    }

                    catch (SqlException e)
                    {
                        for (int i = 0; i < e.Errors.Count; i++)
                        {
                            // Error 8102 is identity error. 
                            if (e.Errors[i].Number == 8102)
                            {
                                // Expensive call but neccessary to inform user of an important configuration setup. 
                                throw new IdentityException(e.Errors[i].Message);
                            }
                        }

                        transaction.Rollback();
                        throw;
                    }

                    catch (Exception)
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

[giammin]

IMHO most of the times you just need an if

          if (entity.Id == 0)
          {
              await Connection.InsertAsync(entity);
          }
          else
          {
               await Connection.UpdateAsync(entity);
          }

[apchenjun]

@giammin i have one to many entity edit InsertOrUpdate

[gismofx]

ref here: #7

and here: https://github.com/gismofx/DapperUpsert