Import batch data in Excel into SQL

// Test. Import Sheet 1 in Excel to private void run_click (Object sender, eventargs e) {system. windows. forms. openfiledialog FD = new openfiledialog (); If (FD. showdialog () = dialogresult. OK) {dataset DS = transferdata (FD. filename, "sheet1"); addbatch (Ds. tables [0]) ;}} public dataset transferdata (string excelfile, string sheetname) {dataset DS = new dataset (); try {// obtain all data string strconn = "provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ excelfile +"; "+" extended properties = Excel 8.0; "; if (excelfile. tolower (). indexof (". XLSX ")> 0 & excelfile. tolower (). endswith ("XLSX") {strconn = "provider = Microsoft. ace. oledb.12.0; Data Source = '"+ excelfile +"'; extended properties = 'excel 12.0; HDR = Yes '";} else if (excelfile. tolower (). indexof (". xls ")> 0 & excelfile. tolower (). endswith ("xls") {strconn = "provider = Microsoft. jet. oledb.4.0; Data Source = '"+ excelfile +"'; extended properties = 'excel 8.0; HDR = yes; '";}else {return NULL ;} oledbconnection conn = new oledbconnection (strconn); Conn. open (); string strexcel = ""; oledbdataadapter mycommand = NULL; strexcel = string. format ("select * from [{0} $]", sheetname); mycommand = new oledbdataadapter (strexcel, strconn); mycommand. fill (DS, sheetname); Return Ds;} catch (exception ex) {system. windows. forms. messageBox. show (ex. message);} return NULL ;} /// <summary> /// batch add /// </Summary> /// <Param name = "DT"> </param> Public void addbatch (datatable DT) {shard [] SC = new sqlbulkcopycolumnmapping [] {New sqlbulkcopycolumnmapping (0, "Site"), new sqlbulkcopycolumnmapping (1, "goodslocationid"), new sqlbulkcopycolumnmapping (2, "area")}; dbhelpersql. transferdata (DT, "wms_goodslocation", SC);} public static void transferdata (datatable dtb, string tablename, sqlbulkcopycolumnmapping [] sbccm) {using (system. data. sqlclient. sqlbulkcopy BCP = new system. data. sqlclient. sqlbulkcopy (connectionstring) {BCP. batchsize = 100; // number of rows transmitted each time (BCP. destinationtablename = tablename; // target table foreach (sqlbulkcopycolumnmapping s in sbccm) BCP. columnmappings. add (s); BCP. writetoserver (dtb );}}