/// <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);
            }

        }
Exemple #2
0
    }//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;            
        }
    }