Click here to Skip to main content
Click here to Skip to main content

An SQL execution engine for DataSets

, 29 Jun 2006
Rate this:
Please Sign up or sign in to vote.
Describes a crude method of executing an SQL statement against an in-memory DataSet and returning a new DataSet containing the result table(s).

Sample Image

Introduction

DataSets in .NET are a powerful tool for manipulating data locally. However, despite their flexibility, they can be time consuming and difficult to use. I am sure I am not alone in wishing many times that I could just "execute some SQL" against a DataSet and have the result presented in a new DataSet. As it stands, I have to create DataViews, clone tables, create calculated columns, loop through rows copying, etc. etc., if I want to take a DataSet and manipulate its contents.

The attached code library shows a crude method for enabling this functionality. It leverages the fact that a DataTable can be converted to a .CSV (Comma Separated Value) table with minimum difficulty, and that Microsoft provides an ODBC driver for .CSV files.

By converting all DataTables in a DataSet to .CSV files and then executing some SQL against them using the ODBC Microsoft Text Driver, we can generate a new DataSet.

Using the code

If you build the solution, a .dll will be created, called DataSetSQLEngine.dll. This exposes a single public class, with a single public shared function, executeSQLDataset(). The arguments to the function are a source DataSet, the SQL string to be executed, and, optionally, a path to a folder for storing the temporarily created .CSV files. If this path is not passed, the local temp folder will be used. If using the library from ASP.NET, you will probably need to pass a path that exists beneath your application's root folder, as the ASPNET user may not have access to the system temp folder.

If anything goes wrong, such as an SQL syntax error or IO error, an error will be raised, so you should always wrap these calls in a Try...Catch block.

Dim newDS As DataSet = _
   DataSetSQLEngine.DataSetSQLEngine.executeSQLDataset(ds, _
   "Select * From myTable.csv as myTable order by dateStamp")

Note in the example that you must reference your tables as aTable.CSV. By default, the ODBC driver looks for files with a .txt extension. There may be an option you can set in the driver to force it to default to .csv; I haven't looked very hard. If anyone finds it, please let me know.

The Microsoft Text Driver ODBC driver appears to use the Jet Engine on which Access is built, and seems to support the Access SQL implementation. It would also appear that the standard Access functions are available, which is a bonus.

The included project, SQLEngineExample, has a very simple example which will give you a good idea of how the function works.

Points of interest

As mentioned, this implementation is quite crude, and will not provide a usable solution in some circumstances. For example, if performance is critical, then this solution fails miserably, as it requires a lot of disk IO and scanning of tables. Also, I have not tested it with many different types of data. You will need to test it well with whatever data you have, before relying on it.

When using string values in your queries, ensure you use single quotes; double quotes cause an error.

Another limitation is that only SELECT and INSERT are supported by the Text ODBC driver, not UPDATE or DELETE. Although, why you would use this library for anything other than SELECT I don't know... Just something to be aware of.

If you take the attached project and add more robust error-checking, or improve the usability in any way, please let me know and I will upload it (and likely use it myself!).

I would like to take this opportunity to request Microsoft that they add this functionality as part of the .NET DataSet implementation, preferably with a syntax that encompasses T-SQL.

History

  • 26/06/06 - Added prototype version.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Lachlan Keown
Web Developer
New Zealand New Zealand
Software developer working with VB.Net and SQL 2000.

Comments and Discussions

 
Generalerror with a cross join PinmemberMember 33245873-Oct-08 15:17 
GeneralLINQ PinmemberLachlan Keown27-Aug-06 17:56 
GeneralExcellent and very useful article PinmemberSabuncu9-Aug-06 5:54 
Generalqueryadataset Pinmemberc4tes3-Jul-06 6:45 
GeneralRe: queryadataset PinmemberLachlan Keown3-Jul-06 10:57 
GeneralRe: queryadataset Pinmemberc4tes3-Jul-06 11:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 29 Jun 2006
Article Copyright 2006 by Lachlan Keown
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid