Inserir toda DataTable no database ao mesmo tempo em vez de linha por linha?

Eu tenho um DataTable e preciso de toda a coisa empurrada para uma tabela de database.

Eu posso colocar tudo lá com foreach e inserir cada linha de cada vez. Isso é muito lento, já que existem alguns milhares de linhas.

Existe alguma maneira de fazer toda a tabela de dados de uma só vez que pode ser mais rápida?

O DataTable tem menos colunas que a tabela SQL. o resto deles deve ser deixado NULL.

Descobri que o SqlBulkCopy é uma maneira fácil de fazer isso e não exige que um procedimento armazenado seja gravado no SQL Server.

Aqui está um exemplo de como eu implementei:

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

Como você já tem uma DataTable e desde que eu suponho que você esteja usando o SQL Server 2008 ou melhor, essa é provavelmente a maneira mais direta. Primeiro, no seu database, crie os dois objects a seguir:

 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 

Agora no seu código 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(); } 

Se você tivesse dado detalhes mais específicos em sua pergunta, eu teria dado uma resposta mais específica.

Considere esta abordagem , você não precisa de um loop 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); } } 

Se pode desviar um pouco do caminho direto da tabela DataTable -> SQL, também pode ser feito através de uma lista de objects:

1) DataTable -> Lista genérica de objects

 public static DataTable ConvertTo(IList list) { DataTable table = CreateTable(); 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; } 

Fonte e mais detalhes podem ser encontrados aqui . As propriedades ausentes permanecerão com seus valores padrão (0 para int s, null para tipos de referência, etc.)

2) Empurre os objects para o database

Uma maneira é usar a extensão EntityFramework.BulkInsert . Um datacontext da EF é necessário, no entanto.

Ele gera o comando BULK INSERT necessário para inserção rápida (a solução de tipo de tabela definida pelo usuário é muito mais lenta que isso).

Embora não seja o método direto, ele ajuda a construir uma base de trabalho com a lista de objects em vez de DataTable s, que parece ser muito mais eficiente na memory .

Você pode fazer isso com um parâmetro de valor de tabela.

Dê uma olhada no seguinte artigo:

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

Eu preferiria o tipo de dados definido pelo usuário: é super rápido.

Etapa 1: Criar tabela definida pelo usuário no database do Sql Server

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

Etapa 2: criar procedimento armazenado com tipo definido pelo usuário

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

Etapa 3: Executar o procedimento armazenado de c #

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

Possível problema: alterar a tabela definida pelo usuário

Na verdade, não há nenhum comando sql server para alterar o tipo definido pelo usuário. Mas, no estúdio de gerenciamento, você pode conseguir isso seguindo as etapas a seguir.

Script 1.generate para o tipo. (Na nova janela de consulta ou como um arquivo) 2.delete tabela desafiada pelo usuário. 3.modificar o script de criação e depois executar.