SqlBulkCopy nella tabella che i valori di colonna Predefiniti hanno esito negativo quando la riga DataTable di origine ha DBNull.Value

c# default-constraint null sqlbulkcopy sql-server

Domanda

Aggiornamento: ecco la mia soluzione

Ho una tabella definita come:

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]

Ho un DataTable con colonne che corrispondono ai nomi delle colonne della tabella e ai tipi di dati. DataTable è compilato con DBNull.Value in CreatedOn , LastUpdatedOn e LastUpdatedUser . ReferenceID è già generato. Quando chiamo il codice seguente ottengo l'errore qui sotto.

Codice:

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

Errore:

Errore durante il tentativo di richiamare la tabella BulkCopy csvrf_References
System.InvalidOperationException: la colonna 'CreatedOn' non consente DBNull.Value.
a System.Data.SqlClient.SqlBulkCopy.ConvertValue (valore dell'oggetto, metadati _SqlMetaData, Boolean isNull, Boolean & isSqlType, Boolean & coercedToDataFeed)

Ho guardato dappertutto e non riesco a trovare una risposta per questo. La classe SqlBulkCopy sembra non rispettare i valori predefiniti anche se dice che lo fa. Cosa sto facendo di sbagliato qui?

Risposta accettata

Per la parte 1, "campo che NON è NULL con un DEFAULT", non devi inviare il campo in primo luogo. Non dovrebbe essere mappato. Non è necessario modificare quel campo per accettare NULL solo per questo.

Da parte 2 "campo che è NULL con un DEFAULT", che lavorerà per ottenere il valore predefinito quando passando DBNull.Value, fino a quando non si hanno le SqlBulkCopyOptions impostati KeepNulls , altrimenti inserisce un database effettivo NULL .

Dato che c'è una certa confusione riguardo allo SqlBulkCopyOption di KeepNulls , diamo un'occhiata alla sua definizione:

Mantieni valori nulli nella tabella di destinazione indipendentemente dalle impostazioni per i valori predefiniti. Se non specificato, i valori nulli vengono sostituiti dai valori predefiniti, ove applicabile.

Ciò significa che una DbNull.Value DataColumn impostata su DbNull.Value verrà inserita come un database NULL , anche se la colonna ha un KeepNulls PREDEFINITO, se viene specificata l'opzione KeepNulls . Non è specificato nel tuo codice. Il che porta alla seconda parte che dice che i valori di DbNull.Value sono sostituiti da "valori predefiniti" dove applicabile. Qui "applicabile" significa che la colonna ha un CONSENSO PREDEFINITO definito su di esso. Quindi, quando esiste un DbNull.Value , verrà inviato un valore non DbNull.Value così com'è mentre DbNull.Value deve tradurre nella parola chiave SQL DEFAULT . Questa parola chiave è interpretata in un'istruzione INSERT come il valore del vincolo DEFAULT. Naturalmente, è anche possibile che SqlBulkCopy , se si emettono singole istruzioni INSERT, possa semplicemente lasciare il campo fuori dall'elenco delle colonne, se impostato su NULL per quella riga, che raccoglierà il valore predefinito. In entrambi i casi, il risultato finale è che funziona come previsto. E i miei test dimostrano che funziona davvero in questo modo.

Per essere chiari sulla distinzione:

  • Se un campo nel database è impostato su NOT NULL e su di esso è definito un CONSENSO PREDEFINITO, le opzioni sono:

    • Passa sul campo (cioè non raccoglierà il valore DEFAULT), nel qual caso non potrà mai essere impostato su DbNull.Value

    • Non passare nel campo affatto (cioè riprenderà il valore di default), che può essere realizzato da uno:

      • Non averlo nel DataTable o nella query o nel DataReader o in qualsiasi altra cosa venga inviata come sorgente, nel qual caso non è necessario specificare la raccolta di ColumnMappings

      • Se il campo si trova nell'origine, è necessario specificare la raccolta ColumnMappings modo da poter lasciare il campo fuori dai mapping.

    • Impostando, o non settando, KeepNulls non cambia il comportamento sopra annotato.

  • Se un campo nel database è impostato su NULL e su di esso è definito un CONSUNTAMENTO PREDEFINITO, le opzioni disponibili sono:

    • Non passare nel campo affatto (cioè riprenderà il valore di default), che può essere realizzato da uno:

      • Non averlo nel DataTable o nella query o nel DataReader o in qualsiasi altra cosa venga inviata come sorgente, nel qual caso non è necessario specificare la raccolta di ColumnMappings

      • Se il campo si trova nell'origine, è necessario specificare la raccolta ColumnMappings modo da poter lasciare il campo fuori dai mapping.

    • Passa nel campo impostato su un valore che non è DbNull.Value , nel qual caso verrà impostato su questo valore e non prenderà il valore DEFAULT

    • Passa nel campo come DbNull.Value , nel qual caso l'effetto è determinato dal fatto che SqlBulkCopyOptions sia passato e sia stato impostato su KeepNulls :

      • KeepNulls non è impostato, raccoglierà il valore DEFAULT

      • KeepNulls è impostato lascerà il campo impostato su NULL


Ecco un semplice test per vedere come funziona la parola chiave 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;

risultati:

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

Risposta popolare

Leggendo la documentazione relativa a SqlBulkCopy , in particolare SqlBulkCopyOptions , vorrei trarre la stessa conclusione che si è fatto: SQL Server dovrebbe essere abbastanza "intelligente" da utilizzare il vincolo predefinito laddove applicabile, soprattutto dal momento che non si utilizza l'attributo SqlBulkCopyOptions.KeepNulls .

Tuttavia, in questo caso, sospetto che la documentazione sia sottilmente errata; se non è corretto, è sicuramente fuorviante.

Come è stato osservato, con un campo non annullabile con un vincolo predefinito (in questo caso GetDate() ), SqlBulkCopy ha esito negativo con l'errore summenzionato.

Come test, prova a creare una seconda tabella che simuli la prima, ma questa volta rende nullable i campi CreatedOn e LastUpdatedOn . Nei miei test, utilizzando le opzioni predefinite ( SqlBulkCopyOptions.Default ) il processo funziona senza errori e CreatedOn e LastUpdatedOn hanno entrambi il valore DateTime corretto popolato nella tabella nonostante il fatto che i valori di DataTable per tali campi fossero DBNull.Value .

Come ancora un altro test, utilizzando la stessa tabella (campi Nullabili), eseguire SqlBulkCopy solo questa volta utilizzando l'attributo SqlBulkCopyOptions.KeepNulls . Sospetto che vedrete gli stessi risultati che ho fatto, ovvero CreatedOn e LastUpdatedOn sono entrambi nulli nella tabella.

Questo comportamento è simile all'esecuzione di un'istruzione T-SQL "vanilla" per inserire dati nella tabella.

Usando la tabella originale (campi non annullabili) come esempio, se si esegue

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

riceverai un errore simile per quanto riguarda i valori nulli non consentiti nella tabella.

Tuttavia, se si omettono i campi non annullabili dall'istruzione, SQL Server utilizza i vincoli predefiniti per tali campi:

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

Sulla base di questo, suggerirei di rendere i campi nullable nella tabella (non è davvero una buona opzione secondo me) O usare una tabella di "staging" per il processo SqlBulkCopy (dove i campi sono annullabili e hanno un vincolo predefinito simile sul posto ). Una volta che i dati sono nella tabella di staging, eseguire una seconda istruzione per spostare i dati nella tabella di destinazione finale effettiva.



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow