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.
