Exemplo n.º 1
0
        void ITransaction.CommitTransaction(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(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();
                    }
                }
            }
        }