c# asp.net bulkcopy

Question

I want to bulkcopy the excel data into the SQL Server using c# asp.net -But before copying the data want to see the excel workbooks and want to select one of them for the bulkcopying further .Please help me I am absolutely stuck in this since last two days:

 

I am attaching the snapshot for my requirement : 

 

My  code is as under:

 

 

 

using System;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Configuration;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnUpload_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile)

{

string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

string FilePath = Server.MapPath(FolderPath + FileName);

FileUpload1.SaveAs(FilePath);

GetExcelSheets(FilePath, Extension, "Yes");

}

}

private void GetExcelSheets(string FilePath, string Extension, string isHDR)

{

string conStr = "";

switch (Extension)

{

case ".xls": //Excel 97-03

conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO'";

break;

case ".xlsx": //Excel 07

conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO'";

break;

}

//Get the Sheets in Excel WorkBoo

conStr = String.Format(conStr, FilePath, isHDR);

OleDbConnection connExcel = new OleDbConnection(conStr);

OleDbCommand cmdExcel = new OleDbCommand();

OleDbDataAdapter oda = new OleDbDataAdapter();

cmdExcel.Connection = connExcel;

connExcel.Open();

//Bind the Sheets to DropDownList

ddlSheets.Items.Clear();

ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));

ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

ddlSheets.DataTextField = "TABLE_NAME";

ddlSheets.DataValueField = "TABLE_NAME";

ddlSheets.DataBind();

connExcel.Close();

lblFileName.Text = Path.GetFileName(FilePath);

Panel2.Visible = true;

Panel1.Visible = false;

}

protected void Button1_Click(object sender, EventArgs e)

{

string filePath = "C:\\Book1.xlsx";

string excelConnectionString = string.Empty;

if (filePath.EndsWith(".xlsx"))

{

//2007 Format

excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath);

}

else

{

//2003 Format

excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'", filePath);

}

using (OleDbConnection connection =

new OleDbConnection(excelConnectionString))

{

OleDbCommand command = new OleDbCommand

("Select * FROM [ + SheetName + ]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet

OleDbDataReader dReader;

dReader = command.ExecuteReader();

{

// SQL Server Connection String

string sqlConnectionString = @"Data Source=Data-SERVER;Initial Catalog=MIDB;Integrated Security=True";

// Bulk Copy to SQL Server

using (SqlBulkCopy bulkCopy =

new SqlBulkCopy(sqlConnectionString))

{

bulkCopy.DestinationTableName = "MIREPORT";

bulkCopy.WriteToServer(dReader);

connection.Close();

}

}

}

}

}