How can I make reading Null values work for uniqueidentifier column types?

[tellmeaboutcode]

Csv file records are something like this:

Id (uniqueidentifier, Not null)      | Name (Nvarchar, null) | OtherId (uniqueidentifier, null) 
deb01846-c208-ec01-a4e4-005056bc1234 | TestName              | NULL

I am reading the csv file like this:

var dt = new DataTable();
// get the table def -> will have all column props
foreach (column in columns)
{
  var columnType = column.DataType; // (uniqueidentifier) I get this value from table schema
  var dataType = Map.GetClrType(columnType); // (GUID) from a SqlDbType -> c# map
  DataColumn columnValue = new DataColumn(column.Name, dataType);
  columnValue.AllowDBNull = true; // comes from IS Nullable column of table schema
  columnValue.DefaultValue = if is nullable => Guid.Empty/Null/DbNUll.Value; // tried these
  dt.Columns.Add(columnValue);
}

using (var reader = new StreamReader(filePath))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
   using (var dr = new CsvDataReader(csv))
   {
      dt.Load(dr); // error here
      return dt;
    }
}

I am having issues when trying to load the datatable using CsvHelper and seeing the error

Unhandled exception. System.AggregateException: One or more errors occurred. (Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).Couldn't store <NULL> in MyGuIdColumn Column. Expected type is Guid.)

[JoshClose]

Can you make the type nullable? Guid?

[tellmeaboutcode]

Hey @JoshClose, thanks for getting back. I did try making the type nullable. However, I am having issues creating the DataTable after the rows are read. Below is the exception:

Unhandled exception. System.AggregateException: One or more errors occurred. (Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).Couldn't store <NULL> in MyNullableGuidColumn Column.  Expected type is Guid.)
 ---> System.ArgumentException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).Couldn't store <NULL> in MyNullableGuidColumn Column.  Expected type is Guid.
 ---> System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
   at System.Guid.GuidResult.SetFailure(Boolean overflow, String failureMessageID)
   at System.Guid.TryParseExactN(ReadOnlySpan`1 guidString, GuidResult& result)
   at System.Guid.TryParseGuid(ReadOnlySpan`1 guidString, GuidResult& result)
   at System.Guid..ctor(String g)
   at System.Data.Common.ObjectStorage.Set(Int32 recordNo, Object value)
   at System.Data.DataColumn.set_Item(Int32 record, Object value)