SQL Bulk Copy by mapping columns dynamically

Question

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)
 { 
 } 
 }
}

• Renats Stozkovs: Can you add your table definition / all values from dataTable.Column?
• Jeremy: 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. stackoverflow.com/questions/438587/sqlbulkcopy-not-working
• user1046415: 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..

Answer 1

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