Desktop database suggestion

[Mark Rae]

Hi,

I'm writing a WinForms app in C# and need to store various items of lookup
data which will be accessible and maintainable by the user. I'm looking for
advice as to the best way to achieve this, according to the following
provisos:

1) The app must be able to "match" records according to various criteria in
a standard sort of "fetch me all the records where..." way. This doesn't
have to be SQL becuase it will all be GUI driven. E,g the user selects
various criteria via dropdowns (age, sex, area etc) and the app returns the
records which match those criteria, so the user won't care whether the app
uses SQL or another technology under the hood.

2) The schema is fairly flat with one main table containing about fifty
fields, and only a handful of related lookup tables.

3) There might be several thousand records in the main table.

4) There must be no additional licensing costs incurred with the database
software.

5) The data might be single-user standalone or accessed by multiple users at
once, so would have to be able to reside on a network share though, because
of 4) above, something like SQL Server is right out, unfortunately.

6) The data must be unreadable by anything or anyone other than my
application.

7) The data must be easily backed up and restored.

Given this, would you recommend:

a) a Jet database

b) some sort of XML file / schema structure

c) something else altogether,

Any assistance gratefully received.

Best regards,

Mark Rae

[ilPostino]

If you don't want licencing costs and if you're not holding much data you
can use an XML file.
If it gets big, just compress it however! if multiple uses open and write to
this file you've got a sync problem and users can and will
overwrite data. You'd have to write a service that can maintain the xml file
in a syncronis pattern. so for eg: your app writes changes to single xml
update files on the disk, a service collects them and updates the main xml
storage file.

or you can use MySql .... its free and works damn well. I guess a normal
Access file would work too.

-p

"Mark Rae" <ma**@markrae.co.uk> wrote in message
news:OK*************@TK2MSFTNGP12.phx.gbl...

Hi,

I'm writing a WinForms app in C# and need to store various items of lookup
data which will be accessible and maintainable by the user. I'm looking for advice as to the best way to achieve this, according to the following
provisos:

1) The app must be able to "match" records according to various criteria in a standard sort of "fetch me all the records where..." way. This doesn't
have to be SQL becuase it will all be GUI driven. E,g the user selects
various criteria via dropdowns (age, sex, area etc) and the app returns the records which match those criteria, so the user won't care whether the app
uses SQL or another technology under the hood.

2) The schema is fairly flat with one main table containing about fifty
fields, and only a handful of related lookup tables.

3) There might be several thousand records in the main table.

4) There must be no additional licensing costs incurred with the database
software.

5) The data might be single-user standalone or accessed by multiple users at once, so would have to be able to reside on a network share though, because of 4) above, something like SQL Server is right out, unfortunately.

6) The data must be unreadable by anything or anyone other than my
application.

7) The data must be easily backed up and restored.

Given this, would you recommend:

a) a Jet database

b) some sort of XML file / schema structure

c) something else altogether,

Any assistance gratefully received.

Best regards,

Mark Rae

[Mark Rae]

"ilPostino" <ne**@ip80.com> wrote in message
news:uc**************@TK2MSFTNGP11.phx.gbl...

If you don't want licencing costs and if you're not holding much data you
can use an XML file.
If it gets big, just compress it however! if multiple uses open and write to this file you've got a sync problem and users can and will
overwrite data. You'd have to write a service that can maintain the xml file in a syncronis pattern. so for eg: your app writes changes to single xml
update files on the disk, a service collects them and updates the main xml
storage file.
I'm sort of veering away from XML because of the multiuser requirement...
or you can use MySql .... its free and works damn well. I guess a normal
Access file would work too.

I'm sort of heading more and more towards an Access (well, Jet!) file
because I know Access inside out and it wouldn't require any runtime
licenses because of System.Data.OleDb

[Mark]

the msde engine might be a good alternative assuming you're comfortable with
SQL Server. It's free, it's SQL Server, but has a limit of something
ridiculous like 20 concurrent users. Assuming a 2 GB limit isn't a problem,
that's a lot of users.

Mark
www.dovetaildatabases.com

"Mark Rae" <ma**@markrae.co.uk> wrote in message
news:OO**************@TK2MSFTNGP09.phx.gbl...

"ilPostino" <ne**@ip80.com> wrote in message
news:uc**************@TK2MSFTNGP11.phx.gbl...

If you don't want licencing costs and if you're not holding much data you can use an XML file.
If it gets big, just compress it however! if multiple uses open and write

to

this file you've got a sync problem and users can and will
overwrite data. You'd have to write a service that can maintain the xml

file

in a syncronis pattern. so for eg: your app writes changes to single xml
update files on the disk, a service collects them and updates the main

xml storage file.

I'm sort of veering away from XML because of the multiuser requirement...

or you can use MySql .... its free and works damn well. I guess a normal
Access file would work too.

I'm sort of heading more and more towards an Access (well, Jet!) file
because I know Access inside out and it wouldn't require any runtime
licenses because of System.Data.OleDb

[Daniel Jin]

----- Mark wrote: ----

the msde engine might be a good alternative assuming you're comfortable wit
SQL Server. It's free, it's SQL Server, but has a limit of somethin
ridiculous like 20 concurrent users. Assuming a 2 GB limit isn't a problem
that's a lot of users

I think MSDE has a limit of running 8 (or 10? can't remember the exact number) concurrent queries. when you exceed that limit, it will put a delay in to slow down the performance on purpose. which won't be a problem when you only have a small amount of users. that's the best option

Access file is the second best, but it's no where near the performance of MSDE, which is essentially SQL Server for a small group of users

XML file is a poor choice because there's no way to update a small piece without rewriting the whole file. and a lot of manual synchronization.

[Mihai N.]

SQLite (www.sqlite.org) ?

4) There must be no additional licensing costs incurred with the database
software. Free, open source.
5) The data might be single-user standalone or accessed by multiple users
at once, so would have to be able to reside on a network share though,
because of 4) above, something like SQL Server is right out, unfortunately. Depends on what you want. It looks the whole database when writing, not only
one record.
6) The data must be unreadable by anything or anyone other than my
application.

This is a problem with most "of the shelf" solutions.
If you plan to use some encryption, it will be a weak one or you will get a
bit performance penalty.
--
Mihai
-------------------------
Replace _year_ with _ to get the real email

[Mark Rae]

"Mihai N." <nm**************@yahoo.com> wrote in message
news:Xn********************@63.240.76.16...

Thanks for the reply. We've decided to give users the choice of using our
desktop database (which will be Jet 4.0) or to use their own. There will be
an option in the app which will allow users to point at a file which
contains the OleDb connection string, either locally or on a network, which
allows for total flexibilty. The only proviso we make is that the RDBMS they
choose must support SQL. We will also provide an SQL script for
administrators to run so that they can create the database, tables and
fields that our app is expecting.

[Brady Kelly]

On Thu, 1 Apr 2004 09:40:57 -0600, "Mark"
<fi******@idonotlikejunkmail.umn.edu> wrote:

the msde engine might be a good alternative assuming you're comfortable with
SQL Server. It's free, it's SQL Server, but has a limit of something
ridiculous like 20 concurrent users. Assuming a 2 GB limit isn't a problem,
that's a lot of users.

Mark

MSDE is a good alternative, but depending on your deployment
constraints, can be problematic to fully integrate into your
deployment package.

If you have no problem with instructing your users to install MSDE and
then your product, its fine.

[Mel Weaver]

This is a problem we having been fighting for years. One example is the
like statement in access is 'Select * from Data where fullname like 'we*';
then sql standard uses 'Select * from Data where fullname like 'we%'.
There are many other examples. The SQL syntax differs for Access(Jet 4.0) vs
Sql Server.
"Mark Rae" <ma**@markrae.co.uk> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

"Mihai N." <nm**************@yahoo.com> wrote in message
news:Xn********************@63.240.76.16...

Thanks for the reply. We've decided to give users the choice of using our
desktop database (which will be Jet 4.0) or to use their own. There will be an option in the app which will allow users to point at a file which
contains the OleDb connection string, either locally or on a network, which allows for total flexibilty. The only proviso we make is that the RDBMS they choose must support SQL. We will also provide an SQL script for
administrators to run so that they can create the database, tables and
fields that our app is expecting.