Create custom driver for System.Data.Common

Question

Background:

Our C# application generates and executes queries to several types of databases (Oracle, SQL Server, MySQL), but a requirement came up to also apply them to a proprietary file format.

1. The namespace used until now is System.Data.Common.
2. The queries we need to apply are non-trivial (nested SELECTs, aliases in FROM, substring methods and string concatenations)

We initially converted the contents of the proprietary file to CSV, for which there exists the Driver {Microsoft Text Driver (*.txt; *.csv)}. However, the client required that no temp files are generated, and everything should happen in-memory.

Creating an ODBC driver to query the file directly seems too time-demanding. So, we are considering creating a driver (possibly ODBC), in which we will "embed" the SQLITE ODBC driver. Inside that driver, we can load the contents of the CSV files in the "in-memory" database, and then forward the query to the inner ODBC driver.

My questions:

1. Does this solution seem feasible?
2. Where should we start in order to create an ODBC driver from scratch?

Thanks

Answer 1

The client request is odd. There will always be files involved, you are reading from a file.

If they want their stuff in memory (again a weird customer) put the CSVs on to a RAM Disk: http://members.fortunecity.com/ramdisk/RAMDisk/ramdriv001.htm

Building and ODBC drive is a non-trival undertaking if you care about performance and stability.