I have a flat file db (CSV) that is about 13.6MB's that I would like to be able to do several types of read-only operations on. Obviously, for a embedded device, loading that whole text file is rather cumbersome.

So, my first question is, is there a better database for this type of application available for the compact framework?

If not, is there a way I can query the file with T-SQL commands in order to only load up the data that is needed at the time?

Please keep in mind that coming from the perspective of PHP programming I am still struggling with the ADO.NET concepts of disconnected data so simple explanations are preferred.

Thanks.

Sounds like you want to use the .NET Compact Framework, which supports ADO.NET, and to some degree implies you'll be running this app on Win CE? You can run SQL CE if that's the case, which uses a dialect of T-SQL. It's not quite as powerful, but it depends what you're doing whether that will be a problem or not.

If you don't have access to SQL CE, I would probably just use the files in CSV format in your shoes. Being able to use T-SQL is a pretty compelling reason to do a data load or transform, but I'm not sure if it would make sense to translate the file into many other formats before you work with it.

In any case, you can open a stream to the file, and either read it in chunks through a buffer, or, for simplicity, just use the ReadToEndOfLine StreamReader method to grab one line / record at a time. Then with just that one line, which should be fairly small, maybe a few KB, you can use String.Split(',') to break the record apart into the columns that make it up. Now, if you've got strings in the file with quote marks to make apostrophes legal, that means a headache. That would force you to write your own line parsing routine, since String.Split would give you bad results.

With 41,996 records parsing line by line to look for matches would not be feasible. I had thought about SQLCE or even SQLCE2005 however I can't find any tools to convert into a SDF file.

Perhaps, to make things a little more clear I should expound a bit on what I am trying to accomplish. Though, I do want to say first that I avoided saying it in the first place because it's almost inevitable that someone will try to hijack this thread to attack my personal beliefs (just what I've learned from experience). So please, everyone, if you don't want to help me accomplish this goal then please don't answer.

When I got my PDA I knew there was plenty of bible study software available, such as e-sword however, what I didn't count on is a total lack of software that includes the book of mormon, doctrine & covenants and pearl of great price. A nice person has put all of the standard works into various formats such as XML, CSV, Access, MySQL (More details at http://scriptures.nephi.org/download.php). So my goal is to create a scripture study program similar to e-sword with all of the standard works.

I first tried the XML file, loaded slow but not too bad however, I ran into the problem of not being able to only extract the records I needed at the time I needed them. So I thought I would be able to use CSV instead but have run into the same problems. Windows Mobile 5 doesn't seem to support mdb files so I can't use that either.

So, building the application seems like the easiest part of this whole project; finding a suitable database that is fast, has a small footprint and will parse through thousands of results in short order seems to be much more complicated than I expected.

Why is parsing 42,000 records unfeasible?

Now this is vastly different hardware, but when DTS and SSIS just weren't working to load CSV files in fast enough - we're talking 400 MB text files with 900,000 rows - I made a C# Windows Service to subscribe to Windows file-system notifications to know when a new file is created, wait until exclusive access is available, and then read the file row-by-row, and fire off an insert command for each row. This was on a database server with a couple gigs, RAID 0+1, lots of procs available, and so on. Not exactly a PDA. Still, with almost a million rows in CSV format, parsing, DB interactivity, and so on, this took about 45 mins with other activity on the server. The nice thing about this, versus XML, is you can read one line at a time, so keep your memory usage low, and you can use multi-threading more easily to keep the UI snappy while your app does its work.

The next thing I might try is using XML and XPath queries? Since it sounds like you're only interested in particular records. After that, next on the menu would probably be using your own custom format and binary serialization. Is it possible for you to crunch all the data beforehand on your workstation, and make those files available for your users?

I don't think you'll find anything to "convert" something into a SQL CD data file. You would have to "build" one; setting up the tables and columns you want to store, then loading the data into it. Now, you should be able to do that on your PDA, copy the SDF file to your computer, and put it on your server, if you'd like to. I don't know what the rules are on redistributing SQL CE, but if you can do that, a few indexes will make things nice and quick.

Originally Posted by Republikin

With 41,996 records parsing line by line to look for matches would not be feasible. I had thought about SQLCE or even SQLCE2005 however I can't find any tools to convert into a SDF file.

Based on my experience with Windows Mobile 2003 I can tell you SQL CE is the best choice. I could manage some 25,000 records quite efficiently and don't think will be a problem with 40 or 50,000 or even more.

Regarding the tool, yes there was none. So, I used to manage that in a desktop MS Access database. I wrote Access query to genearte SQL INSERT, UPDATE commands and could run those SQL to update the sdf file on the mobile device (iPAQ 5550 in my case) without problem.

Visual Studio 2005 and up has a Windows CE device emulator built in. You can download it as an add-on for Visual Studio 2003 if you're still using that.

It builds a virtual network between your PC and your emulated PDA. So you could run Pocket SQL on the thing, build the database on your computer, then copy it off the virtual device. You can probably do all that with a real one, too, but I don't have one.

Yes, but built-in emulators (comes with VS 2003 also) are usually slow for running SQL

I should point out that this thread is long dead however, thanks for all your support.