Windows application using embedded database

I have a small windows application that I needs to have an embedded database.  The database will maintain static data that will be used during the course of the application's use.  The database cannot be an external SQL server but rather an "embedded" resource installed with the application.  The data can be as large as 3 columns with 45,000 records all varchar.  What would the best case scenerio.
andy_eeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rahul Goel ITILSenior Consultant - DeloitteCommented:
If you have got this kind of requirement. then I wll ask you to use XML Files as a embedded files in the assembliese

Or

You can use mdb filea and embed that in assembly..and while using it you need to extract to some temp location and use it and delete it
0
Jaime OlivaresSoftware ArchitectCommented:
this information will require about 0.5 - 1 Mb. This will inflate your application unless you store it compressed.
I think the best option is to save a deflated xml file as an embedded resource. Then inflate it at runtime into memory with an XmlReader or other classes to handle xml.
0
mrjoltcolaCommented:
When you say the database cannot be an external SQL server, do you mean external as in running on a different machine or on the same machine?

You can package and distribute embedded databases such as iAnywhere (SQL Anywhere), SQL Server Compact Edition, SQL Lite, Perst, etc. all as part of your application. This is the most common way I distribute applications with data. However, most of my apps have many tables, not just one.

For a single table of data with 45,000 rows, you can get by with SQL Lite as a free option, it is part of your application, not an "external" server. Most all embedded databases are compiled in as part of the program.

I've also had success packaging a file as a simple fixed width record binary file, and loading in the index at runtime. There is no need to load the whole 45,000 records, you can simply load the key column, then randomly access the data file by index. Have used this technique with drug databases for example with about 80,000 rows.

XML is not the best solution if you want decent performance. If this is on a PC, I agree with loading into memory, but I would use a CSV format packaged with the app and load it completely into a Map or Hash object. Using CSV eases the maintenance of the file, you can use a standard database extract or use a spreadsheet. If it is a PDA or smaller memory device, I would probably not want to load the whole file in memory. Definitely not XML. XML will bloat the data storage, plus it does not solve the problem of random access. XML is not a database driver, it is simply a format. The advantage to using an embedded database is that it is stored in a B-Tree indexed format so there is no memory overhead at all.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantCommented:
I would use http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx. Only 2 DLLs to copy with your application (no installation of SQL is required).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.