Install Oracle DB Express and Connect to ASP.Net MVC C# Using Dapper and ODP.net

Tools Used :

  • Oracle DB Express
  • Toad for Oracle ( To explore database– You can also user Oracle Developer) 
  • Visual Studio .net 2013

This tutorial covers following points.

  1. Install Oracle DB Express.  ==< Link http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
  2. Create a user  — Refer my post => Create user and Schema
  3. Create a Schema — Refer my post => Create user and Schema
  4. Create a Table
ALTER TABLE HPUTTUR.EMPLOYEE DROP PRIMARY KEY CASCADE;

DROP TABLE HPUTTUR.EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE HPUTTUR.EMPLOYEE ( EMPLOYEE_ID NUMBER NOT NULL, EMPLOYEE_NAME VARCHAR2(200 CHAR) NOT NULL, EMPLOYEE_MOBILE VARCHAR2(50 BYTE) NOT NULL, EMPLOYEE_DEPARTMENT VARCHAR2(50 BYTE) NOT NULL ) TABLESPACE SYSTEM RESULT_CACHE (MODE DEFAULT) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;

CREATE UNIQUE INDEX HPUTTUR.EMPLOYEE_PK ON HPUTTUR.EMPLOYEE (EMPLOYEE_ID) LOGGING TABLESPACE SYSTEM NOPARALLEL;

 CREATE OR REPLACE TRIGGER HPUTTUR.EMPLOYEE_TRG BEFORE INSERT ON HPUTTUR.EMPLOYEE REFERENCING NEW AS New OLD AS Old FOR EACH ROW BEGIN -- For Toad: Highlight column EMPLOYEE_ID :new.EMPLOYEE_ID := EMPLOYEE_SEQ.NEXTVAL; END EMPLOYEE_TRG; /

ALTER TABLE HPUTTUR.EMPLOYEE ADD ( CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPLOYEE_ID) USING INDEX HPUTTUR.EMPLOYEE_PK ENABLE VALIDATE); 

Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (1, 'Haneef Puttur', '354354354345', 'Computer'); 

Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (2, 'Minnu', 'y676756565', 'Computer'); 

Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (3, 'Minaal Haneef', '67868768768', 'Computer');

 Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (4, 'Haneef 2', 'yutyutyut', 'IT'); 

Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (5, 'Haneef 3', 'yutyutyut', 'Computer'); COMMIT;

 

  1. Create a Package and Procedure
CREATE OR DROP PACKAGE HPUTTUR.EMP;

 CREATE OR REPLACE PACKAGE HPUTTUR.EMP 

AS 

PROCEDURE p_EMPLOYEE (p_dept IN VARCHAR2, p_emp_list OUT SYS_REFCURSOR); END; / DROP PACKAGE BODY HPUTTUR.EMP;

CREATE OR REPLACE PACKAGE BODY HPUTTUR.EMP 

AS 

PROCEDURE p_EMPLOYEE (p_dept IN VARCHAR2, p_emp_list OUT SYS_REFCURSOR)

 IS

 BEGIN 

OPEN p_emp_list FOR 

SELECT * FROM EMPLOYEE WHERE EMPLOYEE_DEPARTMENT = p_dept;

 END; 

END; 

/
  1. Create a sample Project in C#
  2. Install Dapper
  3. Install Oracle Managed Data Access
  4. Make necessary web.config connection string.
  5. Create a test application.

Download Complete Solution :

https://github.com/haneefputtur/Haneef_Dapper_ODP_Net/archive/master.zip

View Source Codes at :

https://github.com/haneefputtur/Haneef_Dapper_ODP_Net

 

For Complete Video Tutorial Refer Here: