Aggiornamento collettivo in C #

Per inserire un’enorme quantità di dati in un database, ho usato per raccogliere tutte le informazioni di inserimento in un elenco e convertire questo elenco in un DataTable . Quindi inserisco l’elenco in un database tramite SqlBulkCopy .

Dove invio la mia lista generata
LiMyList
che contengono informazioni di tutti i dati di massa che voglio inserire nel database
e passalo alla mia operazione di inserimento in blocco

 InsertData(LiMyList, "MyTable"); 

Dove è InsertData

  public static void InsertData(List list,string TableName) { DataTable dt = new DataTable("MyTable"); clsBulkOperation blk = new clsBulkOperation(); dt = ConvertToDataTable(list); ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal); using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString)) { bulkcopy.BulkCopyTimeout = 660; bulkcopy.DestinationTableName = TableName; bulkcopy.WriteToServer(dt); } } public static DataTable ConvertToDataTable(IList data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); foreach (PropertyDescriptor prop in properties) table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); foreach (T item in data) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) row[prop.Name] = prop.GetValue(item) ?? DBNull.Value; table.Rows.Add(row); } return table; } 

Ora voglio fare un’operazione di aggiornamento, c’è un modo in cui l’inserimento dei dati è fatto da SqlBulkCopy per l’aggiornamento dei dati in DataBase da C # .Net

Quello che ho fatto prima è eseguire un inserimento di massa dai dati in una tabella temporanea e quindi utilizzare un comando o una stored procedure per aggiornare i dati relativi alla tabella temporanea con la tabella di destinazione. La tabella temporanea è un passaggio in più, ma puoi ottenere un guadagno in termini di prestazioni con l’inserimento in blocco e un aggiornamento massiccio se la quantità di righe è elevata, rispetto all’aggiornamento dei dati riga per riga.

Esempio:

 public static void UpdateData(List list,string TableName) { DataTable dt = new DataTable("MyTable"); dt = ConvertToDataTable(list); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString)) { using (SqlCommand command = new SqlCommand("", conn)) { try { conn.Open(); //Creating temp table on database command.CommandText = "CREATE TABLE #TmpTable(...)"; command.ExecuteNonQuery(); //Bulk insert into temp table using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn)) { bulkcopy.BulkCopyTimeout = 660; bulkcopy.DestinationTableName = "#TmpTable"; bulkcopy.WriteToServer(dt); bulkcopy.Close(); } // Updating destination table, and dropping temp table command.CommandTimeout = 300; command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;"; command.ExecuteNonQuery(); } catch (Exception ex) { // Handle exception properly } finally { conn.Close(); } } } } 

Si noti che una singola connessione viene utilizzata per eseguire l’intera operazione, in modo da poter utilizzare la tabella temporanea in ogni passaggio, poiché l’ambito della tabella temporanea è per connessione.

Nella mia esperienza personale, il modo migliore per gestire questa situazione è l’utilizzo di una stored procedure con un Table-Valued Parameter e un User-Defined Table Type . Basta impostare il tipo con le colonne della tabella di dati e passare in detta data tabella come parametro nel comando SQL.

All’interno della stored procedure, puoi unirti direttamente a una chiave univoca (se tutte le righe che stai aggiornando esistono), oppure – se potresti imbatterti in una situazione in cui devi fare entrambi gli aggiornamenti e gli inserti – usa il comando SQL Merge all’interno la procedura memorizzata per gestire sia gli aggiornamenti sia gli inserti, a seconda dei casi.

Microsoft ha sia riferimento alla syntax sia un articolo con esempi per l’unione.

Per il pezzo .NET, è sufficiente impostare il tipo di parametro come SqlDbType.Structured e impostare il valore di said-parameter sulla tabella dati che contiene i record che si desidera aggiornare.

Questo metodo offre il vantaggio sia della chiarezza che della facilità di manutenzione. Sebbene possano esserci modi che offrono miglioramenti delle prestazioni (ad esempio il rilascio in una tabella temporanea e quindi l’iterazione su quella tabella), penso che siano superati dalla semplicità di consentire a .NET e SQL di gestire il trasferimento della tabella e l’aggiornamento dei record stessi. BACIO

Prova SqlBulkTools disponibile su Nuget.

Disclaimer: sono l’autore di questa libreria.

 var bulk = new BulkOperations(); var records = GetRecordsToUpdate(); using (TransactionScope trans = new TransactionScope()) { using (SqlConnection conn = new SqlConnection(ConfigurationManager .ConnectionStrings["SqlBulkToolsTest"].ConnectionString)) { bulk.Setup() .ForCollection(records) .WithTable("MyTable") .AddColumn(x => x.SomeColumn1) .AddColumn(x => x.SomeColumn2) .BulkUpdate() .MatchTargetOn(x => x.Identifier) .Commit(conn); } trans.Complete(); } 

Solo ‘SomeColumn1’ e ‘SomeColumn2’ saranno aggiornati. Altri esempi possono essere trovati qui

Non sono sicuro di aver capito cosa intendi archiviare … Se la tua domanda riguarda la rapida sostituzione dell’intero contenuto della tabella, allora andrei a truncate ( http://technet.microsoft.com/en-us/library/ms177570. aspx ) e l’inserimento di massa di una nuova porzione di dati. Ma questo approccio funzionerà solo nel caso in cui non ci siano vincoli di chiave esterna.

Se vuoi un aggiornamento reale piuttosto che cercare la risposta di Guillermo Gutiérrez dal basso.

Vorrei inserire nuovi valori in una tabella temporanea e quindi fare unire contro la tabella di destinazione, qualcosa del genere:

 MERGE [DestTable] AS D USING #SourceTable S ON D.ID = S.ID WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT (...) VALUES (...); 

Potresti provare a creare una query che contenga tutti i dati. Usa una case . Potrebbe assomigliare a questo

 update your_table set some_column = case when id = 1 then 'value of 1' when id = 5 then 'value of 5' when id = 7 then 'value of 7' when id = 9 then 'value of 9' end where id in (1,5,7,9) 

Mi piacerebbe un approccio TempTable perché in questo modo non stai bloccando nulla. Ma se la tua logica deve essere solo nel front-end e devi usare la copia bulk, proverei un approccio Delete / Insert ma nello stesso SqlTransaction per garantire l’integrità che sarebbe qualcosa del genere:

 // ... dt = ConvertToDataTable(list); using (SqlConnection cnx = new SqlConnection(myConnectionString)) { using (SqlTranscation tran = cnx.BeginTransaction()) { DeleteData(cnx, tran, list); using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cnx, SqlBulkCopyOptions.Default, tran)) { bulkcopy.BulkCopyTimeout = 660; bulkcopy.DestinationTableName = TabelName; bulkcopy.WriteToServer(dt); } tran.Commit(); } }