CODEWITHSUNDEEP
c#linqsqlbulkcopy
Koo SengSeng
Koo SengSeng

Reputation: 953

LINQ: Convert any empty string to Null

I have a list populated with massive amount of data and over hundred columns. I will have to convert the entire list to DataTable and use SQLBulkCopy to batch insert all data into the database.

Everything is okay except that some of the fields could be empty. And for some reason, some of the empty fields causes some formatting exception after converted to DataTable and during SQLBulkCopy insertion. After some R&D, I found that converting the empty fields to NULL solve the issue for batch insertion using SQLBulkCopy.

Is there any way I can use LINQ to find empty fields in the list and convert them to NULL or DBNULL before I convert the list to DataTable?

Even though I'm going to convert the list to DataTable but I don't want to loop each row and each cell to check if the cell is empty and convert to null because it can affect the performance a lot when the data is too much.

Upvotes: 0

Views: 2652

Answers (2)

Zein Makki
Zein Makki

Reputation: 30022

You're already looping over each column/row combination in order to convert to a DataTable, you can add a new parameter to that function, Func<object, object> that you execute on each cell and in that function you check if the value is empty, so replace it with NULL

public DataTable ConvertToDataTable<T>(List<T> list, Func<object, object> transform)
{
   // logic
   object value = getUsingReflaction(); // ignore for now

   if(transform != null)
      value = transform(object);
   // continue
}

Usage:

ConvertToDataTable(myList, o => 
            {
                if (o is string && (string)o == String.Empty)
                    return DBNull.Value;
                else return o;
            });

Upvotes: 1

Sotiris
Sotiris

Reputation: 52

I can answer only to the very first part where you can replace all the empty string fields with the Null value using a conditional statment

public class Person
{
    public string Sex { get; set; }
    public string Name { get; set; }
}
private static void LinQ3()
{
    var namesList = new List<Person>();
    namesList.Add(new Person() { Sex = "M", Name = "Jonh" });
    namesList.Add(new Person() { Sex = "F", Name = string.Empty });
    namesList.Add(new Person() { Sex = "M", Name = string.Empty });

    var nullV = namesList.Select(x => new {
        Sex=x.Sex,
        nullableName= string.IsNullOrWhiteSpace(x.Name)?null: x.Name
    });
}

This will return a list(nullV) where the field "Name" will contain null values for every empty value on the field name. Also, you have to declare every field that you want to convert the empty strings using the same conditional statement

Upvotes: 1

Related Questions