Pages

Tuesday, December 18, 2012

Bulk Import from Excel file to SQl Server in ASP.NET

<asp:FileUpload ID="FileUpload1" runat="server" Width="250px" Height="25px" /><br />
<asp:RequiredFieldValidator ID="rfv" runat="server" ControlToValidate="FileUpload1"
ErrorMessage="Please select file to upload" Display="Dynamic" ValidationGroup="vgInsert" SetFocusOnError="True"></asp:RequiredFieldValidator>
<asp:Button ID="btnInsert" runat="server" OnClientClick="showPleaseWait()" OnClick="btnInsert_Click"
Text="Save" Width="85px" Visible="false" ValidationGroup="vgInsert" />

protected void btnInsert_Click(object sender, EventArgs e)
{
    if (Page.IsValid)
    {
 if (FileUpload1.HasFile)
 {
     foreach (Process process in Process.GetProcessesByName("EXCEL"))
     {
  if (process.MainModule.ModuleName.ToUpper().Equals("EXCEL.EXE"))
  {
      process.Kill();
      Thread.Sleep(2000);
      break;
  }
     }
     try
     {
  sFilePath = Path.GetFileName(FileUpload1.FileName);
  if (Path.GetExtension(sFilePath) == ".xlsx" || Path.GetExtension(sFilePath) == ".xls")
  {
      FileUpload1.SaveAs(Server.MapPath(this.UploadFolderPath) + "\\" + sFilePath);
  }
  else
  {
      Label1.Text = "Please upload excel file only.";
      return;
  }
  DateTime dDate = Convert.ToDateTime(txtDate.Text);
  using (OleDbConnection connection = new OleDbConnection(CreateConnectionString(Server.MapPath(this.UploadFolderPath) + "\\" +sFilePath, Path.GetExtension(FileUpload1.PostedFile.FileName))))
  {
      OleDbCommand command = new OleDbCommand("Select * , '" + dDate.ToString("yyyy-MM-dd") + "' as DateExtracted, '" + ddlProjectName.SelectedValue + "' As ProjectName, '" + ddlArea.SelectedValue + "' As Area" + " FROM [Sheet1$]", connection);
      connection.Open();
      // Create DbDataReader to Data Worksheet
      using (DbDataReader dr = command.ExecuteReader())
      {
   // SQL Server Connection String
   string sqlConnectionString = connectionString;
   // Bulk Copy to SQL Server
   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
   {
       bulkCopy.DestinationTableName = TABLENAME;
       ColumnMapping(bulkCopy);
       try
       {
    bulkCopy.WriteToServer(dr);
       }
       catch (Exception ex)
       {
    Console.WriteLine(ex.Message);
       }
       finally
       {
    connection.Close();
       }
       Label5.Text = "File Data imported succesfully";
   }
      }
  }
     }
     catch (Exception ex)
     {
  Label1.Text = "Upload status: The file could not be uploaded.The following error occured:" + ex.Message;
     }
 }
 else
 {
     Label1.Text = "Upload status: The file could not be uploaded.";
 }
    }
}
private void ColumnMapping(SqlBulkCopy bulkCopy)
{
    bulkCopy.ColumnMappings.Add("ID", "ClashID");
    bulkCopy.ColumnMappings.Add("Status", "ClashStatus");
    bulkCopy.ColumnMappings.Add("Type", "ClashType");
    bulkCopy.ColumnMappings.Add("P G", "ClashPG");
    bulkCopy.ColumnMappings.Add("OID", "ClashOID");
    bulkCopy.ColumnMappings.Add("Remark", "ClashRemarks");
    bulkCopy.ColumnMappings.Add("A-System Path", "PartASystemPath");
    bulkCopy.ColumnMappings.Add("A-Name", "PartAName");
    //bulkCopy.ColumnMappings.Add("A-Parent Name", "L_Name");
    bulkCopy.ColumnMappings.Add("A-Aspect", "PartAAspect");
    bulkCopy.ColumnMappings.Add("A-Clashes", "PartAClashes");
    bulkCopy.ColumnMappings.Add("A-P G", "PartAPG");
    bulkCopy.ColumnMappings.Add("A-OID", "PartAOID");
    bulkCopy.ColumnMappings.Add("B-System Path", "PartBSystemPath");
    bulkCopy.ColumnMappings.Add("B-Name", "PartBName");
    //bulkCopy.ColumnMappings.Add("B-Parent Name B", "City");
    bulkCopy.ColumnMappings.Add("B-Aspect", "PartBAspect");
    bulkCopy.ColumnMappings.Add("B-Clashes", "PartBClashes");
    bulkCopy.ColumnMappings.Add("B-P G", "PartBPG");
    bulkCopy.ColumnMappings.Add("B-OID", "PartBOID");
    bulkCopy.ColumnMappings.Add("DateExtracted", "DateExtracted");
    bulkCopy.ColumnMappings.Add("ProjectName", "ProjectName");
    bulkCopy.ColumnMappings.Add("Area", "Area");
}
public string CreateConnectionString(string fileLocation, string fileExtension)
{
    return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileLocation + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
}

No comments:

Post a Comment