Inserire l’intero DataTable nel database contemporaneamente anziché riga per riga?

Ho un DataTable e ho bisogno dell’intera cosa spinto su una tabella di database.

Posso ottenere tutto lì dentro con un foreach e inserire ogni riga alla volta. Questo è molto lento perché ci sono poche migliaia di righe.

C’è un modo per fare l’intero datatable in una volta che potrebbe essere più veloce?

DataTable ha meno colonne della tabella SQL. il resto di loro dovrebbe essere lasciato NULL.

Ho scoperto che SqlBulkCopy è un modo semplice per farlo e non richiede la scrittura di una stored procedure in SQL Server.

Ecco un esempio di come l’ho implementato:

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

Poiché si dispone già di un DataTable e dal momento che presumo che si stia utilizzando SQL Server 2008 o versione successiva, questo è probabilmente il modo più diretto. Innanzitutto, nel tuo database, crea i seguenti due oggetti:

 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 

Ora nel tuo codice 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 avessi fornito dettagli più specifici nella tua domanda, avrei dato una risposta più specifica.

Considera questo approccio , non hai bisogno di un ciclo 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 può deviare un po ‘dal percorso rettilineo di DataTable -> tabella SQL, può anche essere fatto tramite un elenco di oggetti:

1) DataTable -> Elenco generico di oggetti

 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 maggiori dettagli possono essere trovati qui . Le proprietà mancanti rimarranno ai valori predefiniti (0 per int s, null per tipi di riferimento ecc.)

2) Spingere gli oggetti nel database

Un modo è utilizzare EntityFramework.BulkInsert estensione EntityFramework.BulkInsert . Tuttavia, è richiesto un datacontext EF.

Genera il comando BULK INSERT necessario per l’inserimento rapido (la soluzione del tipo di tabella definita dall’utente è molto più lenta di questa).

Sebbene non sia il metodo diretto, aiuta a build una base di lavoro con la lista di oggetti al posto di DataTable che sembra essere molto più efficiente in termini di memoria .

Puoi farlo con un parametro del valore di tabella.

Dai un’occhiata al seguente articolo:

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

Preferirei il tipo di dati definiti dall’utente: è super veloce.

Passaggio 1: creare la tabella definita dall’utente nel DB del server SQL

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

Passaggio 2: creare stored procedure con tipo definito dall’utente

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

Passaggio 3: Esegui la stored procedure da c #

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

Possibile problema: modificare la tabella definita dall’utente

In realtà non esiste un comando sql server per alterare il tipo definito dall’utente Ma in Management Studio è ansible ottenere questo dai seguenti passaggi

1.generare lo script per il tipo. (In una nuova finestra di query o come file) 2.delete user defied table. 3. Modificare lo script di creazione e quindi eseguire.