larrydavid
Programmer
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
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