[.net] Database Help? (VB.NET)

I use SQLite for a lot of my small database needs. Combined with ActiveRecord, it's hard to get much simpler.

What kind of data are you looking to store?

You might want to use the registry if they are application related settings.

Or, you might consider what is called a flat file database -- which basically means a text file of records. It is usually quite fast, and incurs very little overhead compared with a sql database.

A couple notes...
* Remember to open it with a stream and share access with other applications)
* Also, read from it or write to it if you need to, but always close the file right after you are done with it.
* Leave extra space after each field that contains digits for future expansion.

For example, medical claims in Washington state used to be stored somewhat like this...

There was basically a document that described the format of the file.

Usually a spreadsheet, but here is an example of this kind of documentation.

A record
byte 1 .. 10 last name
byte 11 .. 20 first name
byte 21 .. 27 birthday
byte 28 .. 30 not used
byte 31 .. 40 reason code
byte 41 .. 44 not used
byte 45 ......


B record
... more stuff here


Here is a sample of what the data in the file might look like...

AJohnson Samuel 08171978 0000000398
Bbla, bla, bla

Here are some pros and cons.

No need to know sql. No need to load a sql library or database overhead. No need to authenticate with a sql database. No need for whoever maintains your work later to decipher your sql queries.

XML is better in a lot of ways, but sometimes doing things the old way can be more efficient and/or easier.

Xml would take up more space on disk, but would require slightly less documentation, and there is already some framework created for you to read and write basic xml.

In addition, even if you are very careful the documentation tends to get lost eventually, so xml can be a bit of help in that way. Though you could store the field names in a heading at the top of your flat file.

If you want a robust but easy to use database solution, SQL Server Express is a very good product. Once it's runtime components are installed on your target machine, you can use a standalone database file just like Access and just deploy it along with your executable files.

VB.net 2005 - at every license level - offers good integration with SQL Server products so if you know just a little bit of SQL, you can get your database running smoothly in matter of hours (for first time users).

All that said: if you have extremely high performance expectations, may a custom database engine be a better option for you; you still find SQL Server quite difficult to beat on performance, let alone other important things like robustness and scalability.

Note that these are my personal opinions; Microsoft hasn't paid anything for me to endorse SQL Server, but I simply see it as a great platform for databases of any size.