Home / Questions / 146384
Accepted
g3rv4
Asked: 2020-03-15 16:18:15 +0800 CST 2020-03-15 16:18:15 +0800 CST

SqlBulkCopy: Error inserting a DateTime

  • 772

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 a lot of them). 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);
    }
}

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

Obviously, this is inserting NULLinto the CreationDate... but why?

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

c#

1 Answers

  1. Best Answer

    I don't think it makes a difference to use Linux, in this case using SqlBulkCopy it seems to me that you would need to map the fields so that they can be inserted into the table without problem:

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

    • 3