Bplus/ApiWeb/ApiProcessToken/Models/DataAccess.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using System.Configuration;
namespace ResfullApi.Models
{
    public class DataAccess
    {
       
        public DataAccess()
        {

        }



        public static void updateCharge(string sSQL, List<List<string>> dataColumn)
        {
            OracleConnection connection = null;
            try
            {

                connection = getPoolingConnection();
                connection.Open();
                OracleTransaction transaction;

                // Start a local transaction
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sSQL;
                    command.CommandType = CommandType.StoredProcedure;
                    command.ArrayBindCount = dataColumn[0].Count;
                    command.Transaction = transaction;




                    var v_username = dataColumn[0].ToArray();
                    OracleParameter param0 = new OracleParameter("v_username", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param0.Value = v_username;
                    param0.Size = v_username.Length;
                    command.Parameters.Add(param0);

                    var v_password = dataColumn[1].ToArray();
                    OracleParameter param1 = new OracleParameter("v_password", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param1.Value = v_password;
                    param1.Size = v_password.Length;
                    command.Parameters.Add(param1);

                    var v_serviceid = dataColumn[2].ToArray();
                    OracleParameter param2 = new OracleParameter("v_serviceid", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param2.Value = v_serviceid;
                    param2.Size = v_serviceid.Length;
                    command.Parameters.Add(param2);


                    var v_msisdn = dataColumn[3].ToArray();
                    OracleParameter param3 = new OracleParameter("v_msisdn", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param3.Value = v_msisdn;
                    param3.Size = v_msisdn.Length;
                    command.Parameters.Add(param3);

                    var v_chargetime = dataColumn[4].ToArray();
                    OracleParameter param4 = new OracleParameter("v_chargetime", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param4.Value = v_chargetime;
                    param4.Size = v_chargetime.Length;
                    command.Parameters.Add(param4);

                    var v_paramss = dataColumn[5].ToArray();
                    OracleParameter param5 = new OracleParameter("v_paramss", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param5.Value = v_paramss;
                    param5.Size = v_paramss.Length;
                    command.Parameters.Add(param5);

                    var v_mode = dataColumn[6].ToArray();
                    OracleParameter param6 = new OracleParameter("v_mode", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param6.Value = v_mode;
                    param6.Size = v_mode.Length;
                    command.Parameters.Add(param6);

                    var v_amount = dataColumn[7].ToArray();
                    OracleParameter param7 = new OracleParameter("v_amount", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param7.Value = v_amount;
                    param7.Size = v_amount.Length;
                    command.Parameters.Add(param7);

                    var v_prefix = dataColumn[8].ToArray();
                    OracleParameter param8 = new OracleParameter("v_prefix", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param8.Value = v_prefix;
                    param8.Size = v_prefix.Length;
                    command.Parameters.Add(param8);

                    var v_command = dataColumn[9].ToArray();
                    OracleParameter param9 = new OracleParameter("v_command", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param9.Value = v_command;
                    param9.Size = v_command.Length;
                    command.Parameters.Add(param9);

                    var v_transaction = dataColumn[10].ToArray();
                    OracleParameter param10 = new OracleParameter("v_transaction", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param10.Value = v_transaction;
                    param10.Size = v_transaction.Length;
                    command.Parameters.Add(param10);


                    command.ExecuteNonQuery();



                    transaction.Commit();

                }

            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }



        //coi nhu update cac reqeust tu MPS vao DB
        public static DataSet UPDATE_MPS_DB(string msisdn, string svId, string subService, string requestId, string otp, string responseCode, string moneyCharge, string resultCall)
        {
            // {"title":"xxxx","serviceId":"30","content":"cong hoa xa hoi chu nghia","dateSend":"01/12/2020 01:01:10"}
            string str;
            str = "";
            str = "MPS_PKG.UPDATE_RESPONSE_MPS";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_msisdn", OracleDbType.NVarchar2),
                                new OracleParameter("v_serviceID", OracleDbType.NVarchar2),
                                new OracleParameter("v_subServiceCode", OracleDbType.NVarchar2),
                                new OracleParameter("v_requestID", OracleDbType.NVarchar2),
                                new OracleParameter("v_otp", OracleDbType.NVarchar2),
                                new OracleParameter("v_responseCode", OracleDbType.NVarchar2),
                                new OracleParameter("v_money", OracleDbType.NVarchar2),
                                new OracleParameter("v_fullResult", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = msisdn;
            parms[1].Value = svId;
            parms[2].Value = subService;
            parms[3].Value = requestId;
            parms[4].Value = otp;
            parms[5].Value = responseCode;
            parms[6].Value = moneyCharge;
            parms[7].Value = resultCall;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }

        public static DataSet MPS_GET_BY_ID(string id)
        {
            // {"title":"xxxx","serviceId":"30","content":"cong hoa xa hoi chu nghia","dateSend":"01/12/2020 01:01:10"}
            string str;
            str = "";
            str = "MPS_PKG.MPS_GET_BY_ID";
            OracleParameter[] parms;
            parms = new OracleParameter[] 
							{
                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
			};
            parms[0].Value = id;
        

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet SEND_OTP(string msisdn,string serviceId,string channel,string language)
        {
            // {"title":"xxxx","serviceId":"30","content":"cong hoa xa hoi chu nghia","dateSend":"01/12/2020 01:01:10"}
            string str;
            str = "";
            str = "MPS_PKG.OTP_SEND";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_user", OracleDbType.NVarchar2),
                                new OracleParameter("v_serviceId", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),
                                new OracleParameter("v_language", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = msisdn;
            parms[1].Value = serviceId;
            parms[2].Value = channel;
            parms[3].Value = language;


            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet CONFIRM_OTP(string msisdn, string serviceId, string channel, string language,string otp)
        {
            // {"title":"xxxx","serviceId":"30","content":"cong hoa xa hoi chu nghia","dateSend":"01/12/2020 01:01:10"}
            string str;
            str = "";
            str = "MPS_PKG.OTP_CONFIRM";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_user", OracleDbType.NVarchar2),
                                new OracleParameter("v_serviceId", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),
                                new OracleParameter("v_language", OracleDbType.NVarchar2),
                                new OracleParameter("v_otp", OracleDbType.NVarchar2),
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = msisdn;
            parms[1].Value = serviceId;
            parms[2].Value = channel;
            parms[3].Value = language;
            parms[4].Value = otp;

            return DataAccess.getDataFromProcedure(str, "", parms);
        }


        public static DataSet GET_ERR_MSG_BY_CODE(string errCode,string serviceId, string channel, string language)
        {
            // {"title":"xxxx","serviceId":"30","content":"cong hoa xa hoi chu nghia","dateSend":"01/12/2020 01:01:10"}
            string str;
            str = "";
            str = "MPS_PKG.GET_ERR_MSG_BY_CODE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_err_code", OracleDbType.NVarchar2),
                                new OracleParameter("v_serviceId", OracleDbType.NVarchar2),
                                new OracleParameter("v_channel", OracleDbType.NVarchar2),
                                new OracleParameter("v_language", OracleDbType.NVarchar2),
                                
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = errCode;
            parms[1].Value = serviceId;
            parms[2].Value = channel;
            parms[3].Value = language;
           

            return DataAccess.getDataFromProcedure(str, "", parms);
        }




        public static DataSet getDataFromProcedure(string sSQL, string sTableName, params OracleParameter[] cmdParms)
        {
            DataSet ds = new DataSet();
            using (OracleConnection dbConnection = getPoolingConnection())
            using (OracleCommand command = new OracleCommand(sSQL, dbConnection))
            using (OracleDataAdapter dataAdapter = new OracleDataAdapter(command))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 120;
                for (int i = 0; i < cmdParms.Length; i++)
                    command.Parameters.Add(cmdParms[i]);

                try
                {
                    if (sTableName != "")
                    {
                        dataAdapter.Fill(ds, sTableName);
                    }
                    else
                        dataAdapter.Fill(ds);
                }
                catch (OracleException)
                {
                    OracleConnection.ClearPool(dbConnection);
                    throw;
                }
            }
            return ds;
        }




        public static OracleConnection getPoolingConnection()
        {
            //string conn = ConfigurationSettings.AppSettings["Connection"];
            string conn = Common.GetValuesAppSetting("webConfig", "Connection");
            OracleConnection dbConn = new OracleConnection(conn);
            return dbConn;
        }





    }

}