ODP.NET question

[Mike G.]

I am developing an application that connects to multiple Oracle databases.

I would like to use ODP.NET. My question is how do I use a DSN-less
connection string.

The reason I ask is because in the past we have had terrible issues
deploying applications where the user has to edit the file called
"tnsnames.ora". Our help desks got flooded with calls(over 300 calls
logged) because users didn't set up the connection correctly in the
tnsnames.ora file.

We currently have a few old programs that use Oracle7 that we supply the
connection string in the program so the user does not need to do anything.

It seems the ODP.NET needs a data source name, and won't accept a connection
string that identifes the data source.

Is it possible to use ODP.NET and specify the data source name in the
program. Or is is possible to have ODP.NET set up the datasource to use.

Any help will be much appreciated.

Thanks,

-Mike

 

[David Browne]

I am developing an application that connects to multiple Oracle databases.

I would like to use ODP.NET. My question is how do I use a DSN-less
connection string.

The reason I ask is because in the past we have had terrible issues
deploying applications where the user has to edit the file called
"tnsnames.ora". Our help desks got flooded with calls(over 300 calls
logged) because users didn't set up the connection correctly in the
tnsnames.ora file.

We currently have a few old programs that use Oracle7 that we supply the
connection string in the program so the user does not need to do anything.

It seems the ODP.NET needs a data source name, and won't accept a connection
string that identifes the data source.

Is it possible to use ODP.NET and specify the data source name in the
program. Or is is possible to have ODP.NET set up the datasource to use.

Any help will be much appreciated.

An entry in your tnsnames.ora is an alias for all the stuff after the "=".
You should be able to use that to create a "DSN-less" connection to oracle.

If you have:

MyOracle =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orasvc)
)
)

then you should be able to connect like

Dim connect As String = "User Id=scott;Password=tiger;Data
Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =
dbserver)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orasvc) ) );"
Dim con As New Oracle.DataAccess.Client.OracleConnection(connect)
con.Open()
Dim cmd As New Oracle.DataAccess.Client.OracleCommand("select count(*)
from user_tables", con)
Dim i As Integer = cmd.ExecuteScalar

instead of


Dim connect As String = "User Id=scott;Password=tiger;Data
Source=MyOracle;"
Dim con As New Oracle.DataAccess.Client.OracleConnection(connect)
con.Open()
Dim cmd As New Oracle.DataAccess.Client.OracleCommand("select count(*)
from user_tables", con)
Dim i As Integer = cmd.ExecuteScalar

David

 

[Carlos J. Quintero]

The method of David works fine, I have tested it.

On the other hand, there is a commercial .Net Data Provider from DataDirect
that for the first time avoids the use of the Oracle client libraries, the
OCI layer, (including the TNSNAMES.ORA file), using direct TCP/IP
connections with the wire protocol of Oracle. I have not used it, but client
libraries are other source of headaches for support...

Carlos Quintero

 

[Mike G.]

David, Thanks.

I'm just a little slow here....

I was forgetting to put the "Data Source=" string in the command line.

Everything is working correctly now.

-Mike

 

[Niall Litchfield]

I am developing an application that connects to multiple Oracle databases.

I would like to use ODP.NET. My question is how do I use a DSN-less
connection string.

The reason I ask is because in the past we have had terrible issues
deploying applications where the user has to edit the file called
"tnsnames.ora". Our help desks got flooded with calls(over 300 calls
logged) because users didn't set up the connection correctly in the
tnsnames.ora file.

We currently have a few old programs that use Oracle7 that we supply the
connection string in the program so the user does not need to do anything.

It seems the ODP.NET needs a data source name, and won't accept a connection
string that identifes the data source.

Is it possible to use ODP.NET and specify the data source name in the
program. Or is is possible to have ODP.NET set up the datasource to use.

As well as the suggestions you have made, you might consider using Oracle
Names or the new LDAP naming method rather than relying on tnsnames. Then
you would just configure the names centrally.