Inserting GUIDs with SqlBulkCopy

Question

Inserting GUIDs with SqlBulkCopy

c# sqlbulkcopy sql-server sql-server-2008

Question

I'm trying to do a bulk insert with the SqlBulkCopy class from flatfiles created by the SQL Server Management Import Export Wizard. The files are comma separated.

One row in the file can look like this:

{DCAD82A9-32EC-4351-BEDC-2F8291B40AB3},,{ca91e768-072d-4e30-aaf1-bfe32c24008f},900001:1792756,900001:1792757,basladdning,2011-04-29 02:54:15.380000000,basladdning,2011-04-29 02:54:15.380000000,{20A3C50E-8029-41DE-86F1-DDCDB9A78BA5}

The error I get is:

System.InvalidOperationException was unhandled
Message=The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.

So the problem is the converting from string to GUID.

I have tried the following:

Tried different encodings, UTF8 and ANSI
Removed the { } surrounding the GUIDS
Added ' ' around the GUIDS, with and without { }

Any suggestions? In the flat file where the '' is, is a column that also has Guid as datatype. The column is NULL where the wizard exported it as ''. Can that be the problem?

The code:

using (var file = new StreamReader(filePath))
using (var csv = new CsvReader(file, false)) 
using (var bcp = new SqlBulkCopy(CreateConnectionString()))
{
    bcp.DestinationTableName = "table";
    bcp.WriteToServer(csv);
}

Table definition:

CREATE TABLE [beata].[T_FeatureInstance]( 
    [FeatureInstanceId] [uniqueidentifier] NOT NULL, 
    [FeatureInstanceParentId] [uniqueidentifier] NULL, 
    [FeatureTypeAliasId] [uniqueidentifier] NOT NULL, 
    [Uuid] [varchar](1000) NOT NULL, 
    [VersionId] [varchar](50) NOT NULL, 
    [SkapadAv] [varchar](255) NULL, 
    [Skapad] [datetime] NOT NULL, 
    [UppdateradAv] [varchar](255) NOT NULL, 
    [Uppdaterad] [datetime] NOT NULL, 
    [Gs] [uniqueidentifier] NOT NULL,

Magnus A

Popular Answer

In your DataTable.Columns.Add include the data type as a second parameter. That should do the trick. Example:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(Guid));

Paul Kar.

View more on Stack Overflow

tranceporter · Accepted Answer · 11/29/2012 4:13:04 PM

The problem is that the empty column in {DCAD82A9-32EC-4351-BEDC-2F8291B40AB3},,{ca91e768-072d-4e30-aaf1-bfe32c24008f} is being interpreted as '' instead of NULL. I just tried an INSERT STATEMENT on my local database, and that error went away when I specified NULL instead of '' There is another error you might get:

Conversion failed when converting date and/or time from character string.

The reason for this is that SQL only allows 3 digit decimal precision for milliseconds. So you have to round off '2011-04-29 02:54:15.380000000' to '2011-04-29 02:54:15.380', and it will work fine.

One way of doing it would be as mentioned here Alternatively, you could modify CsvReader code to return DBNULL when the value is string.Empty in CsvReader.GetValue(int) function. Have a look here

Inserting GUIDs with SqlBulkCopy

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator