/// <summary> /// 検索 /// </summary> private void ExcuteSearch() { using (var db = new OleDbIf()) { db.Connect(); var sb = new StringBuilder(); sb.AppendLine("SELECT ME.CD_CO, ME.CD_EMP, ME.NM_EMP, ME.TXT_PASSWD, ME.CD_DEPT, MD.NM_DEPT, ME.TXT_ZIP, ME.TXT_ADDR1, ME.TXT_ADDR2, ME.TXT_ADDR3, ME.TXT_TEL, ME.TXT_FAX, ME.TXT_REM "); sb.AppendLine(" FROM M_EMP ME"); sb.AppendLine(" INNER JOIN M_DEPT MD"); sb.AppendLine(" ON ME.CD_CO = MD.CD_CO AND ME.CD_DEPT = MD.CD_DEPT AND ME.FLG_ACTIVE = MD.FLG_ACTIVE"); sb.AppendLine(" WHERE ME.CD_CO='{0}' AND ME.CD_EMP='{1}' AND ME.FLG_ACTIVE='Y'"); string strSql = sb.ToString(); DataTable tbl = db.ExecuteSql(String.Format(strSql, this.m_strPrimaryKey1, this.m_strPrimaryKey2)); int i = 0; this.cmbCdCo.SelectedValue = tbl.Rows[0][i++].ToString(); this.txtCd_Emp.Text = tbl.Rows[0][i++].ToString(); this.txtNm_Emp.Text = tbl.Rows[0][i++].ToString(); this.txtTxt_Passwd.Text = tbl.Rows[0][i++].ToString(); this.txtCd_Dept.Text = tbl.Rows[0][i++].ToString(); this.txtNm_Dept.Text = tbl.Rows[0][i++].ToString(); this.txtTxt_Zip.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); this.txtTxt_Addr1.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); this.txtTxt_Addr2.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); this.txtTxt_Addr3.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); this.txtTxt_Tel.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); this.txtTxt_Fax.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); this.txtTxt_Rem.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); } }
/// <summary> /// 検索 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { using (var db = new OleDbIf()) { db.Connect(); // 表示するレコードを取得し、DataGridViewに関連付ける var sb = new StringBuilder(); if (this.m_strInitId.Equals(CommonConstants.MasterMode.BUMON)) { sb.Append("SELECT * FROM M_DEPT WHERE FLG_ACTIVE='Y'"); } else { sb.Append("SELECT ME.CD_CO, ME.CD_EMP, ME.NM_EMP, ME.TXT_PASSWD, ME.CD_DEPT, ME.TXT_ZIP, ME.TXT_ADDR1, ME.TXT_ADDR2, ME.TXT_ADDR3, ME.TXT_TEL, ME.TXT_FAX, ME.TXT_REM, ME.CD_CREATE, ME.DTM_CREATE, ME.CD_UPDATE, ME.DTM_UPDATE, ME.FLG_ACTIVE"); sb.Append(" FROM M_EMP ME"); sb.Append(" INNER JOIN M_DEPT MD"); sb.Append(" ON ME.CD_CO = MD.CD_CO AND ME.CD_DEPT = MD.CD_DEPT AND ME.FLG_ACTIVE = MD.FLG_ACTIVE"); sb.Append(" WHERE ME.FLG_ACTIVE='Y'"); } this.dgv.DataSource = db.ExecuteSql(sb.ToString()); SetDgvProperties(dgv); // ボタン活性化 if (this.dgv.RowCount != 0) SwitchButtonView(true); } }
/// <summary> /// 登録 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnRegister_Click(object sender, EventArgs e) { DataTable tbl = new DataTable(); using (var db = new OleDbIf()) { db.Connect(); var sb = new StringBuilder(); sb.AppendLine("SELECT COUNT(*) FROM M_EMP WHERE CD_CO='{0}' AND CD_EMP='{1}' AND FLG_ACTIVE='Y'"); string strSql = sb.ToString(); tbl = db.ExecuteSql(String.Format(strSql, this.cmbCdCo.SelectedValue, this.txtCd_Emp.Text)); } // 一意性エラーチェック if (this.m_strEditMode.Equals(CommonConstants.EditMode.CREATE_MODE) && tbl.Rows.Count > 0) { MessageBox.Show("既に登録されています。", "通知"); } else { if (DialogResult.Yes == MessageBox.Show("登録します。よろしいですか?", "通知", MessageBoxButtons.YesNo)) { ExcuteSql(GetSqlString()); } } }
/// <summary> /// 検索 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { using (var db = new OleDbIf()) { db.Connect(); // 表示するレコードを取得し、DataGridViewに関連付ける var sb = new StringBuilder(); if (this.m_strInitId.Equals(CommonConstants.MasterMode.BUMON)) { sb.Append("SELECT * FROM M_DEPT WHERE FLG_ACTIVE='Y'"); } else { sb.Append("SELECT ME.CD_CO, ME.CD_EMP, ME.NM_EMP, ME.TXT_PASSWD, ME.CD_DEPT, ME.TXT_ZIP, ME.TXT_ADDR1, ME.TXT_ADDR2, ME.TXT_ADDR3, ME.TXT_TEL, ME.TXT_FAX, ME.TXT_REM, ME.CD_CREATE, ME.DTM_CREATE, ME.CD_UPDATE, ME.DTM_UPDATE, ME.FLG_ACTIVE"); sb.Append(" FROM M_EMP ME"); sb.Append(" INNER JOIN M_DEPT MD"); sb.Append(" ON ME.CD_CO = MD.CD_CO AND ME.CD_DEPT = MD.CD_DEPT AND ME.FLG_ACTIVE = MD.FLG_ACTIVE"); sb.Append(" WHERE ME.FLG_ACTIVE='Y'"); } this.dgv.DataSource = db.ExecuteSql(sb.ToString()); SetDgvProperties(dgv); // ボタン活性化 if (this.dgv.RowCount != 0) { SwitchButtonView(true); } } }
/// <summary> /// 検索 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { using (var db = new OleDbIf()) { // DB処理 db.Connect(); string strSql = "SELECT CD_CO, CD_DEPT, NM_DEPT, TXT_REM FROM M_DEPT WHERE CD_CO='{0}' AND FLG_ACTIVE='Y'"; if (!"".Equals(this.txtCd_Dept.Text)) strSql += String.Format("AND CD_DEPT='{0}'", this.txtCd_Dept.Text); dgv.DataSource = db.ExecuteSql(String.Format(strSql, this.m_strCd_Co)); // DataGridViewのHeaderText変更 SetDgvHeaderText(dgv); } }
/// <summary> /// 検索 /// </summary> /// <returns></returns> public bool ExcuteSearch() { bool bResult = false; using (var db = new OleDbIf()) { db.Connect(); string strSql = "SELECT NM_EMP FROM M_EMP WHERE CD_CO='{0}' AND CD_EMP='{1}' AND TXT_PASSWD='{2}' AND FLG_ACTIVE='Y'"; DataTable tbl = db.ExecuteSql(String.Format(strSql, cmbCdCo.SelectedValue.ToString(), txtCd_Emp.Text, txtTxt_Passwd.Text)); bResult = (tbl.Rows.Count > 0) ? true : false; strUserName = (bResult) ? tbl.Rows[0]["NM_EMP"].ToString() : null; } return bResult; }
/// <summary> /// 会社コード指定 /// </summary> private DataTable GetCd_Co() { using (var db = new OleDbIf()) { //表示される値はDataTableのNAME列 cmbCdCo.DisplayMember = "NM_CO_SHORT"; //対応する値はDataTableのID列 cmbCdCo.ValueMember = "CD_CO"; // DB処理 db.Connect(); DataTable tbl = db.ExecuteSql("SELECT CD_CO, NM_CO_SHORT FROM M_CO WHERE FLG_ACTIVE='Y'"); return(tbl); } }
/// <summary> /// 検索 /// </summary> /// <returns></returns> public bool ExcuteSearch() { bool bResult = false; using (var db = new OleDbIf()) { db.Connect(); string strSql = "SELECT NM_EMP FROM M_EMP WHERE CD_CO='{0}' AND CD_EMP='{1}' AND TXT_PASSWD='{2}' AND FLG_ACTIVE='Y'"; DataTable tbl = db.ExecuteSql(String.Format(strSql, cmbCdCo.SelectedValue.ToString(), txtCd_Emp.Text, txtTxt_Passwd.Text)); bResult = (tbl.Rows.Count > 0) ? true : false; strUserName = (bResult) ? tbl.Rows[0]["NM_EMP"].ToString() : null; } return(bResult); }
/// <summary> /// 会社コード指定 /// </summary> private DataTable GetCd_Co() { using (var db = new OleDbIf()) { //表示される値はDataTableのNAME列 cmbCdCo.DisplayMember = "NM_CO_SHORT"; //対応する値はDataTableのID列 cmbCdCo.ValueMember = "CD_CO"; // DB処理 db.Connect(); DataTable tbl = db.ExecuteSql("SELECT CD_CO, NM_CO_SHORT FROM M_CO WHERE FLG_ACTIVE='Y'"); return tbl; } }
/// <summary> /// 検索 /// </summary> private void ExcuteSearch() { using (var db = new OleDbIf()) { db.Connect(); string strSql = "SELECT CD_CO, CD_DEPT, NM_DEPT, TXT_REM FROM M_DEPT WHERE CD_CO='{0}' AND CD_DEPT='{1}' AND FLG_ACTIVE='Y'"; DataTable tbl = db.ExecuteSql(String.Format(strSql, this.m_strPrimaryKey1, this.m_strPrimaryKey2)); int i = 0; this.cmbCdCo.SelectedValue = tbl.Rows[0][i++].ToString(); this.txtCd_Dept.Text = tbl.Rows[0][i++].ToString(); this.txtNm_Dept.Text = tbl.Rows[0][i++].ToString(); this.txtTxt_Rem.Text = (tbl.Rows[0][i] == null) ? null : tbl.Rows[0][i++].ToString(); } }
/// <summary> /// 検索 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { using (var db = new OleDbIf()) { // DB処理 db.Connect(); string strSql = "SELECT CD_CO, CD_DEPT, NM_DEPT, TXT_REM FROM M_DEPT WHERE CD_CO='{0}' AND FLG_ACTIVE='Y'"; if (!"".Equals(this.txtCd_Dept.Text)) { strSql += String.Format("AND CD_DEPT='{0}'", this.txtCd_Dept.Text); } dgv.DataSource = db.ExecuteSql(String.Format(strSql, this.m_strCd_Co)); // DataGridViewのHeaderText変更 SetDgvHeaderText(dgv); } }
/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void txtCd_Dept_LostFocus(object sender, EventArgs e) { if ("".Equals(txtCd_Dept.Text)) { this.txtNm_Dept.Text = null; } else { // TODO: 毎回SQLを投げるとネットワーク的によろしくないので、どこかに値を保持しておきたい。 using (var db = new OleDbIf()) { db.Connect(); string strSql = "SELECT NM_DEPT FROM M_DEPT WHERE CD_CO='{0}' AND CD_DEPT='{1}' AND FLG_ACTIVE='Y'"; DataTable tbl = db.ExecuteSql(String.Format(strSql, this.m_strCd_Co, txtCd_Dept.Text)); this.txtNm_Dept.Text = (tbl.Rows.Count > 0) ? tbl.Rows[0]["NM_DEPT"].ToString() : null; } } }
/// <summary> /// 部門コード変更時部門名取得 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void txtCd_Dept_LostFocus(object sender, EventArgs e) { if ("".Equals(txtCd_Dept.Text)) { this.txtNm_Dept.Text = null; } else { // TODO: 毎回SQLを投げるとネットワーク的によろしくないので、どこかに値を保持しておきたい。 using (var db = new OleDbIf()) { db.Connect(); var sb = new StringBuilder(); sb.AppendLine("SELECT MD.NM_DEPT FROM M_EMP ME"); sb.AppendLine(" INNER JOIN M_DEPT MD "); sb.AppendLine(" ON ME.CD_CO = MD.CD_CO AND ME.CD_DEPT = MD.CD_DEPT AND ME.FLG_ACTIVE = MD.FLG_ACTIVE"); sb.AppendLine(" WHERE ME.CD_CO='{0}' AND ME.CD_EMP='{1}' AND ME.FLG_ACTIVE='Y'"); string strSql = sb.ToString(); DataTable tbl = db.ExecuteSql(String.Format(strSql, cmbCdCo.SelectedValue, txtCd_Emp.Text)); this.txtNm_Dept.Text = (tbl.Rows.Count > 0) ? tbl.Rows[0]["NM_DEPT"].ToString() : null; } } }
/// <summary> /// SQL実行 /// </summary> /// <param name="strSql"></param> private void ExcuteSql(string strSql) { var db = new OleDbIf(); try { db.Connect(); db.BeginTransaction(); db.ExecuteSql(strSql); db.CommitTransaction(); MessageBox.Show("処理が完了しました。", "通知"); } catch (Exception) { db.RollbackTransaction(); MessageBox.Show("処理に失敗しました。", "通知"); } finally { this.Close(); db.Dispose(); } }
/// <summary> /// 更新 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnUpdate_Click(object sender, EventArgs e) { var db = new OleDbIf(); try { string strSql = null; var f = this.MdiParent as MainMDI; var tbl = this.dgv.DataSource as DataTable; var lst = new List <string>(); // 編集された行をコミットする foreach (DataRow row in tbl.Rows) { if (row.RowState != DataRowState.Unchanged) { string cd_co = row["CD_CO", DataRowVersion.Original].ToString(); if (this.m_strInitId.Equals(CommonConstants.MasterMode.BUMON)) { string cd_dept = (row["CD_DEPT", DataRowVersion.Original] != null) ? row["CD_DEPT", DataRowVersion.Original].ToString() : null; strSql = "UPDATE M_DEPT SET CD_CO='{0}', CD_DEPT='{1}', NM_DEPT='{2}', TXT_REM='{3}', CD_UPDATE='{4}', DTM_UPDATE=SYSDATE, FLG_ACTIVE='Y' WHERE CD_CO='{5}' AND CD_DEPT='{6}'"; strSql = String.Format( strSql, row["CD_CO", DataRowVersion.Current].ToString(), row["CD_DEPT", DataRowVersion.Current].ToString(), row["NM_DEPT", DataRowVersion.Current].ToString(), row["TXT_REM", DataRowVersion.Current].ToString(), f.lblUserName.Text, cd_co, cd_dept); } else { string cd_emp = (row["CD_EMP", DataRowVersion.Original] != null) ? row["CD_EMP", DataRowVersion.Original].ToString() : null; strSql = "UPDATE M_EMP SET CD_CO='{0}', CD_EMP='{1}', NM_EMP='{2}', TXT_PASSWD='{3}', CD_DEPT='{4}', TXT_ZIP='{5}', TXT_ADDR1='{6}', TXT_ADDR2='{7}', TXT_ADDR3='{8}', TXT_TEL='{9}', TXT_FAX='{10}', TXT_REM='{11}', CD_UPDATE='{12}', DTM_UPDATE=SYSDATE, FLG_ACTIVE='Y' WHERE CD_CO='{13}' AND CD_EMP='{14}'"; strSql = String.Format( strSql, row["CD_CO", DataRowVersion.Current].ToString(), row["CD_EMP", DataRowVersion.Current].ToString(), row["NM_EMP", DataRowVersion.Current].ToString(), row["TXT_PASSWD", DataRowVersion.Current].ToString(), row["CD_DEPT", DataRowVersion.Current].ToString(), row["TXT_ZIP", DataRowVersion.Current].ToString(), row["TXT_ADDR1", DataRowVersion.Current].ToString(), row["TXT_ADDR2", DataRowVersion.Current].ToString(), row["TXT_ADDR3", DataRowVersion.Current].ToString(), row["TXT_TEL", DataRowVersion.Current].ToString(), row["TXT_FAX", DataRowVersion.Current].ToString(), row["TXT_REM", DataRowVersion.Current].ToString(), f.lblUserName.Text, cd_co, cd_emp); } lst.Add(strSql); } } if (DialogResult.Yes == MessageBox.Show("登録します。よろしいですか?", "通知", MessageBoxButtons.YesNo)) { // SQL実行 db.Connect(); db.BeginTransaction(); lst.ForEach(n => db.ExecuteSql(n)); db.CommitTransaction(); MessageBox.Show("登録を完了しました。", "通知"); } } catch (Exception) { db.RollbackTransaction(); MessageBox.Show("登録に失敗しました。", "通知"); } finally { btnSearch_Click(sender, e); db.Dispose(); } }
/// <summary> /// 更新 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnUpdate_Click(object sender, EventArgs e) { var db = new OleDbIf(); try { string strSql = null; var f = this.MdiParent as MainMDI; var tbl = this.dgv.DataSource as DataTable; var lst = new List<string>(); // 編集された行をコミットする foreach (DataRow row in tbl.Rows) { if (row.RowState != DataRowState.Unchanged) { string cd_co = row["CD_CO", DataRowVersion.Original].ToString(); if (this.m_strInitId.Equals(CommonConstants.MasterMode.BUMON)) { string cd_dept = (row["CD_DEPT", DataRowVersion.Original] != null) ? row["CD_DEPT", DataRowVersion.Original].ToString() : null; strSql = "UPDATE M_DEPT SET CD_CO='{0}', CD_DEPT='{1}', NM_DEPT='{2}', TXT_REM='{3}', CD_UPDATE='{4}', DTM_UPDATE=SYSDATE, FLG_ACTIVE='Y' WHERE CD_CO='{5}' AND CD_DEPT='{6}'"; strSql = String.Format( strSql, row["CD_CO", DataRowVersion.Current].ToString(), row["CD_DEPT", DataRowVersion.Current].ToString(), row["NM_DEPT", DataRowVersion.Current].ToString(), row["TXT_REM", DataRowVersion.Current].ToString(), f.lblUserName.Text, cd_co, cd_dept); } else { string cd_emp = (row["CD_EMP", DataRowVersion.Original] != null) ? row["CD_EMP", DataRowVersion.Original].ToString() : null; strSql = "UPDATE M_EMP SET CD_CO='{0}', CD_EMP='{1}', NM_EMP='{2}', TXT_PASSWD='{3}', CD_DEPT='{4}', TXT_ZIP='{5}', TXT_ADDR1='{6}', TXT_ADDR2='{7}', TXT_ADDR3='{8}', TXT_TEL='{9}', TXT_FAX='{10}', TXT_REM='{11}', CD_UPDATE='{12}', DTM_UPDATE=SYSDATE, FLG_ACTIVE='Y' WHERE CD_CO='{13}' AND CD_EMP='{14}'"; strSql = String.Format( strSql, row["CD_CO", DataRowVersion.Current].ToString(), row["CD_EMP", DataRowVersion.Current].ToString(), row["NM_EMP", DataRowVersion.Current].ToString(), row["TXT_PASSWD", DataRowVersion.Current].ToString(), row["CD_DEPT", DataRowVersion.Current].ToString(), row["TXT_ZIP", DataRowVersion.Current].ToString(), row["TXT_ADDR1", DataRowVersion.Current].ToString(), row["TXT_ADDR2", DataRowVersion.Current].ToString(), row["TXT_ADDR3", DataRowVersion.Current].ToString(), row["TXT_TEL", DataRowVersion.Current].ToString(), row["TXT_FAX", DataRowVersion.Current].ToString(), row["TXT_REM", DataRowVersion.Current].ToString(), f.lblUserName.Text, cd_co, cd_emp); } lst.Add(strSql); } } if (DialogResult.Yes == MessageBox.Show("登録します。よろしいですか?", "通知", MessageBoxButtons.YesNo)) { // SQL実行 db.Connect(); db.BeginTransaction(); lst.ForEach(n => db.ExecuteSql(n)); db.CommitTransaction(); MessageBox.Show("登録を完了しました。", "通知"); } } catch (Exception) { db.RollbackTransaction(); MessageBox.Show("登録に失敗しました。", "通知"); } finally { btnSearch_Click(sender, e); db.Dispose(); } }
/// <summary> /// 登録 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnRegister_Click(object sender, EventArgs e) { DataTable tbl = new DataTable(); using (var db = new OleDbIf()) { var sb = new StringBuilder(); sb.AppendLine("SELECT COUNT(*) FROM M_DEPT WHERE CD_CO='{0}' AND CD_DEPT='{1}' AND FLG_ACTIVE='Y'"); string strSql = sb.ToString(); db.Connect(); tbl = db.ExecuteSql(String.Format(strSql, this.cmbCdCo.SelectedValue, this.txtCd_Dept.Text)); } // 一意性エラーチェック if (this.m_strEditMode.Equals(CommonConstants.EditMode.CREATE_MODE) && tbl.Rows.Count > 0) { MessageBox.Show("既に登録されています。", "通知"); } else { if (DialogResult.Yes == MessageBox.Show("登録します。よろしいですか?", "通知", MessageBoxButtons.YesNo)) { ExcuteSql(GetSqlString()); } } }
/// <summary> /// 部門コード変更時部門名取得 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void txtCd_Dept_LostFocus(object sender, EventArgs e) { if ("".Equals(txtCd_Dept.Text)) { this.txtNm_Dept.Text = null; } else { // TODO: 毎回SQLを投げるとネットワーク的によろしくないので、どこかに値を保持しておきたい。 using (var db = new OleDbIf()) { db.Connect(); string strSql = "SELECT NM_DEPT FROM M_DEPT WHERE CD_CO='{0}' AND CD_DEPT='{1}' AND FLG_ACTIVE='Y'"; DataTable tbl = db.ExecuteSql(String.Format(strSql, cmbCdCo.SelectedValue, txtCd_Dept.Text)); this.txtNm_Dept.Text = (tbl.Rows.Count > 0) ? tbl.Rows[0]["NM_DEPT"].ToString() : null; } } }
/// <summary> /// SQL実行 /// </summary> /// <param name="strSql"></param> private void ExcuteSql(string strSql) { var db = new OleDbIf(); try { db.Connect(); db.BeginTransaction(); db.ExecuteSql(strSql); db.CommitTransaction(); MessageBox.Show("処理が完了しました。", "通知"); } catch (Exception) { db.RollbackTransaction(); MessageBox.Show("登録に失敗しました。", "通知"); } finally { this.Close(); db.Dispose(); } }