In-Memory SQL Engine

Introduction

Note: The addition of LINQ to the .Net Framework has supplanted the need for this stand alone SQL Engine

Wouldn't it be nice if you could use good-ole SQL language to execute a Select statement against a set of locally-cached DataTables that you've already retrieved from an underlying database?

The SQL_Engine class does exactly that... it supports a minimal set of SQL SELECT command statements against a group of ADO.Net DataTables contained in a detached DataSet. The purpose is to prevent a round trip to the server to accomplish simple things like what is available with the JOIN and GROUP BY clauses of the SELECT command.

This is *NOT* a complete SQL Engine... in fact, it's not even a complete implementation of just the SELECT command. Consequently, there is no support for any DDL statements (ALTER, CREATE, or DROP) nor any other DML statements (INSERT, UPDATE, or DELETE) nor any DCL statements (GRANT, DENY, or REVOKE).

The syntax for the SELECT command is designed around the SQL Server 2000 dialect of the SQL language. There is certainly no claim of compliance with any SQL standard. The most significant limitation is the rather poor support for SQL Server 2000's huge list of functions.

Operators, Functions, and Expressions are limited to what is natively supported (or easily translated) by ADO.Net, namely:

Comparison: <, >, <=, >=, <>, =, IN, LIKE, IS NULL
Logical: AND, OR, NOT
Math: +, -, *, /, %
String: +
Wildcards: *, %, []
Aggregation: SUM, AVG, MIN, MAX, COUNT, STDEV, VAR
Functions: LEN, ISNULL, IIF, TRIM, SUBSTRING, CONVERT

What *is* supported is SQL statements like the following:

SELECT Categories.CategoryID, CategoryName, AVG(UnitsInStock) AS AvgUnits
FROM Products JOIN Categories ON Categories.CategoryID = Products.CategoryID
WHERE (UnitsInStock > 0) AND (Discontinued = 0)
GROUP BY Categories.CategoryID, CategoryName
HAVING (AVG(UnitsInStock) > 30)
ORDER BY AvgUnits DESC

OK, I'll admit... If you plan on doing any serious SQL work, you might first consider using a locally-installed copy of Microsoft Desktop Engine (MSDE) or the newer SQL Server 2005 Express Edition. These solutions are well supported and are free!

Note: This is a demonstration project... not production code. It has not been thoroughly tested in a production environment. I'd highly recommend you perform your own side-by-side testing of the output of this DLL and a "live" database.

Design Philosophy

There were two goals for this project... to create a useful tool for doing select statements against a detached dataset and to publish the source code as an example of advanced ADO.Net programming.

Unfortunately, the two goals sometimes were in conflict with each other. There are several locations in the source code where some significant improvements in performance could be obtained. However, those optimizations would make the source code way too complex and difficult to follow. Consequently, I tended to take the side of “source code reability” over performance.

I left enough “bread crumbs” behind so that others can pick up where I left off to make optimizations if they feel they need to. There are also quiet a few “TODO” markers in the code where other features could be added (either by me or anybody else).

How to use SQL_Engine in your project

The source code contains a solution with two projects… one project in a subdirectory called SQL_Engine creates the SQL_Engine DLL file. The other project in the root is just a test bed to demonstrate the features of the DLL. You can compile the DLL and then copy it to your project, or you can copy the entire SQL_Engine subdirectory into your project.

Typically compiled DLL files would be digitally signed and placed in the Global Assembly Cache (GAC) or distributed in the execution directory with your project.

Current Limitations

Although I attempted to follow the Microsoft SQL Server 2000 dialect of the SQL language, there were quite a few features that did not make the cut. The most severe limitation is the lack of support for the hundreds of SQL Server functions. Many could not be easily translated using native ADO.Net features, and adding them is outside the scope of this project.

The entire database, including temporary tables, must exist in RAM on the client PC. That means that you will see significant performance degradation using large databases. Likewise, a query with multiple joins could take several seconds to complete.

Error Messages

There are 3 major categories of error messages:

Documentation

Sorry, there is none. Actually the source code is highly commented and serves as the project's documentation. I hope I've done a sufficient job of commenting the code to allow others to figure out what's going on.

Downloads/Links

Read a related article on Using ADO Expressions
Read a related article on Using ADO DataRelations
Download the complete VB.Net Source code described in this article: SQL_Engine.zip