none
MVC 4 Connecting to Oracle using Code First RRS feed

  • Question

  • Hi

    I am trying to connect to Oracle using EF4.1. I changed the connection sting in Web.config. I am using System.Data.OracleClient. Where can I specify the database name? In Sql Server Intial Catalog = Databasename  but in oracle it gives you the list of schemas. Is there a new API for oracle and code first?

    Thanks in Advance

    Wednesday, July 18, 2012 1:59 PM

Answers

  • Hi Caterpillar_CACI,

    Thank you for your post.

    Since Oracle is a third party database, I cannot reproduce your problem. If I understand correctly, this page can help you: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.connectionstring.aspx

    In addition, you can take a look at this page: http://www.connectionstrings.com/oracle

    You can find some samples in .NET Framework Data Provider for Oracle part.

    You also can try to contact Oracle support: http://www.oracle.com/us/support/index.html

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Friday, August 3, 2012 2:21 AM
    Thursday, July 19, 2012 8:31 AM
  • Hi Alexander

    Thanks for you response. I got Oracle working the day I wrote the message on the message board. I have made some notes that I am going to share just in case someone else is looking for the same answers.

    1. Install the latest ODP.NET for Visual Studio is a must. Make sure your are referencing the latest Oracle.DataAccess.dll. I changed the Environment Variables to the lates Oracle.DataAccess.dll but I am not sure if that helped or if it is even needed.

    2. To connect to oracle use schema name as User ID in the connectionstring(NOT THE USER ID YOU USE TO CONNECT TO ORACLE ). Try to get Data connection in the server Explorer to connect 1st and then copy the connection string to your web.config.

    3. In YourAppNameContext.cs  - OnModelCreating(DbModelBuilder ) Add modelBuilder.Entity<YOURTABLENAME>.ToTable("YOURTABLENAME", "YOURSCHEMANAME"); Remember to make the table name and schema name upper case.

    4. In your Database Model if the column names are lower case use [Column("YOURTABLENAME")] to make the column name upper case. You need to Include System.DataAnnotations.Schema to the model to use [Column("YOURTABLENAME")]).

    5.Oracle does not like .NET INT32 or any of the INT types so use decimal instead and then you can format the field in the view.

    6.When updating a foreign Key table inorder for the primary key table to be updated 1st with the ID make sure you have [DatabaseGenerated(DatbaseGeneratedOption.Identity)] in the model of the primary table above the primary key field. I am assuming you already created a trigger for the ID field of the primary table to be updated with a unique ID. You need nothing more to do with the foreign key table [DatabaseGenerated... takes care of first updating the primary table and then taking the same ID and then updating the foreign key table.

    Remember you cannot drop and recreate an oracle database and tables like you do Sql Server database but all other features work nicely.

    Hope this help someone in the future.

    Best Regards

    Shalini Gali


    Oracle and Code First



    Friday, August 3, 2012 11:49 AM

All replies

  • Hi Caterpillar_CACI,

    Thank you for your post.

    Since Oracle is a third party database, I cannot reproduce your problem. If I understand correctly, this page can help you: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.connectionstring.aspx

    In addition, you can take a look at this page: http://www.connectionstrings.com/oracle

    You can find some samples in .NET Framework Data Provider for Oracle part.

    You also can try to contact Oracle support: http://www.oracle.com/us/support/index.html

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Friday, August 3, 2012 2:21 AM
    Thursday, July 19, 2012 8:31 AM
  • Hi Alexander

    Thanks for you response. I got Oracle working the day I wrote the message on the message board. I have made some notes that I am going to share just in case someone else is looking for the same answers.

    1. Install the latest ODP.NET for Visual Studio is a must. Make sure your are referencing the latest Oracle.DataAccess.dll. I changed the Environment Variables to the lates Oracle.DataAccess.dll but I am not sure if that helped or if it is even needed.

    2. To connect to oracle use schema name as User ID in the connectionstring(NOT THE USER ID YOU USE TO CONNECT TO ORACLE ). Try to get Data connection in the server Explorer to connect 1st and then copy the connection string to your web.config.

    3. In YourAppNameContext.cs  - OnModelCreating(DbModelBuilder ) Add modelBuilder.Entity<YOURTABLENAME>.ToTable("YOURTABLENAME", "YOURSCHEMANAME"); Remember to make the table name and schema name upper case.

    4. In your Database Model if the column names are lower case use [Column("YOURTABLENAME")] to make the column name upper case. You need to Include System.DataAnnotations.Schema to the model to use [Column("YOURTABLENAME")]).

    5.Oracle does not like .NET INT32 or any of the INT types so use decimal instead and then you can format the field in the view.

    6.When updating a foreign Key table inorder for the primary key table to be updated 1st with the ID make sure you have [DatabaseGenerated(DatbaseGeneratedOption.Identity)] in the model of the primary table above the primary key field. I am assuming you already created a trigger for the ID field of the primary table to be updated with a unique ID. You need nothing more to do with the foreign key table [DatabaseGenerated... takes care of first updating the primary table and then taking the same ID and then updating the foreign key table.

    Remember you cannot drop and recreate an oracle database and tables like you do Sql Server database but all other features work nicely.

    Hope this help someone in the future.

    Best Regards

    Shalini Gali


    Oracle and Code First



    Friday, August 3, 2012 11:49 AM
  • Please refer to my step by step article on MSDN. more from MVC4 with Oracle 

    https://code.msdn.microsoft.com/windowsdesktop/AspNet-Mvc4-with-Entity-bf84ecbe

    Friday, March 13, 2015 8:51 AM