Re: Can you use the OleDb classes for SQL Server?

No. Oracle uses different syntax, data flow and other methodologies in their
SPs. They are not compatible with SQL Server. I would write a data layer
that uses the OracleClient .NET data provider to access Oracle.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Gustaf" <gustafl@xxxxxxxxxx> wrote in message
news:umtbSDyvHHA.412@xxxxxxxxxxxxxxxxxxxxxxx
Milosz Skalecki [MCAD] wrote:

Yes you can. OleDb is the old style way of abstracting database
providers. The best way of doing abstraction is using stored procedures,
as you can delegate sql queries to database, otherwise you end up with
different version of queries for different RDBMSes.

I agree that it would be neat to just call a stored procedure for the
INSERT method I need. But a stored procedure I write for SQL Server can't
be moved directly to Oracle, can it? I know nothing about stored
procedures in Oracle. So I think I need to put the INSERT statment in the
C# code, or possibly in a config file.

In addition, if you use .NET 2.0, it offers another way od specialization
for different RDBMS via providers and factories:
http://www.simple-talk.com/dotnet/.net-framework/ado.net-2.0-factory-classes/

That's a good article. I was able to make use of the System.Data.SqlClient
provider, but the OleDb provider did not work. When opening the
connection, I get the useless error (type OleDbException):

No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).

The app.config file currently looks like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="provider" value="System.Data.OleDb"/>
<add key="connectionString" value="Provider=SQLOLEDB;Data
Source=.;Initial
Catalog=MyCompany;Integrated Security=true;User Instance=True"/>
</appSettings>
</configuration>

The solution would still be very useful, if I can just change
"System.Data.SqlClient" to "System.Data.OracleClient" at deployment.

Gustaf


.

Relevant Pages

  • Re: Oracle Linked Servers
    ... To use distributed transactions with the Oracle OLE DB Provider in your ... I created a blank database in sql server ...
    (microsoft.public.sqlserver.server)
  • Re: Password is not saved for Oracle connections
    ... table in SQL Server 2005. ... The package runs just fine from the BID. ... It gives a "ORA-01017: invalid username/password; logon denied" error when I use the Microsoft OLE DB Provider for Oracle, and a "ORA-10005: null password; logon denied" error when I use the Oracle provider for OLE. ...
    (microsoft.public.sqlserver.dts)
  • RE: parameter name problem
    ... stored procedure positionally. ... if you have a proc with two parameters you can call it as: ... > sql server and in oracle it is a simple string, ...
    (microsoft.public.sqlserver.programming)
  • Error 80040e14
    ... I have a simple Stored Procedure and all it does is it adds 4 ... Microsoft OLE DB Provider for SQL Server ...
    (microsoft.public.sqlserver.programming)
  • Re: Custom Procedure Replication Error with Oracle Subscriber
    ... The reason we were drawn to the custom procedure is that we have long column ... We are trying to handle the mapping in the custom stored procedure in Oracle ... since there is no way to do this in SQL Server that we could come up with. ...
    (microsoft.public.sqlserver.replication)
Loading