Comment définir le type de colonne lors de l'utilisation de SqlBulkCopy pour l'insérer dans une colonne sql_variant

ado.net datatable sqlbulkcopy sql-server sql-variant

Question

J'utilise SqlBulkCopy pour insérer / mettre à jour un objet DataTable .net dans une table SQL Server incluant une colonne sql_variant. Cependant, SqlBulkCopy insiste sur le fait de stocker les valeurs DateTime placées dans cette colonne sous le type SQL "datetime" lorsque ce dont j'ai besoin est "datetime2".

Mon DataTable est défini comme ceci:

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

Ensuite, je jette quelques données qui nécessitent un "datetime2" à stocker.

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

Et puis j'utilise SqlBulkCopy pour obtenir ces données dans Sql Server:

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

Ma copie en bloc échouera si une valeur DateTime est présente dans la table de données et ne relève pas de la plage du type sql 'datetime' (tel que '1/1/0001'). C'est pourquoi la colonne doit être de type 'datetime2'.

Lorsque vous écrivez des instructions d'insertion normales insérées dans une colonne sql_variant, vous pouvez contrôler le type de la colonne variant à l'aide de CAST ou de CONVERT. Par exemple:

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

Ensuite, si vous deviez afficher le type actuel de la colonne variante comme ceci:

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

Vous verrez qu'en effet, il est stocké sous le nom "datetime2".

Mais j'utilise SqlBulkCopy et, autant que je sache, il n'y a aucun endroit pour lui dire que les objets .net DateTime doivent être stockés dans des colonnes de type 'datetime2' et non 'datetime'. À ma connaissance, il n'y a aucun endroit sur l'objet DataTable pour le déclarer. Quelqu'un peut-il m'aider à comprendre comment y parvenir?

Réponse acceptée

Selon la page MSDN pour SqlBulkCopy (sous "Remarques"):

SqlBulkCopy échouera lors du chargement en bloc d'une colonne DataTable de type SqlDateTime dans une colonne SQL Server dont le type est l'un des types date / heure ajoutés dans SQL Server 2008.

Donc, SqlBulkCopy ne sera pas capable de gérer les valeurs DateTime2 . Au lieu de cela, je suggérerais une des deux options:

  1. Insérez chaque ligne individuellement (utilisez un foreach sur votre DataTable), en y manipulant le type de données. (Il peut être utile d'utiliser un proc stocké pour envelopper l'insert et d'utiliser SqlCommand.Parameters pour taper les données de l'insert.)
  2. Insertion en bloc dans une table temporaire de chaînes, puis transférez les données dans votre table primaire (en convertissant les types de données si nécessaire) en SQL. (ce qui, je pense, deviendra inutilement compliqué, mais vous pourrez peut- être améliorer certaines performances pour de grands ensembles de données)


Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi