.Net Core With Oracle Database Using Dapper

We start off by creating a project of .net core 3.1. Go to the NuGet Manager and try downloading and installing the following packages,

• Dapper [ Latest stable version ]
• Oracle.ManagedDataAccess.Core [ Latest Stable Version ] 

The installed arena of Nuget package Manager should like something like this,

 

 

Lets begin by creating a static class named Connection Manager. Here first we have to create our connection string which would look something like this. 

private static string connString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host id)(PORT=specified port number)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service name)));User Id = user Id; Password = your password; ";

We have to create our method which should connect to the database. Here we would be creating a static function named GetConnection which should return IDbConnection. 

public static IDbConnection GetConnection()
        {
            var conn = new OracleConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

An object is created with the reference to OracleConnection which is a member of Oracle.ManagedDataAccess.Client library which we have installed earlier through the nuget package manager. Now using this method we would be able to to connect to the database. As of now, we have made the functional procedure to connect to our oracle database so we should also make a function which should do the exact opposite, which is closing the existing connection with the database. Creating a CloseConnection() function which accepts a IDbConnection type object would do our job. 

public static void CloseConnection(IDbConnection conn)
        {
            if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Broken)
            {
                conn.Close();
            }
        }

So finally our ConnectionManager Class should look something like this.

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace CoreWithOracleDBDapperAsORM.Utility
{
    public static class ConnectionManager
    {
        private static string connString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host id)(PORT=specified port number)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service name)));User Id = user Id; Password = your password; ";

        public static IDbConnection GetConnection()
        {
            var conn = new OracleConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }

        public static void CloseConnection(IDbConnection conn)
        {
            if (conn.State == ConnectionState.Open || conn.State == ConnectionState.Broken)
            {
                conn.Close();
            }
        }
    }
}

Let's say we have a table in database named Student who has a first name , last name and roll number. So we create a class name student.

    public class Student
    {
        public int RollNumber { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

Now let's build our querybuilder who will execute all our query. For starters, we will create 3 functions which would be used for different purposes. 

 

Firstly let's create the GetList method which should take an empty object to return its type.

 

But first we need to create a generic method which would build our query based on the object that had been sent. Although we need to perform an additional task which is to create a method which would be returning the pascal form of the property of the object as in the database in oracle, the norm is to follow the Pascal case but in C# we usually use the camelCase to get our job done. Another thing is we need to create a method named GetColumnList which should take an object and return all of the properties as strings converted to the camel case. 

private static string GetColumnList(T entity)
       {
           string selectedColumns = "Select ";
           foreach (var prop in entity.GetType().GetProperties())
           {
               if (!prop.Name.Contains("_"))
               {
                   selectedColumns = selectedColumns + ConvertToPascalCase(prop.Name) + " AS " + prop.Name + ",";
               }
           }

           return selectedColumns + " From "+ ConvertToPascalCase(entity.GetType().Name);
       }

       private static string ConvertToPascalCase(string str)
       {
           return string.Concat(str.Select((x, i) => i > 0 && char.IsUpper(x) ? "_" + x.ToString() : x.ToString())).ToLower();
       }

Now as we have the supporting methods done, let's get back to our GetList Method. 

public static IEnumerable<T> GetList(T entity)
{
    IDbConnection connection = ConnectionManager.GetConnection();

    var result = connection.Query<T>(GetColumnList(entity));

    ConnectionManager.CloseConnection(connection);

    return result;
}

Similarly we need to create a method which would be returning only one row. 

public static T SingleOrDefault(T entity)
{
    IDbConnection connection = ConnectionManager.GetConnection();

    var result = connection.QueryFirstOrDefault<T>(GetColumnList(entity));

    ConnectionManager.CloseConnection(connection);

    return result;
}

Now we have to get to a method which would be performing an action like insert update or delete. 

public static int? ExecuteAction(string query)
{
    IDbConnection connection = ConnectionManager.GetConnection();

    var result = connection.Execute(query);

    ConnectionManager.CloseConnection(connection);

    return result;
}

Now let's look at the whole picture, 

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace CoreWithOracleDBDapperAsORM.Utility
{
    public static class QueryBuilder<T>
    {
        public static IEnumerable<T> GetList(T entity)
        {
            IDbConnection connection = ConnectionManager.GetConnection();

            var result = connection.Query<T>(GetColumnList(entity));

            ConnectionManager.CloseConnection(connection);

            return result;
        }

        public static T SingleOrDefault(T entity)
        {
            IDbConnection connection = ConnectionManager.GetConnection();

            var result = connection.QueryFirstOrDefault<T>(GetColumnList(entity));

            ConnectionManager.CloseConnection(connection);

            return result;
        }

        public static int? ExecuteAction(string query)
        {
            IDbConnection connection = ConnectionManager.GetConnection();

            var result = connection.Execute(query);

            ConnectionManager.CloseConnection(connection);

            return result;
        }

        private static string GetColumnList(T entity)
        {
            string selectedColumns = "Select ";
            foreach (var prop in entity.GetType().GetProperties())
            {
                if (!prop.Name.Contains("_"))
                {
                    selectedColumns = selectedColumns + ConvertToPascalCase(prop.Name) + " AS " + prop.Name + ",";
                }
            }

            return selectedColumns + " From " + ConvertToPascalCase(entity.GetType().Name);
        }

        private static string ConvertToPascalCase(string str)
        {
            return string.Concat(str.Select((x, i) => i > 0 && char.IsUpper(x) ? "_" + x.ToString() : x.ToString())).ToLower();
        }
    }
}

Now let's get back to our controller. 

using CoreWithOracleDBDapperAsORM.Model;
using CoreWithOracleDBDapperAsORM.Utility;
using Microsoft.AspNetCore.Mvc;

namespace CoreWithOracleDBDapperAsORM.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class StudentController : ControllerBase
    {
        [HttpGet]
        [Route("GetStudentList")]
        public object GetStudentList()
        {
            return QueryBuilder<Student>.GetList(new Student());
        }
    }
}

As this is a basic tutorial on how to connect your .net core application with oracle database with the help of Dapper, no repository or service pattern has been used to populate the data. When you do, it's entirely upon you which design pattern you follow to build up your application.

 

THANK YOU.