Our C# application creates and executes queries to various kinds databases (Oracle, SQL Server, MySQL), but essential emerged also to apply these to a proprietary extendable.
- The namespace used so far is System.Data.Common.
- The queries we have to apply are non-trivial (nested Chooses, aliases in FROM, substring techniques and string concatenations)
We initially converted the items in the proprietary file to CSV, that there is available the motive force . However, the customer needed that no temp files are produced, and everything should take place in-memory.
Creating an ODBC driver to question the file directly appears too time-demanding. So, we're thinking about developing a driver (possibly ODBC), by which we'll "embed" the SQLITE ODBC driver. Within that driver, we are able to load the items in the CSV files within the "in-memory" database, after which forward the query towards the inner ODBC driver.
- Performs this solution appear achievable?
- Where must we start to be able to create an ODBC driver on your own?
The customer request is odd. There'll always be files involved, you're reading through from the file.
When they want their stuff in memory (again a strange customer) place the CSVs onto a RAM Disk: http://members.fortunecity.com/ramdisk/RAMDisk/ramdriv001.htm
Building and ODBC drive is really a non-trival undertaking should you worry about performance and stability.
I believe your option would be time intensive. You'll find existing solutions for what you're attempting to do. Listed here are couple of alternates.
Try Linq to text/csv file?
Both solutions have been in memory.
Also try this is the fact that, you are able to export file in xml rather than csv or text (I usually prefer export into xml when I must process during my code). Than you utilize System.Xml or Linq to Xml to do procedures.
If using SQLite like a after sales is really a possibility, I am unable to really understand why you can not make use of an ADO .Internet provider for your SQLite database like System.Data.SQLite.
Out of your comment, I believe you're in effect while using ODBC ADO .Internet provider for those database connections (System.Data.Odbc). If you want to keep your same plan, a custom ODBC provider is what you want (however it's plain C native development, and rather painful In my opinion).
A different way to go is always to give a third parameter for your DB (the very first two being the SQL and also the connection string) : the ADO .Internet provider for use (enjoy it is supposed to be done in configuration files, begin to see the
providerName attribute). By doing this you could utilize any ADO .Internet provider available.
Then you may wrap the the SQLite provider to your own, custom, ADO .Internet provider, that may range from the generation and population from the SQLite DB. Benefit of this solution : pure handled .Internet.
Should you only are restricted to not create temporary files, you might use in-memory mode of SQLite. Here is a sample of connection string for this (source):
In my opinion, this really is simpler than building full-blown provider.$