Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Handling Empty Values in SqlBulkCopy

Status
Not open for further replies.

larrydavid

Programmer
Jul 22, 2010
174
US
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
 
In the constructor, try adding the SqlBulkCopyOptions.KeepNulls option.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top