Click here to Skip to main content
15,868,123 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
Hi,

We have a database application that maintains inventory data for shade tree populations (location / GIS data, site characteristics, tree attributes, etc.) and manages their maintenance via an integrated service request and work order system. The UI and data provider layer are vb.net 2010 and we're currently using MS Access 2007 as the backend.

The databases are generally small to medium size (i.e. 15 main datatables that typically will have 5,000-15,000 rows each. Less commonly, there will be a few 100k or more in each. Use is almost always single-user PC or small workgroup connecting to a shared database file).

Now, we want to add the ability to tie photos and documents (mostly MS Office + .pdf) to individual tree records and believe that some of our users will use this feature to the point of maxing-out Access' 2G size limit, or at least pushing it to inefficiency.

I've read-up on the timeless debate over storing photos and documents in the database versus the filesystem and concluded that, in our case, neither is optimal for several reasons. Primarily:

1. We have a very strong preference, if not a requirement, for a single-file data store - Users frequently copy a data file to pen tablets for field use and back to the desktop at the end of the day... Our application allows users to create and use a seperate database file for each tree population they manage, just like MS Word will create and use multiple documents... We use a file association - double-clicking one of our database files launches our app and establishes the connection parameters. A multi-file / multifolder data store would break or confuse these features and leaves too much risk of data being lost or corrupted.

2. Most of the single-file databases I am familiar with have an imposed size limit that might prove to be too low for some of our users (Access, SQl Server CE), do not support connections over a network (SQL Server CE), are alleged to have certain performance issues that may affect our situation, etc. Others like VistaDB might work, but it's difficult to justify more third-party costs than we already have on this particular project.

So, I started wondering if there was a way get the best of both worlds - embed a database file (i.e. our existing MS Access file) and a directory (for storing photos and documents) into a single file, or something that acts like a single file. Another option would be to wrap multiple database files into a single file and keep each relatively small. I haven't experimented too much yet, but potential solutions might include:

1. Structured Storage / Compound File (it's not clear to me if you can actually connect to a database file while it's in structured storage or if it has to be streamed out to a new file first)

2. Using an uncompressed Zip file (again, it's not clear to me if a database residing in a zip file can be accessed directly, or needs to be unzipped each time i want to open a connection)

3. Something like TrueCrypt, which "creates a virtual disk within a file and mounts it as a real disk" (accesibility of the included files seems easy, but it appears that TrueCrypt comes as a stand-alone application versus a component and I don't love the idea of each of our files appearing as a drive on the user's machine... maybe there is something similar out there?).

So, after that long-winded explanation, my question is - has anyone else wandered down this path and/or have any ideas to offer?

Thanks so much.

Paul
Posted
Comments
Dr.Walt Fair, PE 20-May-10 20:53pm    
This doesn't look like a candidate for a Quick Answer to me. I'd suggest you ask in one of the Forums. I'll certainly watch the discussion, because I've wrestled with similar problems.

Yes, I am on the verge of wondering down a similar path. I intend to use SQL Server Embedded, which is quite limited in general, but allows me to create databases on the fly. In your situation, I'd create an empty file database, in Access format. Every time I needed to add a new database, I'd make a copy of that file. Then, in my metadata table, I'd store both an id to look up an image, and the name of the database that image is in. So, you could make id's unique to each table ( using GUIDs is an obvious way of doing this, but it makes your DB bigger and removes an automatic sort in order of insertion ), or you could make just the pair of the DB name and the id unique.
 
Share this answer
 
Comments
pcowie6680 21-May-10 11:20am    
Thanks Christian. I already toyed with this idea as a way to spread things out to avoid DB size limits and maintain performance, but end up back at the same question - how to turn those multiple databases into something that has the portability, clarity, etc. of a single file.
Read "How to develop a virtual disk for Windows" to understand how to create a virtual drive.

Store your primary database file (Access, SQL Server CE, whatever) as part of a larger file. Also in that file, include your images and such. Allow the database to be fragmented across the file. That way, when the database grows too large for its portion of the larger file, you can just increase the size of the file and put part of the database near the end of the larger file. This will of course require you to have some sort of header (like a master file table that indicates what parts of the larger file map to individual files).

Now, load up the header information for the single file that contains all the other stuff (pictures, database, etc). Use that as the basis of your virtual hard disk. That way, you can essentially stream from your real file to the virtual hard disk. And you can point Access/SQL Server CE to that database in the virtual hard disk. When your program launches, you mount the virtual drive and when it closes, you unmount the virtual disk.

Of course, you could put it in a zip file or whatever (like you mention), but then you wouldn't be able to do the streaming scenario I explained... you'd have to unzip the database when your app runs and then rezip it when it closes, which could take considerable time.

Note that I've not tried any of this. That's just a high level description of one way to accomplish what you are trying to accomplish. Good luck.
 
Share this answer
 
Comments
pcowie6680 21-May-10 10:55am    
This is an intriguing possibility. Unfortunately, it is a realm that I have zero experience in (especially how to "put part of the database near the end of the larger file", dealing with file headers, etc.). I'll have to do some learning and experimenting. I'll report back and let you know how it goes, but it will be at least as few days before I can. Thank you.
Also, you might try SQLLite, which I believe has a higher limit than 2GB (Access) or 4GB (SQL Server CE). See SQLLite Limits.
 
Share this answer
 
Comments
Christian Graus 20-May-10 21:37pm    
Those limits are limits on the 32 bit file system as much as anything, SQLLite is not going to be able to get around them. SQLLite also has no stored proc support, Access is a better DB.

AspDotNetDev: I tried to add a reply, but it seems a CP bug is preventing me from doing so, so I thought I'd reply here instead. NTFS is not a 32-bit file system. It can have terabyte sized files. Also, the lack of stored procedures may not be that big of a deal if it's just being used as a simple data store (i.e., simple relationships between tables).
pcowie6680 21-May-10 11:09am    
I looked at SQLLite when I first started this project some time ago. The documentation clearly recommended against (and still does) using SQLLite on a network filesystem due to locking and performance issues. The lack of stored procedures doesn't matter - all of our DB interaction is through a seperate data access class. I may take a fresh look, given that our use is single-user or very small workgroup and we open and close connections quickly. Still, even if SQLLite solves our problem for now, the challenge of combining multiple files into one is something I'd like to find an answer to. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900