Можно ли вернуть идентификаторы PrimaryKey после SQL BulkCopy?

Ответ 1

В этом сценарии я бы использовал SqlBulkCopy для вставки в таблицу промежуточной (т.е. такую, которая похожа на данные, которые я хочу импортировать, но не входит в основные транзакционные таблицы), а затем в БД до INSERT/SELECT, чтобы переместить данные в первую реальную таблицу.

Теперь у меня есть два варианта в зависимости от версии сервера; Я мог бы сделать вторую INSERT/SELECT во вторую реальную таблицу, или я мог бы использовать предложение INSERT/OUTPUT, чтобы сделать вторую вставку, используя строки идентификации из таблицы.

Например:

     -- dummy schema
     CREATE TABLE TMP (data varchar(max))
     CREATE TABLE [Table1] (id int not null identity(1,1), data varchar(max))
     CREATE TABLE [Table2] (id int not null identity(1,1), id1 int not null, data varchar(max))

     -- imagine this is the SqlBulkCopy
     INSERT TMP VALUES('abc')
     INSERT TMP VALUES('def')
     INSERT TMP VALUES('ghi')

     -- now push into the real tables
     INSERT [Table1]
     OUTPUT INSERTED.id, INSERTED.data INTO [Table2](id1,data)
     SELECT data FROM TMP

Ответ 2

Если ваше приложение позволяет это, вы можете добавить еще один столбец, в котором вы храните идентификатор массовой вставки (например, guid). Вы должны установить этот идентификатор явно.

Затем после объемной вставки вы просто выбираете строки, которые имеют этот идентификатор.

Ответ 3

У меня была такая же проблема, когда мне приходилось возвращать идентификаторы строк, вставленных в SqlBulkCopy. Столбец "Идентификатор" был столбцом идентификации.

Решение:

Я вставил 500 + строк с массовой копией, а затем выбрал их со следующим запросом:

SELECT TOP InsertedRowCount * 
FROM   MyTable 
ORDER BY ID DESC

Этот запрос возвращает строки, которые я только что вставил с их идентификаторами. В моем случае у меня была еще одна уникальная колонка. Поэтому я выбрал этот столбец и идентификатор. Затем сопоставили их с IDictionary следующим образом:

 IDictionary<string, int> mymap = new Dictionary<string, int>()
 mymap[Name] = ID

Надеюсь, что это поможет.

Ответ 4

Мой подход аналогичен описанному в RiceRiceBaby, за исключением того, что нужно добавить одну важную вещь: вызов для извлечения Max (Id) должен быть частью транзакции вместе с вызовом SqlBulkCopy.WriteToServer. В противном случае, кто-то другой может вставить во время вашей транзакции, и это сделает ваш идентификатор неверным. Вот мой код:

public static void BulkInsert<T>(List<ColumnInfo> columnInfo, List<T> data, string 
destinationTableName, SqlConnection conn = null, string idColumn = "Id")
    {
        NLogger logger = new NLogger();

        var closeConn = false;


        if (conn == null)
        {
            closeConn = true;
            conn = new SqlConnection(_connectionString);
            conn.Open();
        }

        SqlTransaction tran = 
    conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

        try
        {
            var options = SqlBulkCopyOptions.KeepIdentity;
            var sbc = new SqlBulkCopy(conn, options, tran);

            var command = new SqlCommand(
                    $"SELECT Max({idColumn}) from {destinationTableName};", conn, 
           tran);
            var id = command.ExecuteScalar();

            int maxId = 0;

            if (id != null && id != DBNull.Value)
            {
                maxId = Convert.ToInt32(id);
            }

            data.ForEach(d =>
                {
                    maxId++;
                    d.GetType().GetProperty(idColumn).SetValue(d, maxId);
                });

            var dt = ConvertToDataTable(columnInfo, data);

            sbc.DestinationTableName = destinationTableName;

            foreach (System.Data.DataColumn dc in dt.Columns)
            {
                sbc.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
            }

            sbc.WriteToServer(dt);

            tran.Commit();

            if(closeConn)
            {
                conn.Close();
                conn = null;
            }
        }
        catch (Exception ex)
        {
            tran.Rollback();
            logger.Write(LogLevel.Error, [email protected]"An error occurred while performing a bulk 
insert into table {destinationTableName}. The entire
                                           transaction has been rolled back. 

{ex.ToString()}");
            throw ex;
        }
    }

Ответ 5

В зависимости от ваших потребностей и уровня контроля над таблицами вы можете захотеть использовать UNIQUEIDENTIFIER (Гиды) вместо первичных ключей IDENTITY. Это перемещает управление ключами за пределы базы данных и в ваше приложение. Есть некоторые серьезные компромиссы к этому подходу, поэтому он может не соответствовать вашим потребностям. Но, возможно, стоит подумать. Если вы точно знаете, что вы будете накачивать большое количество данных в свои таблицы с помощью массивной вставки, часто очень удобно иметь эти ключи, управляемые в вашей объектной модели, а не ваше приложение, основанное на базе данных, чтобы вернуть вам данных.

Вы также можете использовать гибридный подход с промежуточными таблицами, как было предложено ранее. Получите данные в эти таблицы с помощью GUID для отношений, а затем с помощью SQL-инструкций вы можете получить целые внешние ключи и закачать данные в свои производственные таблицы.

Ответ 6

Я бы:

• Вставьте идентификационную вставку в таблицу

• Возьмите идентификатор последней строки таблицы

• Цикл от (int i = Id; i < datable.rows.count+1; i++)

• В цикле присвойте свойству Id значение вашего значения i+1.

• Запустите SQL-вставку с включенным идентификатором keep.

• Поверните идентификационную вставку обратно

Я думаю, что самый безопасный способ получить ваши идентификаторы в SQL-вставке SQL, потому что это предотвратит появление несоответствующих идентификаторов, которые могут быть вызваны приложением в другом потоке.

Ответ 7

Отказ от ответственности: Я являюсь владельцем проекта Операции с С# на бумаге

Библиотека преодолеет ограничения SqlBulkCopy и добавляет гибкие функции, такие как выходное значение идентификации.

За кодом он точно соответствует принятому ответу, но его проще использовать.

var bulk = new BulkOperation(connection);

// Output Identity
bulk.ColumnMappings.Add("ProductID", ColumnMappingDirectionType.Output);
// ... Column Mappings...

bulk.BulkInsert(dt);