SqlBulkCopy is a really powerful & fast way to upload data from one source to another SQL based database.
Most examples are for use when uploading an Excel spreadsheet, however a common requirement is the need to upload a CSV file, yet strangely there is little info on the web. More importantly since we the asp.net FileUpload control doesnt like revealing the full path of the file it is uploading, it becomes near impossible to perform this action as required. This is because we need to know the location of the file inorder to connect to it and read its data.
Here i we show you a nice little work around. It works by first uploading the file to the server, transfering the data from it into the necessary database, before finally deleting the original file from the server.
[more]
if (FileUpload2.PostedFile.FileName != null){
//filenamestring strUPloadFile = FileUpload2.PostedFile.FileName;strUPloadFile = strUPloadFile;//save to tmp folderstring strPath = MapPath("tmp_uploads/");
try{//save to: tmp_uploads/filenameFileUpload2.SaveAs(strPath + strUPloadFile);}catch (Exception ex){throw ex;}
using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ strPath + ";Extended Properties=\"text;HDR=No;FMT=Delimited\"")){
using (OleDbCommand dcmd = new OleDbCommand("SELECT * FROM " + strUPloadFile + "", dconn)){try{dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader()){
// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy = new SqlBulkCopy(WebConfigurationManager.ConnectionStrings["############"].ConnectionString)){
bulkCopy.DestinationTableName = "tbl_your_table_name_here";// Set the BatchSize.bulkCopy.BatchSize = 500;
try{//perform copybulkCopy.WriteToServer(dreader);}
catch (Exception ex){//errorthrow(ex);}}}}finally{dconn.Close();}}
//delete file from servertry{File.Delete(strPath + strUPloadFile);}catch(Exception ex){throw ex;}
Subscribe