SqlBulkCopy Excel Field Mapping workaround one modifies the ColumnName of the middleware DataTable to match the target table

Importing an Excel database with SqlBulkCopy

Because recent projects require a large amount of data from Excel imported into the SQL Server database, the amount of data is very large, comprehensive filtering, using SqlBulkCopy for data import.

Because the headers in the Excel table are not the same as the fields in the database, you need to add the mappings. Before this place took a lot of detours. The actual project is as follows.

The content in the project is rendered in Chinese, and the data header is in English. At first, you read data directly from an Excel table, generate a DataTable, add a custom column (no name, user ID, and so on in Excel), add a list of data, and then directly modify the ColumnName of the DataTable. Then directly to the SqlBulkCopy to update to the database. The result is an error, unable to go from the string type to int, because by modifying columnname, the structure of the two tables is consistent and dazed for a while.

Through the analysis, because the ID in the database is automatically growing, and the use of SqlBulkCopy is not specified to map the corresponding table header, so this problem is caused. Add Sqlbulkcopy.columnmappings to resolve.

Results import successfully, but found that some data is unable to render, from the search, found in the Excel table cell Error alert "This cell number is text format." Find the data found need to modify the server's registry. The relevant articles have been published.

Stick to key code only

--------------------------------------

Ccl:columnmappings Mapping

TableName: Name of the table to be updated

DataTable: Imported Data table

  public static int sqlbulkcopyinsert (string tablename, datatable  DATATABLE, HASHTABLE CCL)         {             sqlbulkcopy sqlbulkcopy = new sqlbulkcopy ( T.getcs (""));                          foreach  (STRING STR IN CCL. Keys)             {                 sqlbulkcopy.columnmappings.add (STR, CCL [STR]. ToString ());             }              sqlBulkCopy.DestinationTableName = tablename;             if  (datatable != null &&  datatable.rows.count != 0)             {                  Sqlbulkcopy.writetoserver (dataTable);                  Sqlbulkcopy.close ();                 return  DataTable.Rows.Count;             }              else              {                 Sqlbulkcopy.close ();                 return 0;             }

}

--------------------------------------------