Database driven windows desktop application with embedded database on Visual Studio 2015

Question

1. I have to build a Windows Desktop application that stores data and is able to manipulate data but the problem is, I cannot decide which database to use?

2. I need the app to be standalone so that minimum to no other software needs to be installed on the client PC( windows vista to 10 ).

3. I need the app to be able to export the data and maybe connect the database to the cloud.

4. I'm using C# as language and WPF as user interface platform.

I read this question it almost covers my requirements.

But as the Sql Compact Edition is depreciated in after 2013 visual studio editions I'm not sure what to use.

I also read this answer, it covers the same things this but I'm not sure what does that mean, does it mean if I use Microsoft SQL Server as database and Embed the MS SQL Server Express ( latest, database only ) with my application I will not need to setup a server explicitly.

And if I do follow this practice what are the other things that I might need to consider before making this choice.

And to be Clear I'm using Visual Studio 2015 Community and I have been using Service Based Database that creates a .mdf file and I like that approach and the main focus of me asking this question is that, if I continue with the same approach ( Service Based Database ) and embed **MS SQL Server Express (**database only ) with my application, will I be ok with point 1,2,3,4 above ?

Please provide any insights into this matter.

I cannot make a decision without knowing that someone has done it and it works.

Answer 1

A few tips to ensure that you're future-proof:

1. Do not interact with the database from your code directly. Set up contracts (Interfaces) and do all implementation in a separate DLL. IF you need to move from a local db to a cloud db, you can then simply re-implement the same interface for the cloud database and your code will run as before

2. With the method in (1) you're basically also quite free to use a radically different type of data store, i.e. one of the many NoSQL databases out there.

I usually call my database contracts for XXXRepository or XXXStore, example:

public interface IProductStore
{
    IEnumerable<Product> All { get; }

    IEnumerable<Product> OutOfStock { get; }

    Product GetById(Guid Id);
}

I can implement this ProductStore contract in a separate DLL that only deals with for example SQLite that has already been mentioned here, or anything else. You then either use a factory or IoC Container to do the mapping between the interface and implementation:

var productsStore = DependencyResolver.Resolve<IProductStore>();

foreach(var product in productsStore.OutOfStock)
{
    System.Console.WriteLine($"Need to refill: {product.Name}");
}

This leaves you VERY free to try out a ton of different databases that can fit your need, and you only need to change the configuration to use a different type of database