C#, Excel and Access

[R.A.F.]

Hi,

I have a dilema now regarding usage of Excel.

I do not have access to main DB (MS SQL), i only get a simple excel file
with particular data with which i must work.
as i want to use "as database", i was wondering if it is better to do
all queries (select ...) on this excel file or if it is better to export
all data to 1 mdb file (access) and to work with this mdb.

the excel file is generated once per week.
so what do you think about that ?

thx,

RAF

 

[Marc Gravell]

Actually, I would use SQL Express 2005, which is free and much better-
featured than Access files. It shoud be able to easily handle the
volume.

You should be able to use SqlBulkCopy from C# to get the data into the
db efficiently, and once there you can use indexing and full-strength
T-SQL to query the data.

Marc

 

[R.A.F.]

thx Marc, but what if i do not want to use SQL express ?
i know that it is free till 4 Gb data, but i do not want to make it more
complex as it is already

moreover, this little C# application should be, later on, used by
several other people so i would like to be sure it does not need any
other server, or application.

RAF

 

[Marc Gravell]

thx Marc, but what if i do not want to use SQL express ?

Then it sounds like you have already made your decision...

should be, later on, used by several other people

do they need to update the same file, or it is readonly? If multi-user
editing, Excel is not very practical anyway, so that leaves options
like Access etc. If you can't deploy either SQL Server Compact Edition
or SQL Server Express Edition then you are quite limited, so mdb may
be your only option, but I wouldn't (by choice) go down that road
myself.

Marc

 

[R.A.F.]

Hi Marc,

basically this XLS file is only an output from SQL server. So nobody
will update it ... it's only an output.
i do not want to install a SQL server because i think it would be too
much work just for a simple excel file rework.
it would be like reinvent hot water...

my application only needs to do some simple : select xx from yy where
name=%or%; for example.
nothing more.

so still better choice access than excel for that ?

RAF

 

[Marc Gravell]

basically this XLS file is only an output from SQL server
Seriously? Why not just use csv/tsv and simplify things?

nobody\will update it ... it's only an output

That keeps life simple

my application only needs to do some simple : select xx from yy
where name=%or%; for example.

Well, in the name of simplicity you could try it "as is" with whatever
works. However, if the complexity grows much you will find that
performance startes to suffer. If you can use mdb, then fine - give it
a whirl.

so still better choice access than excel for that ?

Well Excel is a spreadsheet UI application, and you need a database,
so yes: any of the options (Access, SQL Server Express / Compact
Edition) would have advantages. If you just mean looking at the file
(not via Excel itself) then again, fine, but this is what databases
are designed to do well.

In the end it is a trade-off; do you need the simplest possible
deployment, or do you need the performance/feature advantages of a
database engine (which can still be deployed pretty easily via the
setup options in VS). Only you know the full situation...

Marc