Going through my first attempt ever to connect to a Oracle database with a .Net application.
Using Visual Studio 2012, I have downloaded and installed the latest version of odp.net, actually both the 32 and 64bit versions.
Application is targeting the 4.5 Framework, Latest version of Entity Framework installed (but not important I think at this point)
Added a reference in my project to the Oracle Data Provider ( tried with both 2.0 and 4.0 )
Created a connection string with EZ Connect, stored in web.config file.
I have tried 2 different projects to use EF and one without. I've looked at several tutorials and can't see what I'm missing or doing wrong.
Trying to create a edmx or using a datasource adapter brings up the connection wizard or prompts to use the existing one, but no matter what I try, there are no tables, views or procedures listed.
From Server Explorer, I can open the Data Connections and verify that the connection to the Oracle database is Open. During the Connection Wizard, there were no errors, and all the databases available were listed. Yet, connecting to and opening the connection to this specific database or anyone for that matter no tables, views or procedures are listed.
I have little to no knowledge about Oracle, I've been thrown to the wolves. The Oracle dba for some reason is unable to help me solve this. He is able to login with my account info and view the data, but gets the same thing I get with JAVA. Clue?
Any help is appreciated.
I should be setup as a user that has access to read data, but not to create or modify etc.
As I understand, I have a user account and assigned Roles, but within VS, if I browse the Privileges for Roles, the Roles show up, but nothing granted. I do not show up in list when selecting the 'User' option vs 'Roles'. Again, not real knowledgeable about what I'm looking at here, but seems like something on the database side is not setup for me quite right.
normaly a user who get read access to data, the dba setup a second user and grant this rights. So in this case your user did not own objects. You must then reference this objects with <user>.<object> name.
But i belive you are not sure about this point, so maybe you should check this with your dba, or try select * from all_objects (in case you can excute a query). With this query you should see all objects you can access.
Beginning to think the issue is on my end with Visual Studio 2010 or 2012.
As I work with this more, I'm discovering a few more things.
So I installed both the 32 and 64 bit versions of ODP.NET, I've gone in and added a reference to the Oracle Data Provider (Oracle.DataAccess v4)
When I attempt to create a Data Connection for say a SqlDataSource Object, I never get the Oracle Data Provider for .NET option, only the Microsoft versions. Where am I going wrong on this to get this to show up?
Oracle has this really annoying default where it only shows things you own, and not things you can "see". Here's how you change that.
In Server Explorer, right click on the connection and click on "Modify Connection". Click on the "Filters" tab.
There's a grid there, where the first option is Displayed Schemas. Select that, then click on the "..." button just above it. Now what you have to do is add the schemas where the objects you want to see are. Your DBA can tell you that. Click "Ok", when done, then click "Update" at the bottom of the grid. Click "Ok" to close the window.
Now if you open or refresh the connection, you should see things appearing. Entity Framework will also see them now if you try to build a model from the database.