I tried to figure out which Database system is best for my case.
My needs was a small footprint database system with good interaction with Visual Studio or with good Tools which should have a .Net Sql provider and it would be Free or cost minimum.
There was a good article (i will update here when i find it again) referring
Sql Server Compact (CE),
Sql Server Express,
SqlClient and more.
I ended to the 2 Microsoft's database systems, Sql Server Compact and Sql Express.
There is a good article (MS Word Document) from MSDN comparing these two database systems.
After reading that too i ended up to the Sql Server Compact as it runs in process with my desktop app, it uses a small memory footprint, it does not need an installation procedure (X-Copy) and it seems fast enough for my needs.
One think it doesn't have is stored procedures and views.
As i needed a factor of abstraction into my database system i searched for some DAL (OR/M) components to sit on top of my Database.
Currently there are these three choices:
Why use the Entity Framework?), i preferred to give a chance to Entity Framework with the add on of the Linq queries i like very much.
Linq to Sql seems more like a "Queries simplifier" to me than a real OR/M system.
So, i ended up using Sql Server Compact with Entity Framework and Linq queries.
Trying to harness these beasts the following links seemed most useful to me:
Regarding Sql Server Compact
- Steve Lasker's Web Log
- Renaming tables
- Exporting Database as script
- Accessing SQL Compact from SQL Server "Linked Server"
- Multithreaded Sql Server Compact