How can I execute SQL statements against a Dataset

Question

I would like to be able to use a Dataset as an in-memory datastore. I want to be able to use SELECT, INSERT, UPDATE, and DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE ADD COLUMN, ALTER TABLE DROP COLUMN, and support for Constraints PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY, REFERENCES.

Answer 1

Sorry, that's not what a DataSet is for.

Why do you want to do this?

Answer 2

This is .Net? What version? 3.5 and later let you run sql-like linq queries on any IQueryable

Answer 3

Sounds like you need an in memory database, not a dataset.

Answer 4

If you want to test your database commands on an in-memory data store, DataSet is not the way to go, as its not an in-memory relational database engine. As others have said, you can do all sorts of querying on a DataSet, but not DDL commands. I have looked at various in-memory or embedded database engines (SqlLite, HSQL, Firebird) but never came close to finding a good way to unit test Sql Server code in memory, generally due to the limitations of those engines (e.g. no stored procedures).

If you need an embedded database for your application, have a look at those products. If you need to test Sql Server commands, you'll have to run them on an instance of Sql Server (consider Express, lightweight and free).

Answer 5

The short story is that you can't do those operations on a DataSet or any other .NET datatype. Those commands aren't inherent in the data itself, they're part of database software supporting SQL; you can only use those operations with software that supports them like a database server or embedded database.

Depending on what your are doing, linq may be useful, but doesn't provide exact SQL syntax or any of the specification language bits you mentioned. (ALTER TABLE, etc.)

If your project is not already using an external database, you could also consider using an embedded database (sqlite, Firebird, not sure what else).

Answer 6

you want LINQ! It can perform all the actions you asked for upon your data ... and far more whilst giving you that sexy feeling that it probably shouldn't be that easy.

It will rock your box (if you are used to ado.net anyway)

Also look at Linq2SQL as it offers a little more when it comes to writing/reading your in memory data back to a database ... or linq2xml if you want to use a webservice.

Answer 7

You can't because the DataSet is an in-memory representation on the client-side of the data that you retrieved.

It is also agnostic to the provider of that data. Because of that, you can't use the same syntax as you would against the data source that the data came from.

The DataSet has it's own syntax for filtering data, but it is not heavily based on SQL.

Rather, you should use LINQ to DataSets which will give you a more-SQL-like experience in querying your data in a DataSet.

Answer 8

If you don't want to use LINQ (.NET 2.0 for example), there is a "select" method that allows you to provide a filter with "where-clause-like" syntax. Here's an MSDN link:

http://msdn.microsoft.com/en-us/library/system.data.datatable.select

This is a DataTable method so I don't know that it provides all that you're looking for. Also, this only allows you to select a subset of your dataset, not perform updates/inserts/deletes.

Answer 9

You may want to consider using temporary tables. Or even standard tables if your data has to be shared between database connections.

If you are looking for in-memory datasets for the speed they provide, you may want to look at in-memory table storages (MySQL and PostGreSQL do it, and others can be tricked into doing it by setting the table storage space on a ram-disk for instance).

Short-lived tables are often overlooked, but they are actually very helpful.

Answer 10

Someone has made this a reality in the form of a commercial product. http://www.queryadataset.com It supports all of the features that I originally asked about http://www.queryadataset.com/Documentation/Expressions/tabid/80/Default.aspx.