is it possible to access an Oracle db in .net

[Amueerie]

this may seem a trivial question (as .net may have been solely developed for
this purpose),
but i'm not certain whether this is possible,

i.e. is it possible to access an Oracle DB in .NET (and is it the same as
accessing any other db - except for the connection string & object).
What is mean is, can i use the same select statements, update statements
with the orable database that i used with MSDE & SQL database?

thank u all

 

[rs]

http://msdn.microsoft.com/library/d...ataOracleClientOracleConnectionClassTopic.asp

 

[Bob]

Hi,
Yes,
Change your declaration of the data access variables to the oracle flavour.
Following snippet shows using a compiler directive to control which database
this routine is looking at.
Oracle have OMWB (Oracle migration workbench) which will do a passable job
of converting an SQL Server database to an Oracle one.
The caveat being that you will have to massage the stored procedures to get
them to work.
If you go this way then suggest a copy of TOAD as it takes away the pain
when working with Oracle schemas.
Note: that you need a a cursor output parameter to get data back with an
Oracle SPROC. ( You'll see this below)
HTH
Bob
#If Not Oracle Then

Dim cmdConn As New SqlClient.SqlCommand

Dim cmdSource As SqlClient.SqlDataReader

Try

cmdConn = New SqlClient.SqlCommand(strSQL, New
SqlClient.SqlConnection(mstrConn))

strSQL = "exec proc_GetContact @contact_id"

cmdConn.Parameters.Add(New SqlParameter("@contact_id", SqlDbType.Int))

cmdConn.Parameters("@contact_id").Value = intContactID



cmdConn.CommandText = strSQL

cmdConn.Connection.Open()

cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)

'End If

If cmdSource.Read Then

C.ID = cmdSource.GetSqlInt32(0).Value

If Not cmdSource.GetSqlString(1).IsNull Then C.Firstname =
cmdSource.GetSqlString(1).Value

If Not cmdSource.GetSqlString(2).IsNull Then C.Surname =
cmdSource.GetSqlString(2).Value

If Not cmdSource.GetSqlString(3).IsNull Then C.EmailAddress =
cmdSource.GetSqlString(3).Value

If Not cmdSource.GetSqlString(4).IsNull Then C.SMSAddress =
cmdSource.GetSqlString(4).Value

If Not cmdSource.GetSqlString(5).IsNull Then C.Phone =
cmdSource.GetSqlString(5).Value

If Not cmdSource.GetSqlString(6).IsNull Then C.FAX =
cmdSource.GetSqlString(6).Value

Return True

End If

Catch obja As System.Exception

WriteErrorLog("DT.GetContactList Error " & obja.Message)

Finally

cmdSource.Close()

cmdConn.Dispose()

End Try

#Else

Dim cmdConn As OracleClient.OracleCommand

Dim cmdSource As OracleClient.OracleDataReader

Try

strSQL = "proc_GetContact"

cmdConn = New OracleClient.OracleCommand(strSQL, New
OracleClient.OracleConnection(mstrConn))

cmdConn.Parameters.Add(New OracleParameter("contact_id", OracleType.Number,
0, ParameterDirection.Input, _

True, 0, 0, "", DataRowVersion.Default, mydbNull))

cmdConn.Parameters.Add(New OracleClient.OracleParameter("o_cursor",
OracleClient.OracleType.Cursor, _

2000, ParameterDirection.Output, True, 0, 0, "", DataRowVersion.Default,
mydbNull))

cmdConn.CommandType = CommandType.StoredProcedure

cmdConn.Parameters("contact_id").Value = intContactID

cmdConn.Connection.Open()

cmdSource = cmdConn.ExecuteReader(CommandBehavior.CloseConnection)

'End If

If cmdSource.Read Then

C.ID = CInt(cmdSource.GetOracleNumber(0).Value)

If Not cmdSource.GetOracleString(1).IsNull Then C.Firstname =
cmdSource.GetOracleString(1).Value

If Not cmdSource.GetOracleString(2).IsNull Then C.Surname =
cmdSource.GetOracleString(2).Value

If Not cmdSource.GetOracleString(3).IsNull Then C.EmailAddress =
cmdSource.GetOracleString(3).Value

If Not cmdSource.GetOracleString(4).IsNull Then C.SMSAddress =
cmdSource.GetOracleString(4).Value

If Not cmdSource.GetOracleString(5).IsNull Then C.Phone =
cmdSource.GetOracleString(5).Value

If Not cmdSource.GetOracleString(6).IsNull Then C.FAX =
cmdSource.GetOracleString(6).Value

Return True

End If

Catch obja As System.Exception

WriteErrorLog("DT.GetContactList Error " & obja.Message)

Finally

cmdSource.Dispose()

If cmdConn.Connection.State <> ConnectionState.Closed Then

cmdConn.Connection.Close()

End If

cmdConn.Dispose()

End Try

#End If

 

[Cor Ligthert]

Amueerie,

You can use OleDB for almost everything, however it is not clever.
There are 3 main base classes which acts (almost) the same
SQLClient for SQL Server
OracleClient for Oracle
OleDb for the rest

In my opinion is it better to create seperate classes for the database
handling for those or integrate in your classes for that the different
types.

Just my thought,

Cor

 

[Herfried K. Wagner [MVP]]

i.e. is it possible to access an Oracle DB in .NET (and is it the same as
accessing any other db - except for the connection string & object).
What is mean is, can i use the same select statements, update statements
with the orable database that i used with MSDE & SQL database?

..NET Framework Developer's Guide -- .NET Framework Data Providers
<URL:http://msdn.microsoft.com/library/en-us/cpguide/html/cpconADONETProviders.asp>

<URL:http://support.microsoft.com/search/?catalog=LCID=1033&query=Oracle+.NET>

<URL:http://www.connectionstrings.com/>
-> "Oracle"

 

[Carlos J. Quintero [.NET MVP]]

i.e. is it possible to access an Oracle DB in .NET (and is it the same as

accessing any other db - except for the connection string & object).

The objects are not exactly the same, they are OraXXX objects. The only way
to use portable objects is to use the underlying IdbXXX interfaces that
implement the objects.

What is mean is, can i use the same select statements, update statements
with the orable database that i used with MSDE & SQL database?

Surely not. MSDE and SQL Server uses Transact SQL while Oracle uses PL/SQL.
That means that SQL statements can vary slightly. The industry has done 2
attempts to solve this problem:

- Make vendors to be compliant with ANSI SQL. The conformance level is
increasingly better, but you have to verify your SQL statements.
- Microsoft provided neutral ODBC syntax which at least ODBC drivers and
OLEDB providers support, not sure about .NET Data Providers.

--

Carlos J. Quintero

MZ-Tools 4.0: Productivity add-ins for Visual Studio .NET
You can code, design and document much faster.
http://www.mztools.com