My latest C#/Windows Forms/Visual Studio desktop application uses an SQLite database. All my other similar desktop applications used SQL Server.
The application will be deployed to remote individuals, so having them install a database engine such as SQL Server wasn't practical. Microsoft's Jet database would also have been good, however the end used would have needed MS Access (either full or runtime) installed - which against wasn't practical.
I'd initially built the application to use an XML backend. This worked, but the LINQ code to get and manipulate the data wasn't nice. Also dealing with linkages between tables and parent and child tables got messy.
I didn't know about SQLite when I started the project and it took about a day to convert it from XML to SQLite.
The benefits of SQLite:
- File based database so the end client doesn't need to install anything.
- No dependency on SQL Server or MS Access
- Small database file size
- Free and open source
- So far, it seems to be fast
- Most of the main SQL functions seen to exist - although I've only used to a basic level (also no weird ambiguous out join errors like Access)
- Once you've downloaded and made reference to the right object library, the C# code to manipulate the database is all very straightforward, and almost identical to the code used for SQL Server.
The SQLite website doesn't make it at all easy to download the reference library for Visual Studio - it took me a good few hours to figure it all out. You also need a third party tool to create and administer the databases (like SQL Server Management Studio). I'm using DB Browser and it's working well (although it does occasionally lock the database).
So far it seems to be the ideal tool for the job. I've deployed it to one end user and it worked straight away with no errors or problems. So in conclusion, I'd recommend SQLite for small local applications were the likes of SQL Server or MS Access aren't practical.