/// <summary> /// 使用動態查詢 /// </summary> /// <param name="ParameterList">放入變數</param> /// <returns>回傳查詢結果</returns> public DataTable doQueryByFind(ArrayList RowParameterList, ArrayList ConditionParameterList ) { //ID,CODE,NAME,CREATEDATE,CREATEUID,UPDATEDATE,UPDATEUID,Enable try { #region 選取欄位 //選取欄位struct SQLHelper.SelectField[] selectfieldEntry = new SQLHelper.SelectField[9]; selectfieldEntry[0].FieldName = "ID"; selectfieldEntry[0].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[1].FieldName = "CODE"; selectfieldEntry[1].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[2].FieldName = "NAME"; selectfieldEntry[2].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[3].FieldName = "CREATEDATE"; selectfieldEntry[3].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[4].FieldName = "CREATEUID"; selectfieldEntry[4].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[5].FieldName = "UPDATEDATE"; selectfieldEntry[5].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[6].FieldName = "UPDATEUID"; selectfieldEntry[6].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[7].FieldName = "Enable"; selectfieldEntry[7].FieldType = SQLHelper.SelectFiledType.NormalField; selectfieldEntry[8].FieldName = "Count(*)"; selectfieldEntry[8].FieldType = SQLHelper.SelectFiledType.GroupFiled; #endregion #region 選取資料表 SQLHelper.TableView[] TableViewEntry = new SQLHelper.TableView[1]; TableViewEntry[0].TableOrViewName = "STD_STDMaster"; #endregion #region RowNum檢查 SQLHelper.RowNumProcess RownumEntry; if (RowParameterList.Count > 0) { RownumEntry = (SQLHelper.RowNumProcess)RowParameterList[0]; } else { RownumEntry = new SQLHelper.RowNumProcess(); RownumEntry.UseRowNum = false; RownumEntry.RowNumValue = 0; } #endregion #region 選取條件 SQLHelper.QueryCondition[] conditionEntry = new SQLHelper.QueryCondition[ConditionParameterList.Count]; for (int i = 0; i < ConditionParameterList.Count; i++) { conditionEntry[i] = (SQLHelper.QueryCondition)ConditionParameterList[i]; } #endregion #region Group By Having SQLHelper.QueryCondition[] HavingEntry = new SQLHelper.QueryCondition[1]; HavingEntry[0].ConditionTSQLOperator = SQLHelper.TSQLOperator.None; HavingEntry[0].ConditionField = "Count(*)"; HavingEntry[0].ConditionFieldOperator = SQLHelper.FieldOperator.Equal; HavingEntry[0].ConditionFieldValue.ValueType = SQLHelper.FieldValueType.NUMBER; HavingEntry[0].ConditionFieldValue.Value = "1"; #endregion #region Order By SQLHelper.OrderByField[] OrderEntry = new SQLHelper.OrderByField[2]; OrderEntry[0].FieldName = "CODE"; OrderEntry[0].FileOrderByType = SQLHelper.OrderByType.Asc; OrderEntry[1].FieldName = "Name"; OrderEntry[1].FileOrderByType = SQLHelper.OrderByType.Desc; #endregion SQLHelper.QueryStruct QueryEntry = new SQLHelper.QueryStruct(); QueryEntry.SelectFieldList = selectfieldEntry; QueryEntry.SelectTableViewList = TableViewEntry; QueryEntry.RowNumCheck = RownumEntry; QueryEntry.ConditionList = conditionEntry; QueryEntry.GroupByProcess = true; QueryEntry.HavingList = HavingEntry; QueryEntry.OrderByList = OrderEntry; string cmdStr = SQLHelper.GenerSQL(QueryEntry); DbCommand cmd = db.GetSqlStringCommand(cmdStr); return db.ExecuteDataSet(cmd).Tables[0]; } catch (Exception ex) { throw new Exception("呼叫STD_MasterDBO.doQueryByFind()發生錯誤,錯誤訊息:" + ex.Message); } }
}//ButtonNew_Click #endregion #region 查詢按鈕事件 /// <summary> /// 查詢相關事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void ButtonQuery_Click(object sender, EventArgs e) { try { ErrorMsgLabel.Text = ""; ParameterList.Clear(); #region RowNum處理 //Rownum ArrayList ArrayList RowList = new ArrayList(); SQLModel.SQLHelper.RowNumProcess RowEntry = new SQLModel.SQLHelper.RowNumProcess(); RowEntry.UseRowNum = true; RowEntry.RowNumValue = (TextBoxRowCountLimit.Text=="") ? 0: int.Parse(TextBoxRowCountLimit.Text); RowList.Add(RowEntry); ParameterList.Add(RowList); #endregion #region 動態查詢條件 //條件LIST ArrayList ConditionList = new ArrayList(); //加入動態查詢條件 SQLModel.SQLHelper.QueryCondition ConditionEntry; #region 代號 if (TextBoxCode.Text != "") { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "CODE", CheckBoxLikeSearch.Checked, SQLModel.SQLHelper.FieldOperator.Equal, SQLModel.SQLHelper.FieldValueType.STRING, TextBoxCode.Text, ref ConditionList ); } #endregion #region 有效 if (TextBoxEnable.Text != "") { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "ENABLE", false, SQLModel.SQLHelper.FieldOperator.Equal, SQLModel.SQLHelper.FieldValueType.STRING, (bool.Parse(TextBoxEnable.Text)) ? "1":"0", ref ConditionList ); } #endregion #region 名稱 if (TextBoxName.Text != "") { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "Name", CheckBoxLikeSearch.Checked, SQLModel.SQLHelper.FieldOperator.Equal, SQLModel.SQLHelper.FieldValueType.STRING, TextBoxName.Text, ref ConditionList ); } #endregion #region 建立日期 if ((TextBoxCreateDate.StartDate != "") && (TextBoxCreateDate.EndDate != "")) { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "CREATEDATE", false, SQLModel.SQLHelper.FieldOperator.MoreEqual, SQLModel.SQLHelper.FieldValueType.Date, TextBoxCreateDate.StartDate, ref ConditionList ); SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "CREATEDATE", false, SQLModel.SQLHelper.FieldOperator.LessEqual, SQLModel.SQLHelper.FieldValueType.Date, TextBoxCreateDate.EndDate, ref ConditionList ); } #endregion #region 建立人員 if (TextBoxCreateUID.Text != "") { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "CREATEUID", CheckBoxLikeSearch.Checked, SQLModel.SQLHelper.FieldOperator.Equal, SQLModel.SQLHelper.FieldValueType.STRING, TextBoxCreateUID.Text, ref ConditionList ); } #endregion #region 維護日期 if ((TextBoxUpdateDate.StartDate != "") && (TextBoxUpdateDate.EndDate != "")) { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "UPDATEDATE", false, SQLModel.SQLHelper.FieldOperator.MoreEqual, SQLModel.SQLHelper.FieldValueType.Date, TextBoxUpdateDate.StartDate, ref ConditionList ); SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "UPDATEDATE", false, SQLModel.SQLHelper.FieldOperator.LessEqual, SQLModel.SQLHelper.FieldValueType.Date, TextBoxUpdateDate.EndDate, ref ConditionList ); } #endregion #region 維護人員 if (TextBoxUpdateUID.Text != "") { SQLModel.SQLHelper.AddConditionEntry(SQLModel.SQLHelper.TSQLOperator.AND, "UPDATEUID", CheckBoxLikeSearch.Checked, SQLModel.SQLHelper.FieldOperator.Equal, SQLModel.SQLHelper.FieldValueType.STRING, TextBoxUpdateUID.Text, ref ConditionList ); } #endregion ParameterList.Add(ConditionList); #endregion //throw new Exception("test"); databind(); } catch (Exception ex) { WaringLogProcess(ex.Message); ErrorMsgLabel.Text = ex.Message; } }