Re: .NET data provider or OLEDB provider?

Thanks again for your help Bob, you have given me a couple of avenues to
investigate - I especially like the OLEDB integration with SQL Server idea,
should keep me busy for a while!

--
Much appreciated
Mark

"Bob Beauchemin" <no_bobb_spam@xxxxxxxxxxxxx> wrote in message
news:%23orKZHm3FHA.3844@xxxxxxxxxxxxxxxxxxxxxxx
>I don't see where you see a built-in query engine. There isn't one that I'm
>aware of. Please quote me a sentence on that webpage that mentions it. In
>the OLE DB architecture it is *possible* to write a Query Engine as a
>service provider (as appears in the diagram), but there's not one built-in.
>OLE DB providers don't have to support SQL (or any query language) however,
>the Command object (cotype) is optional. If you support tables but not
>queries, you can choose to implement only IOpenRowset interface on the
>Session cotype. Then your "command" to get a table is simply "tablename".
>
> There are third party tools that provider SQL parsing IIRC, however.
>
> The closest OLE DB comes to this is that, if you host your provider as a
> SQL Server linked server, SQL Server can handle the queries. See the
> "Problem: getting an index provider to work..." for one of the limitations
> of this approach.
>
> There is the OLE DB Simple Provider architecture, but folks outgrow that
> quickly, its fairly limited in scope.
>
> If you send me direct mail (email address deducable from address in this
> message) perhaps we could discuss it.
>
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
>
> "Mark" <swozz_@xxxxxxxxxxx> wrote in message
> news:Oicuibl3FHA.128@xxxxxxxxxxxxxxxxxxxxxxx
>> Thanks for your reply Bob,
>>
>> I have had a preliminary look at both the .NET provider and OLEDB
>> provider. I agree that the .NET provider would be much simpler, but it
>> looks like I would have to write a query processing engine for the
>> Command object. I have noticed that with OLEDB there is already a Query
>> Engine that can consume the provider
>> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbprovmicrosoft_ole_db_providers_overview.asp)
>>
>> If this is the case, then in theory at least I would only have to write
>> (or rather port) the data access methods and then plug them into the
>> existing OLEDB query engine. With .NET, I would have to write the a Query
>> Engine that understands at least a subset of ANSI-92!
>>
>> All client side code will be written using .NET anyway, but it needs to
>> be SQL compatible so we can easily port the database to SQL Server at
>> sometime in the future.
>>
>> I am finding it difficult to obtain sufficient information at the moment,
>> so any light you can shed on this would be much appreciated.
>>
>> --
>> Best regards
>> Mark
>>
>>
>> "Bob Beauchemin" <no_bobb_spam@xxxxxxxxxxxxx> wrote in message
>> news:%23$$o57k3FHA.3588@xxxxxxxxxxxxxxxxxxxxxxx
>>> Hi Mark,
>>>
>>> Having taught an OLE DB provider class and written material on ADO.NET
>>> data providers.... (and written both)....
>>>
>>> An OLE DB provider will be infinately more difficult to write. And to
>>> get right. You can use it in a few more places (third party tools, SQL
>>> Server linked servers, etc). There's an OleDb .NET "bridge" provider,
>>> but it only supports a subset of OLE DB interfaces/properties.
>>>
>>> A .NET data provider would be *much* easier. The model is simpler and
>>> there are minimal (no) reference counting issues. I'd start with a .NET
>>> data provider. Only problem (which could be bigger for ISAM style
>>> database) is that the .NET model doesn't support scrollable, updateable
>>> cursors. .NET provider model is written after the client-server/three
>>> tier model where these are evil, so cursor support is left out. You
>>> could write your own extension classes, though.
>>>
>>> You could also write an ODBC driver, but the bridges (OLE DB provider
>>> for ODBC and .NET Odbc "bridge" provider) are pretty flakey and hard for
>>> folks (your clients) to work with. Some MS and third-party tools have
>>> dropped support for accessing ODBC drivers through the bridges.
>>>
>>> If you decide that Java is your only programming language/API
>>> environment, JDBC driver is the way to go.
>>>
>>> Oh, and there are third-party "your code goes here" toolkits/frameworks
>>> for building OLE DB/ODBC/JDBC. And ATL provider templates for OLE DB. I
>>> don't think there are toolkits for bulding ADO.NET data providers yet,
>>> maybe because its lots easier?
>>>
>>> Hope this helps,
>>> Bob Beauchemin
>>> http://www.SQLskills.com/blogs/bobb
>>>
>>>
>>> "Mark" <swozz_@xxxxxxxxxxx> wrote in message
>>> news:uTieN$j3FHA.3844@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Hi,
>>>>
>>>> We have a propriety database format currently in use by a DOS program.
>>>> There are some 1500 big customers using this system! The application
>>>> that sits on this database is complex and a rewrite would be a 2 year
>>>> project with massive upgrade/training issues at the end of it. This is
>>>> why a rewrite hasn't been commisioned - it's expensive and risky for a
>>>> small company to undertake even if it's 10 years overdue!
>>>>
>>>> To allow for a smoother less risky transition, we intend to rewrite the
>>>> front end using a combination of Windows/Web based technology (.NET)
>>>> whilst still utilizing the existing database. This will allow us the
>>>> gradually replace the front end. Once this is complete, we can port the
>>>> DOS database to something like SQL Server with relatively little
>>>> modifications required for the newly written front end.
>>>>
>>>> This means that we need SQL access to the existing DOS based ISAM style
>>>> data so that we can begin working on the new front end.
>>>>
>>>> The existing database was originally based on Borland Database Toolbox
>>>> (1985?) but has been significantly modified to work in a multiuser
>>>> environment.
>>>>
>>>> The way I see it, we could either write a .NET data provider or an
>>>> OLEDB provider.
>>>>
>>>> If we write a .NET data provider then we will also have to write a
>>>> query engine to support SQL type access. My question is that if we
>>>> decide to write an OLEDB provider to access the ISAM database, is there
>>>> already a Query Engine service that can take advantage of this provider
>>>> to allow us to access the data using SQL?
>>>>
>>>> Any help much appreciated...
>>>>
>>>> --
>>>> Best regards
>>>> Mark
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages