Is there an SQL database (for C#) that allows me to query against on-disk database and then later on (probably offline at client side) query against the in-memory result set using SQL?

I basically want to always be able to SQL query against whatever comes from a previous SQL query whether on-disk or in-memory.

I'm talking about CRUD queries only.



This will be used for general adhoc client-side queries at run-time against previous query results and it is also suitable for caching purposes but that is not the main concern here. And yes I need SQL or TSQL.

In your opinion guys what is the performance cost to save a DataSet(s) into an in memory database (say embedded SQL Server) for this purpose?

  • 2
    Maybe have a look into LINQ msdn.microsoft.com/en-us/library/bb397933.aspx if your data is stored locally in XML you can use LINQ 2 XML it's not exactly SQL, but it might do what you're looking for as you were vague on overall requirements.
    – Seph
    Mar 8, 2012 at 12:35
  • @Seph: Thanks but I really need SQL not LINQ :)
    – geeko
    Mar 8, 2012 at 12:40
  • you will need to be more descriptive then, what features do you need? You're not really going to find anything that can query as SQL inside 'in memory result sets' in C#, so you will need to put your results into a local SQL DB and query from that when offline. So decide what features do you need? your options are SQLite, SQLCompact, and perhaps MySQL / SQL Express and co.
    – Seph
    Mar 8, 2012 at 12:58
  • In your opinion guys what is the performance cost to save a DataSet into an in memory database (say embedded SQL Server)?
    – geeko
    Mar 8, 2012 at 16:15

3 Answers 3


I have a feeling LINQ is the best way to go but if you want to use TSQL on both the database and your in-memory datatable you could try this in-memory SQL engine.


Are you checking to see whether a record has changed since you read it so you don't overwrite another transaction's data? In that case you might want to look at various other methods of implementing optimistic concurrency.. The normal way to do this is with SQL Server to have a timestamp column on the record, record the timestamp when you read and then check it when you write.

You can produce robust queries that won't ever accidentally overwrite a changed record by including an equality check for the timestamp with snippets of SQL like:

update Foo
   set col1 = @col1
      ,col2 = @col2
 where FooID = @FooID
   and time_stamp = @timestamp  -- captured when the record was read,
                                -- this clause ensures a dirty record
                                -- is never overwritten.

select @rec_ct = @@rowcount

if @rec_ct == 0 then [concurrency error condition]

. . .

This will never overwrite a dirty record that's been changed since you read it. Checking @@rowcount allows you to detect whether the write was successful.

A Stackoverflow posting on optimistic concurrency can be found here.


Are you looking for an actual database product that allows you to run disconnected queries, or are you writing an application that allows client-side caching? This looks like the latter. In which case you'll have to write something moderately clever using SQLLite, I should say.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.