SQL Query Against Previous SQL Query Results

Question

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.

Thanks!

Background:

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.