Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Mar 8 '12 at 16:20

This question came from our site for professional and enthusiast programmers.

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 '12 at 12:35
    
@Seph: Thanks but I really need SQL not LINQ :) –  geeko Mar 8 '12 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 '12 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 '12 at 16:15

3 Answers 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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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