SqlBulkCopy: Error inserting a DateTime

Question

SqlBulkCopy: Error inserting a DateTime

Navigation

#1 by (3 votes)

3

I have a table with this structure:

Create Table dbo.DoesNotMakeSense
(
  Id Int Not Null Identity (1, 1),
  Name NVarChar(100),
  CreationDate DateTime Not Null,

  Constraint PK_DoesNotMakeSense Primary Key Clustered (Id Asc)
)
On [Primary]

And I want to insert records using SqlBulkCopy (because there are going to be many). When I use this code:

static void Main(string[] args)
{
    var table = new DataTable();
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("CreationDate", typeof(DateTime));

    table.Rows.Add("Pepe", DateTime.UtcNow);

    using(var db = new SqlConnection("Server=127.0.0.1;Database=Traducir;User Id=sa;Password=SuperP4ssw0rd!;"))
    using(SqlBulkCopy copy = new SqlBulkCopy(db))
    {
        copy.DestinationTableName = "dbo.DoesNotMakeSense";
        db.Open();
        copy.WriteToServer(table);
    }
}

Fails with an exception that says Cannot insert the value NULL into column 'CreationDate', table 'Traducir.dbo.DoesNotMakeSense'; column does not allow nulls. INSERT fails.

Obviously, this is doing an insert with NULL in CreationDate ... but why do you want to?

I'm running SQL Server Express on Linux (although I do not think it makes a difference ... because my suspicion is that SqlBulkCopy is not serializing the value of CreationDate well).

c# sql-server .net-core

asked by g3rv4 15.03.2018 в 01:18

source

1 answer

How to validate start date is less than end date Datepicker Boostrap Ask before reloading / changing page

score 3 · Accepted Answer

I do not think it's a difference to use Linux, in this case using SqlBulkCopy I think you need to map the fields so that they can be inserted without problems in the table:

using(var db = new SqlConnection("Server=127.0.0.1;Database=Traducir;User Id=sa;Password=SuperP4ssw0rd!;"))
using(SqlBulkCopy copy = new SqlBulkCopy(db))
using (var colReader = ObjectReader.Create(/*data, membersExposedToReader*/))
{


   SqlBulkCopyColumnMapping mName = new SqlBulkCopyColumnMapping("Name", "Name");
   SqlBulkCopyColumnMapping mCreationDate = new SqlBulkCopyColumnMapping("CreationDate", "CreationDate");

    copy.ColumnMappings.Add(mName);   
    copy.ColumnMappings.Add(mCreationDate);   
    copy.DestinationTableName = "dbo.DoesNotMakeSense";

    db.Open();

    copy.WriteToServer(colReader/*table*/);
}

Something similar to what was done in this question (English) .