private DataTable GetTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training_2.employeeslist_sup", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "admin_ee_number", "number", "in", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "retresult_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetDataTable()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_fdic.get_evaluation_questions", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "questions_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private void GetCDAnswerList()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_fdic.get_evaluation_cd_answr", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "questions_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
foreach (DataRow dr in tbl.Rows)
{
ListItem li = new ListItem(dr["answer"].ToString(), dr["record_id"].ToString());
lbAnweres.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_trn_budget.budget_detail_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", Request.Params["ee"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ddlBudgetYear.SelectedItem.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "budget_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetGridTable()
{
DataTable tbl = null;
string strProcedureName = "BASDBA.pkg_training.History_List";
if (ViewState["Product_Code"].ToString() == "11001")
{
strProcedureName = "BASDBA.pkg_training_cdp.History_List";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "hstList_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
// private void FillProgramCode()
// {
// System.Data.DataTable dTable;
// Oracle.DataAccess.Client.OracleConnection conn= new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
// Oracle.DataAccess.Client.OracleCommand cmd =
// new Oracle.DataAccess.Client.OracleCommand("pkg_training.program_code_list",conn);
// cmd.CommandType = System.Data.CommandType.StoredProcedure;
//
// SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd,"list_","cursor","out","");
// Oracle.DataAccess.Client.OracleDataAdapter da =
// new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
// DataSet mds=new DataSet();
// conn.Open();
// try
// {
//
// da.Fill(mds,"Purpose");
// dTable = mds.Tables["Purpose"];
// ddlProgramCode.Items.Clear();
// ddlProgramCode.Items.Add(new ListItem("---- Select ----","-1"));
// foreach (DataRow dr in dTable.Rows)
// {
// ListItem li = new ListItem(dr["description"].ToString(),dr["program_code"].ToString());
// ddlProgramCode.Items.Add(li);
// }
// }
// finally
// {
// conn.Close();
// conn.Dispose();
// cmd.Dispose();
// da.Dispose();
// mds.Dispose();
// dTable=null;
// }
// if (ddlProgramCode.Items.Count==1)
// {
// ddlProgramCode.Enabled =false;
// lblStarProgramCode.Visible = false;
// rvProgramCode.Visible = false;
// lblProgramCodeCommingSoon.Visible = true;
// }
// }
private void FillAccountNo()
{
System.Data.DataTable dTable;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd =
new Oracle.DataAccess.Client.OracleCommand("pkg_training.account_no_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "list_", "cursor", "out", "");
Oracle.DataAccess.Client.OracleDataAdapter da =
new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataSet mds = new DataSet();
conn.Open();
try
{
da.Fill(mds, "Purpose");
dTable = mds.Tables["Purpose"];
opnlstAccountNumber.Items.Clear();
foreach (DataRow dr in dTable.Rows)
{
ListItem li = new ListItem(dr["description"].ToString(), dr["account_no"].ToString());
opnlstAccountNumber.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
mds.Dispose();
dTable = null;
}
}
public static DataTable employee_info(string strHeader_id)
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Report.Data.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_employee_3.ee_info_for_pla_report", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", strHeader_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
string strPY = "";
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
cmd.Dispose();
mds.Dispose();
conn.Close();
conn.Dispose();
}
return(tbl);
}
private DataTable GetExpenseTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.GetEmployeeExpenses", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Expenses_List_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetGridTable()
{
string strProcedureName = "BASDBA.pkg_training.term_pending_payment_list";
if (Request.Params["what"] == "2nd")
{
strProcedureName = "BASDBA.pkg_training.term_pending_2nd_sup_list";
}
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "super_user_employee_number_", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.getlearningcategories", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ViewState["Processing_Year"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "learningcategorylist_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_trn_budget.Items_expense_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "PLA_header_record_id", Request.Params["item"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "items_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private void FillExpenseType()
{
ddlExpenseType.Items.Clear();
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.GetExpenseTypes", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Expenses_List_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
foreach (DataRow dr in tbl.Rows)
{
ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString());
ddlExpenseType.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
tbl.Dispose();
}
}
public static DataTable FillDataDateTimes(string strRecord_id)
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Report.Data.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.gettrainingcoursedatetimerec", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", strRecord_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Step_3_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private void FillReasonCheckBoxList()
{
System.Data.DataTable dTable;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd =
new Oracle.DataAccess.Client.OracleCommand("pkg_training.reason_for_admin_partial_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_list_", "cursor", "out", "");
Oracle.DataAccess.Client.OracleDataAdapter da =
new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataSet mds = new DataSet();
conn.Open();
try
{
da.Fill(mds, "Purpose");
dTable = mds.Tables["Purpose"];
chklstReasons.Items.Clear();
foreach (DataRow dr in dTable.Rows)
{
ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString());
chklstReasons.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
mds.Dispose();
dTable = null;
}
}
public static DataTable GetHistoryTable(string strRecord_id)
{
DataTable tbl = null;
string strProcedureName = "BASDBA.pkg_training.History_List";
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Report.Data.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", strRecord_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "hstList_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetGridTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training.paid_request_list", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Adjust_Employee"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "paid_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetDataTable()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_training.ExpenseAdjustmentList", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expense_record_id_", Request.Params["recid"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "adjustmentList_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private DataTable GetTable3()
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.employee_Number_List3", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "search_name_", txtSearch.Text.Replace("'", "''"));
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "emails_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
RemoveSupervisor(tbl);
CleanUpTable(tbl);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
//返回DataTable的SQL执行
public DataTable ExecuteDataTable(string cmdText)
{
cmdText = SqlTransfer(cmdText);
DataTable dt = new DataTable();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr);
Oracle.DataAccess.Client.OracleDataAdapter apt = new Oracle.DataAccess.Client.OracleDataAdapter(cmdText, conn);
apt.SelectCommand.CommandType = CommandType.Text;
try
{
apt.Fill(dt);
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
throw exp;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
DataTableTransfer(cmdText, dt);
return(dt);
}
private void CopyVendorInfo()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.GetHeaderRecord", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
txtVedorName.Text = tbl.Rows[0]["vendor_name"].ToString();
txtVendorContact.Text = tbl.Rows[0]["vendor_contact"].ToString();
txtPhoneNumber.Text = tbl.Rows[0]["vendor_phone_number"].ToString();
txtFaxNumber.Text = tbl.Rows[0]["vendor_fax_number"].ToString();
txtAddressLine1.Text = tbl.Rows[0]["vendor_address1"].ToString();
txtAddressLine2.Text = tbl.Rows[0]["vendor_address2"].ToString();
txtCity.Text = tbl.Rows[0]["vendor_city"].ToString();
string strState = tbl.Rows[0]["vendor_state"].ToString();
txtZipCode.Text = tbl.Rows[0]["vendor_zip_code"].ToString();
txtProvince.Text = tbl.Rows[0]["province"].ToString();
string strCountry = tbl.Rows[0]["country_code"].ToString();
foreach (ListItem li in ddlContries.Items)
{
li.Selected = li.Value == strCountry;
}
for (int i = 0; i < ddlStates.Items.Count; i++)
{
if (ddlStates.Items[i].Value == strState)
{
ddlStates.SelectedIndex = i;
break;
}
}
txtState.Text = ddlStates.SelectedItem.Text;
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
//带参，带SQL执行类型，返回DataTable的SQL执行
public DataTable ExecuteDataTable(string cmdText, int statRowNum, int maxRowNum, CommandType cmdtype)
{
cmdText = SqlTransfer(cmdText);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
int dtflag = 0;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr);
Oracle.DataAccess.Client.OracleDataAdapter apt = new Oracle.DataAccess.Client.OracleDataAdapter(cmdText, conn);
apt.SelectCommand.CommandType = cmdtype;
try
{
if (statRowNum == 0 && maxRowNum == 0)
{
apt.Fill(dt);
}
else
{
apt.Fill(ds, statRowNum, maxRowNum, "ThisTable");
dtflag = 1;
}
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
throw exp;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
if (dtflag == 1)
{
dt = ds.Tables["ThisTable"];
}
DataTableTransfer(cmdText, dt);
return(dt);
}
private void FillData()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.GetHeaderRecord", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
string strPY = "";
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
if (tbl.Rows.Count != 0)
{
SetValue(txtCourseCode, tbl.Rows[0]["course_code"].ToString());
SetValue(txtCourseTitle, tbl.Rows[0]["course_title"].ToString());
SetValue(txtVedorName, tbl.Rows[0]["vendor_name"].ToString());
SetValue(txtVendorContact, tbl.Rows[0]["vendor_contact"].ToString());
SetValue(txtPhoneNumber, tbl.Rows[0]["vendor_phone_number"].ToString());
SetValue(txtFaxNumber, tbl.Rows[0]["vendor_fax_number"].ToString());
SetValue(txtAddressLine1, tbl.Rows[0]["vendor_address1"].ToString());
SetValue(txtAddressLine2, tbl.Rows[0]["vendor_address2"].ToString());
SetValue(txtCity, tbl.Rows[0]["vendor_city"].ToString());
txtState.Text = tbl.Rows[0]["vendor_state"].ToString();
SetValue(txtZipCode, tbl.Rows[0]["vendor_zip_code"].ToString());
SetValue(txtWebSite, tbl.Rows[0]["vendor_website"].ToString());
SetValue(txtDescribtion, tbl.Rows[0]["desription_of_course_value"].ToString());
SetValue(txtLearningAddress1, tbl.Rows[0]["training_address1"].ToString());
SetValue(txtLearningAddress2, tbl.Rows[0]["training_address2"].ToString());
SetValue(txtLearningCity, tbl.Rows[0]["training_city"].ToString());
txtLearningState.Text = tbl.Rows[0]["training_state"].ToString();
SetValue(txtLearningZipCode, tbl.Rows[0]["training_zip"].ToString());
ViewState["application_status"] = tbl.Rows[0]["application_status"].ToString();
ViewState["description"] = tbl.Rows[0]["description"].ToString();
strPY = tbl.Rows[0]["processing_year"].ToString();
ViewState["Application_Processing_Year"] = strPY;
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
public static void FillBudgetYears(DropDownList ddl, Label lbl, string strEmployeeNumber, string strDefaultBudgetYear)
{
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Data.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_budget.availablebalanceslist", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", strEmployeeNumber);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", strDefaultBudgetYear);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "budget_years_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
ddl.Items.Clear();
bool blnFound = false;
lbl.Text = "";
foreach (DataRow dr in tbl.Rows)
{
ListItem li = new ListItem(dr["budget_year"].ToString(), dr["budget_year"].ToString() + "_" + dr["total_remaining_amount"].ToString());
if (li.Text == strDefaultBudgetYear)
{
li.Selected = true;
lbl.Text = FormatedRemainingAmount(li, strEmployeeNumber);
blnFound = true;
}
ddl.Items.Add(li);
}
if ((ddl.Items.Count > 0) && (!blnFound))
{
ddl.SelectedIndex = 0;
lbl.Text = FormatedRemainingAmount(ddl.Items[0], strEmployeeNumber);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
private DataTable GetTable()
{
DataTable tbl = null;
if ((Request.Params["Inst"] == "4") || (Request.Params["Inst"] == "6") || (Request.Params["Inst"] == "7"))
{
return(GetTable2());
}
if (Request.Params["Inst"] == "5")
{
return(GetTable3());
}
string strProcedureName = "BASDBA.PKG_Training.employee_Number_List";
if (ViewState["Product_Code"].ToString() == "11001")
{
strProcedureName = "BASDBA.PKG_Training_cdp.employee_Number_List";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "list_type_", ViewState["List_Type"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "search_name_", txtSearch.Text.Replace("'", "''"));
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "emails_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
RemoveSupervisor(tbl);
if (ViewState["List_Type"].ToString() != "0")
{
CleanUpTable(tbl);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
}
return(tbl);
}
private void FillData()
{
string strCourseDateTime_id = GetCourseDateTime_Record_Id();
if (strCourseDateTime_id == "-1")
{
SetValue(txtStartDate, "");
SetValue(txtEndDate, "");
SetValue(txtCourseHoursDuty, "0");
SetValue(txtCourseHoursNonDuty, "0");
SetValue(txtCourseHoursTotal, "0");
return;
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.gettrainingcoursedatetimerec", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Step_3_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
SetValue(txtStartDate, tbl.Rows[0]["course_start_date"].ToString());
SetValue(txtEndDate, tbl.Rows[0]["course_end_date"].ToString());
string strCourseTime = tbl.Rows[0]["course_time"].ToString();
SetValue(txtCourseHoursDuty, tbl.Rows[0]["course_hours_duty"].ToString());
SetValue(txtCourseHoursNonDuty, tbl.Rows[0]["course_hours_non_duty"].ToString());
SetValue(txtCourseHoursTotal, tbl.Rows[0]["course_hours_total"].ToString());
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "CoursePY", Convert.ToDateTime(txtStartDate.Text).Year.ToString(), Training_Source.TrainingClass.ConnectioString);
}
private void FillData()
{
string strTypesAndNeeds = TrainingTypeAndNeedsRecord_Id();
if (strTypesAndNeeds == "-1")
{
ddlSourseTraining.SelectedIndex = 0;
ddlPurposeOfTraining.SelectedIndex = 0;
txtPurposeOfTainingOther.Text = "";
return;
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.TrainingTypesNeedsRecord", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Step_4_record", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
// SetDropDownIndex(ddlSourseTraining, tbl.Rows[0]["source_of_training"].ToString());
// SetDropDownIndex(ddlPurposeOfTraining, tbl.Rows[0]["purpose_of_training"].ToString());
SetValue(ddlSourseTraining, tbl.Rows[0]["source_of_training"].ToString());
SetValue(ddlPurposeOfTraining, tbl.Rows[0]["purpose_of_training"].ToString());
SetValue(txtProgramCode, tbl.Rows[0]["program_code_description"].ToString());
SetValue(opnlstAccountNumber, tbl.Rows[0]["account_no"].ToString());
SetValue(txtPurposeOfTainingOther, tbl.Rows[0]["purpose_of_training_other"].ToString());
SetValue(txtTypeofDevelopmentOthers, tbl.Rows[0]["type_of_development_other"].ToString());
SetValue(txtDepartmentID, tbl.Rows[0]["dept_id"].ToString());
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
private void FillSelectedTypeofDevelopment()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.PKG_Training.type_of_development_List", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataTable tbl = null;
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
int intColPos = 0;
int intRowPos = 0;
foreach (DataRow dr in tbl.Rows)
{
if (intColPos == 1)
{
tblTypeofDeve.Rows[intRowPos].Cells[1].Text = dr["description"].ToString();
intColPos = 0;
intRowPos++;
}
else
{
intColPos = 1;
tblTypeofDeve.Rows[intRowPos].Cells[0].Text = dr["description"].ToString();
}
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (tbl != null)
{
tbl.Dispose();
}
}
}
private DataTable GetData()
{
string strProcedureName = "pkg_training.reason_for_suprvsl_denial_list";
if (Request.Params["type"] == "2")
{
strProcedureName = "pkg_training.reason_for_supr_partial_list";
}
if (Request.Params["type"] == "3")
{
strProcedureName = "pkg_training.reason_for_admin_denial_list";
}
if (Request.Params["type"] == "4")
{
strProcedureName = "pkg_training.reason_for_admin_partial_list";
}
if (Request.Params["type"] == "5")
{
strProcedureName = "pkg_training_cdp.reason_for_suprvsl_denial_list";
}
System.Data.DataTable dTable;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd =
new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_list_", "cursor", "out", "");
Oracle.DataAccess.Client.OracleDataAdapter da =
new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataSet mds = new DataSet();
conn.Open();
try
{
da.Fill(mds, "Purpose");
dTable = mds.Tables["Purpose"];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
mds.Dispose();
}
return(dTable);
}
private void FillReasonCheckBoxList()
{
string strProcedureName = "pkg_training.reason_for_suprvsl_denial_list";
if (Request.Params["who"] == "payor")
{
strProcedureName = "pkg_training.reason_for_admin_denial_list";
}
else if (ViewState["Product_Code"].ToString() == "11001")
{
strProcedureName = "pkg_training_cdp.reason_for_suprvsl_denial_list";
}
System.Data.DataTable dTable;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd =
new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_list_", "cursor", "out", "");
Oracle.DataAccess.Client.OracleDataAdapter da =
new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
DataSet mds = new DataSet();
conn.Open();
try
{
da.Fill(mds, "Purpose");
dTable = mds.Tables["Purpose"];
chklstReasons.Items.Clear();
foreach (DataRow dr in dTable.Rows)
{
ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString());
chklstReasons.Items.Add(li);
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
mds.Dispose();
dTable = null;
}
}
private DataTable GetGridTable()
{
bool blnIsAll = ddlWhat.SelectedValue == "1000";
string steProcedureName = "BASDBA.pkg_training.pendig_payor_List";
if (blnIsAll)
{
steProcedureName = "BASDBA.pkg_training.payor_List";
}
DataTable tbl = null;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(steProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Supervisor_Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ViewState["Processing_Year"].ToString());
if (!blnIsAll)
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "application_status_", ddlWhat.SelectedValue);
}
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "pnding_list_", "cursor", "out", "");
DataSet mds = new DataSet();
Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
try
{
conn.Open();
da.Fill(mds);
tbl = mds.Tables[0];
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
mds.Dispose();
if (da != null)
{
da.Dispose();
}
}
return(tbl);
}
public override DataSet Retrieve()
{
if (!(Command is Oracle.DataAccess.Client.OracleCommand))
throw new Exception("Not a Valid SQL Command");
var command = Command as Oracle.DataAccess.Client.OracleCommand;
SetTimeOut(command);
foreach (Oracle.DataAccess.Client.OracleParameter parameter in command.Parameters)
{
if (parameter.Value == null)
{
parameter.Value = DBNull.Value;
}
}
try
{
command.Connection = new Oracle.DataAccess.Client.OracleConnection(connectionString);
command.Connection.Open();
var da = new Oracle.DataAccess.Client.OracleDataAdapter(command);
var ds = new DataSet();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
string exceptionMessage = GetOracleExceptionMessage(ex);
throw new Exception(exceptionMessage);
}
finally
{
command.Connection.Close();
}
}
