Post
Ask Question

Upload Excelsheet Data In SQL Server Table

  1. protected void btnSend_Click(object sender, EventArgs e) {  
  2.     try {  
  3.         string path = string.Concat(Server.MapPath("~/File/" + fileuploadExcel.FileName));  
  4.         fileuploadExcel.SaveAs(path);  
  5.         string connExcelString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", path);  
  6.         OleDbConnection excelConnection = new OleDbConnection(connExcelString);  
  7.         OleDbCommand cmd = new OleDbCommand("Select [Name],[Mobile],[Email],[City],[DataId],[Date],[Source] from [Sheet1$]", excelConnection);  
  8.         excelConnection.Open();  
  9.         OleDbDataReader dReader;  
  10.         dReader = cmd.ExecuteReader();  
  11.         SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);  
  12.         SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "Name");  
  13.         sqlBulk.ColumnMappings.Add(mapName);  
  14.         SqlBulkCopyColumnMapping mapMobile = new SqlBulkCopyColumnMapping("Mobile", "Mobile");  
  15.         sqlBulk.ColumnMappings.Add(mapMobile);  
  16.         SqlBulkCopyColumnMapping mapEmail = new SqlBulkCopyColumnMapping("Email", "Email");  
  17.         sqlBulk.ColumnMappings.Add(mapEmail);  
  18.         SqlBulkCopyColumnMapping mapCity = new SqlBulkCopyColumnMapping("City", "City");  
  19.         sqlBulk.ColumnMappings.Add(mapCity);  
  20.         //SqlBulkCopyColumnMapping mapState = new SqlBulkCopyColumnMapping("State", "State");  
  21.         //sqlBulk.ColumnMappings.Add(mapState);  
  22.         SqlBulkCopyColumnMapping mapDataId = new SqlBulkCopyColumnMapping("DataId", "DataId");  
  23.         sqlBulk.ColumnMappings.Add(mapDataId);  
  24.         SqlBulkCopyColumnMapping mapAmount = new SqlBulkCopyColumnMapping("Date", "Date");  
  25.         sqlBulk.ColumnMappings.Add(mapAmount);  
  26.         SqlBulkCopyColumnMapping mapSource = new SqlBulkCopyColumnMapping("Source", "Source");  
  27.         sqlBulk.ColumnMappings.Add(mapSource);  
  28.         //Give your Destination table name  
  29.         sqlBulk.DestinationTableName = "UploadedExcelData";  
  30.         sqlBulk.WriteToServer(dReader);  
  31.         excelConnection.Close();  
  32.         UpdateRecords();  
  33.         lblMsg.Text = "File Data Uploaded Successfully... ";  
  34.         File.Delete(path);  
  35.     } catch (Exception ex) {  
  36.         lblMsg.Text = "Something Went Wrong... Plz Check Excel File ";  
  37.         //string script = "<script>alert('" + ex.Message + "');</script>";  
  38.     }  
  39. }