Dapper POCO Oracle (edit)

• Oracle 12c 64bit database (Nuget: Oracle.ManagedDataAccess)
• MySQL 5.7.21 (GPL) database (Nuget: MySqlConnector)

DBeaver (kết nối cơ sở dữ liệu MySQL, Oracle)

• Tự động sinh ra Entity Relationship Diagram (ERD)
• Tự động sinh ra các cấu Raw SQL tương ứng các bảng trong cơ sở dữ liệu
• Kết hợp Dapper + DapperExtensions để thực hiện CRUD, tạo POCO từ database có sẵn cấu trúc bảng
• Hỗ trở cả MySQL và Oracle

Tools:

• Generate C# POCO classes to encapsulate results of SQL queries
  • https://github.com/adamfoneil/Postulate.Zinger (C# WinForms app that helps you generate C# POCO classes to encapsulate results of SQL queries)
  • Tools sinh ra class từ câu query (chỉ hỗ trợ SQL Server, MySQL and OleDb)
• Dapper + DapperExtensions + CodeGenerator easy to play sqlserver、mysql、oracle、postgresql、sqlite
  • https://github.com/znyet/DapperExtensions
• EntityFramework Reverse POCO Generator
  
  • https://marketplace.visualstudio.com/items?itemName=SimonHughes.EntityFrameworkReversePOCOGenerator

Samples:

.NET code samples for Oracle database developers

oracle/dotnet-db-samples: .NET code samples for Oracle database developers (github.com)

https://github.com/oracle/dotnet-db-samples/issues/208

https://github.com/oracle/dotnet-db-samples/

Oracle + Net Core (edit)

• Clean Architecture
• CQRS & Mediator in .NET Core
• MediatR Library
• DbUp
• Oracle
• EF Core
• Dapper
• Web API
• .NET Core
• Swagger

Clean Architecture CQRS + ORACLE + EF CORE + DAPPER

referbruv/CqrsNinja: CQRS Ninja is a boilerplate solution, built to demonstrate implementing CQRS in ASP.NET Core (.NET 6) via MediatR. (github.com)

Using Entity Framework Core and Dapper in ASP.NET Core - Safe Transactions (codewithmukesh.com)

Dapper in ASP.NET Core with Repository Pattern - Detailed (codewithmukesh.com)

DbUp - @manhng

Mediatr & Mediator - @manhng

ASP.NET Identity

Securing ASP.NET MVC Applications with ASP.NET Identity | CodeGuru

ASP.NET MVC5 - Keeping Users in Oracle Database - Stack Overflow

Script for creating ASP.NET Identity 2.0 tables on OracleDB (github.com)

arichika/AspNet.Identity.Oracle: AspNet.Identity.Oracle for ASP.NET Identity 2.0 with ODP.NET (github.com)

Oracle with EF Core

Oracle (entityframeworkcore.com)

Oracle + Entity Framework Core - @manhng

.NET Core With Oracle Database Using Dapper - @manhng

.NET Core

NuGet Gallery | Microsoft.EntityFrameworkCore 6.0.1

NuGet Gallery | Microsoft.EntityFrameworkCore.Tools 6.0.1

NuGet Gallery | Microsoft.EntityFrameworkCore.Design 6.0.1

NuGet Gallery | Oracle.EntityFrameworkCore 6.21.5

NuGet Gallery | Oracle.ManagedDataAccess.Core 3.21.50

.NET Core 2.1

• Microsoft.EntityFrameworkCore(2.2.6)
• Microsoft.EntityFrameworkCore.Design(2.2.6)
• Microsoft.EntityFrameworkCore.Relational(2.2.6)
• Microsoft.EntityFrameworkCore.Tools(2.2.6)
• Oracle.EntityFrameworkCore(2.19.60)
• Oracle.ManagedDataAccess.Core(2.19.60)

public class DataContext : DbContent {
    public DataContext(DbContextOptions options) : base(options) {}
}

[Table("Test")]
public class TestEntity{
    [Key]
    [Column("id")]
    [MaxLength(36)]
    public string ID{get;set;}
    [Column("text")]
    [MaxLength(50)]
    public string Text{get;set;}
    [Column("count")]
    public int? Count{get;set;}
    ……
}

public class DataContext : DbContent {
    public DataContext(DbContextOptions options) : base(options) {}
    public DbSet<TestEntity> TestEntities {get;set;}
}

• Microsoft.Extensions.DependencyInjection(6.0.0)
• Oracle.EntityFrameworkCore(2.19.60)
• Microsoft.EntityFrameworkCore.Design(2.2.6)

public void ConfigureServices(IServiceCollection Services) {
    ……
    services.AddDbContext<DataContext>(options.UseOracle(Configuration.GetConnectionString("OracleConnectionString")));
    ……
}

• Add-Migration AddTestEntity
• Update-Database

.NET Framework

NuGet Gallery | Oracle.ManagedDataAccess 21.5.0

• Oracle.ManagedDataAccess.Client

Dapper

ASP.NET Core Web API with Oracle Database and Dapper | Mukesh Kumar

.NET Core + EF Core + DbContext

Entity Framework Core creating model from existing Oracle database - Stack Overflow

• Oracle.EntityFrameworkCore
• Oracle.ManagedDataaccess.Core
• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Tools

PM> Scaffold-DbContext "User Id=test;Password=test;Data Source=localhost:1521/orcl;" 

-Provider Oracle.EntityFrameworkCore

-OutputDir Models 

-Context TestDbContext 

-Tables USER 

sql - How to create id with AUTO_INCREMENT on Oracle? - Stack Overflow

c# - How to connect to an Oracle database Connection from .Net Core - Stack Overflow

using Oracle.ManagedDataAccess.Client;

public void Execute(string queryString, string connectionString)
{
    using (OracleConnection connection = new OracleConnection(connectionString))
    {
        OracleCommand command = new OracleCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

ORACLE ID AUTO INCREMENT

ASP.NET Core Web API with Oracle Database and Dapper

This article will focus on how to create Asp.Net Core Web API to get data from Oracle database using Dapper ORM. First thing, here we are not using SQL, because of so many articles available on Internet where mostly SQL server is using for demonstration. So, we think, let write one article where we will use Oracle as a database. To reduce the complexity of database access logic we are using Dapper ORM. So, let's move to practical demonstration.

Create Asp.Net Core Web API Project

To create a new project in Asp.Net Core Web API. Just open Visual Studio 2017 version 15.3 and we have to follow below steps.

1. Go to File menu and click to New and then choose Project.
2. From the New Project window, first, you have to choose .Net Framework 4.6 or above version and then from the left panel, choose Visual C# and then .Net Core.
3. From the right panel, choose “Asp.Net Core Web Application” and provide the save location where you want to save project and click OK.
4. From the next windows, which will provide you different kinds of the template, you have to choose Web API.

Now click to OK. It will take few minutes to configure Asp.Net Core Web API project.

Setup Oracle Table and Stored Procedures

To create database and tables for this demonstration, we are using Oracle Developer Tools. It is very lightweight and flexible which help us to work with database smoothly.  

As per Oracle

Create a database name call it "TEST_DB" and inside that create a table name as "EMPLOYEE". You can use the following syntax to create the table inside "TEST_DB" database.

  CREATE TABLE "TEST_DB"."EMPLOYEE" 
   (	
    "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER  NOCYCLE , 
	"NAME" VARCHAR2(255 BYTE), 
	"SALARY" NUMBER(10,0), 
	"ADDRESS" VARCHAR2(500 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TEST_DATA" ;

Need to add some dummy records inside the tables, so that we can directly get the data from PostMan. So, we are adding four records here as follows.

Insert into TEST_DB.EMPLOYEE (ID,NAME,SALARY,ADDRESS) values (100,'Mukesh',20000,'India');
Insert into TEST_DB.EMPLOYEE (ID,NAME,SALARY,ADDRESS) values (101,'Rion',28000,'US');
Insert into TEST_DB.EMPLOYEE (ID,NAME,SALARY,ADDRESS) values (102,'Mahesh',10000,'India');
Insert into TEST_DB.EMPLOYEE (ID,NAME,SALARY,ADDRESS) values (103,'Banky',20000,'India');

Now its time to create one SP which will bring the list of employees records. Here we are using Cursor for returning list of data as an output parameter.

CREATE OR REPLACE PROCEDURE "TEST_DB"."USP_GETEMPLOYEES" (
    EMPCURSOR OUT SYS_REFCURSOR
)
AS
Begin
Open EMPCURSOR For
SELECT ID, NAME, SALARY,ADDRESS FROM Employee;
End;

Now going to create one SP which will get the individual record for an employee based on their employee id. 

CREATE OR REPLACE PROCEDURE "TEST_DB"."USP_GETEMPLOYEEDETAILS" 
(
  EMP_ID IN INT,
  EMP_DETAIL_CURSOR OUT SYS_REFCURSOR  
) AS 
BEGIN
    OPEN EMP_DETAIL_CURSOR FOR
    SELECT ID, NAME, SALARY,ADDRESS FROM Employee WHERE ID = EMP_ID;
END;

Install Dapper ORM

Open "Package Manager Console" from the "Nuget Package Manager" of Tools menu and type following command and press enter to install dapper and its dependencies if have.

Install-Package Dapper -Version 1.50.5

After installation, you can check with references section of the project. One reference as "Dapper" has added inside that.

Install Oracle Manage Data Access for Core

As we are using Asp.Net Core Web API application with Oracle and need to access Oracle database from the Core application. To use Oracle database with .Net Core application, we have Oracle library which will help us to manage logic of database access. So, we have to install following package that is beta version. 

Add Oracle Connection

Now we have everything ready related to the database like the database, tables, and SPs etc. To access the database from Web API, we have to create connection string as usual inside the "appsettings.json" file. 

{
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  },
  "ConnectionStrings": {
    "EmployeeConnection": "data source=mukesh:1531;password=**********;user id=mukesh;Incr Pool Size=5;Decr Pool Size=2;"
  }
}

Create Repositories

To keep the separation of concern in mind, we are using Repository here. Create a new folder as "Repositories" inside the Web API project and create an interface as "IEmployeeRepository" and a class as "EmployeeRepository" which will implement to IEmployeeRepository. 

namespace Core2API.Repositories
{
    public interface IEmployeeRepository
    {
        object GetEmployeeList();

        object GetEmployeeDetails(int empId);
        
    }
}

Following is the EmployeeRepository class which is implementing IEmployeeRepository. To access configuration, we are injecting IConfiguration in the constructor. So, we have configuration object is ready to use. Apart from that we have GetConnection() method which will get the connection string from the appsettings.json and provide it to OracleConnection to create a connection and finally return connection. As we have implemented "IEmployeeRepository" which have two methods as GetEmployeeDetails and GetEmployeeList.

using Core2API.Oracle;
using Dapper;
using Microsoft.Extensions.Configuration;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;


namespace Core2API.Repositories
{
    public class EmployeeRepository : IEmployeeRepository
    {
        IConfiguration configuration;
        public EmployeeRepository(IConfiguration _configuration)
        {
            configuration = _configuration;
        }
        public object GetEmployeeDetails(int empId)
        {
            object result = null;
            try
            {
                var dyParam = new OracleDynamicParameters();
                dyParam.Add("EMP_ID", OracleDbType.Int32, ParameterDirection.Input, empId);
                dyParam.Add("EMP_DETAIL_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

                var conn = this.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "USP_GETEMPLOYEEDETAILS";

                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        public object GetEmployeeList()
        {
            object result = null;
            try
            {
                var dyParam = new OracleDynamicParameters();

                dyParam.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output);

                var conn = this.GetConnection();
                if(conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                if (conn.State == ConnectionState.Open)
                {
                    var query = "USP_GETEMPLOYEES";

                    result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        public IDbConnection GetConnection()
        {
            var connectionString = configuration.GetSection("ConnectionStrings").GetSection("EmployeeConnection").Value;
            var conn = new OracleConnection(connectionString);           
            return conn;
        }
    }
}

public IDbConnection GetConnection()
{
     var connectionString = configuration.GetSection("ConnectionStrings").GetSection("EmployeeConnection").Value;
     var conn = new OracleConnection(connectionString);           
     return conn;
}

To use Oracle datatypes with .Net Core, we are using OracleDyamicParameters class which will provide the list of function to manage Oracle parameters behaviors. 

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Data;

namespace Core2API.Oracle
{
    public class OracleDynamicParameters : SqlMapper.IDynamicParameters
    {
        private readonly DynamicParameters dynamicParameters = new DynamicParameters();
        private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();

        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)
        {
            OracleParameter oracleParameter;
            if (size.HasValue)
            {
                oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);
            }
            else
            {
                oracleParameter = new OracleParameter(name, oracleDbType, value, direction);
            }

            oracleParameters.Add(oracleParameter);
        }

        public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
        {
            var oracleParameter = new OracleParameter(name, oracleDbType, direction);
            oracleParameters.Add(oracleParameter);
        }

        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);

            var oracleCommand = command as OracleCommand;

            if (oracleCommand != null)
            {
                oracleCommand.Parameters.AddRange(oracleParameters.ToArray());
            }
        }
    }
}

Configure Dependencies in Startup.cs

To access the dependencies on the controller or repository classes, we have to configure or we can say register our dependency classes with interfaces inside the ConfigureServices method of Startup class. 

using Core2API.Repositories;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace Core2API
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddTransient<IEmployeeRepository, EmployeeRepository>();
            services.AddSingleton<IConfiguration>(Configuration);
            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }
}

Add EmployeeController

Now its time to finally create API call in EmployeeControler. First, we have added IEmployeeRepository inside the constructor to use dependencies. Secondly, we have to create API call with Route attribute for both methods.

using Core2API.Repositories;
using Microsoft.AspNetCore.Mvc;

namespace CoreAPI.Controllers
{
    [Produces("application/json")]    
    public class EmployeeController : Controller
    {
        IEmployeeRepository employeeRepository;
        public EmployeeController(IEmployeeRepository _employeeRepository)
        {
            employeeRepository = _employeeRepository;
        }

        [Route("api/GetEmployeeList")]
        public ActionResult GetEmployeeList()
        {
            var result = employeeRepository.GetEmployeeList();
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);            
        }

        [Route("api/GetEmployeeDetails/{empId}")]
        public ActionResult GetEmployeeDetails(int empId)
        {
            var result = employeeRepository.GetEmployeeDetails(empId);
            if (result == null)
            {
                return NotFound();
            }
            return Ok(result);
        }
    }
}

Now we have ready everything like repository is ready, connection with Oracle database is ready and finally, API call is also ready inside the controller. So, its time to run the API and see the result in PostMan. Just press F5 to run the Web API and open PostMan to test the result.

To test in PostMan, first, choose "Get" as a method and provide the URL to get the list of employee records and click to SEND button which will make a request to our API and get the list of employees which we have added at the beginning while creating the database scripts.

To get the single employee record, just pass the following URL as you can see in the image. You can see here, we want to see the record for employee id 103. Once you send the request, you can see the output something like as below.

Conclusion

So, today we have learned how to create Asp.Net Core Web API project and use Dapper with Oracle database.

I hope this post will help you. Please put your feedback using comment which helps me to improve myself for next post. If you have any doubts please ask your doubts or query in the comment section and If you like this post, please share it with your friends.

ASP.NET Core Web API with Oracle Database and Dapper | Mukesh Kumar

Getting Started with ODP.Net Core (oracle.com) (Source Code)

Entity Framework Code First and Code First Migrations for Oracle Database (Source Code)

Entity Framework Core Database-First Tutorial for .NET Core for Oracle (devart.com)

Connect to Oracle database from .NET core application. – taithienbo

CRUD Operations In ASP.NET Core-3.1 Using Oracle Database (c-sharpcorner.com)

Oracle ASP.NET Google (edit)

How to use OracleMembershipProvider in ASP.NET MVC Application​

c# - oracle database connection in web.config asp.net - Stack Overflow

NuGet Gallery | Oracle.Web 21.5.0

ASP.NET Membership Management Tool

sthakuri/ASP.NET-Membership-Management-tool: ASP.NET Membership Management Tool (github.com)

Oracle Data Access Components (ODAC)

Oracle Developer Tools for Visual Studio

Oracle Data Provider for .NET (ODP.NET)

oracle/dotnet-db-samples: .NET code samples for Oracle database developers (github.com)

Overview of Oracle Providers for ASP.NET

Oracle Providers for ASP.NET offer ASP.NET developers an easy to use method to store application state common to web applications (such as web user information, shopping carts) within an Oracle database. These providers are modeled on existing Microsoft ASP.NET providers, sharing similar schema and programming interfaces to provide .NET developers a familiar interface.

Oracle supports the following providers:

• Membership Provider

• Role Provider

• Site Map Provider

• Session State Provider

• Profile Provider

• Web Events Provider

• Web Parts Personalization Provider

• Cache Dependency Provider

Each ASP.NET provider can be used individually or in combination with other Oracle ASP.NET providers for your web site. Each of them store a specific set of web site information.

Oracle Providers for ASP.NET classes, their use, installation, and requirements are described in Oracle Providers for ASP.NET Developer's Guide, which is also provided as dynamic help.

Oracle Providers for ASP.NET Installation

<connectionStrings>
<add name="OraAspNetConString" connectionString="User Id=aspnet;Password=aspnet;Data Source=oracle; " />
</connectionStrings>

Oracle.DataAccess.Client

Web.config file for EPiServer 6.0.530.0 for IIS6 and Visual Studio development server (github.com)

OracleMembershipProvider

Managing Users by Using Membership | Microsoft Docs

OracleMembershipProvider

Migrate ASP.NET to Google Cloud Platform

Its show how to migrate existing legacy .NET application to Google Cloud Platform.

dotnet-docs-samples/applications/liftAndShiftPetshop at main · GoogleCloudPlatform/dotnet-docs-samples (github.com)

ASP.NET Membership and Role Provider using Oracle database

We all know that in ASP.NET 2.0 there are Built-In Providers to help developers to build a robust application.

• Membership
• Role management
• Site map
• Profile
• Session state
• Web events
• Web Parts personalization
• Protected configuration

What i want to write here is about Membership Provider and Role Provider,

Membership
System.Web.Security.ActiveDirectoryMembershipProvider
System.Web.Security.SqlMembershipProvider

Role management
System.Web.Security.AuthorizationStoreRoleProvider
System.Web.Security.SqlRoleProvider
System.Web.Security.WindowsTokenRoleProvider

Api Convert Currency (edit)

• DbUp: Chạy script để đồng bộ database
• Oracle Server Side Paging: Paging phía Server truyền lên PageSize, PageIndex dùng câu Raw SQL
• Dapper: dùng với Raw SQL
• cUrl: Chuyển đổi tiền tệ giữa VND-USD-JPY (Việt-Nhật-Mỹ) dùng API online public
• Oracle DbDataReader: Pagination in Oracle - @manhng
• Oracle Command Timeout: Pagination in Oracle - @manhng

Pagination in Oracle - @manhng

Api Convert Currency Online

1 VND to USD - Vietnamese Dongs to US Dollars Exchange Rate (xe.com)

1 USD to VND - US Dollars to Vietnamese Dongs Exchange Rate (xe.com)

1 JPY to VND - Japanese Yen to Vietnamese Dongs Exchange Rate (xe.com)

1 VND to JPY - Vietnamese Dongs to Japanese Yen Exchange Rate (xe.com)

cURL - @manhng

curl -X GET "https://www.xe.com/currencyconverter/convert/?Amount=1&From=VND&To=USD"

Dapper

Working With Parameters When Using Dapper | Learn Dapper

Paging

Paging in ASP.NET Core Web API - Code Maze (code-maze.com)

Paging Oracle

SELECT A.*, CEIL(TOTAL_NUM_ROWS / 10) TOTAL_NUM_PAGES
FROM ( SELECT B.ID, B.FIRST_NAME, B.LAST_NAME, B.UPLOAD_DATE, row_number() OVER(ORDER BY UPLOAD_DATE DESC) rn, COUNT(*) OVER() TOTAL_NUM_ROWS
 FROM PERSON B 
 ) A 
WHERE rn BETWEEN (1 - 1) * 10 + 1 AND 1 * 10


SELECT A.*, CEIL(TOTAL_NUM_ROWS / 10) TOTAL_NUM_PAGES
FROM ( SELECT B.ID, B.FIRST_NAME, B.LAST_NAME, B.UPLOAD_DATE, row_number() OVER(ORDER BY UPLOAD_DATE DESC) rn, COUNT(*) OVER() TOTAL_NUM_ROWS
 FROM PERSON B 
 ) A 
WHERE rn BETWEEN (2 - 1) * 10 + 1 AND 2 * 10


SELECT A.*, CEIL(TOTAL_NUM_ROWS / :PAGESIZE) TOTAL_NUM_PAGES
FROM ( SELECT B.ID, B.FIRST_NAME, B.LAST_NAME, B.UPLOAD_DATE, row_number() OVER(ORDER BY UPLOAD_DATE DESC) rn, COUNT(*) OVER() TOTAL_NUM_ROWS
 FROM PERSON B 
 ) A 
WHERE rn BETWEEN (:PAGEINDEX - 1) * :PAGESIZE + 1 AND :PAGEINDEX * :PAGESIZE

DbDataReader Oracle

Retrieving Data Using a DataReader - ADO.NET | Microsoft Docs

DbUp Oracle

Releases · DbUp/DbUp (github.com)

DbUp/DbUp: DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date. (github.com)

Using Database Project and DbUp for database management - Kamil Grzybek

Simplifying database development with docker and DbUp | Blog (mcode.it) (HAY HAY HAY)

Snippet

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="dbup-core" version="4.5.0" targetFramework="net472" />
  <package id="dbup-oracle" version="4.5.0" targetFramework="net472" />
  <package id="Oracle.ManagedDataAccess" version="18.3.0" targetFramework="net472" />
</packages>

Snippet

using DbUp;
using DbUp.Oracle;
using System;
using System.Linq;
using System.Reflection;
 
namespace Oracle.DbUp
{
    class Program
    {
        [Obsolete]
        static void Main(string[] args)
        {
            var connectionString =
                args.FirstOrDefault()
                ?? "DATA SOURCE=localhost/ORCL;USER ID=sa;PASSWORD=123456;Min Pool Size=5;Decr Pool Size=10;PERSIST SECURITY INFO=True";
            var upgrader = DeployChanges.To
                .OracleDatabase(connectionString)
                .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                .LogToConsole()
                .Build();
            var result = upgrader.PerformUpgrade();
            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                Environment.Exit(-1);
            }
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine(value: "Success!");
            Console.ResetColor();
            Environment.Exit(0);
        }
    }
}

Oracle Database Documentation (edit)

Production: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Oracle Database Online Documentation 10g Release 1 (10.1)

Oracle Database Documentation - Oracle Database (All versions)

Oracle Data Access Components Documentation 12c Release 2 (12.1.0.1.2)

Oracle Data Access Components Documentation12c Release 2 (12.1.0.1.2)

Data Access Components Documentation 11g Release 4 (11.2.0.3.0)

Oracle Data Access Components Documentation

Data Provider for .NET Developer's Guide

Data Provider for .NET Developer's Guide - Contents (oracle.com)

Oracle Database Online Documentation Library, 11g Release 2 (11.2) Database 2 Day Developer's Guide

Preface (oracle.com)

Oracle Import Export (edit)

Cài đặt:

Cài đặt cơ sở dữ liệu Oracle 12c trên Windows (openplanning.net)

How To Install Oracle Database 18c On Windows 10 | RebellionRider

How to install Oracle Database 18c XE on Windows – Gerald on IT

How to Install Oracle Database 18c on Windows (toadworld.com)

Hướng dẫn cài đặt Oracle Database 19c trên Windows - VinaSupport

Installing Oracle Database 12c on Windows

Install Oracle Database: A Step By Step Guide To Install Oracle Database (oracletutorial.com)

Có 2 cách để làm việc với dòng lệnh trong Oracle như sau:

1) Open CMD với quyền Admin (sau đó paste câu lệnh imp username/password@localhost FILE=Northwind.dmp FULL=y vào để tiến hành import)
2) Open CMD với quyền Admin (sau đó paste câu lệnh sqlplus system/password@localhost/ORCL as sysdba vào để chạy các câu lệnh với SQL Plus)

Lưu ý: Phải start service OracleOraDB12Home1TNSListener của Oracle trước.

ORCL là alias name (net_service_name) trong tệp tnsnames.ora

2) Đường dẫn ORACLE_HOME:

ORACLE_HOME: C:\app\ORACLE\virtual\product\12.2.0\dbhome_1\

C:\app\ORACLE\virtual\product\12.2.0\dbhome_1\network\admin\tnsnames.ora

C:\app\ORACLE\virtual\product\12.2.0\dbhome_1\network\admin\listener.ora

Local Naming Parameters (tnsnames.ora) (oracle.com)

3) Lệnh cần nhớ:

sqlplus system/password as sysdba

sqlplus system/password@localhost/ORCL as sysdba

alter session set "_ORACLE_SCRIPT"=true;

4) Tham khảo:

Import, export file dmp Oracle database 11g (viblo.asia)

Import và Export cơ sở dữ liệu Oracle (openplanning.net)

Hướng dẫn sử dụng Oracle Database Link và Synonym (openplanning.net)

oracle11g - How to import an Oracle database from dmp file and log file? - Stack Overflow

20 Original Export and Import (oracle.com)

DROP TABLESPACE (oracle.com)

Dapper Extensions with Oracle database (edit)

Advanced operations using Dapper.Net | InfoWorld

Dapper Extensions

Dapper + DapperExtensions + CodeGenerator easy to play SqlServer + MySql + Oracle + PostgreSql + Sqlite

GitHub - znyet/DapperExtensions: Dapper + DapperExtensions + CodeGenerator easy to play sqlserver、mysql、oracle、postgresql、sqlite

GitHub - tmsmith/Dapper-Extensions: Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs. For more advanced querying scenarios, Dapper Extensions provides a predicate system. The goal of this library is to keep your POCOs pure by not requiring any attributes or base class inheritance.

Dapper Extensions Alternatives - .NET ORM | LibHunt

A simple but effective mini-profiler for ASP.NET (and Core) websites

GitHub - MiniProfiler/dotnet: A simple but effective mini-profiler for ASP.NET (and Core) websites

NUnit 3 for VS 2019

NUnit 3 Test Adapter - Visual Studio Marketplace

Running Tests with the NUnit Visual Studio Adapter - Alteridem

https://dapper-tutorial.net/query
https://zetcode.com/csharp/dapper/
https://www.learndapper.com/parameters
https://medium.com/geekculture/integrating-dapper-with-entity-framework-core-55aacc94b5b0
https://github.com/znyet/DapperExtensions
https://github.com/tmsmith/Dapper-Extensions

Hello World Oracle (edit)

Entity Framework 6 vs Oracle

Oracle.ManagedDataAccess.dll
Oracle.ManagedDataAccess.EntityFramework.dll

EF6 CodeFisrt支持Oracle - Rick Carter - 博客园 (cnblogs.com)

Developing .NET Applications for Oracle Database (On-Premises)

Developing .NET Applications for Oracle Database (On-Premises) for .NET Core 3.1 or higher.

Build a .NET Application on the Oracle Database with Microsoft Visual Studio 2010

by John Paul Cook
Published June 2011

Build a .NET Application on the Oracle Database with Microsoft Visual Studio 2010

.NET Data Provider

Note: You can use Oracle.ManagedDataAccess NuGet package (.NET >= 4.0, database >= 10g Release 2).

ODP.NET, Unmanaged Driver: Oracle.DataAccess.Client.dll (for example namespace: Oracle.DataAccess.Types)

ODP.NET, Managed Driver: Oracle.ManagedDataAccess.dll (for example namespace: Oracle.ManagedDataAccess.Types)

Connection String

using Oracle.DataAccess.Client; // C#, ODP.NET Oracle managed provider

string oradb = "Data Source=OraDb;User Id=hr;Password=hr;";

string oradb = "Data Source=(DESCRIPTION="
  + "(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521))"
  + "(CONNECT_DATA=(SERVICE_NAME=ORCL)));"
  + "User Id=hr;Password=hr;";

OracleConnection conn = new OracleConnection(oradb);

try
{
conn.Open();

string sql = " select department_name from departments where department_id = 10";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;

OracleDataReader dr = cmd.ExecuteReader();
dr.Read();

label1.Text = dr["department_name"].ToString(); // Retrieve by column name
label1.Text = dr.GetString(0).ToString();  // Return a .NET data type
label1.Text = dr.GetOracleString(0).ToString();  // Return an Oracle data type

label1.Text = dr.GetInt16("department_id").ToString();

conn.Close();
conn.Dispose();

}
catch (OracleException ex) // catches only Oracle errors
{
    ....
}

Development Environment

OS: Windows Server 2016

Oracle Server Database: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Show me the code

SELECT banner FROM v$version WHERE ROWNUM = 1;

alter session set "_ORACLE_SCRIPT"=true;

create user Manh identified by Manh;

grant dba to MyDb;

ALTER USER Manh ACCOUNT UNLOCK IDENTIFIED BY Manh;

 select sys_guid() from dual
 union all
 select sys_guid() from dual
 union all 
 select sys_guid() from dual

Entity Framework Core + Oracle (edit)

1. Update Model from Database...
2. Generate Database from Model...

Entity Framework, LINQ and Model-First for the Oracle Database

https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm

Entity Framework Core tools reference - .NET Core CLI

https://docs.microsoft.com/en-us/ef/core/cli/dotnet

dotConnect for Oracle

https://www.devart.com/dotconnect/oracle/articles/efcore-database-first-net-core.html

Starting with an existing database

https://www.learnentityframeworkcore.com/walkthroughs/existing-database

Oracle DB First

https://www.devart.com/dotconnect/oracle/articles/efcore-database-first-net-core-entity-developer.html

Oracle Command - Inserting Data in Run Time

https://www.devart.com/dotconnect/oracle/articles/tutorial-command.html

To insert the first row into table dept you can use the following statement:

1. CREATE TABLE dept:

   CREATE TABLE dept (
     deptno INT PRIMARY KEY,
     dname VARCHAR(14),
     loc VARCHAR(13)
   )
   

2. CREATE TABLE emp:

   CREATE TABLE emp (
     empno INT PRIMARY KEY,
     ename VARCHAR(10),
     job VARCHAR(9),
     mgr INT,
     hiredate DATE,
     sal FLOAT,
     comm FLOAT,
     deptno INT REFERENCES dept
   )

INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')

The following code fragment executes the query:

OracleConnection conn = new OracleConnection("User Id=scott;Password=tiger;Server=OraServer;");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')";
cmd.Connection = conn;
conn.Open();
try {
  int aff = cmd.ExecuteNonQuery();
  MessageBox.Show(aff + " rows were affected.");
}
catch {
  MessageBox.Show("Error encountered during INSERT operation.");
}
finally {
  conn.Close();
}

Console Application - How You Can Create a .NET Core Application Using Entity Framework Core with Oracle

https://www.talkingdotnet.com/create-net-core-application-using-entity-framework-core-with-oracle/

1. Update Model From Database...
2. Update Database from Model...
3. Generate Database Script From Model...

Oracle (edit)

The ADO.NET provider with invariant name 'Oracle.ManagedDataAccess.Client' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details

c# - The ADO.NET provider 'Oracle.ManagedDataAccess.Client' is either not registered in the machine or application config file, or could not be loaded - Stack Overflow

Solution: I resolved this by adding ODP.NET (Oracle.ManagedDataAccess.EntityFramework 12c) from NuGet. I'm running VS2015 with EF 6.2.0.

Oracle Dapper C# : Problem with Kanji characters (Oracle database in Japanese)

C# - How to save unicode data to oracle? - Stack Overflow

ASP.NET - Why are my Chinese characters not displayed correctly in c# string - Stack Overflow

C# - OdbcConnection returning Chinese Characters as "?" - Stack Overflow

C#: DbType.String versus DbType.AnsiString - Stack Overflow

C#/Oracle: Specify Encoding/Character Set of Query? - Stack Overflow

Oracle database: Unicode Database and Unicode Datatype

Ký tự hán tự (kanji character): 鄭 瑋萱

Supporting Multilingual Databases with Unicode (oracle.com)

 //INSERT WITH PARAMETER BINDING - UNICODE SAVED
 using (System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection(connString))
 {
     cn.Open();
     System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("insert into kuku (kuku) values(:UnicodeString)", cn);
     cmd.Parameters.Add(":UnicodeString", System.Data.OracleClient.OracleType.NVarChar).Value = input + " OK" ;
     cmd.ExecuteNonQuery();
     cn.Close();
 }

 //FETCH RESULT
 using (System.Data.OracleClient.OracleConnection cn = new System.Data.OracleClient.OracleConnection(connString))
 {
     cn.Open();
     System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand("select kuku from kuku", cn);
     System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader();
     if(dr.Read())
     {
         string output = (string) dr[0];
         char sa = output[0];
     }
     cn.Close();
 }

Oracle With Dapper

whit-wu/OracleWithDapper: Shows .NET devs working with Oracle DBs how to utilize Dapper. Shows nuances between using Dapper with SQL Server and using it with Oracle. (github.com)

DIPSAS/Dapper.Oracle: Oracle support for Dapper Micro ORM. (github.com)

https://aspnetmaker.dev/ (Code Generate: ASP.NET Maker)

Oracle Managed ODP.NET | Vijay Ganesh (BASIC)

Connect to Oracle database from .NET core application

[Translation] ASP.NET Core Web API using Oracle database and Dapper read this is enough - Programmer All

Oracle Design Basics (haw-hamburg.de)

Oracle/dotnet-db-samples: .NET code samples for Oracle database developers (github.com)

ASP.NET Core Web API With Oracle Database And Dapper (c-sharpcorner.com)

• NTierOracleIdentityExample.Bll
• NTierOracleIdentityExample.Dll
• NTierOracleIdentityExample.Web

ASP.NET Core MVC with .NET 5.0 ORACLE, Identity using N-tier architecture-Part 01 | Codementor

ASP.NET Core MVC with .NET 5.0 ORACLE, Identity using N-tier architecture-Part 02 | Codementor

• .NET CORE
• ASP.NET CORE
• ASP.NET MVC
• DESIGN PATTERN
• ORACLE

jQuery Ajax CRUD in ASP.NET Core MVC with Modal Popup - CodAffection

Build ASP.NET MVC Project with Oracle Data Base using Entity Framework DB first approach

• Install-Package EntityFramework -Version 6.2.0
• Install-Package EntityFramework.ja -Version 6.2.0
• Oracle.ManagedDataAccess.dll -Version 18.3.0
• Oracle.ManagedDataAccess.EntityFramework.dll -Version 18.3.0

Oracle/dotnet-db-samples: .NET code samples for Oracle database developers (github.com)

Oracle Data Access Components (ODAC) for Windows Downloads

Using ASP.NET with Oracle Database

Connecting ASP.NET with Oracle Database - Doyensys Blog

Oracle .NET and Visual Studio Downloads for Oracle Database

ASP.NET Core Web API with Entity Framework Core - @manhng

Dapper - @manhng

Using Dapper - @manhng

Dapper - @manhng

Dapper POCO Oracle - @manhng

CQRS Raw SQL and DDD with Dapper - @manhng

Mapping Generator - @manhng

Dapper Extensions with Oracle database - @manhng

Dapper + Object Extensions + Visual Studio Extensions - @manhng

Dapper Generic Repository PostgreSQL - @manhng

Dapper Log4net AutoMapper - @manhng

Dapper Transaction - @manhng

Work with EFCore Dapper together in the PostgreSQL database - @manhng

Integration Tests (Microsoft.VisualStudio.TestTools.UnitTesting + Dapper + Stored Procedure + XML) - @manhng

.NET Core With Oracle Database Using Dapper - @manhng (HAY HAY HAY)

CRUD Operations In ASP.NET Core-3.1 Using Oracle Database (c-sharpcorner.com) (BASIC)

.NET 5.0 - CRUD API Example and Tutorial | Jason Watmore's Blog

ECRUD (Easy-CRUD)

wvswill/ecrud (github.com)

Dapper.Fast.CRUD

MoonStorm/Dapper.FastCRUD: The fastest micro-orm extension for Dapper (github.com)

Connect application with Oracle Database using entity framework in MVC 5

Here are good example that discus step by step , please following below links:

https://csharp.today/entity-framework-6-database-first-with-oracle/

http://www.codeproject.com/Tips/421105/Using-Oracle-with-EntityFramework

Please following below link, show you CRUD

http://www.codeproject.com/Tips/869553/CRUD-operation-with-Entity-Framework-Database-Fi

http://www.codeproject.com/Articles/812005/ASP-NET-MVC-CRUD-Application-with-drop-down-list

http://www.c-sharpcorner.com/uploadfile/dhananjaycoder/crud-operation-in-Asp-Net-mvc-framework/

http://cybarlab.com/crud-operation-in-asp-net-mvc-using-entity-framework

Oracle XE

https://geraldonit.com/2019/08/19/how-to-install-oracle-database-18c-xe-on-windows/

https://blog.toadworld.com/2018/09/26/how-to-install-oracle-database-18c-on-windows/

https://rustamkhodjaev.com/2020/04/28/install_database_express_edition_18c_on_windows/

A WinForms SQL client for generating C# query result classes, Dapper productivity

Note: Only for OleDb, MySql, SqlServer, SqlCe, not for Oracle

adamfoneil/Postulate.Zinger: A WinForms SQL client for generating C# query result classes, Dapper productivity (github.com)

HigLabo

higty/higlabo: HigLabo library provide features of Mail, Ftp, Rss, Twitter, ObjectMapper, ORM, ASP.NET Mvc...etc (github.com)

Dapper + DapperExtensions + CodeGenerator easy to play sqlserver, mysql, oracle, postgresql, sqlite

znyet/DapperExtensions: Dapper + DapperExtensions + CodeGenerator easy to play sqlserver、mysql、oracle、postgresql、sqlite (github.com)

ASP.NET Core MVC with .NET 5.0 ORACLE, Identity using N-tier architecture-Part 01

In this tutorial series we will be creating ASP.NET Core MVC Web application using ORACLE Database. Before we dive into anything, whole code with database scripts will be available on Github.This tutorial series will be split into 3 parts.

First part is setting up our development environment, setting up multiple projects with structure that will use couple of design patterns like N-tier architecture, SOC, Unit of Work and scaffolding existing ORACLE 12.2 database and creating our first migration.

Second part will be explaining our architecture of application, implementing Repository pattern for our Data layer, keeping SoC, implementing base interfaces for CRUD operations on existing database. Then we will create our service or Business logic layer by implementing interfaces that will represent Unit of Work. And last, in our web project we will use model to view model mapping and create our mapping profile.

Third part will be explaining how to use ASP.NET Identity with ORACLE database and with Windows authentication. So lets start shall we?
First things first lets explain what type of project are we going to use and how are we going to connect to database using code first approach of entity framework. Open your Visual Studio (Mine is 2019), then File -> New -> Project.

Next we are going to choose out project type. We will be using ASP.NET Core Web App(Model-View-Controller) type of project.

Then click next and name your project and solution and choose folder. For out N-Tier artchitecture we will be creating three projects. First will be our Web project, Second will be our Business logic layer and last will be Data logic layer. So when creating we will name our Web project with .Web extension. Next two project will have .Bll and .Dll extensions as well.

Next we will use .NET 5.0 as our target framework and will not choose any type of authentication for now, as we will be adding Windows authentication with ASP.NET Identity later. Click create and we are done.

So now we have to add two more projects for our business and data layers. Right click on solution and choose New project.

Choose Class library(not .NET Framework one), and click next. Choose same name of web project but with .Dll and .Bll extensions.

Click Next and choose .NET 5.0 as target framework and last create your project.

We will have structure like on next image.

Next thing that we will do is to setup our Dll project. This will include installing additional packages for connecting to Oracle database, organizing our project with SoC pattern (separation of concerns) and setting up our first migration.

So go to NuGet package manager, either by right clicking on Dll project and choosing “Manage NuGet packages” or opening console. Then browse for next packages and install them:

• Microsoft.EntityFrameworkCore.Design
• Oracle.EntityFrameworkCore

Next we will add a few folders that will structure our application in order to implement our design patterns and organize it properly.
First add next 4 folders:

• Context
• Entities
• Migrations
• Repositories

And in Repositories add Abstract and Implementation folders. These folders will hold our abstract repositories for accessing our data. Context folder will hold our generated Context class, Migrations folder will hold our migrations and Entities will hold our generated classes/models for data access. Project structure should look like this.

Next we will generate our Context class and Entities. Just to mention, we are generating context and entities from existing database, which means that we are scaffolding from existing database. Initially if we did not have already existing database, we would just create those classes and execute our first migration that will create new tables in our database.

First we need to install dotnet cli. For more reference check this official link:

Go to package manager console, choose .Dll project and execute this command:

So, now we will generate our context and entity classes from our existing database schema on Oracle 12c release 2 (12.2). To do that we need to use dotnet ef command with specifying of couple parameters:
-o tells us what output directory of our Dll project will entities be placed in. In our case it is Entities

• -context-dir tells us what output directory of our Dll project will context be placet. In our case it is Context folder
  -c tells what will be name of our
  -p tells us on what project command will execute. In our case it id Dll project
  -f tells us that we do not need to confirm our scaffolding

Whole command should look like this:

dotnet ef dbcontext scaffold "User Id=EXAMPLE_SCHEMA;Password=my_password;Data Source=server:1521/orcl;Connection Timeout=600;min pool size=0;connection lifetime=18000;PERSIST SECURITY INFO=True;" Oracle.EntityFrameworkCore -o Entities --context-dir Context -c EXAMPLE_SCHEMA_Context -f -p NTierOracleIdentityExmple.Dll

After we execute command we will generate context class, entities and migration in our folders that we have created.

Also there is a shortcut to dotnet ef and that is EF Core Power Tools that can be found on next link.

It can be installed as and VS2019 extension that can generate needed stuff either by scaffolding existing DB using code first or database first approach(create edmx file) and can generate migrations for existing code.
So lets assume that we have our schema already created in our database with some tables in it. So it might look like this.

After execution of previous command, our project should include next generated classes for our context and existing entities.

Next thing is to modify our Context. First install next package from NuGet:

• Microsoft.EntityFrameworkCore.Proxies
  https://stackoverflow.com/questions/55234943/what-is-the-equivalent-of-configuration-proxycreationenabled-in-ef-core
  Next modify OnConfiguring method in context and add next line that contain our connection string:

if (!optionsBuilder.IsConfigured)
{
//Proxy for navigation properties //Microsoft.EntityFrameworkCore.Proxies
optionsBuilder.UseLazyLoadingProxies().UseOracle(“User Id=SCHEMA_NAME;Password=xxxxx;Data Source=server:1521/SID;Connection Timeout=600;min pool size=0;connection lifetime=18000;PERSIST SECURITY INFO=True;”);
}

First our edit contains connection string that will be used from our context to connect and create/apply new migrations. Second change is change that is using UseLazyLoadingProxies that includes navigational properties from our context entities that we select. This is explained in next Stackoverflow question.

Our last change will include changing the OnModelCreating method. Add next line:

base.OnModelCreating(modelBuilder);

Now we are ready to create our first migration. Our first migration will include only one table that will represent our log table for logging data from our application. Later in next part we will create repository, service and controller actions for implementing CRUD functionalities over that table. Also we will create our CRUD functionalities over Identity table in part 3.

So first things first, lets define our table. Create next class in Entities folder of our DLL project.

public class Log
{
  public int pk { get; set; }
  public string CreatedBy { get; set; }
  public DateTime CreationDate { get; set; }
  public string ModifiedBy { get; set; }
  public DateTime ModifiedDate { get; set; }
  public DateTime? Date { get; set; }
  public string Value { get; set; }
}

Next thing is to create how our table is going to look when we create it in Oracle database schema. Open Context class from Context folder of our DLL project.
First add our table as DbSet.

public virtual DbSet<Log> Log{ get; set; }

Next is to define how our table is going to look when our context class build our entity. Add next code to OnModelCreating method.

modelBuilder.Entity<Log>(entity =>
{
  entity.HasKey(e => e.pk).HasName("LOG_PK");
  entity.ToTable("LOG");
  entity.Property(e => e.pk)
  .HasColumnType("NUMBER(10)")
  .HasColumnName("PK");
  entity.Property(e => e.CreatedBy)
  .HasMaxLength(256)
  .IsUnicode(false)
  .HasColumnName("CREATED_BY");
  entity.Property(e => e.CreationDate)
  .HasColumnType("DATE")
  .HasColumnName("CREATION_DATE");
  entity.Property(e => e.ModifiedBy)
  .HasMaxLength(256)
  .IsUnicode(false)
  .HasColumnName("MODIFIED_BY");
  entity.Property(e => e.ModifiedDate)
  .HasColumnType("DATE")
  .HasColumnName("MODIFIED_DATE");
  entity.Property(e => e.Value)
  .IsUnicode(false)
  .HasColumnName("LOG_VALUE");
});

Only thing remaining is to create our first migration and update database. Lets create our DATA_LOG migration.

dotnet ef migrations add EXAMPLE_SCHEMA_LOG -p NTierOracleIdentityExmple.Dll -c NTierOracleIdentityExample.Dll.Context.EXAMPLE_SCHEMA_Context -o Migrations

After executing this command, our migration class will be created in Migrations folder of our DLL project.

Now lets update our database

dotnet ef database update EXAMPLE_SCHEMA_LOG -p NTierOracleIdentityExmple.Dll -c NTierOracleIdentityExample.Dll.Context.EXAMPLE_SCHEMA_Context

Now if you open the Oracle database from any client tool like SQLDeveloper or Toad you will see our table created, with migration history table.

That is it for now. Next part will be explaining how to create base repository, services for our CRUD operations with couple of design patterns.iting here...

ASP.NET Core MVC with .NET 5.0 ORACLE, Identity using N-tier architecture-Part 02

Published Jan 11, 2022

In this part of our tutorial, we will dive into creating basic project structure for N-tier project including creating base repository and creating data layer, creating service layer and using all of newly created stuff in our Web project.
First things first, in previous part we have created our context with entities, explained how to create migration (code first approach) with Oracle database using EFCore and how to scaffold existing database entities into our project (database first approach). Now we will put everything in use through couple of design patterns in multiple layers.

Data logic layer and Repository Pattern

First pattern that we will implement is Repository pattern in our data-access layer. Our code will create generic IBaseRepository with CRUD operations that contains INSERT, UPDATE, DELETE and SELECT operations and later we are going to add implementation of IBaseRepository.

Entity Framework already implements the repository pattern as we can access, add, modify and delete entities through DbSet. We are creating generic IBaseRepository interface and implementation of that repository to avoid boilerplate code for CRUD operations of every repostory for each entity.

Now lets create generic interface IBaseRepository with basic CRUD operation.
Add IBaseRepository interface class in Repositores/Abstract folder of DLL project:

public interface IBaseRepository<T> where T : class
{
  #region Select methods
  Task<T> SelectById(int id);
  Task<List<T>> SelectAll();
  #endregion
  #region Insert methods
  Task<T> Insert(T entity);
  #endregion
  #region Update methods
   void Update(T entity);
  #endregion
  #region Delete methods
  void Delete(int Id);
  #endregion
  #region Other methods
  void SaveChanges();
  #endregion
}

The IBaseRepository interface accept one generic type T. T as defined must be a class, so it must be entity just like one from our Entities folder in DLL project. As previously explained our generic IBaseRepository interface is defining base CRUD operations that we will implement later in our BaseRepository implementation. Lets create implementation of our generic IBaseRepository.
Add BaseRepository implementation in Repositores/Implementation folder of DLL project:

public class BaseRepository<T> : IBaseRepository<T> where T : class
{
  #region Fields
  protected readonly DbContext _context;
  protected readonly DbSet<T> dbSet;
  #endregion
  #region Constructor
  protected BaseRepository(DbContext context)
  {
    _context = context;
    dbSet = context.Set<T>();
  }
  #endregion
  #region Select methods
  public virtual async Task<T> SelectById(int id)
  {
    return await dbSet.FindAsync(id);
  }
  public virtual async Task<List<T>> SelectAll()
  {
    return await dbSet.ToListAsync();
  }
  #endregion
  #region Insert methods
  public virtual async Task<T> Insert(T entity)
  {
   var addedEntity = (await dbSet.AddAsync(entity)).Entity;
   await _context.SaveChangesAsync();
   return addedEntity;
  }
  #endregion
  #region Update methods
  public virtual async void Update(T entity)
  {
   dbSet.Update(entity);
   await _context.SaveChangesAsync();
  }
  #endregion
  #region Delete methods
  public async void Delete(int Id)
  {
   T entity = dbSet.Find(Id);
   var removedEntity = dbSet.Remove(entity).Entity;
   await _context.SaveChangesAsync();
  }
  #endregion
  #region Other methods
  public void SaveChanges()
  {
    _context.SaveChanges();
  }
  #endregion
}

Your project structure should look like this.

Now, next step would be for us to implement entity repository for our database entities. In previous chapter we have created two entities: Log and ExampleTable. Lets create interfaces for our entity repositories and implementation of those repositories. First add ILogRepository interface in Repositores/Abstract folder of DLL project:

public interface ILogRepository : IBaseRepository<Log>, IDisposable
{
 //Additional methods or override ones from BaseRepository
 #region Select methods
 #endregion
 #region Insert methods
 #endregion
 #region Update methods
 #endregion
 #region Delete methods
 #endregion
 #region Other methods
 #endregion
}

Next, Add LogRepository implementation in Repositores/Implementation folder of DLL project:

public class LogRepository : BaseRepository<Log>, ILogRepository
{
 //Implement additional methods or override ones from BaseRepository and implement them
 #region Select methods
 public LogRepository(EXAMPLE_SCHEMA_Context context) : base(context)
 { }
 #endregion
#region Select methods
#endregion
#region Insert methods
#endregion
#region Update methods
#endregion
#region Delete methods
#endregion
#region Other methods
public void Dispose()
 {
 _context.Dispose();
 }
#endregion
}

Now lets do the same for our second entity ExampleTable. First add IExampleTableRepository interface in Repositores/Abstract folder of DLL project:

public interface IExampleTableRepository : IBaseRepository<ExampleTable>, IDisposable
{
 //Additional methods or override ones from BaseRepository
 #region Select methods
 #endregion
 #region Insert methods
 #endregion
 #region Update methods
 #endregion
 #region Delete methods
 #endregion
 #region Other methods
 #endregion
}

Next, Add ExampleTableRepository implementation in Repositores/Implementation folder of DLL project:

public class ExampleTableRepository : BaseRepository<ExampleTable>, IExampleTableRepository
{
 //Implement additional methods or override ones from BaseRepository and implement them
 #region Select methods
 public ExampleTableRepository(EXAMPLE_SCHEMA_Context context) :  base(context)
 { }
 #endregion
 #region Select methods
 #endregion
 #region Insert methods
 #endregion
 #region Update methods
 #endregion
 #region Delete methods
 #endregion
 #region Other methods
 public void Dispose()
 {
   _context.Dispose();
 }
 #endregion
}

As we can see, we are extending BaseRepository in our entity repositories. In ILogRepository or any other interface we can add additional methods that are applicable only for that entity. That is why we are extending LogRepository as BaseRepository and implementing ILogRepository. BaseRepository contains basic CRUD operations and ILogRepository contains entity specific methods, procedures and other LINQ code.

Your project structure should look like this.

Business logic layer and Services

Now that we have implemented the repositories in our DLL project it is time to move to Business logic layer project to implement our services which will implement Unit of Work pattern. Unit of work pattern tracks a business transaction and translate that transaction into database transaction. For example if we have ten steps for our business transaction, and those steps are connected with business logic in some way, we can collectively run that transaction as one unit.
Implementation of Unit of Work pattern should look like this.

So lets create ILogService, IExampleTableServices interfaces and LogService, ExampleTableService as implementation of those interfaces.

public interface ILogService
{
 #region Select methods
 Task<List<Log>> SelectLog();
 Task<Log> SelectLogById(int Id);
 #endregion
 #region Insert methods
 void InsertLog(Log entity);
 #endregion
 #region Update methods
 void UpdateLog(Log entity);
 #endregion
 #region Delete methods
 void DeleteLog(int Id);
 #endregion
}
public class LogService : ILogService
{
 //Service can have multiple repositories implementing UoW(Unit oof Work) design pattern.
 #region Fields
 private readonly ILogRepository _logRepository;
 #endregion
 #region Constructor
 public LogService(ILogRepository logRepository)
 {
  _logRepository = logRepository;
 }
 #endregion
 #region Select methods
 public Task<List<Log>> SelectLog()
 {
   return _logRepository.SelectAll();
 }
 public Task<Log> SelectLogById(int Id)
 {
  return _logRepository.SelectById(Id);
 }
 #endregion
 #region Insert methods
 public void InsertLog(Log entity)
 {
   _logRepository.Insert(entity);
 }
 #endregion
 #region Update methods
 public void UpdateLog(Log entity)
 {
   _logRepository.Update(entity);
 }
 #endregion
 #region Delete methods
 public void DeleteLog(int Id)
 {
   _logRepository.Delete(Id);
 }
 #endregion
 #region Other methods
 #endregion
}
public interface IExampleTableService
{
 #region Select methods
 Task<List<ExampleTable>> SelectExampleTable();
 Task<ExampleTable> SelectExampleTableById(int Id);
 #endregion
 #region Insert methods
 void InsertExampleTable(ExampleTable entity);
 #endregion
 #region Update methods
 void UpdateExampleTable(ExampleTable entity);
 #endregion
 #region Delete methods
 void DeleteExampleTable(int Id);
 #endregion
}
public class ExampleTableService : IExampleTableService
{
 //Service can have multiple repositories implementing UoW(Unit oof Work) design pattern.
 #region Fields
 private readonly IExampleTableRepository _exampleTableRepository;
 #endregion
 #region Constructor
 public ExampleTableService(IExampleTableRepository ExampleTableRepository)
 {
   _exampleTableRepository = ExampleTableRepository;
 }
 #endregion
 #region Select methods
 public Task<List<ExampleTable>> SelectExampleTable()
 {
   return _exampleTableRepository.SelectAll();
 }
 public Task<ExampleTable> SelectExampleTableById(int Id)
 {
   return _exampleTableRepository.SelectById(Id);
 }
 #endregion
 #region Insert methods
 public void InsertExampleTable(ExampleTable entity)
 {
   _exampleTableRepository.Insert(entity);
 }
 #endregion
 #region Update methods
 public void UpdateExampleTable(ExampleTable entity)
 {
   _exampleTableRepository.Update(entity);
 }
 #endregion
 #region Delete methods
 public void DeleteExampleTable(int Id)
 {
   _exampleTableRepository.Delete(Id);
 }
 #endregion
 #region Other methods
 #endregion
}

Our BLL project structure should look like this:

That is it. Unit of work consist of multiple repositories that implement some business logic, incorporating all of them in single service.

Dependency Injection

So what is our next step? Next we are going to use our repositories and services in our controllers. We are going to inject repositories into our services and later those services into our controllers which is a technique for achieving Inversion of Control (IoC) between classes and their dependencies. There are multiple libraries that allow us to inject needed dependencies of a class, but we will use standard ASP.NET CORE DI framework for injecting our dependencies.

Before we dive into code, lets explain 3 lifetime modes for service being injected:
Scoped - lifetime services are created once per request within the scope.
Transient - lifetime services are created each time they are requested.
Singleton - which creates a single instance throughout the application. It creates the instance for the first time and reuses the same object in the all calls.

Lets open Startup.cs class and in ConfigureServices method inject our DBContext and needed services and repositories.

public void ConfigureServices(IServiceCollection services)
{
 services.AddControllersWithViews();
 //Dependency injection   services.AddEntityFrameworkOracle().AddDbContext<EXAMPLE_SCHEMA_Cont ext>();  services.AddScoped(typeof(IBaseRepository<>),typeof(BaseRepository<> ));  services.AddScoped(typeof(ILogRepository),typeof(LogRepository));  services.AddScoped(typeof(IExampleTableRepository),typeof(ExampleTab leRepository));
}

AutoMapper mapping between DLL entities and ViewModels

Next, problem that we are going to solve is problem of mapping of our DLL entities that are generated either by code first or database first approach. Every time we need to modify our entity we loose DataAnotations and we need to apply that change again, or we need to add new properties to every entity where we are copying data and change our logic for copying one object to another in our code (Entity to EntityViewModel and vice versa).

The AutoMapper in C# is a mapper between two objects. It maps the properties of two different objects by transforming the input object of one type to the output object of another type.

Lets first install AutoMapper either through GUI on our web project by Right Click on Dependencies of Web project and then Manage NuGet Packages

or executing following PM console commands on Web project:

• Install-Package AutoMapper -Version 11.0.0
• Install-Package AutoMapper.Extensions.Microsoft.DependencyInjection -Version 11.0.0

Next in our Startup.cs class in ConfigureServices method add next code:

public void ConfigureServices(IServiceCollection services)
{
 …
  //AutoMapper setup
  services.AddAutoMapper(typeof(Startup));
 …
 
 …
  //Dependency injection
  services.AddSingleton(provider => new MapperConfiguration(cfg =>
  {
    cfg.AddProfile(new EXAMPLE_SCHEMA_Profile());
  }).CreateMapper());
 …
}

Next, lets create ViewModel for our Log and ExampleTable entity classes:

public class LogViewModel
{
 public int pk { get; set; }
 public string CreatedBy { get; set; }
 public DateTime CreationDate { get; set; }
 public string ModifiedBy { get; set; }
 public DateTime ModifiedDate { get; set; }
 public DateTime? Date { get; set; }
 public string Value { get; set; }
}
public class ExampleTableViewModel
{
 public int Pk { get; set; }
 public string CreatedBy { get; set; }
 public DateTime CreationDate { get; set; }
 public string ModifiedBy { get; set; }
 public DateTime ModifiedDate { get; set; }
 public string Name { get; set; }
}

Next lets create AutoMapperConfigurations folder in NTierOracleIdentityExample.Web project. Then create new profile called EXAMPLE_SCHEMA_Profile class in newly created folder:

public class EXAMPLE_SCHEMA_Profile : Profile
{
 public EXAMPLE_SCHEMA_Profile()
 {
   CreateMap<Log, LogViewModel>().ReverseMap();
   CreateMap<ExampleTable, ExampleTableViewModel>().ReverseMap();
 }
}

As you can see from previous code, we need to map each property of entity class to the correspondent ViewModel class using AutoMapper.

Note: When the property names are different in Source and Destination types, then by default the C# Automapper will not map those properties.

Our Web project structure should look like this:

Wrapping up Controller

Last, lets add some code to our HomeController. We will inject ILogService and IMapper interfaces in contructor of HomeController. Next we will add some dummy methods for CRUD operations of our service, like GetLog, GetLogById, EditLog and DeleteLog.

public class HomeController : Controller
{
 #region Fields
 private readonly IMapper _mapper;
 private readonly ILogService _logService;
 #endregion
 #region Constructor
 public HomeController(ILogService logService, IMapper mapper)
 {
   _logService = logService;
   _mapper = mapper;
 }
 #endregion
 #region GET methods
 public IActionResult Index()
 {
   return View();
 }
 public JsonResult GetLog()
 {
   List<LogViewModel> logs = _mapper.Map<List<Log>,            List<LogViewModel>>(_logService.SelectLog().Result);
 
 var logList = from l in logs
 select new
 {
   Id = l.pk,
   LogDate = l.Date,
   LogValue = l.Value
 };
  return Json(new { status = “success”, records = logList.OrderBy(l => l.Id).ToList(), total = logList.Count() });
 }
 public JsonResult GetLogById(int pk)
 {
 LogViewModel log = _mapper.Map<Log, LogViewModel>(_logService.SelectLogById(pk).Result);
  return Json(new { status = “success”, records = log });
 }
 #endregion
#region POST methods
 [HttpPost]
 public JsonResult EditLog(LogViewModel viewModel)
 {
  if (ModelState.IsValid)
  {
   //Map viewModel to model
   Log model = _mapper.Map<Log>(viewModel);
   model.ModifiedBy = “DummyUser”;
   model.ModifiedDate = DateTime.Now;
   _logService.UpdateLog(model);
  return Json(new
  {
   success = true,
   message = “Log saved!”
  });
  }
  else
  {
   return Json(new { success = false, message = “Error!” });
  }
 }
[HttpPost]
 public JsonResult DeleteLog(int pk)
 {
 _logService.DeleteLog(pk);
 return Json(new { success = false, message = “Log deleted!” });
 }
#endregion
#region Helper methods
#endregion
}

So, that is it for this part. In next part we will cover the ASP.NET Identity with ORACLE database and how to create needed entities in database for Identity and how to implement it in our application. And finally run our application.