Query from multiple tables

Question

We started working with the "ADO.NET Entity Data Model" Template and we like it a lot.
We are having some trouble with the queries though.

How do we do a query from multiple tables so it would return a single result and then show the result in a DataGrid?
We're working in C# and Windows Forms.

Answer 1

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?

Answer 2

 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


 

Answer 3

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

Answer 4

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.

Answer 5

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?

Answer 6

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

Answer 7

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..

Answer 8

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());
        }

Answer 9

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.

Answer 10

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?

Answer 11

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?

Answer 12

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();

Answer 13

Is this possible only with a connection string?