Come posso impostare il tipo di colonna quando si utilizza SqlBulkCopy per inserirlo in una colonna sql_variant

ado.net datatable sqlbulkcopy sql-server sql-variant

Domanda

Sto usando SqlBulkCopy per inserire / aggiornare da un oggetto DataTable .net a una tabella di SQL Server che include una colonna sql_variant. Tuttavia, SqlBulkCopy insiste sulla memorizzazione dei valori DateTime inseriti in tale colonna come tipo sql 'datetime' quando quello di cui ho bisogno è 'datetime2'.

My DataTable è definito in questo modo:

DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("VariantValue", typeof(object))); //this represents my sql_variant column

Quindi inserisco alcuni dati che richiedono un 'datetime2' da memorizzare.

DataRow row = dataTable.NewRow();
row[0] = DateTime.MinValue;
dataTable.Rows.Add(row);

E poi uso SqlBulkCopy per ottenere quei dati in Sql Server:

using (SqlBulkCopy bulk = new SqlBulkCopy(myConnection))
{
     bulk.DestinationTableName = "tblDestination";     
     bulk.WriteToServer(dataTable);     
}

La mia copia bulk fallirà se un valore DateTime è presente nella tabella dati che non rientra nell'intervallo del tipo sql 'datetime' (come '1/1/0001'). Ecco perché la colonna deve essere di tipo "datetime2".

Quando si scrivono normali istruzioni di inserimento che vengono inserite in una colonna sql_variant, è possibile controllare il tipo di colonna della variante utilizzando CAST o CONVERT. Per esempio:

insert into [tblDestination] (VariantValue) values (CAST('1/1/0001' AS datetime2))

Quindi se dovessi visualizzare il tipo effettivo della colonna variant in questo modo:

SELECT SQL_VARIANT_PROPERTY(VariantValue,'BaseType') AS basetype FROM test

Vedresti che in effetti viene memorizzato come un 'datetime2'.

Ma sto usando SqlBulkCopy e, per quanto ne so, non c'è spazio per dirgli che gli oggetti .net DateTime dovrebbero essere archiviati in colonne di tipo 'datetime2' e non 'datetime'. Non c'è posto sull'oggetto DataTable, che io sappia, per dichiararlo. Qualcuno può aiutarmi a capire come farlo accadere?

Risposta accettata

In base alla pagina MSDN per SqlBulkCopy (in "Note"):

SqlBulkCopy avrà esito negativo quando si carica in massa una colonna DataTable di tipo SqlDateTime in una colonna di SQL Server il cui tipo è uno dei tipi di data / ora aggiunti in SQL Server 2008.

Quindi, SqlBulkCopy non sarà in grado di gestire i valori DateTime2 . Invece, suggerirei una delle due opzioni:

  1. Inserisci ogni riga individualmente (ad esempio, utilizza un foreach sul tuo DataTable), gestendo il tipo di dati lì. (Potrebbe essere utile utilizzare un proc memorizzato per avvolgere l'inserto e utilizzare SqlCommand.Parameters per digitare i dati per l'inserto.)
  2. Inserimento di massa in una tabella temporanea di stringhe, quindi trasferire i dati nella tabella principale (conversione dei tipi di dati, se necessario) in SQL. (che credo andranno inutilmente complicato, ma si può essere in grado di eek fuori alcune prestazioni per grandi insiemi di dati)


Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché