Link to home
Start Free TrialLog in
Avatar of dodgerfan
dodgerfanFlag for United States of America

asked on

SQL Bulk Upload with c# column mapping handling nulls

I have a c# program that uses sql bulk upload to insert records into a table. It works very well but I would like to handle the null values differently. The text file that contains the data has over 300,000 records. Some of those records do not have data, so an empty string is inserted. I'd like to leave the fields null. Perhaps use DNNull? The records also have a date field, which I have had to make nvarchar in the SQL table. If it is a datetime field, it errors out. Is there a way to keep it as datetime and insert the date? Thanks fro any help. My code is below.

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

namespace LoadData
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            string line = null;
            int i = 0;

            using (StreamReader sr = File.OpenText(@"c:\temp\empdata.txt"))
            {
                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++;
                        }
                        DataRow row = dt.NewRow();
                        row.ItemArray = data;
                        dt.Rows.Add(row);
                    }
                }
            }

            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))
            {
                cn.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(cn))
                {
                    copy.ColumnMappings.Add(0, 0);
                    copy.ColumnMappings.Add(1, 1);
                    copy.ColumnMappings.Add(2, 2);
                    copy.ColumnMappings.Add(3, 3);
                    copy.ColumnMappings.Add(4, 4);
                    copy.ColumnMappings.Add(5, 5);
                    copy.ColumnMappings.Add(6, 6);
                    copy.ColumnMappings.Add(7, 7);
                    copy.ColumnMappings.Add(8, 8);
                    copy.ColumnMappings.Add(9, 9);
                    copy.ColumnMappings.Add(10, 10);
                    copy.DestinationTableName = "Employees";
                    copy.WriteToServer(dt);
                }
            }
        }
    }
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Deja Anbu
Deja Anbu
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial