Way to interface with Dataset through ODBC or OLE?

[googlegroups.dsfm]

Hi, all,

I'm somewhat stumped on a sticky problem - I've got a Dataset that I'd
like to be able to send through an ODBC or OLE connection, so a client
accessing it on the other end would be completely abstracted from any
of my code and see it just the way they'd see output from a "regular"
database.

I've already set up methods to 'query' my strongly-typed dataset, and
I've exposed it as a web service, which is just fine for .NET
implementations, but I want to take it to the next level and fully
abstract it somehow. Does anyone know of a way to do this that doesn't
involve a COTS SDK like OpenAccess? Some example code or a kick in the
right direction would be most appreciated.

Thanks in advance!

 

[Nicholas Paldino [.NET/C# MVP]]

Well, providing the data through ODBC or OLEDB would mean that you have
to create a driver for ODBC or a provider for OLEDB, both of which are not
trivial tasks. It can be done, but you are probably better off doing this
in unmanaged code if you want to create an ODBC provider (this requires you
to create a dll which will export functions, and there are general issues
about resolving which version of the framework to use if other .NET code is
used in-process in whatever is using your driver).

 

[Guest]

Writing an ODBC driver is not trivial. You basically have to create a
standard Win32 DLL (not-managed) that exposes an interface conforming to the
ODBC standard. Your DLL can call in to managed code for the bulk of the
processing, but you'd still have to deal with formatting responses correctly.
I looked in to it once for work and was able to build a very basic proof of
concept driver to do a simple read of all records on a table.

If I can track down the code to my POC I'll post it on my website and
provide a link. I had changed source control since then, though, and didn't
bring it across. I'll need to dust off some backups.

The resources I used were the Open Source ODBC drivers for PostgreSQL & SQL
Lite as well as the ODBC API documentation in the MSDN library.

Depending on how databases are going to be accessed you may not need to do
this. You could build a set of classes that inherit from DbConnection,
DbCommand, DbDataReader, etc. that provides access. This would allow .Net
coders to access your database via standard .Net development. The downside is
it would only work for .Net development. Other tools that only talk to plain
ODBC wouldn't work.

 

[googlegroups.dsfm]

Nicholas,

I was afraid it would come to that. I'm somewhat familiar with c++,
but unfortunately not to the point that I could just whip up a custom
driver.

Thanks anyway!

 

[googlegroups.dsfm]

Writing an ODBC driver is not trivial. You basically have to create a
standard Win32 DLL (not-managed) that exposes an interface conforming to the
ODBC standard. Your DLL can call in to managed code for the bulk of the
processing, but you'd still have to deal with formatting responses correctly.
I looked in to it once for work and was able to build a very basic proof of
concept driver to do a simple read of all records on a table.

If I can track down the code to my POC I'll post it on my website and
provide a link. I had changed source control since then, though, and didn't
bring it across. I'll need to dust off some backups.

The resources I used were the Open Source ODBC drivers for PostgreSQL & SQL
Lite as well as the ODBC API documentation in the MSDN library.

Depending on how databases are going to be accessed you may not need to do
this. You could build a set of classes that inherit from DbConnection,
DbCommand, DbDataReader, etc. that provides access. This would allow .Net
coders to access your database via standard .Net development. The downside is
it would only work for .Net development. Other tools that only talk to plain
ODBC wouldn't work.

Andrew,

Unfortunately, I wanted to avoid a .NET dependency - if I was going to
go to all the trouble to create classes inheriting from
System.Data.Db*, I'd just as soon spit out the resultant Dataset and
have the app deal with it clientside.

If you can find the POC, I'd be grateful. I'd at least like to give it
a shot if there's at least a basic framework to go by - and the real
benefit is that it would only need to accept SELECT statements, as
this is a one-way connection.

Thanks!