How to connect a Oracle DB?

Question

Hello,
I searched the forum and possible articles. I read all articles but actually I understand nothing. I still dont know how to connect.

1. Is this possible to connect a oracle db, without installing a program? (Like Oracle Data Provider for .NET)

But, I installed "ODAC 11.2 Release 3 and Oracle Developer Tools for Visual Studio (11.2.0.2.1)" from Oracle web side. Then accordinf this article --> http://www.c-sharpcorner.com/UploadFile/nipuntomar/4749/

I want to add;
using Oracle.DataAccess.Client;
line to my code but VS is not recognize this line.

Actually I couldnt find an article that explaining step by step.

Simply, How can I do that? Is this possible to connect only a single connection string?

John Penn · Answer

Sorry it's "Oracle.DataAccess" not "Oracle.DataAccess.Client".

When you installed the Oracle Developer Tools, it should have created a folder on your hard drive with all of the necessary files. Buried within that folder is a subfolder called "odp.net". Within that folder is a "bin" folder, and in bin there are 2 folders "2.x" and "4". Each one of those contains a file "Oracle.DataAccess.dll". You need to add a reference to the one that corresponds to the version of .Net you're using (if you're using 4.0 then reference the dll in the "4" folder, otherwise use the one in the "2.x" folder).

Do you know how to add a reference to a project in VS?

Suthish Nair · Answer

In future if you face difficulty in finding the connection string then you can follow this article.. Find Database Connection and Generate Connection String using Visual Studio

yokzu · Answer

Thanks John. I am using Microsoft's provider and seems works well.

John Penn · Answer

It's the only way I know of to connect to Oracle currently. Microsoft does supply their own Oracle provider (System.Data.OracleClient) but it is being deprecated and will not be supported by them.

yokzu · Answer

Thanks for your reply John.

I just download and and instaled from this link ---> http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

I think you have mentioning this link ---> http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Now I am donwnloading.

Yes I know how to reference .dll files to project.

After I'll install "ODAC 11.2 Release 3" I'll let you know.

But I am curious about this, is this the only way? Isnt there any other way to connect to DB without installing programs from Oracle?

yokzu · Answer

Mayur; Where should I write Server IP, Server Name in the connection string? Your connection string has only "user" and "password" informations?

yokzu · Answer

John ; I cant add to reference "Oracle.DataAccess.Client" to project. Because VS is not recognize this line and getting error. Im already asking how may I add this? using Oracle.DataAccess.Client ---> getting error..

Mayur Gujrathi · Answer

Add name space of system.data.oledb

This will test the connection of oracle database
private void button1_Click(object sender, EventArgs e)
        {
            oracon = new OleDbConnection("Provider=Msdaora;User Id=Scott;Password=tiger");
            oracon.Open();
            MessageBox.Show(oracon.State.ToString());
            oracon.Close();
            MessageBox.Show(oracon.State.ToString());
        }

this will test the connection of sql server database
        private void button2_Click(object sender, EventArgs e)
        {
            sqlcon = new OleDbConnection();
            sqlcon.ConnectionString = "Provider=SQLOLEDB;User Id=citrus_usr;Password=surtic;Database=DEMAT;Data Source=server";
            sqlcon.Open();
            MessageBox.Show(sqlcon.State.ToString());
            sqlcon.Close();
            MessageBox.Show(sqlcon.State.ToString());
        }

John Penn · Answer

Did you add a reference to Oracle.DataAccess to your project?

You don't need to specify anything in tnsnames.ora, instead you can place all required parameters on the connection string like this:

<add name="OracleConnectionString" connectionString="Data Source = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP_ADDRESS_OR_HOSTNAME_HERE)(PORT=PORT_NUMBER_HERE))(CONNECT_DATA=(SID=YOUR_SID_HERE)));User Id=YOUR_USER_ID_HERE;Password=YOUR_PASSWORD_HERE;"/>

EDIT: note that not all of the parameters I specified above may apply to your configuration, but hopefully my example is enough to get you going in the right direction.

yokzu · Answer

I have tnsnames.ora file to connect with TOAD.
-----
NRM_PROD=
(DESCRIPTION=
(LOAD_BALANCE=yes)
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=99.99.27.30)
(PORT=1521)
)
(ADDRESS=
(PROTOCOL=TCP)
(HOST=99.99.27.31)
(PORT=1521)
)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=TELLRA.company.com)
)
)
--------

How should I write these informations into connection string?

yokzu · Answer

Muralidharan thanks for answer. But I still couldnt find what should I have download? And what is the links? Is the connection strings works without downloading a program?

In the paper, guys said just "Instead of using OLEDB Provider, use the Oracle Provider. ".. I know I should use Oracle Provider, how?? He didnt write anything and it isnt help me.

Please give me a specific link or string?

Muralidharan Deenathayalan · Answer

You can connect oracle db by using "OLEDB".

http://webdesign.ittoolbox.com/groups/technical-functional/asp-dotnet-l/how-to-connect-oracle-database-in-aspnet-2005-c-using-oledb-provider-1705513

string strCon = string.Empty;

string strCmdText = string.Empty;

DataSet ObjDS = new DataSet();

// strCon = "Provider=SQLOLEDB;server=MAHESH\\MAHESH;database=E CF;uid=sa;pwd=sa;";

strCon = "Data Source=KMS;User Id=system;Password=tiger;";

OracleConnection ObjCon = new OracleConnection(strCon);

ObjCon.Open();

strCmdText = "select top 20 Users_Salutation,Users_FirstName,Users_MiddleName, Users_LastName from Users";

OracleCommand ObjSQLCom = new OracleCommand(strCmdText, ObjCon);

OracleDataAdapter ObjSQLDA = new OracleDataAdapter(ObjSQLCom);

ObjSQLDA.Fill(ObjDS);

ObjCon.Close();

yokzu · Answer

Is this possible only with a connection string?