ASP.NET - I am trying to import excel file to SQL server using asp.net web application

Asked By venu yelle on 26-Sep-12 05:01 AM
manish postariya replied to venu yelle on 26-Sep-12 01:21 PM
protected void Button1_Click(object sender, EventArgs e)
{
       
String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";
       
//file upload path
       
string path = FileUpload1.PostedFile.FileName;
       
//string path="C:\\ Users\\ Hemant\\Documents\\example.xlsx";
       
//Create connection string to Excel work book
       
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
       
//Create Connection to Excel work book
       
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
       
//Create OleDbCommand to fetch data from Excel
       
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection);
        excelConnection
.Open();
       
OleDbDataReader dReader;
        dReader
= cmd.ExecuteReader();
       
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
       
//Give your Destination table name
        sqlBulk
.DestinationTableName = "Excel_table";
        sqlBulk
.WriteToServer(dReader);
        excelConnection
.Close();
   
}

You can then think about deleting the file you've just uploaded - manish postariya replied to venu yelle on 26-Sep-12 01:24 PM

  private void SaveFileToDatabase(string filePath)
   
{
       
String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";

       
String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
       
//Create Connection to Excel work book
       
using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
       
{
           
//Create OleDbCommand to fetch data from Excel
           
using (OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection))
           
{
                excelConnection
.Open();
               
using (OleDbDataReader dReader = cmd.ExecuteReader())
               
{
                   
using(SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                   
{
                       
//Give your Destination table name
                        sqlBulk
.DestinationTableName = "Excel_table";
                        sqlBulk
.WriteToServer(dReader);
                   
}
               
}
           
}
       
}
   
}


   
private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
   
{


       
string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

        fileUploadControl
.SaveAs(filePath);

       
return filePath;

   
}
Jitendra Faye replied to venu yelle on 27-Sep-12 01:29 AM
Refer this link for solution-

http://www.eggheadcafe.com/community/sql-server/13/10351461/how-to-import-excel-data-to-a-sql-table.aspx
Keating Megan replied to Jitendra Faye on 28-Sep-12 02:39 AM
You should first import this excel to datatable and then connect it to database, please see below code:

Workbook workbook = new Workbook();
workbook.LoadFromFile("DataTableSample.xls");
Worksheet sheet = workbook.Worksheets[0];
DataTable dataTable = sheet.ExportDataTable();

I use a .NET MS excel component:http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html