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> 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 { connection.Close(); } } //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(); OracleConnection dbConnection = getPoolingConnection(); OracleDataAdapter dataAdapter; dataAdapter = new OracleDataAdapter(); try { dataAdapter.SelectCommand = new OracleCommand(sSQL, dbConnection); dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure; for (int i = 0; i < cmdParms.Length; i++) dataAdapter.SelectCommand.Parameters.Add(cmdParms[i]); if (sTableName != "") { dataAdapter.Fill(ds, sTableName); } else dataAdapter.Fill(ds); } catch (OracleException loadException) { throw loadException; } catch (Exception unException) { throw unException; } finally { dbConnection.Close(); } return ds; } public static OracleConnection getPoolingConnection() { //string conn = ConfigurationSettings.AppSettings["Connection"]; string conn = Common.GetValuesAppSetting("webConfig", "Connection"); OracleConnection dbConn = new OracleConnection(conn); return dbConn; } } }