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: 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_SalutationUsers_FirstNameUsers_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?