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> 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; } } }