Oracle Query Builder writen C# / .NET. (Stored Procedure,Functions,Tables,Queries)

OracleQueryBuilder.cs

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace MSU.Recourse.Extensions
{
    public class OracleQueryBuilder : IExecutableQuery
    {
        private readonly List<OracleParameter> _oracleParameters;
        private string _commandText;
        private CommandType _commandType;
        public OracleQueryBuilder()
        {
            _oracleParameters = new List<OracleParameter> { };
        }
        public CommandType GetCommandType()
        {
            return _commandType;
        }
        public object[] GetParameters()
        {
            return _oracleParameters.ToArray();
        }
        public string GetCommandText()
        {
            return _commandText;
        }
        public OracleQueryBuilder BuildCommandText(string commandText)
        {
            _commandText = commandText;
            _commandType = CommandType.Text;
            return this;
        }
        public OracleQueryBuilder BuildTableText(string tableName)
        {
            _commandText = tableName;
            _commandType = CommandType.TableDirect;
            return this;
        }
        public OracleQueryBuilder BuildStoreProcedure(string procedureName)
        {
            _commandText = procedureName;
            _commandType = CommandType.StoredProcedure;
            return this;
        }
        public OracleQueryBuilder BuildStoreProcedure(string packageName, string procedureName)
        {
            _commandText = $"{packageName}.{procedureName}";
            _commandType = CommandType.StoredProcedure;
            return this;
        }
        public OracleQueryBuilder AddInputParameter(string parameterName, object value, OracleDbType dbType)
        {
            _oracleParameters.Add(new OracleParameter
            {
                ParameterName = parameterName,
                Value = value,
                OracleDbType = dbType,
                Direction = ParameterDirection.Input
            });
            return this;
        }
        public OracleQueryBuilder AddInputParameter(int index, object value, OracleDbType dbType)
        {
            _oracleParameters.Add(new OracleParameter
            {
                Value = value,
                OracleDbType = dbType,
                Direction = ParameterDirection.Input
            });
            return this;
        }
        public OracleQueryBuilder AddInputParameter(object value, OracleDbType dbType)
        {
            _oracleParameters.Add(new OracleParameter
            {
                Value = value,
                OracleDbType = dbType,
                Direction = ParameterDirection.Input
            });
            return this;
        }
        public OracleQueryBuilder AddReturnParameter(OracleDbType dbType)
        {
            _oracleParameters.Insert(0, new OracleParameter
            {
                ParameterName = "ret",
                Direction = ParameterDirection.ReturnValue,
                OracleDbType = dbType,
            });
            return this;
        }
        public OracleQueryBuilder AddParameter(string parameterName, object value, OracleDbType dbType, ParameterDirection direction)
        {
            _oracleParameters.Add(new OracleParameter
            {
                ParameterName = parameterName,
                Value = value,
                Direction = direction,
                OracleDbType = dbType
            });
            return this;
        }
        public OracleQueryBuilder AddParameter(int index, object value, OracleDbType dbType, ParameterDirection direction)
        {
            _oracleParameters.Insert(index + 1, new OracleParameter
            {
                Value = value,
                OracleDbType = dbType,
                Direction = direction
            });
            return this;
        }
        public string GetPreparedCommandText()
        {
            StringBuilder stringBuilder = new StringBuilder();
            if (_commandType == CommandType.TableDirect)
            {
                stringBuilder.Append($"SELECT * FROM {_commandText}");
            }
            else if (_commandType == CommandType.Text)
            {
                stringBuilder.Append(_commandText);
            }
            else if (_commandType == CommandType.StoredProcedure)
            {
                stringBuilder.Append("BEGIN ");
                if (_oracleParameters.Any(i => i.Direction == ParameterDirection.ReturnValue))
                {
                    stringBuilder.Append($":ret := ");
                }
                stringBuilder.Append($"{_commandText} (");
                for (int i = 0; i < _oracleParameters.Count; i++)
                {
                    if (_oracleParameters[i].Direction != ParameterDirection.ReturnValue)
                    {
                        if (string.IsNullOrEmpty(_oracleParameters[i].ParameterName))
                        {
                            _oracleParameters[i].ParameterName = $"v{i}";
                            stringBuilder.Append($":v{i}");
                        }
                        else
                        {
                            stringBuilder.Append($"{_oracleParameters[i].ParameterName} => :{_oracleParameters[i].ParameterName}");
                        }
                        if (i != _oracleParameters.Count - 1)
                        {
                            stringBuilder.Append(",");
                        }
                    }
                }
                stringBuilder.Append("); END;");
            }
            return stringBuilder.ToString();
        }
        public TResult GetReturnValue<TResult>()
        {
            OracleParameter param = _oracleParameters[0];
            switch (param.OracleDbType)
            {
                case OracleDbType.Int16:
                    {
                        return (TResult)Convert.ChangeType(short.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Int32:
                    {
                        return (TResult)Convert.ChangeType(int.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Int64:
                    {
                        return (TResult)Convert.ChangeType(long.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Long:
                    {
                        return (TResult)Convert.ChangeType(long.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Single:
                    {
                        return (TResult)Convert.ChangeType(float.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Double:
                    {
                        return (TResult)Convert.ChangeType(double.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Decimal:
                    {
                        return (TResult)Convert.ChangeType(decimal.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.Date:
                    {
                        return (TResult)Convert.ChangeType(DateTime.Parse(param.Value.ToString()), typeof(TResult));
                    }
                case OracleDbType.TimeStamp:
                    {
                        return (TResult)Convert.ChangeType(DateTime.Parse(param.Value.ToString()), typeof(TResult));
                    }
                default:
                    return default;
            }
        }
    }
    public interface IExecutableQuery
    {
        string GetCommandText();
        string GetPreparedCommandText();
        CommandType GetCommandType();
        object[] GetParameters();
    }
}