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

While both can be called in pretty much the same way, the way resultsets and
returned parameters and multiple rowsets are handled is very different.

--
____________________________________
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)
-----------------------------------------------------------------------------------------------------------------------

"Milosz Skalecki [MCAD]" <mily242@xxxxxxxxxxxxxxxxx> wrote in message
news:EA07F097-A5AC-4BF6-84B5-EE8AE1CE76F5@xxxxxxxxxxxxxxxx
Howdy,

I didn't mean using the same stored procedures for different RDBMSes. I
meant you can use OleDb to call SPs (which is pretty much the same ADO.NET
code), but you would have to implement them differently for each RDBMS. In
addition, real life applications are much more advanced than simple
SELECT,
INSERT, UPDATE statements, and that's why i mentioned you would end up
with
different versions of queries for different RDBMS. I definitely agree with
William it's better to use abstraction in the code such as providers and i
mention about in my previous post.

Best regards
--
Milosz


"Gustaf" wrote:

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

  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Enterprise Library - DataAccessApplicationBlock - MySql/DB2
    ... The fact that you set parameters differently for different providers isn't ... Oracle or Microsoft told me "We don't like the way you did that", ... ISDATE doesn't produce the same results in both Sql Server ... and Oracle both have a ton of specific features that others don't have. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7
    ... For parsing a string to a date/time, the 103 format doesn't limits the user ... > which is used as the row source for the results form). ... > other solutions assuming I have to use SQL Server 7? ... > I don't understand how I can use parameterized stored procedures to solve ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Limiting views on data in a table
    ... returns the Windows login the user uses to connect to SQL Server. ... > The way I was implementing this was to use Windows Auth with a Windows ... > covered by which salespeople, ... > with it via the stored procedures. ...
    (microsoft.public.sqlserver.security)