Handling Empty Values in SqlBulkCopy

[larrydavid]

Hello, I have a FileImport class which is using SqlBulkCopy method. It works great if all the values in the .txt file are populated, but if there are any empty values it fails.

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class FileImport
{
public static void Import(string savePath)
{
DataTable dt = new DataTable();
string line = null;
int i = 0;

using (StreamReader sr = File.OpenText(savePath))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split('|');
if (data.Length > 0)
{
if (i == 0)
{
foreach (var item in data)
{
dt.Columns.Add(new DataColumn());
}
i++;
}
else
{
DataRow row = dt.NewRow();
/*
for (int j = 0; j < data.Length; j++)
{
if (!string.IsNullOrEmpty(data[j]))
row[j] = System.Convert.ChangeType(data, row.Table.Columns.DataType);
}
*/
row.ItemArray = data;
dt.Rows.Add(row);
}
}
}

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TESTConnectionString"].ToString()))
{
conn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(conn))
{
copy.ColumnMappings.Add(0, 0); //BATCH_TYPE_ID
copy.ColumnMappings.Add(1, 1); //LOAD_DATE (throws exception if empty)
copy.ColumnMappings.Add(2, 2); //VENDOR_ID
copy.ColumnMappings.Add(3, 3); //TAB_TYPE_ID
copy.ColumnMappings.Add(4, 4); //TYPE_1
copy.ColumnMappings.Add(5, 5); //TYPE_2

copy.DestinationTableName = "TestSubmission";
copy.WriteToServer(dt);
}
}
}
}
}

Any help would be greatly appreciated.

Thanks,
Larry

 

[phinoppix]

In the constructor, try adding the SqlBulkCopyOptions.KeepNulls option.