connection string reading data from excel in asp.net

Question

Hello,

I am trying to read data from excel file in asp.net. I have added the connection string to webConfig file:

<add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=HPM_DB.xls;Extended Properties=Excel 8.0"/>

But it shows me an errormessage when I run this query:

string query = "Select * from [IO_Definition$]";

IO_Definition is the name of the spreadsheet in my excel file. I also added the excel file to the App_Data folder of the website.

The error is:

The Microsoft Jet database engine could not find the object 'IO_Definition$'. Make sure the object exists and that you spell its name and the path name correctly.

The thing is, when I write the absolute path of the excel file in the connectionString it does work. Is there anyway I can make it work without writing the absolute path?

Thanks,

Greg

Answer 1

The issue here is that for this sort of 'file' connection, the OleDb provider needs, as you've already found, the absolute path to the file. Historically, we've done this with Server.MapPath, in which case you'd do something like:

<connectionstrings>
    <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"/>
</connectionstrings>

String connString;
OleDbConnection conn;

connString = Web.Configuration.WebConfigurationManager.ConnectionStrings["xls"].ConnectionString;
connString = connString + Server.MapPath("~/App_Data/HPM_DB.xls");

conn = new OleDbConnection(connString);

conn.Open();
...

which works, but you need to either do all that every time you want to open a connection and query the data, or break it out into a connection factory class.

A neater way to do it is this:

<connectionstrings>
    <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;
    Data Source=|DataDirectory|\HPM_DB.xls;"/>
</connectionstrings>

OleDbConnection conn;

conn = new OleDbConnection(Web.Configuration.WebConfigurationManager.ConnectionStrings["xls"].ConnectionString);
conn.Open();
...

At runtime the |DataDirectory| token in the connection string acts as a macro which silently does all that Server.MapPath stuff for you.