SqlBulkCopy dans la table dont les valeurs de colonne par défaut échouent lorsque la ligne source DataTable a DBNull.Value

c# default-constraint null sqlbulkcopy sql-server

Question

Mise à jour: Voici ma solution

J'ai une table définie comme:

CREATE TABLE [dbo].[csvrf_References]
(
    [Ident] [int] IDENTITY(1,1) NOT NULL,
    [ReferenceID] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
    [Type] [nvarchar](255) NOT NULL,
    [Location] [nvarchar](1000) NULL,
    [Description] [nvarchar](2000) NULL,
    [CreatedOn] [datetime] NOT NULL DEFAULT (getdate()),
    [LastUpdatedOn] [datetime] NOT NULL DEFAULT (getdate()),
    [LastUpdatedUser] [nvarchar](100) NOT NULL DEFAULT (suser_sname()),

    CONSTRAINT [PK_References] PRIMARY KEY NONCLUSTERED ([ReferenceID] ASC)
) ON [PRIMARY]

J'ai un DataTable avec des colonnes qui correspondent aux noms de colonne et aux types de données de la table. Le DataTable est rempli avec DBNull.Value dans CreatedOn , LastUpdatedOn et LastUpdatedUser . ReferenceID est déjà généré. Lorsque j'appelle le code suivant, j'obtiens l'erreur ci-dessous.

Code:

SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, bulkCopyTran);
bulkCopy.DestinationTableName = table.TableName;
bulkCopy.ColumnMappings.Clear();
foreach (DataColumn col in table.Columns) bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
bulkCopy.WriteToServer(table);

Erreur:

Erreur lors de la tentative de la table BulkCopy csvrf_References
System.InvalidOperationException: La colonne 'CreatedOn' n'autorise pas DBNull.Value.
at System.Data.SqlClient.SqlBulkCopy.ConvertValue (valeur d'objet, métadonnées _SqlMetaData, booléen isNull, booléen et isSqlType, booléen et forcéToDataFeed)

J'ai regardé partout et je n'arrive pas à trouver de réponse à cette question. La classe SqlBulkCopy ne semble pas respecter les valeurs par défaut même si elle le dit. Qu'est-ce que je fais mal ici?

Réponse acceptée

Pour la partie 1, "champ NOT NULL avec DEFAULT", vous ne devriez pas envoyer le champ en premier lieu. Il ne devrait pas être mappé. Il n'est pas nécessaire de modifier ce champ pour accepter les valeurs NULL uniquement pour cela.

Pour la partie 2, « champ qui est NULL avec une DEFAULT », qui fonctionnera pour obtenir la valeur par défaut lors du passage dans DBNull.Value, aussi longtemps que vous n'avez pas les SqlBulkCopyOptions mis à KeepNulls , sinon il va insérer une base de données réelle NULL

Puisqu'il y a une certaine confusion à propos de SqlBulkCopyOption de KeepNulls , examinons sa définition:

Conservez les valeurs NULL dans la table de destination, quels que soient les paramètres définis pour les valeurs par défaut. Lorsqu'elles ne sont pas spécifiées, les valeurs NULL sont remplacées par les valeurs par défaut, le cas échéant.

Cela signifie qu'un DataColumn défini sur DbNull.Value sera inséré en tant que base de données NULL , même si la colonne a une DEFAULT CONSTRAINT, si l'option KeepNulls est spécifiée. Ce n'est pas spécifié dans votre code. Ce qui conduit à la deuxième partie qui dit DbNull.Value valeurs DbNull.Value sont remplacées par des "valeurs par défaut" le cas échéant. Ici, "applicable" signifie que la colonne a une contrainte par défaut définie. Par conséquent, lorsqu'une DbNull.Value DEFAULT existe, une valeur non DbNull.Value sera envoyée telle DbNull.Value tandis que DbNull.Value devrait être DbNull.Value en mot clé SQL DEFAULT . Ce mot clé est interprété dans une instruction INSERT comme prenant la valeur de la contrainte DEFAULT. Bien entendu, il est également possible que SqlBulkCopy , si elle émet des instructions INSERT individuelles, laisse simplement ce champ en dehors de la liste des colonnes s'il est défini sur NULL pour cette ligne, ce qui permettra de choisir la valeur par défaut. Dans les deux cas, le résultat final est que cela fonctionne comme prévu. Et mes tests montrent que cela fonctionne effectivement de cette manière.

Pour être clair sur la distinction:

  • Si un champ de la base de données est défini sur NOT NULL et que sa contrainte par défaut est définie, vos options sont les suivantes:

    • Passez dans le champ (c’est-à-dire qu’il ne prendra pas la valeur DEFAULT), auquel cas il ne peut jamais être défini sur DbNull.Value

    • Ne passez pas du tout dans le champ (c’est-à-dire qu'il prendra la valeur DEFAULT), ce qui peut être accompli soit:

      • Ne pas l'avoir dans le DataTable ou la requête ou DataReader ou tout ce qui est envoyé en tant que source, auquel cas vous n'aurez peut-être pas besoin de spécifier la collection ColumnMappings

      • Si le champ est dans la source, vous devez alors spécifier la collection ColumnMappings afin de pouvoir laisser ce champ en dehors des mappages.

    • Définir ou non, KeepNulls ne modifie pas le comportement mentionné ci-dessus.

  • Si un champ de la base de données est défini sur NULL et que le paramètre DEFAULT CONSTRAINT est défini dessus, vos options sont les suivantes:

    • Ne passez pas du tout dans le champ (c’est-à-dire qu'il prendra la valeur DEFAULT), ce qui peut être accompli soit:

      • Ne pas l'avoir dans le DataTable ou la requête ou DataReader ou tout ce qui est envoyé en tant que source, auquel cas vous n'aurez peut-être pas besoin de spécifier la collection ColumnMappings

      • Si le champ est dans la source, vous devez alors spécifier la collection ColumnMappings afin de pouvoir laisser ce champ en dehors des mappages.

    • DbNull.Value le champ défini à une valeur autre que DbNull.Value , auquel cas il sera défini sur cette valeur et ne prendra pas la valeur DEFAULT.

    • DbNull.Value le champ sous la forme DbNull.Value , auquel cas l'effet est déterminé par le fait que SqlBulkCopyOptions est transmis ou non et qu'il a été défini sur KeepNulls :

      • KeepNulls n'est pas défini va prendre la valeur DEFAULT

      • KeepNulls est défini laissera le champ défini sur NULL


Voici un test simple pour voir comment fonctionne le mot clé DEFAULT :

--DROP TABLE ##DefaultTest;
CREATE TABLE ##DefaultTest
(
  Col1 INT,
  [CreatedOn] [datetime] NOT NULL DEFAULT (GETDATE()),
  [LastUpdatedOn] [datetime] NULL DEFAULT (GETDATE())
);
INSERT INTO ##DefaultTest (Col1, CreatedOn) VALUES (1, DEFAULT);
INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (2, DEFAULT);
INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (3, NULL);
INSERT INTO ##DefaultTest (Col1, LastUpdatedOn) VALUES (4, '3333-11-22');

SELECT * FROM ##DefaultTest ORDER BY Col1 ASC;

Résultats:

Col1   CreatedOn                  LastUpdatedOn
1      2014-11-20 12:34:31.610    2014-11-20 12:34:31.610
2      2014-11-20 12:34:31.610    2014-11-20 12:34:31.610
3      2014-11-20 12:34:31.610    NULL
4      2014-11-20 12:34:31.613    3333-11-22 00:00:00.000

Réponse populaire

En lisant la documentation concernant SqlBulkCopy , en particulier SqlBulkCopyOptions , je tirerais la même conclusion que celle que vous avez faite: SQL Server devrait être suffisamment "intelligent" pour utiliser la contrainte par défaut, le cas échéant, d'autant plus que vous n'utilisez pas l'attribut SqlBulkCopyOptions.KeepNulls .

Cependant, dans ce cas, je soupçonne que la documentation est subtilement incorrecte; si ce n’est pas incorrect, c’est certainement trompeur.

Comme vous l'avez observé, avec un champ non nullable avec une contrainte par défaut (dans ce cas, GetDate() ), SqlBulkCopy échoue avec l'erreur susmentionnée.

En guise de test, essayez de créer une deuxième table qui imite la première, mais cette fois, LastUpdatedOn champs CreatedOn et LastUpdatedOn . Dans mes tests, en utilisant les options par défaut ( SqlBulkCopyOptions.Default ), le processus fonctionne sans erreur et CreatedOn et LastUpdatedOn ont la valeur DateTime correcte renseignée dans la table malgré le fait que les valeurs du DataTable pour ces champs étaient DBNull.Value .

En tant que test supplémentaire, en utilisant la même table (champs nullables), effectuez la SqlBulkCopy uniquement cette fois-ci en utilisant l’attribut SqlBulkCopyOptions.KeepNulls . Je suppose que vous obtiendrez les mêmes résultats que moi, à savoir que CreatedOn et LastUpdatedOn sont tous deux nuls dans la table.

Ce comportement est similaire à l'exécution d'une instruction "vanille" T-SQL pour insérer des données dans la table.

Utilisation de la table d'origine (champs non nullables) à titre d'exemple, si vous exécutez

INSERT INTO csvrf_References ([Type], [Location], [Description], [CreatedOn], [LastUpdatedOn], [LastUpdatedUser]) 
VALUES ('test', 'test', 'test', null, null, null)

vous recevrez une erreur similaire concernant les valeurs NULL non autorisées dans la table.

Toutefois, si vous omettez les champs non nullables de l'instruction, SQL Server utilise les contraintes par défaut pour ces champs:

INSERT INTO csvrf_References ([Type], [Location], [Description]
VALUES ('test', 'test', 'still testing')

Sur cette base, je suggèrerais soit de rendre les champs nullables dans la table (ce qui n'est pas vraiment une bonne option à mon avis) OU d'utiliser une table "staging" pour le processus SqlBulkCopy (où les champs sont nullables et ont une contrainte par défaut similaire en place ). Une fois que les données sont dans la table intermédiaire, exécutez une deuxième instruction pour les déplacer dans la table de destination finale.



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