SqlBulkCopy excel Field ing solution 1. Modify the ColumnName of the middleware able to make it consistent with the target table

Use SqlBulkCopy to import data to an Excel database

Because the recent project requires a large amount of data to be imported from Excel to the SQL Server database, the data volume is very large, and the SQL bulkcopy is used for data import.

 

Because the header in the Excel table is different from the field in the database, you need to add a ing. I had a lot of detours in this place before. The actual situation of the project is as follows.

 

The project content is presented in Chinese, while the data table header is in English. At the beginning, data is read directly from the Excel table, a DataTable is generated, a custom column is added (no name or user ID exists in Excel), a data list is added, and the ColumnName of the DataTable is directly modified, then, update SqlBulkCopy to the database. The result is incorrect and cannot be converted from the String type to the Int type. Because ColumnName is modified, the structure of the two tables is the same, and it takes a while.

 

Through analysis, because the IDs in your own database are automatically increased, and SqlBulkCopy is not used to specify the corresponding header to be mapped, this problem is caused. Add sqlBulkCopy. ColumnMappings.

 

After the results are imported successfully, some data cannot be displayed. After searching, an error message "the number of this cell is in text format" is displayed in cells in an Excel table ". Find the information and find that you need to modify the registry of the server. Related articles have been published.

 

Only paste key code

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

Ccl: ColumnMappings ing

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 ;}

}

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