TheSwamp

Code Red => .NET => Topic started by: tfieker on February 15, 2023, 03:29:58 PM

Title: Database and Worsheets Communication
Post by: tfieker on February 15, 2023, 03:29:58 PM
We develop applications for BricsCAD/AutoCAD using VB.NET. These apps communicate with MS Access (mdb/accdb) and Excel (xls/xlsx) files. For this we use "ADO.Net" Object Model to Select, Update, Insert, etc... data to the database, connecting through OleDbConnection.
The Provider we use is: "Microsoft.ACE.OLEDB.12.0" installing MS Access Database Engine 2010.
Since few months when MS Office (or Windows) is updated, it corrupts "cpprestsdk.dll" file, which forces us to reinstall MS Access Database Engine 2010.

We tested SQLite a few days ago and it can replace MS Access (mdb/accdb) perfectly. However it would be necessary to continue using ACE.OLEDB.12 & ADO.NET to access Excel.
Communication with Excel worksheets using INTEROP.EXCEL is very slow and does not replace ACE.OLEDB.12 & ADO.NET.

Can anyone give us any suggestions for alternatives to communicate with EXCEL files, not using "Microsoft Access Database Engine 2010" or "Interop.Excel" but allows to Insert, Update and Select data as a database.

Regards,
Tácito Fieker
CADGRAPH
Title: Re: Database and Worsheets Communication
Post by: It's Alive! on February 15, 2023, 03:59:04 PM
Hi, welcome to the swamp

In C++, I use ODBC
Some of this might be useful https://www.arclab.com/en/kb/csharp/connection-string-enumerate-odbc-drivers-access-excel.html
Title: Re: Database and Worsheets Communication
Post by: 57gmc on February 16, 2023, 02:06:12 PM
In C#, I like to use the Visual Studio Datasource objects. Once you connect to a datasource, VS creates objects for tables, etc. that you can code with. To show the Datasources palette, View>Other Windows or Shift+Alt_D.
Code: [Select]
    Dim ds As CadmgrDataSet = New CadmgrDataSet
    Dim taLog As CadmgrDataSetTableAdapters.LogTableAdapter = New CadmgrDataSetTableAdapters.LogTableAdapter
    Dim dtLog As CadmgrDataSet.LogDataTable = taLog.GetData()
    Dim taMgr As CadmgrDataSetTableAdapters.TableAdapterManager = New CadmgrDataSetTableAdapters.TableAdapterManager
Title: Re: Database and Worsheets Communication
Post by: It's Alive! on February 16, 2023, 04:31:50 PM
There’s a few libraries on GITHub that can read write .xlsx files, basically .xlsx is a zip file containing .xml.
Some are really fast too! I use OpenXLSX in C++ and it’s crazy fast at over a million cells per second.

ODBC is around 1200 queries per second
 
If your just transferring ranges from excel to SQLite, that would be a really fast solution.

Might not work if your doing complex SQL queries on excel data. though, I’d bet you could create an in memory SQLite db, move an entire xlsx sheet into a table, then perform the query, and it would still be faster than COM.

As a quick test, my ACEODBC driver was updated 1/17/2023 and it still works, I don’t know the relationship with cpprestsdk.dll, part of the puzzle is missing





 
Title: Re: Database and Worsheets Communication
Post by: tfieker on February 28, 2023, 12:45:35 PM
Yes, we do complex SQL queries and update the worksheet too.
Transfer for a SQLite database in memory is a good solution for queries, but we need to update the Excel.
This is the reason we use ACE.OLEDB and ADO.NET...so far I haven't found anything similar.

Title: Re: Database and Worsheets Communication
Post by: tfieker on February 28, 2023, 02:21:47 PM
The only alternative I found is: https://www.cdata.com/drivers/excel/ado/
But it is very expensive....
Title: Re: Database and Worsheets Communication
Post by: It's Alive! on February 28, 2023, 03:38:47 PM
So ODBC didn't work?
I would be interested on your findings with ODBC so I don't run into the same issue