Tuesday, December 30, 2014

Use Oracle with ASP.net MVC

MVC is well known software architecture pattern which is widely using. Combination of .net framework and Visual studio facilitates few well defined templates to implement applications as MVC-1, MCV-2,MVC-3... MVC-6. Not only that there are several 3rd party templates and extended modules such as "Hot towel", "SPA", that are supportive to form applications using this modal.  

As usual, these templates originally defined to use MS SQL database. This is good enough when we use code first approach or modal first approach, but database approach. 

When it comes to a scenario to replace an industrial level application which handles an existing database, we need to combine the templates with existing database technology. Oracle DB has plenty of usages in industry level data management. in this case its better to know how to connect Oracle db with the MVC template. 

To start this we need to install oracle client and oracle .net provider in our computer. After successful installation of above two applications you need to configure TNS settings to connect to the Oracle DB server. 

(REF : http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection
REF: http://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF007)

After create the MVC project in visual studio we need to add reference to the Oracle.DataAccess.dll. 

To setup modals from existing database

right click the project and select "add new" option form the database to get following window

as the picture select ADO.NET entity data modal and add appropriate name for the modal. 


after click the add button you will end with the following window.

fro this select the "Generate form the database" option and click next 


From this window you need to select "New Connection" button to setup a connection to to the DB server. This will give you the following sub window to collect information.

when you expand the "Data source name" drop down, you will be able to see a list of  data sorceress that you are defined in TNS configuration file at the very beginning.Select the source and select the "Use a specific name and password" option to add the database username and password by your self rather using windows defaults. its better to save password to avoid unnecessary log in steps in testing environments.  

Before press "Ok" button you can test the connection by using "Test Connection" button. 

After complete the connection creation you can see the parent window with filled details as follow

you can select to save or not sensitive data as your preference and give an appropriate name for the context before click next

now you can select the tables views and procedures that you need to map for the modal

after finish the process you can treat the oracle database as same as the MS SQL database.