Empty values are written as empty string to database instead of NULL

See more:

C#

SQL

SQL-Server

CSV

SqlBulkCopy

, +

I generate lines of rows to insert:

private StringReader MapWithHeaders(StreamReader reader, int clientId, HeadersMap dataHeadersMap, string delimiter, int uploadDataId, string dateFormat)
        {
            var lines = new HashSet<string?>();
            var headers = new List<string>();
            var i = 0;
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var csvLineInOrder = "";
                var values = line.Split(delimiter).Select(x => x.Trim(new[] { '/', '"' })).ToList();

                if (i == 0)
                {
                    headers = values.Select(x => x.Trim(new[] { '/', '"' })).ToList();
                    csvLineInOrder = String.Join(delimiter, DataHeaders);
                    i++;
                }
                else
                {
                    var campaignColumnNameId = headers.FindIndex(x => x.Equals(dataHeadersMap.CampaignColumnName));
                    var clusterColumnNameId = headers.FindIndex(x => x.Equals(dataHeadersMap.ClusterColumnName));
                    var userIdColumnNameId = headers.FindIndex(x => x.Equals(dataHeadersMap.UserIdColumnName));
                    var channelColumnNameId = headers.FindIndex(x => x.Equals(dataHeadersMap.ChannelColumnName));

                    csvLineInOrder = values[campaignColumnNameId] + delimiter + values[clusterColumnNameId] + delimiter +
                                     values[userIdColumnNameId] + delimiter + values[channelColumnNameId];

/* e.g.: ,,11111,,7,,674 */

                }
                lines.Add(csvLineInOrder);
            }

            var stringCsv = string.Join("\n", lines);
            return new StringReader(stringCsv);
        }

then I generate csvDataReader:

private async Task BulkImportCsvExtra(Stream file, int clientId, HeadersMap dataExtraHeadersMap, string delimiter, int uploadDataId, string dateFormat)
        {
            using (var reader = new StreamReader(file))
            {
                var config = new CsvConfiguration(CultureInfo.InvariantCulture);
                config.BadDataFound = null;
                config.Delimiter = delimiter;

// doesn't get triggered?
                config.TypeConverterCache.AddConverter<string>(new EmptyAsNullConverter());

                var csvStreamReader = MapWithHeaders(reader, clientId, dataExtraHeadersMap, delimiter, uploadDataId, dateFormat);
                using (var csv = new CsvReader(csvStreamReader, config))
                {
                    var dataReader = new CsvDataReader(csv);
                    
                    // Need for header parsing
                    csv.ReadHeader();
                    if (!HeadersValid(csv.Context.HeaderRecord, DataHeadersExtra))
                        throw new Exception(ExceptionCode.Import.InvalidHeaders);
                    
                    await _repository.BulkAdd(dataReader);
                }
            }
        }

and finally use bulkCopy to add data:

public async Task BulkAdd(IDataReader data)
        {
            if (Connection.State == ConnectionState.Broken || Connection.State == ConnectionState.Closed)
            {
                await Connection.OpenAsync();
            }
            using (SqlBulkCopy bulk = new SqlBulkCopy(Connection))
            {
                bulk.DestinationTableName = GetTableName();
                bulk.BatchSize = BATCH_SIZE;
                bulk.BulkCopyTimeout = 0; // for infinity write 0
                bulk.EnableStreaming = true;
                await bulk.WriteToServerAsync(data);
            }
        }

What I have tried:

however empty values are written as empty strings rather than NULL even though I have default null constraints on database side. I've been stuck here for ages. any Ideas how to fix?

I tried adding null converter:

public class EmptyAsNullConverter : CsvHelper.TypeConversion.StringConverter
   {
       public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
       {
           if (string.IsNullOrWhiteSpace(text))
               return null;
           return text;
       }
   }

but it's not used even thouhg I add it as type converter and I don't know how to trigger it/solve this problem