public DataTable GetDataProject(string PRO_ID) { DataTable dt = new DataTable(); OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); string query = "SELECT * FROM TB_PROJECT"; if (!string.IsNullOrEmpty(PRO_ID)) { query += "where 1=1"; if (!string.IsNullOrEmpty(PRO_ID)) { query += " and PRO_ID :PRO_ID"; } } using (OracleCommand com = new OracleCommand(query, con)) { if (!string.IsNullOrEmpty(PRO_ID)) { com.Parameters.Add(new OracleParameter("PRO_ID", PRO_ID)); } using (OracleDataAdapter da = new OracleDataAdapter(com)) { da.Fill(dt); } } } return(dt); }
public int INSERT_PROJECT() { int id = 0; OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("INSERT INTO TB_PROJECT (CITIZEN_ID,CATEGORY_ID,COUNTRY_ID,SUB_COUNTRY_ID,PROJECT_NAME,ADDRESS_PROJECT,START_DATE,END_DATE,EXPENSES,FUNDING,CERTIFICATE,SUMMARIZE_PROJECT,RESULT_TEACHING,RESULT_ACADEMIC,DIFFICULTY_PROJECT,RESULT_PROJECT,RESULT_RESEARCHING,RESULT_OTHER,COUNSEL,PDF_FILE) VALUES (:CITIZEN_ID,:CATEGORY_ID,:COUNTRY_ID,:SUB_COUNTRY_ID,:PROJECT_NAME,:ADDRESS_PROJECT,:START_DATE,:END_DATE,:EXPENSES,:FUNDING,:CERTIFICATE,:SUMMARIZE_PROJECT,:RESULT_TEACHING,:RESULT_ACADEMIC,:DIFFICULTY_PROJECT,:RESULT_PROJECT,:RESULT_RESEARCHING,:RESULT_OTHER,:COUNSEL,:PDF_FILE)", con)) { com.Parameters.Add(new OracleParameter("CITIZEN_ID", CITIZEN_ID)); com.Parameters.Add(new OracleParameter("CATEGORY_ID", CATEGORY_ID)); com.Parameters.Add(new OracleParameter("COUNTRY_ID", COUNTRY_ID)); com.Parameters.Add(new OracleParameter("SUB_COUNTRY_ID", SUB_COUNTRY_ID)); com.Parameters.Add(new OracleParameter("PROJECT_NAME", PROJECT_NAME)); com.Parameters.Add(new OracleParameter("ADDRESS_PROJECT", ADDRESS_PROJECT)); com.Parameters.Add(new OracleParameter("START_DATE", START_DATE)); com.Parameters.Add(new OracleParameter("END_DATE", END_DATE)); com.Parameters.Add(new OracleParameter("EXPENSES", EXPENSES)); com.Parameters.Add(new OracleParameter("FUNDING", FUNDING)); com.Parameters.Add(new OracleParameter("CERTIFICATE", CERTIFICATE)); com.Parameters.Add(new OracleParameter("SUMMARIZE_PROJECT", SUMMARIZE_PROJECT)); com.Parameters.Add(new OracleParameter("RESULT_TEACHING", RESULT_TEACHING)); com.Parameters.Add(new OracleParameter("RESULT_ACADEMIC", RESULT_ACADEMIC)); com.Parameters.Add(new OracleParameter("DIFFICULTY_PROJECT", DIFFICULTY_PROJECT)); com.Parameters.Add(new OracleParameter("RESULT_PROJECT", RESULT_PROJECT)); com.Parameters.Add(new OracleParameter("RESULT_RESEARCHING", RESULT_RESEARCHING)); com.Parameters.Add(new OracleParameter("RESULT_OTHER", RESULT_OTHER)); com.Parameters.Add(new OracleParameter("COUNSEL", COUNSEL)); com.Parameters.Add(new OracleParameter("PDF_FILE", PDF_FILE)); id = com.ExecuteNonQuery(); } } return(id); }
public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] commandParameters) { OracleCommand cmd = new OracleCommand(); OracleConnection conn = new OracleConnection(connectionString); try { OracleConnection.ClearAllPools(); PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); DataSet ds = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter(cmd); adapter.Fill(ds); return(ds); } catch { conn.Close(); throw; } finally { conn.Close(); } }
protected void Search_Click(object sender, EventArgs e) { string oracleConnectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString; OracleConnection con = new OracleConnection(oracleConnectionString); int ab = con.ConnectionTimeout; try { string query = QueryBuilderClass.BowlerQueryBuilder(DropTeam, EconomyDrop, Wickets_Taken, Name); OracleCommand cmd = new OracleCommand(query, con); con.Open(); OracleConnection.ClearAllPools(); OracleDataReader rdr = cmd.ExecuteReader(); BowlerSearchRes.DataSource = rdr; BowlerSearchRes.DataBind(); } catch (OracleException ex) { Response.Write("<br>/" + "<br>/" + "<br>/" + "<br>/" + "<br>/" + ex); } finally { con.Close(); } }
public static List <DateTime> GetLeaveDateTimeFromToDate(string citizenID) { OracleConnection.ClearAllPools(); List <DateTime> list = new List <DateTime>(); using (OracleConnection con = new OracleConnection(CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("SELECT FROM_DATE, TO_DATE FROM LEV_DATA WHERE PS_ID = '" + citizenID + "'", con)) { using (OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { DateTime start = reader.GetDateTime(0); DateTime to = reader.GetDateTime(1); while (true) { if (!list.Contains(start)) { list.Add(start); } start = start.AddDays(1); if ((to - start).TotalDays < 0) { break; } } } } } } return(list); }
protected void btnUpdateInsig_Click(object sender, EventArgs e) { string ValueDateInsig = tbInsertDateInsig.Text; if (Session["DefaultIdInsig"] == null) { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('กรุณาเลือกรายการที่จะแก้ไขก่อน')", true); return; } if (ValueDateInsig != "") { OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("UPDATE TB_INSIG_PERSON SET GET_DATE = :GET_DATE, IP_STATUS_ID = :IP_STATUS_ID WHERE IP_ID = :IP_ID", con)) { com.Parameters.Add(new OracleParameter("GET_DATE", Util.ToDateTimeOracle(tbInsertDateInsig.Text))); com.Parameters.Add(new OracleParameter("IP_STATUS_ID", ddlStatusID.SelectedValue)); com.Parameters.Add(new OracleParameter("IP_ID", Session["DefaultIdInsig"].ToString())); com.ExecuteNonQuery(); } } ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('อัพเดทข้อมูลเรียบร้อย')", true); BindInsig(); ClearInsig(); Session.Remove("DefaultIdInsig"); } }
public override async Task DeleteAsync(CancellationToken cancellationToken = default) { using (var masterConnection = _connection.CreateMasterConnection()) { retry: try { OracleConnection.ClearAllPools(); await Dependencies.MigrationCommandExecutor .ExecuteNonQueryAsync(CreateDropCommands(), masterConnection, cancellationToken); } catch (OracleException e) { if (e.Number == 1940 || e.Number == 31 || e.Number == 30 || e.Number == 26) { // ORA-01940: cannot drop a user that is currently connected // ORA-00031: session marked for kill // ORA-00030: User session ID does not exist // ORA-00026: missing or invalid session ID goto retry; } throw; } } }
public void SetUp() { if (this.connectionType == ConnectionType.Oracle) { OracleConnection.ClearAllPools(); } }
protected void lbuAllow_Click(object sender, EventArgs e) { if (rbAllow.Checked) { UPDATE_PERSON(); DataShow.Visible = false; Accept.Visible = true; //ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('บันทึกข้อมูลเรียบร้อย')", true); } else if (rbNotAllow.Checked) { OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("UPDATE TB_REQUEST SET DATE_END=:DATE_END, R_STATUS_ID=:R_STATUS_ID, COMMENT_INFO=:COMMENT_INFO, R_ALLOW=:R_ALLOW WHERE R_ID = '" + MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()) + "'", con)) { com.Parameters.Add(new OracleParameter("DATE_END", DateTime.Today)); com.Parameters.Add(new OracleParameter("R_STATUS_ID", "4")); com.Parameters.Add(new OracleParameter("COMMENT_INFO", tbComment.Text)); com.Parameters.Add(new OracleParameter("R_ALLOW", "2")); com.ExecuteNonQuery(); } } DataShow.Visible = false; NoAccept.Visible = true; } }
/// <summary> /// 针对性处理数据库异常 /// </summary> /// <param name="oraEx"></param> /// <param name="conn"></param> public static void HandleOraException(OracleException oraEx, OracleConnection conn) { if (oraEx == null) { return; } switch (oraEx.Number) { case 3113: //ORA-03113: 通信通道的文件结尾 //可能发生在重启数据库 OracleConnection.ClearAllPools(); throw new Exception("发生ORA-03113错误,已清空数据库连接池。", oraEx); case 28: //ORA-00028: 会话己被终止(session kill) - 发生在会话被Kill之后 case 1012: //ORA-01012: 没有登录(not logon) - 发生在ORA-00028后再访问数据库 case 2396: //ORA-02396: 超出最大空闲时间(exceeded maximum idle time) case 12535: //ORA-12535: TNS操作超时(TNS:operation timed out) if (conn != null) { OracleConnection.ClearPool(conn); throw new Exception("发生ORA-" + oraEx.Number.ToString() + "错误,已从数据库连接池清理掉当前连接。", oraEx); } else { OracleConnection.ClearAllPools(); throw new Exception("发生ORA-" + oraEx.Number.ToString() + "错误,已清空数据库连接池。", oraEx); } default: break; } //ORA-01089: 正在执行立即关闭 - 发生在数据库正在关闭 }
private void PrepareCommand(IDbCommand command) { try { if (Transaction == null && Connection.State == ConnectionState.Closed) { if (Connection.ConnectionString != ConnectionString) { Connection.ConnectionString = ConnectionString; } Connection.Open(); } } catch (Exception ex) { TraceSource.TraceEvent(TraceEventType.Error, 5110, "{0}", ex); try { Connection.Close(); } catch { } try { OracleConnection.ClearAllPools(); } catch (Exception cpex) { TraceSource.TraceEvent(TraceEventType.Error, 5111, "{0}", cpex); } if (Connection.State == ConnectionState.Closed) { if (Connection.ConnectionString != ConnectionString) { Connection.ConnectionString = ConnectionString; } Connection.Open(); } } command.Connection = Connection; command.Transaction = Transaction; }
private void fBack() { PersonnelSystem ps = PersonnelSystem.GetPersonnelSystem(this); Person loginPerson = ps.LoginPerson; int count = 0; OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("SELECT COUNT(LEAVE_ID) FROM LEV_LEAVE WHERE CMD_LOW_ID = '" + loginPerson.PS_CITIZEN_ID + "' AND LEV_LEAVE.STATE_ID = 1", con)) { using (OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { count = int.Parse(reader.GetValue(0).ToString()); } } } } if (count == 0) { error_area.InnerHtml = "ไม่มีรายการที่ท่านต้องลงความเห็น"; } else { error_area.InnerHtml = "กรุณาเลือกรายการที่ต้องการลงความเห็น"; } //error_area.Attributes["class"] = "alert alert_info"; error_area.Attributes["class"] = null; error_area.InnerHtml = ""; MultiView1.ActiveViewIndex = 0; }
/// <summary> /// 数据连接对象 /// </summary> /// <returns>OracleConnection数据连接对象</returns> private OracleConnection GetConnection() { int num = 0; for (int i = 0; i <= num; i++) { try { if (this._conn == null) { this._conn = new OracleConnection(this._connectionString); } if (this._conn.State == ConnectionState.Closed) { this._conn.Open(); } } catch (Exception ex) { OracleConnection.ClearAllPools(); num = 1; throw ex; } } return(this._conn); }
private void FuncGVCH() { OracleConnection.ClearAllPools(); SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT LEV_DATA.LEAVE_ID รหัสการลา, (SELECT LEAVE_TYPE_NAME FROM LEV_TYPE WHERE LEV_TYPE.LEAVE_TYPE_ID = LEV_DATA.LEAVE_TYPE_ID) ประเภทการลา, REQ_DATE วันที่ข้อมูล, (SELECT PS_FIRSTNAME || ' ' || PS_LASTNAME FROM PS_PERSON WHERE PS_CITIZEN_ID = LEV_DATA.PS_ID) ชื่อผู้ลา, (SELECT TB_POSITION_WORK.POSITION_WORK_NAME FROM TB_POSITION_WORK, PS_PERSON WHERE TB_POSITION_WORK.POSITION_WORK_ID = PS_PERSON.PS_WORK_POS_ID AND PS_PERSON.PS_CITIZEN_ID = LEV_DATA.PS_ID) ตำแหน่ง, (SELECT ADMIN_POSITION_NAME FROM TB_ADMIN_POSITION, PS_PERSON WHERE ADMIN_POSITION_ID = PS_PERSON.PS_ADMIN_POS_ID AND PS_PERSON.PS_CITIZEN_ID = LEV_DATA.PS_ID) ระดับ, (SELECT LEAVE_STATUS_NAME FROM LEV_STATUS WHERE LEV_STATUS.LEAVE_STATUS_ID = LEV_DATA.LEAVE_STATUS_ID) สถานะ, NVL(LEV_DATA.V_ALLOW,0) ผลการอนุมัติ FROM LEV_DATA, LEV_BOSS_DATA WHERE LEV_DATA.LEAVE_ID = LEV_BOSS_DATA.LEAVE_ID AND LEV_BOSS_DATA.CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY LEV_DATA.LEAVE_ID DESC"); gvCH.DataSource = sds; gvCH.DataBind(); if (gvCH.Rows.Count > 0) { lbCH.Visible = false; TableHeaderCell headerCell = new TableHeaderCell(); headerCell.Text = "ดูข้อมูล"; gvCH.HeaderRow.Cells.Add(headerCell); gvCH.HeaderRow.Cells[0].Text = "<img src='Image/Small/ID.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[0].Text; gvCH.HeaderRow.Cells[1].Text = "<img src='Image/Small/list.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[1].Text; gvCH.HeaderRow.Cells[2].Text = "<img src='Image/Small/calendar.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[2].Text; gvCH.HeaderRow.Cells[3].Text = "<img src='Image/Small/person2.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[3].Text; gvCH.HeaderRow.Cells[6].Text = "<img src='Image/Small/question.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[6].Text; gvCH.HeaderRow.Cells[7].Text = "<img src='Image/Small/correct.png' class='icon_left'/>" + gvCH.HeaderRow.Cells[7].Text; for (int i = 0; i < gvCH.Rows.Count; ++i) { string ID = gvCH.Rows[i].Cells[0].Text; TableCell cell = new TableCell(); LinkButton btn = new LinkButton(); btn.CssClass = "ps-button-img"; btn.Text = "<img src='Image/Small/search.png'></img>"; btn.Click += (e2, e3) => { Response.Redirect("ViewLeaveForm.aspx?LeaveID=" + ID); }; cell.Controls.Add(btn); gvCH.Rows[i].Cells.Add(cell); if (Util.StringEqual(gvCH.Rows[i].Cells[7].Text, new string[] { "0" })) { gvCH.Rows[i].Cells[7].Text = "-"; gvCH.Rows[i].Cells[7].ForeColor = System.Drawing.Color.Black; } if (Util.StringEqual(gvCH.Rows[i].Cells[7].Text, new string[] { "2" })) { gvCH.Rows[i].Cells[7].Text = "ไม่อนุญาต"; gvCH.Rows[i].Cells[7].ForeColor = System.Drawing.Color.Red; } if (Util.StringEqual(gvCH.Rows[i].Cells[7].Text, new string[] { "1" })) { gvCH.Rows[i].Cells[7].Text = "อนุญาต"; gvCH.Rows[i].Cells[7].ForeColor = System.Drawing.Color.Green; } } Util.NormalizeGridViewDate(gvCH, 2); } else { lbCH.Visible = true; } }
public static void BindDropDown(DropDownList ddl, string sql, string text, string value) { OracleConnection.ClearAllPools(); ddl.DataSource = CreateSQLDataSource(sql); ddl.DataTextField = text; ddl.DataValueField = value; ddl.DataBind(); }
public static void BindGridView(GridView gv, string sql) { OracleConnection.ClearAllPools(); SqlDataSource sds = CreateSQLDataSource(sql); gv.DataSource = sds; gv.DataBind(); }
public static OracleConnection OC() { OracleConnection.ClearAllPools(); OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING); con.Open(); return(con); }
public void Load(int ID) { HasData = false; OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("SELECT TB_PROJECT.*, (SELECT CATEGORY_NAME FROM TB_PROJECT_CATEGORY WHERE TB_PROJECT_CATEGORY.CATEGORY_ID = TB_PROJECT.CATEGORY_ID) CATEGORY_NAME FROM TB_PROJECT WHERE PRO_ID = " + ID, con)) { using (OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { HasData = true; int i = 1; PRO_ID = ID; CITIZEN_ID = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; COUNTRY_ID = reader.GetInt32(i++); SUB_COUNTRY_ID = reader.GetInt32(i++); CATEGORY_ID = reader.GetInt32(i++); PROJECT_NAME = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; ADDRESS_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; if (reader.IsDBNull(i)) { START_DATE = null; } else { START_DATE = reader.GetDateTime(i); } ++i; if (reader.IsDBNull(i)) { END_DATE = null; } else { END_DATE = reader.GetDateTime(i); } ++i; EXPENSES = reader.GetInt32(i++); FUNDING = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; CERTIFICATE = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; SUMMARIZE_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; RESULT_TEACHING = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; RESULT_ACADEMIC = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; DIFFICULTY_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; RESULT_PROJECT = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; RESULT_RESEARCHING = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; RESULT_OTHER = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; COUNSEL = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; PDF_FILE = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; category_name = reader.IsDBNull(i) ? "" : reader.GetString(i); ++i; } } } person = DatabaseManager.GetPerson(citizen_id); } }
public override bool HandleFatalException(IDbConnection connection, Exception e) { #if !MONO #pragma warning disable 618 OracleConnection.ClearAllPools(); #pragma warning restore 618 #endif return(false); }
public static void BindDropDown(DropDownList ddl, string sql, string text, string value, string first) { OracleConnection.ClearAllPools(); ddl.DataSource = CreateSQLDataSource(sql); ddl.DataTextField = text; ddl.DataValueField = value; ddl.DataBind(); ddl.Items.Insert(0, new ListItem(first, "")); }
//public static readonly string CONNECTION_STRING_FIXED = @"Provider=OraOLEDB.Oracle; Data Source = 203.158.140.67:1521/orcl;USER ID=rmutto;PASSWORD=Zxcvbnm"; public static void ExecuteNonQuery(string sql) { OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand(sql, con)) { com.ExecuteNonQuery(); } } }
private void FuncGVFinish() { OracleConnection.ClearAllPools(); SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT R_ID รหัสคำร้องแก้ไขข้อมูล, DATE_START วันที่ข้อมูล, (SELECT R_STATUS_NAME FROM TB_REQUEST_STATUS WHERE TB_REQUEST_STATUS.R_STATUS_ID = TB_REQUEST.R_STATUS_ID) สถานะ, NVL(R_ALLOW,0) ผลการอนุมัติ, DATE_END วันที่อนุมัติ FROM TB_REQUEST WHERE R_STATUS_ID IN(2,4) AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY R_ID DESC"); gvFinish.DataSource = sds; gvFinish.DataBind(); if (gvFinish.Rows.Count > 0) { lbFinish.Visible = false; TableHeaderCell headerCell = new TableHeaderCell(); headerCell.Text = "ตกลง"; gvFinish.HeaderRow.Cells.Add(headerCell); for (int i = 0; i < gvFinish.Rows.Count; ++i) { string ID = gvFinish.Rows[i].Cells[0].Text; TableCell cell = new TableCell(); LinkButton btn = new LinkButton(); btn.CssClass = "ps-button-img"; btn.Text = "ตกลง"; btn.Click += (e2, e3) => { DatabaseManager.ExecuteNonQuery("UPDATE TB_REQUEST SET R_STATUS_ID = R_STATUS_ID+1 WHERE R_ID = " + ID); Response.Redirect("RequestHistory.aspx"); }; cell.Controls.Add(btn); gvFinish.Rows[i].Cells.Add(cell); if (Util.StringEqual(gvFinish.Rows[i].Cells[3].Text, new string[] { "0" })) { gvFinish.Rows[i].Cells[3].Text = "-"; gvFinish.Rows[i].Cells[3].ForeColor = System.Drawing.Color.Black; } if (Util.StringEqual(gvFinish.Rows[i].Cells[3].Text, new string[] { "2" })) { gvFinish.Rows[i].Cells[3].Text = "ไม่อนุมัติ"; gvFinish.Rows[i].Cells[3].ForeColor = System.Drawing.Color.Red; } if (Util.StringEqual(gvFinish.Rows[i].Cells[3].Text, new string[] { "1" })) { gvFinish.Rows[i].Cells[3].Text = "อนุมัติ"; gvFinish.Rows[i].Cells[3].ForeColor = System.Drawing.Color.Green; } } Util.NormalizeGridViewDate(gvFinish, 1); Util.NormalizeGridViewDate(gvFinish, 4); } else { lbFinish.Visible = true; } }
private void FuncGVFinish() { OracleConnection.ClearAllPools(); SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT IP_ID รหัสการขอเครื่องราช, REQ_DATE วันที่ขอ, (SELECT INSIG_GRADE_NAME_L FROM TB_INSIG_GRADE WHERE INSIG_GRADE_ID = INSIG_ID) ระดับชั้นเครื่องราชที่ขอ, (SELECT IP_STATUS_NAME FROM TB_INSIG_PERSON_STATUS WHERE TB_INSIG_PERSON_STATUS.IP_STATUS_ID = TB_INSIG_PERSON.IP_STATUS_ID) สถานะ, NVL(I_ALLOW,0) ผลการอนุมัติ, GET_DATE วันที่อนุมัติ FROM TB_INSIG_PERSON WHERE IP_STATUS_ID IN(2,4) AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY IP_ID DESC"); gvFinish.DataSource = sds; gvFinish.DataBind(); if (gvFinish.Rows.Count > 0) { lbFinish.Visible = false; TableHeaderCell headerCell = new TableHeaderCell(); headerCell.Text = "ตกลง"; gvFinish.HeaderRow.Cells.Add(headerCell); for (int i = 0; i < gvFinish.Rows.Count; ++i) { string ID = gvFinish.Rows[i].Cells[0].Text; TableCell cell = new TableCell(); LinkButton btn = new LinkButton(); btn.CssClass = "ps-button-img"; btn.Text = "ตกลง"; btn.Click += (e2, e3) => { DatabaseManager.ExecuteNonQuery("UPDATE TB_INSIG_PERSON SET IP_STATUS_ID = IP_STATUS_ID+1 WHERE IP_ID = " + ID); Response.Redirect("INS_History.aspx"); }; cell.Controls.Add(btn); gvFinish.Rows[i].Cells.Add(cell); if (Util.StringEqual(gvFinish.Rows[i].Cells[4].Text, new string[] { "0" })) { gvFinish.Rows[i].Cells[4].Text = "-"; gvFinish.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Black; } if (Util.StringEqual(gvFinish.Rows[i].Cells[4].Text, new string[] { "2" })) { gvFinish.Rows[i].Cells[4].Text = "ไม่ได้รับ"; gvFinish.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Red; } if (Util.StringEqual(gvFinish.Rows[i].Cells[4].Text, new string[] { "1" })) { gvFinish.Rows[i].Cells[4].Text = "ได้รับ"; gvFinish.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Green; } } Util.NormalizeGridViewDate(gvFinish, 1); Util.NormalizeGridViewDate(gvFinish, 5); } else { lbFinish.Visible = true; } }
protected void tbUsername_TextChanged(object sender, EventArgs e) { OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("SELECT ST_LOGIN_ID FROM UOC_STAFF WHERE CITIZEN_ID ='" + tbUsername.Text + "'", con)) { using (OracleDataReader reader = com.ExecuteReader()) { while (reader.Read()) { if (!reader.IsDBNull(0)) { int Login = reader.GetInt32(0); if (tbUsername.Text.Length == 13) { if (Login == 0) { LabelTop.Text = "รหัสบัตรประชาชนดังกล่าวเป็นการล็อคอินครั้งแรก โปรดยืนยันตัวตน ด้วยการใส่รหัสผ่านเป็นวันเกิด รูปแบบ(01/01/2500)"; ScriptManager.GetCurrent(this.Page).SetFocus(this.tbPassword); } if (Login == 1) { LabelTop.Text = ""; ScriptManager.GetCurrent(this.Page).SetFocus(this.tbPassword); } } } } } } using (OracleCommand com2 = new OracleCommand("SELECT COUNT(*) FROM UOC_STAFF WHERE CITIZEN_ID ='" + tbUsername.Text + "'", con)) { using (OracleDataReader reader2 = com2.ExecuteReader()) { while (reader2.Read()) { if (reader2.GetInt32(0) == 0) { LabelBottom.Text = "ไม่พบผู้ใช้งาน!"; ScriptManager.GetCurrent(this.Page).SetFocus(this.tbUsername); return; } else { LabelBottom.Text = ""; ScriptManager.GetCurrent(this.Page).SetFocus(this.tbPassword); } } } } } }
protected void txtAbbr_TextChanged(object sender, EventArgs e) { OracleConnection oraConn = new OracleConnection(Session["constring"].ToString()); if (oraConn.State != ConnectionState.Open) { oraConn.Open(); } try { DataTable dtLoanAbbr = new DataTable(); sSql = " SELECT LSCH_DES,LSCH_ABBREV "; sSql += " FROM LOANSCHEMESETUP"; sSql += " WHERE LSCH_BRANCH = " + ddlBranch.SelectedValue; sSql += " AND LSCH_ABBREV = '" + txtAbbr.Text.Trim().ToUpper() + "'"; dtLoanAbbr = objDataFetch.DatatablePassSQL(sSql); if (dtLoanAbbr.Rows.Count <= 0) { ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Invalid Loan Abbr')</script>"); txtAbbr.Text = ""; txtAbbr.Focus(); return; } //else //{ // if (!DBNull.Value.Equals(dtLoanAbbr.Rows[0]["LSCH_ABBREV"]) && (dtLoanAbbr.Rows[0]["LSCH_ABBREV"].ToString() != string.Empty)) // { // } //} if (dtLoanAbbr.Rows[0]["LSCH_DES"].ToString() != "") { strLoanDes = dtLoanAbbr.Rows[0]["LSCH_DES"].ToString(); } Session["LoanName"] = strLoanDes; txtAcno.Focus(); } catch (Exception ex) { objLog.WriteLog(ex.Message); ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('" + ex.Message + "')</script>"); } finally { if (oraConn.State == ConnectionState.Open) { oraConn.Close(); } oraConn.Dispose(); OracleConnection.ClearAllPools(); } }
private void FuncGVHistory() { OracleConnection.ClearAllPools(); SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT R_ID รหัสคำร้องแก้ไขข้อมูล, (SELECT PS_FIRSTNAME || ' ' || PS_LASTNAME FROM PS_PERSON WHERE PS_PERSON.PS_CITIZEN_ID = TB_REQUEST.CITIZEN_ID) ชื่อ, (SELECT (SELECT CAMPUS_NAME FROM TB_CAMPUS WHERE PS_PERSON.PS_CAMPUS_ID = TB_CAMPUS.CAMPUS_ID) FROM PS_PERSON WHERE PS_PERSON.PS_CITIZEN_ID = TB_REQUEST.CITIZEN_ID) วิทยาเขต, (SELECT (SELECT STAFFTYPE_NAME FROM TB_STAFFTYPE WHERE PS_PERSON.PS_STAFFTYPE_ID = TB_STAFFTYPE.STAFFTYPE_ID) FROM PS_PERSON WHERE PS_PERSON.PS_CITIZEN_ID = TB_REQUEST.CITIZEN_ID) ประเภทบุคลากร, DATE_START วันที่ข้อมูล, (SELECT R_STATUS_NAME FROM TB_REQUEST_STATUS WHERE TB_REQUEST_STATUS.R_STATUS_ID = TB_REQUEST.R_STATUS_ID) สถานะ, NVL(R_ALLOW,0) ผลการอนุมัติ, DATE_END วันที่อนุมัติ FROM TB_REQUEST WHERE R_STATUS_ID IN(2,3,4,5) ORDER BY R_ID DESC"); gvHistory.DataSource = sds; gvHistory.DataBind(); if (gvHistory.Rows.Count > 0) { lbHistory.Visible = false; TableHeaderCell headerCell = new TableHeaderCell(); headerCell.Text = "ดูข้อมูล"; gvHistory.HeaderRow.Cells.Add(headerCell); for (int i = 0; i < gvHistory.Rows.Count; ++i) { string ID = gvHistory.Rows[i].Cells[0].Text; TableCell cell = new TableCell(); LinkButton btn = new LinkButton(); btn.CssClass = "ps-button-img"; btn.Text = "<img src='Image/Small/search.png'></img>"; btn.Click += (e2, e3) => { Response.Redirect("ViewRequestForm.aspx?id=" + MyCrypto.GetEncryptedQueryString(ID).ToString()); }; cell.Controls.Add(btn); gvHistory.Rows[i].Cells.Add(cell); if (Util.StringEqual(gvHistory.Rows[i].Cells[6].Text, new string[] { "0" })) { gvHistory.Rows[i].Cells[6].Text = "-"; gvHistory.Rows[i].Cells[6].ForeColor = System.Drawing.Color.Black; } if (Util.StringEqual(gvHistory.Rows[i].Cells[6].Text, new string[] { "2" })) { gvHistory.Rows[i].Cells[6].Text = "ไม่อนุมัติ"; gvHistory.Rows[i].Cells[6].ForeColor = System.Drawing.Color.Red; } if (Util.StringEqual(gvHistory.Rows[i].Cells[6].Text, new string[] { "1" })) { gvHistory.Rows[i].Cells[6].Text = "อนุมัติ"; gvHistory.Rows[i].Cells[6].ForeColor = System.Drawing.Color.Green; } } Util.NormalizeGridViewDate(gvHistory, 4); Util.NormalizeGridViewDate(gvHistory, 7); } else { lbHistory.Visible = true; } }
protected void OnDeletePosition(object sender, EventArgs e) { RepeaterItem item = (sender as LinkButton).Parent as RepeaterItem; string ValuePHID = (item.FindControl("HFPH_ID") as HiddenField).Value; if (ValuePHID != "") { string CheckNull = DatabaseManager.ExecuteString("SELECT COUNT(*) FROM PS_POSITION_HISTORY WHERE CITIZEN_ID = '" + MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()) + "'"); OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); if (CheckNull == "1") { using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_POSITION_ID = :PS_POSITION_ID WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con)) { com.Parameters.Add(new OracleParameter("PS_POSITION_ID", DBNull.Value)); com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()))); com.ExecuteNonQuery(); } using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_FIRST_POSITION_ID = :PS_FIRST_POSITION_ID WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con)) { com.Parameters.Add(new OracleParameter("PS_FIRST_POSITION_ID", DBNull.Value)); com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()))); com.ExecuteNonQuery(); } } else { using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_POSITION_ID = (select P_ID from ( select P_ID, rownum as rn from ( select P_ID from PS_POSITION_HISTORY where CITIZEN_ID = :CITIZEN_ID order by GET_DATE desc )) where rn=2) WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con)) { com.Parameters.Add(new OracleParameter("CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()))); com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()))); com.ExecuteNonQuery(); } //using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_FIRST_POSITION_ID = (select P_ID from ( select P_ID, rownum as rn from ( select P_ID from PS_POSITION_HISTORY where CITIZEN_ID = :CITIZEN_ID order by GET_DATE desc )) where rn=2) WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con)) using (OracleCommand com = new OracleCommand("UPDATE PS_PERSON SET PS_FIRST_POSITION_ID = (SELECT P_ID FROM PS_POSITION_HISTORY WHERE CITIZEN_ID = :CITIZEN_ID AND GET_DATE = (SELECT MIN(GET_DATE) FROM PS_POSITION_HISTORY WHERE CITIZEN_ID = :CITIZEN_ID)) WHERE PS_CITIZEN_ID = :PS_CITIZEN_ID", con)) { com.Parameters.Add(new OracleParameter("CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()))); com.Parameters.Add(new OracleParameter("PS_CITIZEN_ID", MyCrypto.GetDecryptedQueryString(Request.QueryString["id"].ToString()))); com.ExecuteNonQuery(); } } DatabaseManager.ExecuteNonQuery("DELETE PS_POSITION_HISTORY WHERE PH_ID = '" + ValuePHID + "'"); ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('ลบข้อมูลเรียบร้อย')", true); BindPosition(); } } }
protected void Page_Load(object sender, EventArgs e) { objLog.WriteLog("HousingLoan.aspx: Entered Page_Load"); if (Session.Contents.Count == 0) { Response.Redirect("ShowMessage.aspx?msg=" + GlobalConstants.msg); } else { try { if (!IsPostBack) { OracleConnection oraConn = new OracleConnection(Session["constring"].ToString()); if (oraConn.State != ConnectionState.Open) { oraConn.Open(); } sSQL = objFill.BranchFill(); objDataFetch.FillDdl(sSQL, ddlBranch, "BRAN_SNO", "BRAN_NAME"); ddlBranch.SelectedValue = Session["BranchLogin"].ToString(); ddlBranch.Enabled = false; txtAbbr.Focus(); Bal.Gen objfilldate = new Bal.Gen(); objfilldate.Call_Date(ddfrmday, ddfrmMonth, ddfrmYear); Bal.Gen objfilldate1 = new Bal.Gen(); objfilldate1.Call_Date(ddtoday, ddtoMonth, ddtoYear); } strdtfrm = ddfrmday.SelectedItem.Value + "-" + ddfrmMonth.SelectedItem.Text + "-" + ddfrmYear.SelectedItem.Value; strdtTo = ddtoday.SelectedItem.Value + "-" + ddtoMonth.SelectedItem.Text + "-" + ddtoYear.SelectedItem.Value; objLog.WriteLog("HousingLoan.aspx: strdtfrm=" + strdtfrm); objLog.WriteLog("HousingLoan.aspx: strdtTo=" + strdtTo); } catch (Exception ex) { objLog.WriteLog("HousingLoan Exception in PageLoad : " + ex.Message); ClientScript.RegisterStartupScript(Page.GetType(), "VALIDATION", "<script language='javascript'>alert('" + ex.Message + "')</script>"); } finally { if (oraConn.State == ConnectionState.Open) { oraConn.Close(); } oraConn.Dispose(); OracleConnection.ClearAllPools(); } } }
private void FuncGVHistory() { OracleConnection.ClearAllPools(); SqlDataSource sds = DatabaseManager.CreateSQLDataSource("SELECT IP_ID รหัสการขอเครื่องราช, REQ_DATE วันที่ขอ, (SELECT INSIG_GRADE_NAME_L FROM TB_INSIG_GRADE WHERE INSIG_GRADE_ID = INSIG_ID) ระดับชั้นเครื่องราชที่ขอ, (SELECT IP_STATUS_NAME FROM TB_INSIG_PERSON_STATUS WHERE TB_INSIG_PERSON_STATUS.IP_STATUS_ID = TB_INSIG_PERSON.IP_STATUS_ID) สถานะ, NVL(I_ALLOW,0) ผลการอนุมัติ, GET_DATE วันที่อนุมัติ FROM TB_INSIG_PERSON WHERE IP_STATUS_ID IN(3,5) AND CITIZEN_ID = '" + loginPerson.PS_CITIZEN_ID + "' ORDER BY IP_ID DESC"); gvHistory.DataSource = sds; gvHistory.DataBind(); if (gvHistory.Rows.Count > 0) { lbHistory.Visible = false; //TableHeaderCell headerCell = new TableHeaderCell(); //headerCell.Text = "ดูข้อมูล"; //gvHistory.HeaderRow.Cells.Add(headerCell); for (int i = 0; i < gvHistory.Rows.Count; ++i) { /*string ID = gvHistory.Rows[i].Cells[0].Text; * TableCell cell = new TableCell(); * LinkButton btn = new LinkButton(); * btn.CssClass = "ps-button-img"; * btn.Text = "<img src='Image/Small/search.png'></img>"; * btn.Click += (e2, e3) => * { * Response.Redirect("ViewRequestForm.aspx?RequestID=" + ID); * }; * cell.Controls.Add(btn); * gvHistory.Rows[i].Cells.Add(cell);*/ if (Util.StringEqual(gvHistory.Rows[i].Cells[4].Text, new string[] { "0" })) { gvHistory.Rows[i].Cells[4].Text = "-"; gvHistory.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Black; } if (Util.StringEqual(gvHistory.Rows[i].Cells[4].Text, new string[] { "2" })) { gvHistory.Rows[i].Cells[4].Text = "ไม่ได้รับ"; gvHistory.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Red; } if (Util.StringEqual(gvHistory.Rows[i].Cells[4].Text, new string[] { "1" })) { gvHistory.Rows[i].Cells[4].Text = "ได้รับ"; gvHistory.Rows[i].Cells[4].ForeColor = System.Drawing.Color.Green; } } Util.NormalizeGridViewDate(gvHistory, 1); Util.NormalizeGridViewDate(gvHistory, 5); } }
public int INSERT_REQUEST() { int id = 0; OracleConnection.ClearAllPools(); using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { con.Open(); using (OracleCommand com = new OracleCommand("INSERT INTO TB_REQUEST (UOC_ID,STATUS_ID,DATE_START,UNIV_ID,PREFIX_NAME,STF_FNAME,STF_LNAME) VALUES (:UOC_ID,:STATUS_ID,:DATE_START,:UNIV_ID,:PREFIX_NAME,:STF_FNAME,:STF_LNAME)", con)) { com.Parameters.Add(new OracleParameter("UOC_ID", loginPerson.UOC_ID)); com.Parameters.Add(new OracleParameter("STATUS_ID", "0")); com.Parameters.Add(new OracleParameter("DATE_START", DateTime.Today)); if (cbUniv.Checked) { com.Parameters.Add(new OracleParameter("UNIV_ID", ddlUniv.SelectedValue)); } else { com.Parameters.Add(new OracleParameter("UNIV_ID", DBNull.Value)); } if (cbPrefixName.Checked) { com.Parameters.Add(new OracleParameter("PREFIX_NAME", ddlPrefixName.SelectedValue)); } else { com.Parameters.Add(new OracleParameter("PREFIX_NAME", DBNull.Value)); } if (cbName.Checked) { com.Parameters.Add(new OracleParameter("STF_FNAME", tbName.Text)); } else { com.Parameters.Add(new OracleParameter("STF_FNAME", DBNull.Value)); } if (cbLastName.Checked) { com.Parameters.Add(new OracleParameter("STF_LNAME", tbLastName.Text)); } else { com.Parameters.Add(new OracleParameter("STF_LNAME", DBNull.Value)); } id = com.ExecuteNonQuery(); } } return(id); }