Subscribe to this thread
Home - General / All posts - Custom ADO.NET Data Provider for Manifold
rk
575 post(s)
#28-Mar-19 16:05

I try to create a 'dummy' ADO.NET Data Provider that Manifold 9 can connect to.

(Someday I want to create provider for a particular file format)

I took the sample implementation [1] of the interfaces, compiled into dll and put the dll beside manifold.exe

M9 recognizes it as .NET provider. Yay!

But it does not go further.

2019-03-28 16:40:40 *** (root)::[DummyConnection] Cannot connect to database.

2019-03-28 16:40:40 *** (root)::[DummyConnection] Cannot connect to database.

Any suggestions on how to debug it?

What fails? What calls Manifold makes?

[1] https://docs.microsoft.com/en-us/previous-versions/aa720697(v=vs.71)

Attachments:
adonet.jpg

rk
575 post(s)
#30-Mar-19 16:03

OK, I now know how to debug the dll with Visual Studio.

However, so far I have seen M9 call only the DummyConnection() constructor.

adamw


10,281 post(s)
#01-Apr-19 14:43

After creating an instance of the connection object, we cast the result to System.Data.Common.DbConnection, then set the ConnectionString property, then call Open, then call CreateCommand, then run commands, iterate through returned data, etc.

I guess you might not be getting past the constructor because your class implements System.Data.IDbConnection, but does not actually inherit from System.Data.Common.DbConnection as per above, so it all stops right after the cast. I will check why exactly we have this slight desync between what we list as acceptable connection objects and what we can actually use, it looks like we have to either stop listing objects that do not derive from the abstract class or to accept objects that merely implement the interface.

adamw


10,281 post(s)
#01-Apr-19 14:58

A follow up to the above: we do need the object for a custom connection to inherit from System.Data.Common.DbConnection and not merely implement the System.Data.IDbConnection interface, because the interface does not have the GetSchema function and we do need it.

We'll strengthen the code that populates the list of available custom connection objects in the UI to check accordingly.

rk
575 post(s)
#02-Apr-19 07:04

Thanks. I used Runtime Flow VS extension and saw the invalid cast exception. Comparing my code with npgsql.dll source I suspected cast to DbConnection. I can continue tomorrow.

rk
575 post(s)
#24-Apr-19 15:30

It has been a puzzle. Sometimes fun, sometimes frustrating. I have been solving this gradually when I have had time.

For a while I was stuck at exactly the same place where Npgsql also fails (tried with v2.2.7 and v4.0.7).

NpgsqlConnection.GetSchema("DataSourceInformation", { }); //OK

NpgsqlConnection.GetSchema("Tables", { }); //OK

NpgsqlConnection.GetSchema("Views", { });  //OK

NpgsqlConnection.GetSchema("Columns", { """myschema""mytable"});  //Invalid value (null)

My diagnosis: M9 does not recognise Npgsql's response to GetSchema("Columns", ...

At first I followed https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-schema-collections

My errors were the same as above.

Then I modified the GetSchema("Columns") response to mirror GetSchemaTable (https://docs.microsoft.com/en-us/dotnet/api/system.data.datatablereader.getschematable?view=netframework-4.7.2)

That seemes to work somewhat. Now M9 asks for Indexes.

DummyConnection.GetSchema("DataSourceInformation", { }); //OK

DummyConnection.GetSchema("Tables", { }); //OK 

DummyConnection.GetSchema("Views", { });  //OK

DummyConnection.GetSchema("Columns", { """DummySchema""DummyTable"});  //OK

DummyConnection.GetSchema("Indexes", { """DummySchema""DummyTable"});  //Invalid value (null), if not empty

I can return empty table of indexes. Then I do not get any error, but schema and table windows remain empty.

Then these commands get created and called ExecuteDbDataReader :

SELECT COUNT(*) FROM "DummySchema"."DummyTable"

SELECT 0 FROM "DummySchema"."DummyTable"

I think, I correctly fake these results.

Then I expect, but do not see a command:

SELECT * FROM "DummySchema"."DummyTable"

I'm out of ideas how to proceed. Should I even bother (ADO.NET is dead)? I wanted to create an "adapter" that would work with M9 Viewer also. Now I feel that it may be pointless and if I really need it, then an "importer" that reads a file and creates M9 tables directly is more sane approach.

Attachments:
DummyTable.PNG
emptySchema.PNG
invalid_value_null.PNG

adamw


10,281 post(s)
#24-Apr-19 16:30

ADO.NET is a little dead, yes. Still, since we allow connecting to generic ADO.NET data sources, perhaps it makes sense for us to create an example data source and publish its sources - then you would be able to use that as a reference.

For long term use, we are looking at creating the infrastructure for pluggable dataports - we'd publish the interfaces (similar to Database / Table / Sequence in the object model, possibly with some extensions) and you'd write a DLL with classes implementing those interfaces, then we'd load your DLL and call your classes. That would work better than ADO.NET.

What specifically are you after - enabling access to data in the format that we do not support natively? With a lot of files in that format already? Scripts that read data into MAP and write data back into the format might not be a bad first step, although not as pleasant as just working with data pretending it is a database.

rk
575 post(s)
#25-Apr-19 20:38

What specifically are you after - enabling access to data in the format that we do not support natively?

Yes, that's the idea. There is no immediate need. But I've always wanted have something like this.

Pluggable dataports would be exactly that. I used to work with sensors like this and other kinds of files that had to be converted to CSV before ingesting.

Soon there might be a need and I do not know the format yet.

rk
575 post(s)
#21-May-19 13:56

I was reading this and thought that perhaps ADO.NET is not so dead. It is somewhat standard. I do not know what would be the replacement. ODBC?

An example data source would be very helpful.

https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.