Вставить целую DataTable в базу данных сразу вместо строки за строкой?

Ответ 1

Я обнаружил, что SqlBulkCopy - это простой способ сделать это и не требует, чтобы хранимая процедура записывалась на SQL Server.

Вот пример того, как я его реализовал:

// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.  

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

Ответ 2

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

CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
AS TABLE
(
  col1 INT,
  col2 DATETIME
  -- etc etc. The columns you have in your data table.
);
GO

CREATE PROCEDURE dbo.InsertMyDataTable
  @dt AS dbo.MyDataTable READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.RealTable(column list) SELECT column list FROM @dt;
END
GO

Теперь в коде С#:

DataTable tvp = new DataTable();
// define / populate DataTable

using (connectionObject)
{
    SqlCommand cmd = new SqlCommand("dbo.InsertMyDataTable", connectionObject);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    cmd.ExecuteNonQuery();
}

Если бы вы задали более конкретные детали в своем вопросе, я бы дал более конкретный ответ.

Ответ 3

Рассмотрим этот подход, вам не нужен цикл for:

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = 
        "dbo.BulkCopyDemoMatchingColumns";

    try
    {
        // Write from the source to the destination.
        bulkCopy.WriteToServer(ExitingSqlTableName);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Ответ 4

Если вы можете немного отклониться от прямого пути к таблице DataTable → SQL, это также можно сделать с помощью списка объектов:

1) DataTable → Общий список объектов

public static DataTable ConvertTo<T>(IList<T> list)
{
    DataTable table = CreateTable<T>();
    Type entityType = typeof(T);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (T item in list)
    {
        DataRow row = table.NewRow();

        foreach (PropertyDescriptor prop in properties)
        {
            row[prop.Name] = prop.GetValue(item);
        }

        table.Rows.Add(row);
    }

    return table;
}

Источник и более подробную информацию можно найти здесь. Отсутствующие свойства останутся до значений по умолчанию (0 для int s, null для ссылочных типов и т.д.)

2) Вставьте объекты в базу данных

Один из способов - использовать расширение EntityFramework.BulkInsert. Однако необходим файл данных EF.

Он генерирует команду BULK INSERT, необходимую для быстрой вставки (определенное пользователем решение типа таблицы намного медленнее, чем это).

Хотя это не прямой метод, он помогает создать базу для работы со списком объектов вместо DataTable, который кажется гораздо более эффективным с точки зрения памяти.

Ответ 5

Вы можете сделать это со значениями параметров таблицы.

Взгляните на следующую статью:

http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters

Ответ 6

Я бы предпочел пользовательский тип данных: он очень быстрый.

Шаг 1: Создание пользовательской таблицы в Sql Server DB

CREATE TYPE [dbo].[udtProduct] AS TABLE(
  [ProductID] [int] NULL,
  [ProductName] [varchar](50) NULL,
  [ProductCode] [varchar](10) NULL
)
GO

Шаг 2. Создание хранимой процедуры с использованием пользовательского типа

CREATE PROCEDURE ProductBulkInsertion 
@product udtProduct readonly
AS
BEGIN
    INSERT INTO Product
    (ProductID,ProductName,ProductCode)
    SELECT ProductID,ProductName,ProductCode
    FROM @product
END

Шаг 3: Выполнение сохраненной процедуры из С#

SqlCommand sqlcmd = new SqlCommand("ProductBulkInsertion", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@product", productTable);
sqlcmd.ExecuteNonQuery();

Возможная проблема: изменить пользовательскую таблицу

На самом деле нет команды sql server для изменения определенного пользователем типа Но в студии управления вы можете достичь этого из следующих шагов.

1.generate script для типа. (в новом окне запроса или в виде файла) 2. Удалите таблицу, на которую указывает пользователь. 3.modify create script, а затем выполните.