Вопрос по sql, c#, sql-server – Вставить весь DataTable в базу данных сразу, а не строка за строкой?

24

У меня есть DataTable, и мне нужно, чтобы все это было помещено в таблицу базы данных.

Я могу получить все это с помощью foreach и вставлять каждую строку за раз. Это идет очень медленно, хотя, поскольку есть несколько тысяч строк.

Есть ли способ сделать все данные одновременно, что может быть быстрее?

В DataTable меньше столбцов, чем в таблице SQL. остальные из них должны быть пустыми.

codedigest.com/Articles/Framework/… для примера SqlBulkCopy. JamieSee
SqlBulkCopy - это, безусловно, путь Wiktor Zychla

Ваш Ответ

6   ответов
56

что 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);
}
@AaronBertrand & quot; Ответы & quot; вот почему я избегаю ТАК. Пост Кайла - это именно то, что я искал в этой конкретной ситуации. К сожалению, я пробирался через твои придирки, чтобы найти его.
Я не уверен, откуда пришел ответ, если в нем нет полезной информации. Причина, по которой я сделал новый, заключалась в том, что комментарий больше не имел смысла в обновленном ответе и не имел отношения к вопросу. Kyle
@AaronBertrand У меня был предыдущий ответ, который был просто «SQL Bulk Copy», без каких-либо подробностей. Я удалил его и заменил на этот подробный. Во многих ситуациях SP не плох, в некоторых это так. Я никогда не заявлял, что это плохо, я просто предлагаю решение, которое не требует этого. Kyle
Я нашел это полезным. Хватит тупить.
Для потомков вы должны отредактировать такой ответ, а не удалять его и публиковать новый. Тогда для других будет более интуитивно понятно, откуда появился этот новый ответ.
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);
    }
}
для новичков, таких как я: bulkCopy.WriteToServer (ExitingSqlTableName); Здесь ExistingSqlTableName означает существующий источник данных sql. Это может быть таблица sql, datatable или читатель.
1

Таблицу SQL, это также можно сделать через список объектов:

1) DataTable -> Generic list of objects

public static DataTable ConvertTo<T>(ILi,st<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 дляints, null для ссылочных типов и т. д.)

2) Push the objects into the database

Одним из способов является использованиеEntityFramework.BulkInsert расширение. EF datacontext требуется, однако.

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

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

0

Step 1 : Create User Defined Table in Sql Server DB

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

Step 2 : Create Stored Procedure with User Defined Type

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

Step 3 : Execute Stored Procedure from c#

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

Possible Issue : Alter User Defined Table

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

1. создать скрипт для типа. (В новом окне запроса или в виде файла) 2. удалить пользовательскую таблицу. 3. Измените сценарий создания и затем выполните.

31

и, как я предполагаю, вы используете 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

Теперь в вашем коде C #:

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();
}

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

Если я не ошибаюсь, это будет работать прилично только для относительно небольших объемов данных, так как @dt будет заполняться построчно.SqlBulkCopy гораздо более эффективен для больших данных (тысячи и более).
во время вставки я могу проверить и создать таблицу из заголовков Datatable
@ Аарон Бертран У меня есть более 600000 записей для 3 разных таблиц. Таблица A не зависит от любой другой таблицы, в то время как для таблицы B требуется первичный ключ из таблицы A, а для таблицы C требуется значение первичного ключа из A и B. Также для таблицы D требуется обновление 10000 записей. Как я могу этого добиться.
0

Посмотрите на следующую статью:

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

Похожие вопросы