c# asp.net bulkcopy

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();

}

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Bhoir11y ago
Accepted answer
Hi,

You have to set correct file path. As per your code you have to use
string FilePath = Server.MapPath(FolderPath + FileName);
Manoj Bhoir11y ago
Welcome Laxman.
Laxman Sharma11y ago
Thank you sir for your unselfish help
Laxman Sharma11y ago
Sir can you tell then what path should I use to proceed it further
Manoj Bhoir11y ago
Hi,

Please check that your file path is correct.

For GetExcelSheets you are using :

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

And for Button1_Click you are using :

string filePath = "C:\\Book1.xlsx";
Laxman Sharma11y ago
Thank you sir for your help , But I am still getting error as under : System.Data.OleDb.OleDbException: The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.
Manoj Bhoir11y ago
Hi,

I think there is no problem in your code it seems correct. Only you are not passing Sheet name in your query properly.
Please ensure that if your GetExcelSheets method is woking properly then it means you are getting all excel sheet names from workbook in your dropdownlist.

Then just change your this line :

OleDbCommand command = new OleDbCommand
("Select * FROM [ + SheetName + ]", connection);

With this

OleDbCommand command = new OleDbCommand
("Select * FROM [" + ddlSheets.Text + "]", connection);
and you are done.