C# and SQLite - Part 1: 32-bit, 64-bit, or Both.
So you've started writing a nifty little C# application that's going to make the world a better place and you need to add a little lightweight database activity to it. You don't need a server oriented database, just something simple that you can include with your application. SQLite is very good choice for this. But how do you get started?
First off you have to make a decision about bits. Like the operating system, SQLite has 32-bit dll's and 64-bit dll's. That leaves you with 3 options to consider (at the time of this writing).
- Write your application in 32-bit only. Force visual studio to compile in x86 and lose any advantages 64-bit offers on an x64 system.
- Write you application in 64-bit only. Forget about the poor saps running a 32-bit OS and reap the benefits of an x64 system.
- Setup visual studio with an x86 and x64 environments, then add the appropriate references for each different environment to it's respective 32-bit and 64-bit SQLite dll's. Finally, compile two different versions of the program.
Get out a 3 sided coin and flip it to see what route you take. Or, apply a little logic and choose the path that works best for you.
What are the advantages of 64-bit systems over 32-bit systems? For our awesome little application it's mostly about memory addresses available and data handling. 64-bit environments can access more (lots more) memory addresses. You can store about 1 TB of data in memory if you have the memory in the system. Also, 64-bit systems are able to handle more complex processor instructions which greatly increases the speed with which they process data.
So you've decided to go with either option 2 or 3? Slow down a minute Turbo. If you remember our project isn't using a server oriented database so we probably aren't going to be handling zounds of data. If we were, a non-server database probably isn't the way to go.
In my personal experience using SQLite on ordinary desktop computers, SQLite memory stored databases of more then 500,000 records are quite slow. And SQLite disk stored databases of more then 80,000 records are quite slow. So you may want to figure out how much data you are eventually going to be handling and decide if you really need a server based SQL solution instead of SQLite.
Alright, you've decided that SQLite is still the best choice because you're not going to ever have hundreds of thousands of records in your database. If you aren't working with large amounts of data, you won't need a terabyte of memory. And you won't be processing so much data that you really need the larger processor instructions of a 64-bit system.
You can still pick any of the 3 original options if you want. But, for lightweight data handling, option 1 is just fine for both 32-bit and 64-bit environments. And, option 1 is simpler to implement then option 3.
For the following parts of this guide we will go with option 1 and just create a 32-bit application compiled for "x86" in visual studio. This will force 64-bit versions of windows to run the application as 32-bit and then we don't need to include any 64-bit dll's.