The assembly implements a complete set of SQL commands. The syntax follows Microsoft's SQL Server syntax as much as possible.
SELECT supports the following features:
- UNION,
- INNER JOIN,
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- GROUP BY
- HAVING
- ORDER BY
- predicates (LIKE, BETWEEN, IS, IN, EXISTS and sub-queries)
- expressions
- aggregates (AVG, COUNT, MIN, MAX, SUM, STDEV, VAR)
- functions including CAST (date and time, math, string and system)
- CASE
- PIVOT and UNPIVOT
- OPENROWSET (use for joing DataSet with data in an external database)
The following Data Definition Language (DDL) commands to define and manage schema are supported:
- CREATE DATABASE - create or load a DataSet
- ALTER DATABASE - change DataSet properties
- DROP DATABASE - unload a DataSet
- CREATE TABLE - create a DataTable, supports identity, primary key, foreign key and unique constraints
- ALTER TABLE - alter a DataTable schema, supports renaming a column or the table
- DROP TABLE - delete a DataTable
- CREATE INDEX - create an index on a DataTable
- DROP INDEX - delete an index
The following Data Manipulation Language (DML) commands to manipulate data are supported:
- INSERT - insert one or more rows of data. DEFAULT values are supported
- UPDATE - update one or more rows of data based on a condition
- DELETE - delete one or more rows of data based on a condition
- TRUNCATE - remove all rows from a table. Reset of IDENTITY columns is supported.
- MERGE - insert, update or delete data on a target table based on the results of a join with a source table.
- MERGE DATABASE - equivalent to the underlying ADO.NET DataSet.Merge
- MERGE TABLE - Equivalent to the underlying ADO.NET DataTable.Merge
Separating multiple commands in a batch using a semi-colon is supported.
The -- (double hyphen) and /* ... */ comment syntaxes are supported and can be embedded anywhere in a SQL statement.
Four part identifiers are recognized including support for the square brackets ([table].[column]) when a name contains spaces or matches a reserved keyword.
[ [ [ [ server. ] [ database ] . ] [ namespace ] . ] table .] column
- Server is reserved for future use.
- Database is the name of a loaded dataset. Use CREATE DATABASE to load multiple datasets.
- Namespace corresponds to the DataTable.Namespace which allows the same table to appear in a Dataset multiple times.
- Table corresponds to a DataTable.TableName or alias
- Column corresponds to DataColumn.ColumnName in a DataTable or alias
Flow control commands DECLARE, SET variable, IF, WHILE, WAIT, SLEEP, USE, PRINT, INCLUDE and EXEC sql are also supported for running external scripts against a loaded Dataset.
INFORMATION_SCHEMA is supported for accessing the schema of the dataset including SCHEMATA (Datasets), TABLES, COLUMNS and CONSTRAINTS.
The OPENROWSET table source allows joining data in a DataSet with data in an external database. This is useful when part of the data is too large or practical to cache in a DataSet. It is also useful when the underlying database such as SQLLite does not support a feature of QueryADataset such as PIVOT/UNPIVOT or MERGE.
See the online documentation for more details.