Excel ADO question

[Nikolay Petrov]

I use Excel file as a data source for my Web Service.

I want to know a few things.

1. When reading from the Excel file, does it remain locked for the time of
reading the data? Must I implement some logic, to check if the file is
locked?
2. May I use SQL statements to Insert, Update and Delete information in the
Excel file?
3. Because I am using and OleDB connection to fill a DataSet, can I use the
DataAdapter to update the data to the Excel file, like the way when a SQL
Server connection is used?

Thanks

 

[W.G. Ryan eMVP]

1) the file is locked for the time you have the connection open or the time
it takes to fill a table if you let an adapter open/close it for you
2) Yes
3) Yes

 

[Nikolay Petrov]

Thanks.

Should I then try to check, if the file is locked first, or the OLEDB
connection check the file status by itself, and do some retries if it is
locked?

 

[Cor Ligthert]

Should I then try to check, if the file is locked first, or the OLEDB
connection check the file status by itself, and do some retries if it is
locked?

No just open and close it in every webservice method. (Assuming that you are
not working with multiprocessors or load balanced, however in that case
looks an excel sheet me very much underpowered).

Cor

 

[Nikolay Petrov]

Thanks Cor

Yes Excel is underpowered, but for storing a 10 row of data in easy for
users to edit format is just enough.

 

[Paul Clement]

¤ I use Excel file as a data source for my Web Service.
¤
¤ I want to know a few things.
¤
¤ 1. When reading from the Excel file, does it remain locked for the time of
¤ reading the data? Must I implement some logic, to check if the file is
¤ locked?

Are you experiencing read locks? Although Microsoft doesn't recommend concurrent access to an Excel
file, AFAIK it can be configured for shared mode using Microsoft Excel.

¤ 2. May I use SQL statements to Insert, Update and Delete information in the
¤ Excel file?

Yes, yes and no.

¤ 3. Because I am using and OleDB connection to fill a DataSet, can I use the
¤ DataAdapter to update the data to the Excel file, like the way when a SQL
¤ Server connection is used?

Yes.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[User]

If all you are going to do is store 10 rows of data, why not just use an XML
file instead of all that Excel overhead.

 

[Cor Ligthert]

User,

In my opinon is a xml file is a very dangerous datafile to use in this case.
It has everytime to be written and rewritten and with one write error all
the data is gone when you don't create very good and file IO expensive
routines arround it to prevent that.

I would use an access database instead of an Excel sheet when it has to be a
kind of database in a file.

However, just my thought

Cor

 

[Nikolay Petrov]

I am sure that any kind of Database is preferable then Excel.
But the generated file must be opened easily by users, and the should do
some calculation on data in it. They do this in Excel, so I think that this
is the most appropriate way in this scenario.