How can I make AllowDBNull work for GUID?

Question

I am using DataColumn to create a new column for a DataTable. I am using AllowDBNull to specify that a column can have NULL values.

This works fine except for the case when I have a column with uniqueidentifier type where I am doing

public static Type GetClrType(SqlDbType sqlType, bool isNullable)
{
  switch (sqlType)
  {
     case SqlDbType.UniqueIdentifier:
        return isNullable ? typeof(Guid?) : typeof(Guid);
     ......other types......
  }
}

DataColumn columnValue = DataColumn(column.Name, GetClrType(columnType, IsNullable))
dt.Columns.Add(columnValue);

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

Any help to solve this is appreciated.

Update (More details):

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

Answer 1

Oh.. It took me a while to see it, but the problem is, I suspect, that your CSV file literally contains the string "NULL" in the OtherId column; you have to tell CSVH that "NULL" means DBNull.Value

The following code will read the CSV you posted into github:

        using var reader = new CsvReader(File.OpenText(_openFileDialog.FileName), conf);
        using var dataReader = new CsvDataReader(reader);

        var dt = dataReader.GetSchemaTable(); //the schema table is huge, get CSVH to make it

        //the schema table describes the file layout
        dt.Rows[0]["DataType"] = typeof(Guid); //first column is a GUID
        dt.Rows[0]["AllowDBNull"] = false;     //and not null (default true)
        dt.Rows[2]["DataType"] = typeof(Guid); //third column is also a GUID

        //tell CSVH that a string of "NULL" is a null value
        var tco = new CsvHelper.TypeConversion.TypeConverterOptions();
        tco.NullValues.Add("NULL");
        reader.Context.TypeConverterOptionsCache.AddOptions<string>(tco);


        var ddt = new DataTable();
        ddt.Load(dataReader);

Now ddt contains a Guid column for OtherId, and it's allowing null guids

You don't have to know the column types in advance. By the time the var dt = dataReader.GetSchemaTable();var dt = dataReader.GetSchemaTable(); line has executed, dt will have the column names. If the headers are as you have posted, then by the time you've got the schema, but before you read any data you'll be able to enumerate the schema table and tweak it:

ps; I cheated a bit with your CSV, because I couldn't be bothered looking up how to set pipe as the delimiter - exercise for the reader(hah):