Bplus/ConvertLog/business/DataAccess.cs

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

namespace ConvertLog.common
{
    public class DataAccess
    {


        public DataAccess()
        {

        }




        public static DataSet importGetFile(string type)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.IMPORT_GET_FILE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_type", OracleDbType.NVarchar2),
                              new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = type;

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

        public static DataSet importFinishFile(string v_uload_id, string v_uload_detail_id, string v_total_row, string v_total_row_false)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.IMPORT_FINSH_FILE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_uload_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_uload_detail_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_total_row", OracleDbType.NVarchar2),
                                new OracleParameter("v_total_row_false", OracleDbType.NVarchar2),

                              new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_uload_id;
            parms[1].Value = v_uload_detail_id;
            parms[2].Value = v_total_row;
            parms[3].Value = v_total_row_false;

            return getDataFromProcedure(str, "", parms);
        }
        public static DataSet importFinishAllFile(string v_uload_id)
        {

            string str;
            str = "";
            str = "BALANCE_PKG.IMPORT_FINSH_ALL_FILE";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("v_uload_id", OracleDbType.NVarchar2),


                              new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_uload_id;

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

        public static void upload_data(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_upload_id = dataColumn[0].ToArray();
                    OracleParameter param0 = new OracleParameter("v_upload_id", OracleDbType.NVarchar2, ParameterDirection.Input);
                    param0.Value = v_upload_id;
                    param0.Size = v_upload_id.Length;
                    command.Parameters.Add(param0);

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

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

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



                    command.ExecuteNonQuery();



                    transaction.Commit();

                }

            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.ToString());
                throw;
            }
            finally
            {
                connection.Close();
            }
        }

        public static DataSet termIn(string V_TERM_ID, string V_NAME, string V_DISPLAY
            , string V_START_DATE, string V_END_DATE, string V_DRAW_DATE, string V_NOTE,
            string V_TOTAL_MONEY_DRAW_ON, string V_TOTAL_MONEY_DRAW_PREVIOUS_ON,
            string V_TOTAL_MONEY_DRAW_OFF, string V_TOTAL_MONEY_DRAW_PREVIOUS_OFF,
            string typeDraw, string limitJackport, string curentRevenue, string curentRevenueWin,
            string curentJackport, string remainJackport, string curentOther, string remainOther,
            string irdTerm


            )
        {

            string str;
            str = "";
            str = "LUCKY_PKG.TERM_INSERT";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {
                                new OracleParameter("V_TERM_ID", OracleDbType.NVarchar2),
                                new OracleParameter("V_NAME", OracleDbType.NVarchar2),
                                new OracleParameter("V_DISPLAY", OracleDbType.NVarchar2),
                                new OracleParameter("V_START_DATE", OracleDbType.NVarchar2),
                                new OracleParameter("V_END_DATE", OracleDbType.NVarchar2),
                                new OracleParameter("V_DRAW_DATE", OracleDbType.NVarchar2),
                                new OracleParameter("V_NOTE", OracleDbType.NVarchar2),

                                new OracleParameter("V_TOTAL_MONEY_DRAW_ON", OracleDbType.NVarchar2),
                                new OracleParameter("V_TOTAL_MONEY_DRAW_PRE_ON", OracleDbType.NVarchar2),
                                new OracleParameter("V_TOTAL_MONEY_DRAW_OFF", OracleDbType.NVarchar2),
                                new OracleParameter("V_TOTAL_MONEY_DRAW_PRE_OFF", OracleDbType.NVarchar2),

                                new OracleParameter("V_typeDraw", OracleDbType.NVarchar2),
                                new OracleParameter("V_limitJackport", OracleDbType.NVarchar2),
                                new OracleParameter("V_curentRevenue", OracleDbType.NVarchar2),
                                new OracleParameter("V_curentRevenueWin", OracleDbType.NVarchar2),
                                new OracleParameter("V_curentJackport", OracleDbType.NVarchar2),
                                new OracleParameter("V_remainJackport", OracleDbType.NVarchar2),
                                new OracleParameter("V_curentOther", OracleDbType.NVarchar2),
                                new OracleParameter("V_remainOther", OracleDbType.NVarchar2),
                                new OracleParameter("V_irdTerm", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = V_TERM_ID;
            parms[1].Value = V_NAME;
            parms[2].Value = V_DISPLAY;
            parms[3].Value = V_START_DATE;
            parms[4].Value = V_END_DATE;
            parms[5].Value = V_DRAW_DATE;
            parms[6].Value = V_NOTE;
            parms[7].Value = V_TOTAL_MONEY_DRAW_ON;
            parms[8].Value = V_TOTAL_MONEY_DRAW_PREVIOUS_ON;
            parms[9].Value = V_TOTAL_MONEY_DRAW_OFF;
            parms[10].Value = V_TOTAL_MONEY_DRAW_PREVIOUS_OFF;

            parms[11].Value = typeDraw;
            parms[12].Value = limitJackport;
            parms[13].Value = curentRevenue;
            parms[14].Value = curentRevenueWin;
            parms[15].Value = curentJackport;
            parms[16].Value = remainJackport;
            parms[17].Value = curentOther;
            parms[18].Value = remainOther;
            parms[19].Value = irdTerm;

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


        public static DataSet get_data_sub(string v_ap_id, string v_total_row)
        {

            string str;
            str = "";
            str = "SYN_SUB_PKG.get_data";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_ap_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_total_row", OracleDbType.NVarchar2),
                               
                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_ap_id;
            parms[1].Value = v_total_row;
           


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


        public static DataSet update_sub(string v_id, string v_errCode, string v_errMsg)
        {

            string str;
            str = "";
            str = "SYN_SUB_PKG.syn_update";
            OracleParameter[] parms;
            parms = new OracleParameter[]
                            {

                                new OracleParameter("v_id", OracleDbType.NVarchar2),
                                new OracleParameter("v_errCode", OracleDbType.NVarchar2),
                                new OracleParameter("v_errMsg", OracleDbType.NVarchar2),

                                new OracleParameter("P_RESULT",OracleDbType.RefCursor,ParameterDirection.Output),
            };
            parms[0].Value = v_id;
            parms[1].Value = v_errCode;
            parms[2].Value = v_errMsg;



            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 =ConfigData.GetValuesAppSetting("app", "DbConnection"); ;
          
            OracleConnection dbConn = new OracleConnection(conn);
            return dbConn;
        }








    }
}