protected virtual void WfIniSqlBody() { StringBuilder sbSql; StringBuilder sbSqlSelect, sbSqlFrom, sbSqlInner, sbSqlWhere; try { if (MsgInfoReturned == null) { throw new Exception("未實體化msgInfoReturned"); } sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" SELECT * FROM vw_admi650 WHERE azp01='{0}' ", StrPickNo)); var drAzp = BoMaster.OfGetDataRow(sbSql.ToString()); if (drAzp == null) { throw new Exception("查無此pick_no!"); } sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" SELECT * FROM vw_admi650s WHERE azq01='{0}' ", StrPickNo)); sbSql.AppendLine(string.Format(" ORDER BY azq02 ")); var dtAzq = BoMaster.OfGetDataTable(sbSql.ToString()); if (drAzp == null) { throw new Exception("查無此pick_no 明細資料!"); } Admi650Model = drAzp.ToItem <vw_admi650>(); Admi650sList = dtAzq.ToList <vw_admi650s>(); //設定form名稱 this.Text = string.Format("{0} {1}", Admi650Model.azp01, Admi650Model.azp02); #region 取得select body sbSqlSelect = new StringBuilder(); sbSqlSelect.AppendLine(string.Format("SELECT ")); if (Admi650Model.azp06 == "Y") { sbSqlSelect.AppendLine(string.Format("DISTINCT")); } if (_iMaxPickRow != 1) //表示多選,增加is_pick_yn { sbSqlSelect.AppendLine(string.Format("convert(nvarchar(1),'N') is_pick,")); //0,1 } //組合select 欄位,最後一欄不加, for (int i = 0; i < Admi650sList.Count; i++) { var selectField = string.Format(Admi650sList[i].azq03 + "." + Admi650sList[i].azq04); if (i != Admi650sList.Count - 1) { selectField += ","; } sbSqlSelect.AppendLine(selectField); } #endregion #region 取得from body sbSqlFrom = new StringBuilder(); sbSqlFrom.AppendLine(string.Format("FROM " + Admi650Model.azp03)); #endregion #region 取得join sbSqlInner = new StringBuilder(); //if (GlobalFn.isNullRet(drAzp["azp04"], "") != "") if (GlobalFn.isNullRet(Admi650Model.azp04, "") != "") { //sbSqlInner.AppendLine(string.Format(GlobalFn.isNullRet(drAzp["azp04"], ""))); sbSqlInner.AppendLine(string.Format(GlobalFn.isNullRet(Admi650Model.azp04, ""))); } #endregion #region 取得where sbSqlWhere = new StringBuilder(); if (GlobalFn.isNullRet(Admi650Model.azp05, "") != "") { sbSqlWhere.AppendLine(string.Format("WHERE " + GlobalFn.isNullRet(Admi650Model.azp05, ""))); } else { sbSqlWhere.AppendLine(string.Format("WHERE 1=1 ")); } #endregion //組合好不含order by的字串 StrSqlBody = sbSqlSelect.ToString() + sbSqlFrom.ToString() + sbSqlInner.ToString() + sbSqlWhere.ToString(); if (MsgInfoReturned.StrWhereAppend != "") { StrSqlBody += MsgInfoReturned.StrWhereAppend; } if (MsgInfoReturned.ParamSearchList == null) { DtMaster = BoMaster.OfGetDataTable(StrSqlBody + " AND 1<>1 "); } else { DtMaster = BoMaster.OfGetDataTable(StrSqlBody + " AND 1<>1 ", MsgInfoReturned.ParamSearchList.ToArray()); } //修改column 型別為string 並將實際的型別丟到 column.prefix foreach (DataColumn dcTempColumn in DtMaster.Columns) { if (dcTempColumn.Prefix != "") { continue; } dcTempColumn.Prefix = dcTempColumn.DataType.Name; dcTempColumn.DataType = typeof(string); } BindingMaster.DataSource = DtMaster; uGrid_Master.DataSource = BindingMaster; } catch (Exception ex) { throw ex; } }
private void WfIniSqlBody() { StringBuilder sbSql; StringBuilder sbSqlSelect, sbSqlFrom, sbSqlInner, sbSqlWhere; //string masterTable; //string distinctYn; try { if (MsgInfoReturned == null) { throw new Exception("未實體化msgInfoReturned"); } sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" SELECT * FROM azp_tb WHERE azp01='{0}' ", _strPickNo)); _drAzp = _boMaster.OfGetDataRow(sbSql.ToString()); if (_drAzp == null) { throw new Exception("查無此pick_no!"); } sbSql = new StringBuilder(); sbSql.AppendLine(string.Format(" SELECT * FROM azq_tb WHERE azq01='{0}' ", _strPickNo)); sbSql.AppendLine(string.Format(" ORDER BY azq02 ")); _dtAzq = _boMaster.OfGetDataTable(sbSql.ToString()); if (_drAzp == null) { throw new Exception("查無此pick_no 明細資料!"); } _azpModel = _drAzp.ToItem <azp_tb>(); _azqTbList = _dtAzq.ToList <azq_tb>(); //distinctYn = GlobalFn.isNullRet(_drAzp["azp06"], "N"); //masterTable = GlobalFn.isNullRet(_drAzp["azp03"], ""); //this.Text = GlobalFn.isNullRet(_drAzp["azp01"], "") + " " + GlobalFn.isNullRet(_drAzp["azp02"], ""); //設定form名稱 this.Text = string.Format("{0} {1}", _azpModel.azp01, _azpModel.azp02); #region 取得select body sbSqlSelect = new StringBuilder(); sbSqlSelect.AppendLine(string.Format("SELECT ")); if (_azpModel.azp06 == "Y") { sbSqlSelect.AppendLine(string.Format("DISTINCT")); } if (iMaxPickRow != 1) //表示多選,增加is_pick_yn { sbSqlSelect.AppendLine(string.Format("convert(nvarchar(1),'N') is_pick,")); //0,1 } //組合select 欄位,最後一欄不加, for (int i = 0; i < _azqTbList.Count; i++) { var selectField = string.Format(_azqTbList[i].azq03 + "." + _azqTbList[i].azq04); if (i != _azqTbList.Count - 1) { selectField += ","; } sbSqlSelect.AppendLine(selectField); } #endregion #region 取得from body sbSqlFrom = new StringBuilder(); sbSqlFrom.AppendLine(string.Format("FROM " + _azpModel.azp03)); #endregion #region 取得join sbSqlInner = new StringBuilder(); if (GlobalFn.isNullRet(_drAzp["azp04"], "") != "") { sbSqlInner.AppendLine(string.Format(GlobalFn.isNullRet(_drAzp["azp04"], ""))); } #endregion #region 取得where sbSqlWhere = new StringBuilder(); if (GlobalFn.isNullRet(_drAzp["azp05"], "") != "") { sbSqlWhere.AppendLine(string.Format("WHERE " + GlobalFn.isNullRet(_drAzp["azp05"], ""))); } else { sbSqlWhere.AppendLine(string.Format("WHERE 1=1 ")); } #endregion //組合好不含order by的字串 _strSqlBody = sbSqlSelect.ToString() + sbSqlFrom.ToString() + sbSqlInner.ToString() + sbSqlWhere.ToString(); if (MsgInfoReturned.StrWhereAppend != "") { _strSqlBody += MsgInfoReturned.StrWhereAppend; } if (MsgInfoReturned.ParamSearchList == null) { _dtMaster = _boMaster.OfGetDataTable(_strSqlBody + " AND 1<>1 "); } else { _dtMaster = _boMaster.OfGetDataTable(_strSqlBody + " AND 1<>1 ", MsgInfoReturned.ParamSearchList.ToArray()); } //修改column 型別為string 並將實際的型別丟到 column.prefix foreach (DataColumn dcTempColumn in _dtMaster.Columns) { if (dcTempColumn.Prefix != "") { continue; } dcTempColumn.Prefix = dcTempColumn.DataType.Name; dcTempColumn.DataType = typeof(string); } BindingMaster.DataSource = _dtMaster; uGrid_Master.DataSource = BindingMaster; } catch (Exception ex) { throw ex; } }