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