Easily distributable database

[JB]

I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

[Nicholas Paldino [.NET/C# MVP]]

JB,

Personally, I would go with SQL Server Express. You would have to run
the install program for it (I believe there is an MSI that you can
distribute with your app), which, given the benefits outweigh the install
issue.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"JB" <ja*******@gmail.comwrote in message
news:11**********************@22g2000hsm.googlegro ups.com...

I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

[steve.falzon]

On Mon, 23 Jul 2007 13:10:08 -0700, JB <ja*******@gmail.comwrote:

>I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

Hi

SQL Server would be the best choice IMHO. It sounds perfect for your
requirments and with the advanced version (also free) you can have
full text searching and other SQL Sqerver features. Databases can be
up to 4gb in size.

It can be deployed using ClickOnce technology so you should have no
problems distributing it with your app. You might want to read this

http://msdn2.microsoft.com/en-us/library/Bb264562.aspx

HTH

Cheers
Steve

[JB]

Cheers,
SQL Express does look pretty amazing, but i have still have some
issues.
How big would the installer be? SQL Server express off the MS website
is around 40MB, would the full thing be required for an end user?
My app at the moment compiles to around 3MB, it may go up or down when
its finished.
I'd hate to have a <5mb program with a 40mb sql server install.
Where instead i could just include a 500kb blank access .mdb file.

If the installer were only a meg or two then it would be the ideal
solution.

On 23 Jul, 21:19, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail]:

JB,

Personally, I would go with SQL Server Express. You would have to run
the install program for it (I believe there is an MSI that you can
distribute with your app), which, given the benefits outweigh the install
issue.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail]

"JB" <[e-mail] in message

news:11**********************@22g2000hsm.googlegro ups.com...

I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

[Nicholas Paldino [.NET/C# MVP]]

Well, that's a decision you have to make. In this case, it is ease of
install vs. the performance profile and usability of the back end data
source.

And yes, you would have to include that install for SQL server express.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"JB" <ja*******@gmail.comwrote in message
news:11*********************@d55g2000hsg.googlegro ups.com...

Cheers,
SQL Express does look pretty amazing, but i have still have some
issues.
How big would the installer be? SQL Server express off the MS website
is around 40MB, would the full thing be required for an end user?
My app at the moment compiles to around 3MB, it may go up or down when
its finished.
I'd hate to have a <5mb program with a 40mb sql server install.
Where instead i could just include a 500kb blank access .mdb file.

If the installer were only a meg or two then it would be the ideal
solution.

On 23 Jul, 21:19, "Nicholas Paldino [.NET/C# MVP]"
<[e-mail]:

>JB,

Personally, I would go with SQL Server Express. You would have to
run
the install program for it (I believe there is an MSI that you can
distribute with your app), which, given the benefits outweigh the install
issue.

--
- Nicholas Paldino [.NET/C# MVP]
- [e-mail]

"JB" <[e-mail] in message

news:11**********************@22g2000hsm.googlegr oups.com...

I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

I have nothing against SQLExpress, but unless you have to use stored procs,
SQLite runs rings around the others. All you distribute is the ADO.NETSqlite
assembly and your database file and you are DONE. Nothing to install. And -
its fast as all hell.
-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
bogMetaFinder: http://www.blogmetafinder.com

"JB" wrote:

I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

[Jesse Houwing]

* JB wrote, On 23-7-2007 22:10:

I'm writing a data driven app, its all just about finished but i
havent decided how to store the data.

I'm looking at some form of database. Requirements:
Easy to Distribute, hopefully just some hard disk files.
Relatively quick to query.
I think in this case, speed is secondary to ease of use for the end
user. Although speed is always important, which is why i wont be using
xml :)

Data *shouldnt* get any bigger than 1GB (it shouldnt get more than a
few hundred meg but i'd like room for expansion).

My choices:
1) Access: Easy to use, distributes as stand alone files.
But then i'd have to go out and buy a copy of office :(
2) SQLite, never used it, oonly just heard of it today, starting to
read up about it now, seems like it will work similarly to access
(from the outside at least), dont know how the speed is though.
3) SQL Server Express: "Free to download, free to redistribute, free
to embed" according to microsoft.
It seems like a really good option, a free SQLServer seems like it
would be incredibly flexible and fast. But I am concerned about how
easy it would be to distribute with a prog? Will any potential users
have to download the sql server express app? or a couple of run-time
files? or nothing?
4) Fill in the blanks. Any other suggestions?

Opinions welcomed and appreciated.

5) SqlServer Compact Edition. It's basically the SQL Server version that
used to be only for the Windows Mobile & Windows CE platform. it can
now be used on any windows platform. it consists of just 3 dll's and a
data file that need to be packages with your application. No 10s of
megabyte large setup files, no system services no nothing. It's
performance is very good and it supports most of the functionality of
for example Access. It comes with a special ADO.NET provider which looks
almost like the standard System.Data.SqlServer you're already used to.

There's a few gotcha's:
- No Stored Procedures
- No Triggers

Other than that, you're all set.

http://www.microsoft.com/sql/edition...t/default.mspx

Jesse

[Mark Rae [MVP]]

"JB" <ja*******@gmail.comwrote in message
news:11**********************@22g2000hsm.googlegro ups.com...

1) Access: Easy to use, distributes as stand alone files.

Firstly, there is actually no such thing as an Access database. Microsoft
Access is not a database - it's a software product for developing database
solutions. Until the most recent version Microsoft Access used the Jet
database (.mdb) database format, the same database format that several other
Microsoft products used e.g. Visual Basic, Visual C++ etc...

Secondly, you do *not* need a copy of Microsoft Office (or even Microsoft
Access) to use Jet databases...

However, the Jet database file format is to all intents and purposes
obsolete now, so you'd do well to avoid it...

4) Fill in the blanks. Any other suggestions?

As others have suggested, SQL Server Compact Edition would seem to be your
ideal choice...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Bob Johnson]

RE:
<< However, the Jet database file format is to all intents and purposes
obsolete now >>

Can you expand on that a bit? Is MS no longer developing the Jet database
file format? What are current or near-term future versions of MS Access
using if not the Jet database?

Thanks

"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...

"JB" <ja*******@gmail.comwrote in message
news:11**********************@22g2000hsm.googlegro ups.com...

>1) Access: Easy to use, distributes as stand alone files.

Firstly, there is actually no such thing as an Access database. Microsoft
Access is not a database - it's a software product for developing database
solutions. Until the most recent version Microsoft Access used the Jet
database (.mdb) database format, the same database format that several
other Microsoft products used e.g. Visual Basic, Visual C++ etc...

Secondly, you do *not* need a copy of Microsoft Office (or even Microsoft
Access) to use Jet databases...

However, the Jet database file format is to all intents and purposes
obsolete now, so you'd do well to avoid it...

>4) Fill in the blanks. Any other suggestions?

As others have suggested, SQL Server Compact Edition would seem to be your
ideal choice...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mark Rae [MVP]]

"Bob Johnson" <[e-mail] in message
news:OI**************@TK2MSFTNGP04.phx.gbl...

>However, the Jet database file format is to all intents and purposes
obsolete now

Can you expand on that a bit?

http://msdn2.microsoft.com/en-us/library/ms810810.aspx - scroll down to
"Deprecated MDAC components"

Is MS no longer developing the Jet database file format?

That's correct. No further development of the Jet database file format is
planned.
http://technet2.microsoft.com/Office....mspx?mfr=true

Also, there is no 64-bit version of the Jet engine. That means that it's not
possible to develop 64-bit apps which use Jet. Some people recommend using a
32-bit subproject for this, but my feeling on that is that if you're having
to use a 32-bit database for what is probably the most important part of
your app, you may as well write the whole thing in 32-bit anyway, or find
another database solution...

What are current or near-term future versions of MS Access using if not
the Jet database?

Access 2007 does not use Jet natively at all, though it can work with Jet
databases for backwards compatibility. Instead, it uses a completely new
database file format (.accdb) which, though based on Jet, most certainly
isn't Jet.
http://office.microsoft.com/en-us/ac...678311033.aspx
http://blogs.msdn.com/access/archive...05/618366.aspx

AAMOI, Jet was originally written by the SQL Server team, but was abandoned
as not being robust enough as a server-side RDBMS.

So it was handed over to the Office team instead...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Bob Johnson]

Thanks for the Info Mark,

Not to wander too far away from the OP, but I find the following "feature"
of the new MS Access database format to be absolutely stunning, and in a bad
way :
The following quote is from
http://office.microsoft.com/en-us/ac...678311033.aspx

<<"Multivalued lookup fields Most database programs, including earlier
versions of Access, allow you to store only a single value in each field. In
Office Access 2007, however, you can create a lookup field that allows you
to store more than one value in each field. In effect, Access creates a
many-to-many relationship within the field and hides the details of the
implementation by using system tables.">>
Once again MS Access is out there making it very easy for bad things to
happen in a database. This "feature" goes directly against first normal form
(1NF).

I have made a lot of money cleaning up after people who implement
spreadsheets in a database (rather than learning proper normalization). This
new feature should keep lots of real developers employed for a very long
time as neophytes learn how to do things incorrectly. Whose to fault them
now... if MS enables them - heck, *encourages* them - to do the wrong thing
like having multi-valued columns. This is so wrong. Geeze



"Mark Rae [MVP]" <ma**@markNOSPAMrae.netwrote in message
news:eO**************@TK2MSFTNGP06.phx.gbl...

"Bob Johnson" <[e-mail] in message
news:OI**************@TK2MSFTNGP04.phx.gbl...

>>However, the Jet database file format is to all intents and purposes
obsolete now

Can you expand on that a bit?

http://msdn2.microsoft.com/en-us/library/ms810810.aspx - scroll down to
"Deprecated MDAC components"

>Is MS no longer developing the Jet database file format?

That's correct. No further development of the Jet database file format is
planned.
http://technet2.microsoft.com/Office....mspx?mfr=true

Also, there is no 64-bit version of the Jet engine. That means that it's
not possible to develop 64-bit apps which use Jet. Some people recommend
using a 32-bit subproject for this, but my feeling on that is that if
you're having to use a 32-bit database for what is probably the most
important part of your app, you may as well write the whole thing in
32-bit anyway, or find another database solution...

>What are current or near-term future versions of MS Access using if not
the Jet database?

Access 2007 does not use Jet natively at all, though it can work with Jet
databases for backwards compatibility. Instead, it uses a completely new
database file format (.accdb) which, though based on Jet, most certainly
isn't Jet.
http://office.microsoft.com/en-us/ac...678311033.aspx
http://blogs.msdn.com/access/archive...05/618366.aspx

AAMOI, Jet was originally written by the SQL Server team, but was
abandoned as not being robust enough as a server-side RDBMS.

So it was handed over to the Office team instead...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Mark Rae [MVP]]

"Bob Johnson" <[e-mail] in message
news:em*************@TK2MSFTNGP02.phx.gbl...

Thanks for the Info Mark,

Once again MS Access is out there making it very easy for bad things to
happen in a database. This "feature" goes directly against first normal
form (1NF).

Ted Codd will be turning in his grave... :-)

I have made a lot of money cleaning up after people who implement
spreadsheets in a database (rather than learning proper normalization).

That is so true. I was once asked to evaluate an Access for Beginners
training course, and the trainer's first words were "Think of Access tables
as a bit like a spreadsheet." The course proceeded no further... :-)

This new feature should keep lots of real developers employed for a very
long time as neophytes learn how to do things incorrectly. Whose to fault
them now... if MS enables them - heck, *encourages* them - to do the wrong
thing like having multi-valued columns. This is so wrong. Geeze

Apparently, it's not quite so bad as it sounds...

I haven't really looked into the accdb file format much and, quite frankly,
with SqlCe, I doubt very much whether I'll ever have any need to...

Expecially when the 64-bit version of SqlCe comes out which, I'm reliably
informed, isn't too far away now:
http://blogs.msdn.com/stevelasker/ar...-released.aspx

64-bit Office (and, therefore, 64-bit .accdb) is still a long way off...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net