Oracle Safe Type Mapping .NET Data Access layer

using DAL.Common;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web;

namespace Presentation.Models
{
public class T_DataManager
{
/**** Method Implemented By Afzal Hossain Date:08-Dec-2019 ( Mobile NO: 01960319000 ) *****/

public OutputResult TExecuteNonQuery<T>(OracleConnection Conn, string SpName, params object[] parameters) where T : class, new()
{
OutputResult objResult = new OutputResult() { ResultId = 0, ErrorNo = 0, Message = "0", NoofRows = 0 };
try
{
using (var cmd = Conn.CreateCommand())
{
cmd.CommandText = SpName;
cmd.CommandType = CommandType.StoredProcedure;

if (parameters != null)
foreach (var p in parameters)
cmd.Parameters.Add(p);

Conn.Open();
cmd.ExecuteNonQuery();

objResult.ResultId = int.Parse(cmd.Parameters["oIdentityValue"].Value.ToString());
objResult.NoofRows = int.Parse(cmd.Parameters["oRowCount"].Value.ToString());

if (objResult.NoofRows > 0 && objResult.ResultId > 0)
{
objResult.Message = "Operation successful.";
}
else
{
if (objResult.ErrorNo == -1)
{
objResult.Message = "Operation failed.";
}
}
}
}
catch (Exception ex)
{
objResult.ExceptionError = ex.ToString();
objResult.ResultId = 0;
objResult.ErrorNo = -1;
objResult.Message = "Exception: Operation failed.";
}
finally
{
Conn.Close();
}
return objResult;
}
///****************** BASIC USAGES****************

//OutputResult result = new OutputResult();
//string SpName = @"XX_SP_SET_TestTable";
//DataManager dm = new DataManager();
//TEST_Table table = new TEST_Table() { TEST_ID = 0, TEXT_COLUMN = "Md. Afzal Hossain", NUMBER_COLUMN = 1234, DATETIME_COLUMN = "8-Dec-2019" };


//List<OracleParameter> parameterList = new List<OracleParameter>();
//parameterList.Add(new OracleParameter("SaveOption", 1));
//parameterList.Add(new OracleParameter("TEST_ID", table.TEST_ID));
//parameterList.Add(new OracleParameter("TEXT_COLUMN", table.TEXT_COLUMN));
//parameterList.Add(new OracleParameter("NUMBER_COLUMN", table.NUMBER_COLUMN));
//parameterList.Add(new OracleParameter("DATETIME_COLUMN", table.DATETIME_COLUMN));
//parameterList.Add(new OracleParameter("IdentityValue", OracleDbType.Int32, 20, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, null));
//parameterList.Add(new OracleParameter("RowCount", OracleDbType.Int32, 20, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, null));
//OracleParameter[] parameters = parameterList.ToArray();

//result = dm.TExecuteNonQuery<TEST_Table>(dm.getConnection_Oracle(), SpName, parameters);
// */
public List<T> ReturnListBySP<T>(OracleConnection Conn, string SpName, params object[] parameters) where T : class, new()
{
using (var command = Conn.CreateCommand())
{
command.CommandText = SpName;
command.CommandType = CommandType.StoredProcedure;
Conn.Open();
if (parameters != null)
foreach (var p in parameters)
command.Parameters.Add(p);

using (var reader = command.ExecuteReader())
{
var lst = new List<T>();
var lstColumns = new T().GetType().GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic).ToList();
while (reader.Read())
{
var newObject = new T();
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
PropertyInfo prop = lstColumns.FirstOrDefault(a => a.Name.ToLower().Equals(name.ToLower()));
if (prop == null)
{
continue;
}
var val = reader.IsDBNull(i) == true ? "" : reader[i];
prop.SetValue(newObject, val.ToString(), null);
}
lst.Add(newObject);
}
Conn.Close();
return lst;
}
}
}
// /*
//List<OracleParameter> parameterList = new List<OracleParameter>();
// parameterList.Add(new OracleParameter("pLoadOption", 1));
// parameterList.Add(new OracleParameter("pTest_ID", table.TEST_ID));
// parameterList.Add(new OracleParameter ("cResult_Cursor", OracleDbType.RefCursor, ParameterDirection.Output));
// OracleParameter[] parameters = parameterList.ToArray();

// var lst = dm.ReturnListBySP<ViewModelTEST_Table>(dm.getConnection_Oracle(), SpName, parameters);
// */


}
}