Any reason to use DataSets with Local Databases with SQL Server Compact?

Question

Does it make sense to use .NET DataSets even in applications that use a local in-process database for application data persistence?

If DataSets are mainly meant for in-memory caching of database results, it sounds like they're not so beneficial when using something like SQL Server Compact local database that runs in the same process as the application.

Are there any other reasons to use typed DataSets? Say, do they ease WPF data binding?

Answer 1

I have removed all typed datasets from my application. It takes too long to create them. By creation I mean the new statement / constructor. Also retrieving data is not as efficient as using DataReaders directly.

Don't know if many queries (with DataReader) to a in-process database will make your app a lot slower. I think it just depends on you application.

Mostly it is faster to cache the data as to parse an extra SQL statement and retrieve the info. But then again the extra memory it takes also has a cost.

Answer 2

Let me try to answer my own question.

It seems to me that DataSets were designed for use-cases such as this:

1. Data is loaded from a remote DB to an in-memory cache (the DataSet).
2. The cached copy is operated on in non-trivial ways (multiple tables, delete, add, update) without an active connection to the database.
   • DB relations need to be modeled into the local cache to enable these operations.
   • Data binding to the UI (e.g., WPF) is trivial because we're working on in-memory copy of the data.
3. The cached copy is sometimes updated to the remote DB for true persistence.
   
   • This may happen for example when the client returns to online state or the user presses "Apply" to truly commit his data.

With local in-process databases, there's no need for the ability to work completely offline -- the local DB connection is always available. This would suggest that modeling (potentially) complex DB relations into the local cache to enable add, delete and update is unnecessary. Rather, one would directly modify data in the database and only maintain a custom local cache for viewing the data. The local cache could be decoupled from the DB layer and put into its own ViewModel layer (MVVM).

Answer 3

You can use SQL server compact to easily unit test an application which should work with SQL server. Without Sql server compact, your test is an integration test rather than a unit test (harder to configure and takes more time to run).