2

I read the file data into dataset and trying to bulk insert using SQL bulk copy by mapping the columns. All my column names in the database are in lower case which is created from my code.

I am not sure why I get "The specified column doesn't exist in the database" exception. Although I see all the columns mapped in the bulk copy object. Please advise. 

public static void BatchBulkCopy(DataTable dataTable, string DestinationTbl,  List<string> columnMapping,string filename)
{         
    // Get the DataTable 
    DataTable dtInsertRows = dataTable;

    using (SqlBulkCopy sbc = new SqlBulkCopy(program.connectionStr.ToString()))
    {
        try 
        {
            foreach (DataColumn col in dataTable.Columns)
            {                   
                sbc.ColumnMappings.Add(col.ColumnName.ToLower(), col.ColumnName.ToLower());
                // Console.WriteLine("ok\n");
            }

            sbc.DestinationTableName = DestinationTbl.ToLower();
            sbc.BulkCopyTimeout = 8000;
            sbc.DestinationTableName = "["+ DestinationTbl.ToLower() + "]";
            sbc.WriteToServer(dtInsertRows);
            sbc.Close();
        }

        catch (Exception ex)
        {           

        }      
    }
}
Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
user1046415
  • 779
  • 4
  • 23
  • 43
  • 1
    Can you add your table definition / all values from `dataTable.Column`? – Renats Stozkovs Feb 27 '17 at 17:35
  • 1
    are you SURE you don't have casing issues? Why are to calling toLower on your source table? the mapping should match case exactly on both sides. http://stackoverflow.com/questions/438587/sqlbulkcopy-not-working – Jeremy Feb 27 '17 at 17:57
  • my datatable has the column data from excel file which I first create a table in DB from my code with all lower case columns.So they should match..Can some one provide a sample please,not sure where I am doing it wrong.. – user1046415 Feb 27 '17 at 22:38

1 Answers1

1

You haven't open database connection. Insert sbc.Open(); before sbc.WriteToServer(dtInsertRows);

Amin
  • 11
  • 1