|
很多通用查詢器，對(duì)查詢條件中的AND及OR的支持度不是很好，要么全部是AND要么全部是OR。筆者通過一段時(shí)間的摸索，終于完成了一個(gè)自己較為滿意的通用查詢器， 可以實(shí)現(xiàn)多條件的AND及OR，現(xiàn)將實(shí)現(xiàn)過程記錄一下： 1、在App.config中添加數(shù)據(jù)庫連接字符串。 <connectionStrings> <add name ="connString" connectionString="server=.;database=db_test;uid=sa;pwd=********;"/> </connectionStrings> 2、添加一個(gè)數(shù)據(jù)庫操作幫助類，命名為DBHelper。 
/// <summary> /// SQL數(shù)據(jù)庫訪問類 /// </summary> public static class DBHelper { private static SqlConnection conn = null; /// <summary> /// 連接對(duì)象 /// </summary> public static SqlConnection Connection { get { string connString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString; if (conn == null) { try { conn = new SqlConnection(connString); } catch (Exception) { throw; } } return conn; } } /// <summary> /// 打開數(shù)據(jù)庫連接 /// </summary> public static void Open() { if (conn.State != ConnectionState.Open) { conn.Open(); } } /// <summary> /// 關(guān)閉數(shù)據(jù)庫連接 /// </summary> public static void Close() { if (conn.State == ConnectionState.Open) { conn.Close(); } } /// <summary> /// 創(chuàng)建一個(gè)新的命令對(duì)象 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <param name="parameters">參數(shù)數(shù)組</param> private static SqlCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = commandText; cmd.CommandType = commandType; cmd.Connection = Connection; if (parameters != null) { foreach (SqlParameter param in parameters) { cmd.Parameters.Add(param); } } return cmd; } } catch (Exception) { throw; } } /// <summary> /// 執(zhí)行SQL命令，并輸出影響的行數(shù)。 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <return>返回影響的行數(shù)</return> public static int RunCommand(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); Close(); return rows; } } catch (Exception) { throw; } } /// <summary> /// 執(zhí)行增刪改的方法 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns>成功返回true</returns> public static bool Save(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); int n = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); Close(); if (n > 0) return true; else return false; } } catch (Exception) { throw; } } /// <summary> /// 執(zhí)行增刪改的方法 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns>成功返回true</returns> public static bool Save(string commandText, params SqlParameter[] parameters) { try { return Save(commandText, CommandType.Text, parameters); } catch (Exception) { throw; } } /// <summary> /// 獲得DataTable /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns></returns> public static DataTable GetTable(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { SqlDataAdapter da = new SqlDataAdapter { SelectCommand = cmd }; DataTable dt = new DataTable(); da.Fill(dt); cmd.Parameters.Clear(); Close(); return dt; } } catch (Exception) { throw; } } /// <summary> /// 獲得DataTable /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns></returns> public static DataTable GetTable(string commandText, params SqlParameter[] parameters) { try { return GetTable(commandText,CommandType.Text,parameters); } catch (Exception) { throw; } } /// <summary> /// 獲得DataTable /// </summary> /// <param name="commandText">要執(zhí)行的sql語句</param> /// <returns></returns> public static DataTable GetTable(string commandText) { return GetTable(commandText, CommandType.Text, null); } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, CommandType commandType, params SqlParameter[] parameters) { try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); return cmd.ExecuteReader(); } } catch (Exception) { throw; } } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, params SqlParameter[] parameters) { return GetReader(commandText, CommandType.Text, parameters); } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, CommandType commandType) { return GetReader(commandText, commandType, null); } /// <summary> /// 獲得SqlDataReader /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText) { return GetReader(commandText, CommandType.Text, null); } /// <summary> /// 執(zhí)行SQL命令，并返回一個(gè)值。 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns></returns> public static object GetScalar(string commandText, CommandType commandType, params SqlParameter[] parameters) { object obj = null; try { using (SqlCommand cmd = CreateCommand(commandText, commandType, parameters)) { Open(); obj = cmd.ExecuteScalar(); cmd.Parameters.Clear(); Close(); return obj; } } catch (Exception) { throw; } } /// <summary> /// 執(zhí)行SQL命令，并返回一個(gè)值。 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="parameters">參數(shù)數(shù)組</param> /// <returns></returns> public static object GetScalar(string commandText, params SqlParameter[] parameters) { return GetScalar(commandText, CommandType.Text, parameters); } /// <summary> /// 執(zhí)行SQL命令，并返回一個(gè)值。 /// </summary> /// <param name="commandText">要執(zhí)行的sql語句或存儲(chǔ)過程名稱</param> /// <param name="commandType">CommandType枚舉值，表示執(zhí)行sql語句還是存儲(chǔ)過程。</param> /// <returns></returns> public static object GetScalar(string commandText, CommandType commandType) { return GetScalar(commandText, commandType, null); } /// <summary> /// 執(zhí)行SQL命令，并返回一個(gè)值。 /// </summary> /// <returns></returns> public static object GetScalar(string commandText) { return GetScalar(commandText, CommandType.Text, null); } } 3、添加一個(gè)數(shù)據(jù)類型轉(zhuǎn)換類，命名為SqlDbTypeHelper。
/// <summary> /// SqlDbType轉(zhuǎn)換為C#數(shù)據(jù)類型 /// </summary> /// <param name="sqlDbType"></param> /// <returns></returns> public static Type SqlDbTypeToCsharpType(SqlDbType sqlDbType) { switch (sqlDbType) { case SqlDbType.BigInt: return typeof(Int64); case SqlDbType.Binary: return typeof(Object); case SqlDbType.Bit: return typeof(Boolean); case SqlDbType.Char: return typeof(String); case SqlDbType.DateTime: return typeof(DateTime); case SqlDbType.Decimal: return typeof(Decimal); case SqlDbType.Float: return typeof(Double); case SqlDbType.Image: return typeof(Object); case SqlDbType.Int: return typeof(Int32); case SqlDbType.Money: return typeof(Decimal); case SqlDbType.NChar: return typeof(String); case SqlDbType.NText: return typeof(String); case SqlDbType.NVarChar: return typeof(String); case SqlDbType.Real: return typeof(Single); case SqlDbType.SmallDateTime: return typeof(DateTime); case SqlDbType.SmallInt: return typeof(Int16); case SqlDbType.SmallMoney: return typeof(Decimal); case SqlDbType.Text: return typeof(String); case SqlDbType.Timestamp: return typeof(Object); case SqlDbType.TinyInt: return typeof(Byte); case SqlDbType.Udt://自定義的數(shù)據(jù)類型 return typeof(Object); case SqlDbType.UniqueIdentifier: return typeof(Object); case SqlDbType.VarBinary: return typeof(Object); case SqlDbType.VarChar: return typeof(String); case SqlDbType.Variant: return typeof(Object); case SqlDbType.Xml: return typeof(Object); default: return null; } } /// <summary> /// SQL Server數(shù)據(jù)類型轉(zhuǎn)換為SqlDbType類型 /// </summary> /// <param name="sqlTypeString"></param> /// <returns></returns> public static SqlDbType SqlTypeStringToSqlDbType(string sqlTypeString) { SqlDbType dbType = SqlDbType.Variant; //默認(rèn)為Object switch (sqlTypeString) { case "int": dbType = SqlDbType.Int; break; case "varchar": dbType = SqlDbType.VarChar; break; case "bit": dbType = SqlDbType.Bit; break; case "datetime": dbType = SqlDbType.DateTime; break; case "decimal": dbType = SqlDbType.Decimal; break; case "float": dbType = SqlDbType.Float; break; case "image": dbType = SqlDbType.Image; break; case "money": dbType = SqlDbType.Money; break; case "ntext": dbType = SqlDbType.NText; break; case "nvarchar": dbType = SqlDbType.NVarChar; break; case "smalldatetime": dbType = SqlDbType.SmallDateTime; break; case "smallint": dbType = SqlDbType.SmallInt; break; case "text": dbType = SqlDbType.Text; break; case "bigint": dbType = SqlDbType.BigInt; break; case "binary": dbType = SqlDbType.Binary; break; case "char": dbType = SqlDbType.Char; break; case "nchar": dbType = SqlDbType.NChar; break; case "numeric": dbType = SqlDbType.Decimal; break; case "real": dbType = SqlDbType.Real; break; case "smallmoney": dbType = SqlDbType.SmallMoney; break; case "sql_variant": dbType = SqlDbType.Variant; break; case "timestamp": dbType = SqlDbType.Timestamp; break; case "tinyint": dbType = SqlDbType.TinyInt; break; case "uniqueidentifier": dbType = SqlDbType.UniqueIdentifier; break; case "varbinary": dbType = SqlDbType.VarBinary; break; case "xml": dbType = SqlDbType.Xml; break; } return dbType; } 4、添加一個(gè)自定義控件，命名為：ConditionControl。
注：底下的是panel1，上面的控件名分別為：cmbLeft1、cmbFieldText1、cmbOperator1、txtValue1、cmbRight1、cmbRelation1、btnAdd、btnRemove。 5、ConditionControl的代碼實(shí)現(xiàn)：
public partial class ConditionControl : UserControl { #region 字段 private int conditionCount = 1; //panel個(gè)數(shù) private int panelSpace = 2; //panel間隔 private string[] tempFieldNames, tempFieldTypes, tempFieldTexts; private Control tempTargetControl; //添加ConditionControl控件承載控件 #endregion #region 屬性 //字段名 public string[] FieldNames { get { return tempFieldNames; } set { if (value != null) { tempFieldNames = new string[value.Length]; Array.Copy(value, tempFieldNames, value.Length); } } } //字段數(shù)據(jù)類型 public string[] FieldTypes { get { return tempFieldTypes; } set { if (value != null) { tempFieldTypes = new string[value.Length]; Array.Copy(value, tempFieldTypes, value.Length); } } } //字段文本 public string[] FieldTexts { get { return tempFieldTexts; } set { if (value != null) { tempFieldTexts = new string[value.Length]; Array.Copy(value, tempFieldTexts, value.Length); } } } //要處理的控件 public Control TargetControl { get { return tempTargetControl; } set { if (value != null) { tempTargetControl = value; } } } #endregion #region 構(gòu)造函數(shù) /// <summary> /// 構(gòu)造函數(shù) /// </summary> public ConditionControl() { InitializeComponent(); } #endregion #region 設(shè)置其它下拉框數(shù)據(jù)源 /// <summary> /// 設(shè)置左括號(hào)下拉框數(shù)據(jù)源 /// </summary> /// <param name="comboBox"></param> private void SetLeftDataSource(ComboBox comboBox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow newRow = dt.NewRow(); newRow["Name"] = ""; newRow["Value"] = ""; dt.Rows.Add(newRow); newRow = dt.NewRow(); newRow["Name"] = "("; newRow["Value"] = "("; dt.Rows.Add(newRow); comboBox.DataSource = dt; comboBox.DisplayMember = "Name"; comboBox.ValueMember = "Value"; } /// <summary> /// 設(shè)置字段文本下拉框數(shù)據(jù)源 /// </summary> /// <param name="comboBox"></param> private void SetFieldTextDataSource(ComboBox comboBox) { if (VerifyFieldMatch()) { comboBox.Items.AddRange(tempFieldTexts); } } /// <summary> /// 設(shè)置右括號(hào)下拉框數(shù)據(jù)源 /// </summary> /// <param name="comboBox"></param> private void SetRightDataSource(ComboBox comboBox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow newRow = dt.NewRow(); newRow["Name"] = ""; newRow["Value"] = ""; dt.Rows.Add(newRow); newRow = dt.NewRow(); newRow["Name"] = ")"; newRow["Value"] = ")"; dt.Rows.Add(newRow); comboBox.DataSource = dt; comboBox.DisplayMember = "Name"; comboBox.ValueMember = "Value"; } /// <summary> /// 設(shè)置關(guān)系符下拉框數(shù)據(jù)源 /// </summary> /// <param name="combox"></param> private void SetRelationDataSource(ComboBox comboBox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow newRow = dt.NewRow(); newRow["Name"] = "并且"; newRow["Value"] = "AND"; dt.Rows.Add(newRow); newRow = dt.NewRow(); newRow["Name"] = "或者"; newRow["Value"] = "OR"; dt.Rows.Add(newRow); comboBox.DataSource = dt; comboBox.DisplayMember = "Name"; comboBox.ValueMember = "Value"; } #endregion #region 初始化 public void Initialize() { if (VerifyFieldMatch()) { //左括號(hào) SetLeftDataSource(cmbLeft1); //字段文本 if (tempFieldTexts[0] == "") { SetFieldTextDataSource(cmbFieldText1); } else { //第一行設(shè)為"" List<string> listFieldName = tempFieldNames.ToList(); listFieldName.Insert(0, ""); tempFieldNames = listFieldName.ToArray(); List<string> listFieldType = tempFieldTypes.ToList(); listFieldType.Insert(0, ""); tempFieldTypes = listFieldType.ToArray(); List<string> listFieldText = tempFieldTexts.ToList(); listFieldText.Insert(0, ""); tempFieldTexts = listFieldText.ToArray(); SetFieldTextDataSource(cmbFieldText1); } //右括號(hào) SetRightDataSource(cmbRight1); //關(guān)系符 SetRelationDataSource(cmbRelation1); } } #endregion #region 驗(yàn)證字段是否匹配 /// <summary> /// 驗(yàn)證字段是否匹配 /// </summary> /// <returns>通過返回true</returns> public bool VerifyFieldMatch() { if (tempFieldNames == null || tempFieldTypes == null || tempFieldTexts == null) { MessageBox.Show("字段的名稱或數(shù)據(jù)類型或標(biāo)題未賦值。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } else { if (tempFieldNames.Length != tempFieldTypes.Length || tempFieldNames.Length != tempFieldTexts.Length) { MessageBox.Show("字段的名稱或數(shù)據(jù)類型或標(biāo)題長(zhǎng)度不一致。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } } return true; } #endregion #region 查找控件 /// <summary> /// 查找Panel /// </summary> /// <param name="panelName">panel名</param> /// <returns>返回panel</returns> private Control FindPanel(string panelName) { foreach (Control ctrl in Controls) { if (ctrl.Name == panelName) { return ctrl; } } return null; } /// <summary> /// 查找Panel中指定的控件 /// </summary> /// <param name="panelName">panel名</param> /// <param name="controlName">要找的控件名</param> /// <returns>返回控件</returns> private Control FindControl(string panelName, string controlName) { Control panel = FindPanel(panelName); if (panel != null) { foreach (Control ctrl in panel.Controls) { if (ctrl.Name == controlName) { return ctrl; } } } return null; } #endregion #region 根據(jù)數(shù)據(jù)類型進(jìn)行獲取 /// <summary> /// 根據(jù)數(shù)據(jù)類型返回其所屬類型 /// </summary> /// <param name="fieldType">字段類型</param> /// <returns>所屬類型</returns> private string GetKindByFieldType(string fieldType) { switch (fieldType.ToLower()) { //值為""時(shí)返回"" case "": return ""; //二進(jìn)制類型，無運(yùn)算符。 case "binary": case "varbinary": case "image": return null; //文本類型，可用（= like > >= < <= <>）運(yùn)算符。 case "char": case "nchar": case "varchar": case "nvarchar": case "text": case "ntext": return "text"; //數(shù)字、日期類型，只能用（= > >= < <= <>）運(yùn)算符。 case "datetime": case "smalldatetime": case "int": case "tinyint": case "smallint": case "bigint": case "float": case "money": case "smallmoney": case "real": case "decimal": case "numeric": return "number"; //bool類型，只能用（= <>）運(yùn)算符。 case "bit": return "bool"; default: return null; } } /// <summary> /// 根據(jù)數(shù)據(jù)類型返回對(duì)應(yīng)類型的字段值 /// </summary> /// <param name="fieldType">字段類型</param> /// <param name="value">字段值</param> /// <returns>對(duì)應(yīng)類型的字段值</returns> private object GetValueByFieldType(string fieldType, string value) { switch (fieldType.ToLower()) { //值為""時(shí)返回"" case "": return ""; //二進(jìn)制類型 case "binary": case "varbinary": case "image": return null; //文本類型 case "char": case "nchar": case "varchar": case "nvarchar": case "text": case "ntext": return value; //日期類型 case "datetime": case "smalldatetime": return DateTime.Parse(value).ToShortDateString(); //整型類型 case "int": return int.Parse(value); case "tinyint": return byte.Parse(value); case "smallint": return short.Parse(value); case "bigint": return long.Parse(value); //單精度類型 case "float": case "money": case "smallmoney": case "real": return float.Parse(value); //雙精度類型 case "decimal": case "numeric": return double.Parse(value); //bool類型 case "bit": return bool.Parse(value); default: return null; } } #endregion #region 設(shè)置運(yùn)算符下拉框數(shù)據(jù)源 /// <summary> /// 設(shè)置運(yùn)算符下拉框數(shù)據(jù)源(文本類型) /// </summary> /// <param name="combox">來源下拉框</param> /// <param name="isEmpty">值是否為""</param> private void SetOperatorDataSourceForText(ComboBox combox, bool isEmpty) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); if (isEmpty == true) { DataRow rowNew = dt.NewRow(); rowNew["Name"] = ""; rowNew["Value"] = ""; dt.Rows.Add(rowNew); } else { DataRow rowNew = dt.NewRow(); rowNew["Name"] = "等于"; rowNew["Value"] = "="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "包含"; rowNew["Value"] = "LIKE"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "大于"; rowNew["Value"] = ">"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "大于等于"; rowNew["Value"] = ">="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "小于"; rowNew["Value"] = "<"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "小于等于"; rowNew["Value"] = "<="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "不等于"; rowNew["Value"] = "<>"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "為NULL"; rowNew["Value"] = "IS NULL"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "不為NULL"; rowNew["Value"] = "IS NOT NULL"; dt.Rows.Add(rowNew); } combox.DataSource = dt; combox.DisplayMember = "Name"; combox.ValueMember = "Value"; } /// <summary> /// 設(shè)置運(yùn)算符下拉框數(shù)據(jù)源(數(shù)字、日期類型) /// </summary> /// <param name="combox"></param> private void SetOperatorDataSourceForNumber(ComboBox combox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow rowNew = dt.NewRow(); rowNew["Name"] = "等于"; rowNew["Value"] = "="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "大于"; rowNew["Value"] = ">"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "大于等于"; rowNew["Value"] = ">="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "小于"; rowNew["Value"] = "<"; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "小于等于"; rowNew["Value"] = "<="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "不等于"; rowNew["Value"] = "<>"; dt.Rows.Add(rowNew); combox.DataSource = dt; combox.DisplayMember = "Name"; combox.ValueMember = "Value"; } /// <summary> /// 設(shè)置運(yùn)算符下拉框數(shù)據(jù)源(bool類型) /// </summary> /// <param name="combox"></param> private void SetOperatorDataSourceForBool(ComboBox combox) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Name")); dt.Columns.Add(new DataColumn("Value")); DataRow rowNew = dt.NewRow(); rowNew["Name"] = "等于"; rowNew["Value"] = "="; dt.Rows.Add(rowNew); rowNew = dt.NewRow(); rowNew["Name"] = "不等于"; rowNew["Value"] = "<>"; dt.Rows.Add(rowNew); combox.DataSource = dt; combox.DisplayMember = "Name"; combox.ValueMember = "Value"; } #endregion #region 字段文本選擇改變時(shí) /// <summary> /// 字段文本選擇改變時(shí) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void cmbFieldText_SelectedIndexChanged(object sender, EventArgs e) { int index = -1; ComboBox cmbFieldText = sender as ComboBox; if (cmbFieldText != null) { index = cmbFieldText.SelectedIndex; } if (index == -1) { return; } string i = cmbFieldText.Name.Substring(12); string fieldType = tempFieldTypes[index].ToLower(); ComboBox cmbOperator = FindControl("panel" + i, "cmbOperator" + i) as ComboBox; //如果不是日期類型 if (fieldType != "datetime" && fieldType != "smalldatetime") { Control txtValue = FindControl("panel" + i, "txtValue" + i); if (txtValue != null) { //如果是日期控件 if (txtValue.GetType().Name == "DateTimePicker") { Control panelI = FindPanel("panel" + i); if (panelI != null) { Point point = txtValue.Location; Size size = new Size(txtValue.Width, txtValue.Height); panelI.Controls.Remove(txtValue); TextBox txtValueI = new TextBox { Name = "txtValue" + i, Location = point, Size = size }; panelI.Controls.Add(txtValueI); } } else { if (txtValue.GetType().Name == "TextBox") { if (fieldType == "") { txtValue.Text = ""; } } } } } switch (GetKindByFieldType(fieldType).ToLower()) { case "": SetOperatorDataSourceForText(cmbOperator, true); break; case "text": SetOperatorDataSourceForText(cmbOperator, false); break; case "number": SetOperatorDataSourceForNumber(cmbOperator); //如果是日期類型 if (fieldType == "datetime" || fieldType == "smalldatetime") { Control panelI = FindPanel("panel" + i); if (panelI != null) { Control txtValueI = FindControl("panel" + i, "txtValue" + i); if (txtValueI != null) { Point point = txtValueI.Location; Size size = new Size(txtValueI.Width, txtValueI.Height); panelI.Controls.Remove(txtValueI); DateTimePicker dateTimePicker = new DateTimePicker { Name = "txtValue" + i, Location = point, Size = size, CustomFormat = "yyyy-MM-dd", Format = DateTimePickerFormat.Custom }; panelI.Controls.Add(dateTimePicker); } } } break; case "bool": SetOperatorDataSourceForBool(cmbOperator); break; default: break; } } #endregion #region 加減按鈕 /// <summary> /// 加按鈕 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnAdd_Click(object sender, EventArgs e) { try { if (tempTargetControl != null) { //驗(yàn)證字段是否符合 if (!VerifyFieldMatch()) { return; } conditionCount++; //panel Point pointPanel = new Point(panel1.Location.X, panel1.Location.Y + (panel1.Height + panelSpace) * (conditionCount - 1)); Size sizePanel = new Size(panel1.Width, panel1.Height); Panel panelI = new Panel { Name = "panel" + conditionCount.ToString(), Location = pointPanel, Size = sizePanel }; //左括號(hào) Size sizeLeft = new Size(cmbLeft1.Width, cmbLeft1.Height); ComboBox cmbLeftI = new ComboBox { FormattingEnabled = true, Name = "cmbLeft" + conditionCount.ToString(), Size = sizeLeft, DropDownStyle = ComboBoxStyle.DropDownList }; //字段文本 Size sizeFieldText = new Size(cmbFieldText1.Width, cmbFieldText1.Height); ComboBox cmbFieldTextI = new ComboBox { FormattingEnabled = true, Name = "cmbFieldText" + conditionCount.ToString(), Size = sizeFieldText, DropDownStyle = ComboBoxStyle.DropDownList }; //運(yùn)算符 Size sizeOperator = new Size(cmbOperator1.Width, cmbOperator1.Height); ComboBox cmbOperatorI = new ComboBox { FormattingEnabled = true, Name = "cmbOperator" + conditionCount.ToString(), Size = sizeOperator, DropDownStyle = ComboBoxStyle.DropDownList }; //文本 Size sizeValue = new Size(txtValue1.Width, txtValue1.Height); TextBox txtValueI = new TextBox { Name = "txtValue" + conditionCount.ToString(), Size = sizeValue }; //右括號(hào) Size sizeRight = new Size(cmbRight1.Width, cmbRight1.Height); ComboBox cmbRightI = new ComboBox { FormattingEnabled = true, Name = "cmbRight" + conditionCount.ToString(), Size = sizeRight, DropDownStyle = ComboBoxStyle.DropDownList }; //關(guān)系符 Size sizeRelation = new Size(cmbRelation1.Width, cmbRelation1.Height); ComboBox cmbRelationI = new ComboBox { FormattingEnabled = true, Name = "cmbRelation" + conditionCount.ToString(), Size = sizeRelation, DropDownStyle = ComboBoxStyle.DropDownList }; //字段文本注冊(cè)事件 cmbFieldTextI.SelectedIndexChanged += new EventHandler(cmbFieldText_SelectedIndexChanged); //設(shè)置數(shù)據(jù)源 SetLeftDataSource(cmbLeftI); SetFieldTextDataSource(cmbFieldTextI); SetRightDataSource(cmbRightI); SetRelationDataSource(cmbRelationI); //將控件添加到panelI panelI.Controls.Add(cmbLeftI); cmbLeftI.Left += cmbLeft1.Left; cmbLeftI.Top += cmbLeft1.Top; panelI.Controls.Add(cmbFieldTextI); cmbFieldTextI.Left += cmbFieldText1.Left; cmbFieldTextI.Top += cmbFieldText1.Top; panelI.Controls.Add(cmbOperatorI); cmbOperatorI.Left += cmbOperator1.Left; cmbOperatorI.Top += cmbOperator1.Top; panelI.Controls.Add(txtValueI); txtValueI.Left += txtValue1.Left; txtValueI.Top += txtValue1.Top; panelI.Controls.Add(cmbRightI); cmbRightI.Left += cmbRight1.Left; cmbRightI.Top += cmbRight1.Top; panelI.Controls.Add(cmbRelationI); cmbRelationI.Left += cmbRelation1.Left; cmbRelationI.Top += cmbRelation1.Top; //添加panelI Controls.Add(panelI); Height += panel1.Height + panelSpace; tempTargetControl.Height += panel1.Height + panelSpace; } } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } /// <summary> /// 減按鈕 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnRemove_Click(object sender, EventArgs e) { if (tempTargetControl != null) { if (conditionCount > 1) { Control panelI = FindPanel("panel" + conditionCount.ToString()); if (panelI != null) { Controls.Remove(panelI); Height -= panelI.Height + panelSpace; tempTargetControl.Height -= panelI.Height + panelSpace; conditionCount--; } } } } #endregion #region 獲取Where條件 /// <summary> /// 獲取Where條件 /// </summary> /// <param name="parameters"></param> /// <returns>Where條件</returns> public string GetWhereCondition(out SqlParameter[] parameters) { parameters = null; //驗(yàn)證字段是否符合 if (!VerifyFieldMatch()) { return string.Empty; } //遍歷產(chǎn)生Where條件 StringBuilder sbWhere = new StringBuilder(); List<SqlParameter> lstParams = new List<SqlParameter>(); int leftCount = 0, rightCount = 0; for (int i = 1; i <= conditionCount; i++) { //所選字段序號(hào)及文本 int index = -1; string fieldText = ""; if (FindControl("panel" + i, "cmbFieldText" + i) is ComboBox cmbFieldText) { index = cmbFieldText.SelectedIndex; fieldText = cmbFieldText.Text; } //左括號(hào) ComboBox cmbLeft = FindControl("panel" + i, "cmbLeft" + i) as ComboBox; if (cmbLeft != null) { if (cmbLeft.Text != string.Empty) { leftCount++; if (i == 1) { sbWhere.Append("("); } else { sbWhere.Append(" " + "("); } } } //字段文本 if (index != -1 && fieldText != "") { if ((cmbLeft != null && cmbLeft.Text != string.Empty) || i == 1) { sbWhere.Append(tempFieldNames[index]); } else { sbWhere.Append(" " + tempFieldNames[index]); } } //運(yùn)算符 ComboBox cmbOperator = null; if (index != -1 && fieldText != "") { cmbOperator = FindControl("panel" + i, "cmbOperator" + i) as ComboBox; if (cmbOperator != null && cmbOperator.SelectedIndex != -1) { sbWhere.Append(" " + cmbOperator.SelectedValue.ToString()); } } //文本值 if (index != -1 && fieldText != "") { Control txtValue = FindControl("panel" + i, "txtValue" + i); if (txtValue != null) { string strKind = GetKindByFieldType(tempFieldTypes[index]); var strValue = GetValueByFieldType(tempFieldTypes[index], txtValue.Text); //SQL參數(shù)化查詢(防注入) SqlParameter param = new SqlParameter { ParameterName = "@" + txtValue.Name, SqlDbType = SqlDbTypeHelper.SqlTypeStringToSqlDbType(tempFieldTypes[index]) }; param.Value = strValue; lstParams.Add(param); if (strKind == "text") { if (cmbOperator != null) { switch (cmbOperator.SelectedValue.ToString().ToUpper()) { case "LIKE": sbWhere.Append(" " + "'%'+" + "@" + txtValue.Name + "+'%'"); break; case "IS NULL": txtValue.Text = string.Empty; break; case "IS NOT NULL": txtValue.Text = string.Empty; break; default: sbWhere.Append(" " + "@" + txtValue.Name); break; } } } else { sbWhere.Append(" " + "@" + txtValue.Name); } } } //右括號(hào) if (FindControl("panel" + i, "cmbRight" + i) is ComboBox cmbRight && cmbRight.Text != string.Empty) { rightCount++; if (rightCount > leftCount) { MessageBox.Show("左括號(hào)與右括號(hào)不匹配。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return string.Empty; } sbWhere.Append(")"); } //關(guān)系符 if (FindControl("panel" + i, "cmbRelation" + i) is ComboBox cmbRelation) { if (i < conditionCount) { sbWhere.Append(" " + cmbRelation.SelectedValue.ToString()); } } } //括號(hào)匹配 if (leftCount != rightCount) { MessageBox.Show("左括號(hào)與右括號(hào)不匹配。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return string.Empty; } //處理無效關(guān)鍵字及開頭或末尾是AND或OR string strWhere = sbWhere.ToString().Trim(); Dictionary<string, string> dictInvalid = new Dictionary<string, string>(); dictInvalid.Add("()", ""); dictInvalid.Add("( ", "("); dictInvalid.Add(" )", ")"); dictInvalid.Add("(AND", "("); dictInvalid.Add("(OR", "("); dictInvalid.Add("AND)", ")"); dictInvalid.Add("OR)", ")"); dictInvalid.Add("(AND)", ""); dictInvalid.Add("(OR)", ""); dictInvalid.Add("AND AND", "AND"); dictInvalid.Add("AND OR", "AND"); dictInvalid.Add("OR AND", "OR"); dictInvalid.Add("OR OR", " OR"); dictInvalid.Add(" ", " "); for (int i = 0; i < 99; i++) { //處理次數(shù) int j = 0; //處理開頭[AND] if (strWhere.Length >= 3) { if (strWhere.ToUpper().Substring(0, 3) == "AND") { strWhere = strWhere.Substring(3, strWhere.Length - 3).Trim(); j++; } } //處理開頭是[OR] if (strWhere.Length >= 2) { if (strWhere.ToUpper().Substring(0, 2) == "OR") { strWhere = strWhere.Substring(2, strWhere.Length - 2).Trim(); j++; } } //處理字典無效關(guān)鍵字 foreach (KeyValuePair<string, string> dict in dictInvalid) { if (strWhere.Contains(dict.Key)) { strWhere = strWhere.Replace(dict.Key, dict.Value).Trim(); j++; } } //處理末尾[AND] if (strWhere.Length >= 3) { if (strWhere.Length - 3 == strWhere.ToUpper().LastIndexOf("AND")) { strWhere = strWhere.Substring(0, strWhere.Length - 3).Trim(); j++; } } //處理末尾是[OR] if (strWhere.Length >= 2) { if (strWhere.Length - 2 == strWhere.ToUpper().LastIndexOf("OR")) { strWhere = strWhere.Substring(0, strWhere.Length - 2).Trim(); j++; } } //無處理次數(shù)時(shí)退出 if (j == 0) { break; } } //返回值 if (lstParams.Count > 0) { parameters = lstParams.ToArray(); } return strWhere.Trim(); } #endregion } 6、新建一個(gè)WinForm窗體，命名為：GeneralQuery。加入3個(gè)panel，分別命名為：topPanel、middlePanel、bottomPanel。 topPanel拖入上面新建的ConditionControl middlePanel拖入一個(gè)DataGridView bottomPanel拖入一個(gè)自定義分頁控件(詳情請(qǐng)看：DataGridView使用自定義控件實(shí)現(xiàn)簡(jiǎn)單分頁功能)
7、GeneralQuery的代碼實(shí)現(xiàn)：
//委托及事件 public delegate void ReturnResult(Dictionary<string, object> dicts); public event ReturnResult ReturnResultEvent; //屬性 public string[] FieldNames { get; set; } //字段名 public string[] FieldTypes { get; set; } //字段數(shù)據(jù)類型 public string[] FieldTexts { get; set; } //字段文本 public string[] FieldResults { get; set; } //要返回的字段結(jié)果 public StringBuilder TotalCountSql { get; set; } //總記錄數(shù)SQL public StringBuilder PageSql { get; set; } //分頁SQL(需包含@PageSize、@PageIndex，條件需包含@Where。) public int PageSize { get; set; } = 12; //每頁顯示記錄數(shù) public GeneralQuery() { InitializeComponent(); } private void GeneralQuery_Load(object sender, EventArgs e) { try { //條件控件賦初始值 if (FieldNames != null) Array.Copy(FieldNames, conditionControl1.FieldNames, FieldNames.Length); if (FieldTypes != null) Array.Copy(FieldTypes, conditionControl1.FieldTypes, FieldTypes.Length); if (FieldTexts != null) Array.Copy(FieldTexts, conditionControl1.FieldTexts, FieldTexts.Length); conditionControl1.TargetControl = topPanel; conditionControl1.Initialize(); //dataGridView1初始化 if (conditionControl1.VerifyFieldMatch()) { for (int i = 0; i < FieldNames.Length; i++) { DataGridViewTextBoxColumn textBoxColumn = new DataGridViewTextBoxColumn { Name = FieldNames[i].ToString(), DataPropertyName = FieldNames[i].ToString(), HeaderText = FieldTexts[i].ToString() }; dataGridView1.Columns.Add(textBoxColumn); } } //分頁控件賦初始值 pageControl1.PageSize = PageSize; pageControl1.PageIndex = 0; pageControl1.BindPageEvent += BindPage; } catch(Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } /// <summary> /// 綁定頁 /// </summary> /// <param name="pageSize">每頁顯示記錄數(shù)</param> /// <param name="pageIndex">頁序號(hào)</param> /// <param name="totalCount">總記錄數(shù)</param> private void BindPage(int pageSize, int pageIndex, out int totalCount) { totalCount = 0; try { if (conditionControl1.VerifyFieldMatch()) { string totalCountSql = TotalCountSql.ToString(); string pageSql = PageSql.ToString(); pageSql = pageSql.Replace("@PageSize", pageSize.ToString()).Replace("@PageIndex", pageIndex.ToString()).ToString(); string strWhere = conditionControl1.GetWhereCondition(out SqlParameter[] parameters); if (strWhere != string.Empty) { strWhere = "(" + strWhere + ")"; totalCountSql = totalCountSql.Replace("@Where", strWhere); pageSql = pageSql.Replace("@Where", strWhere); } else { totalCountSql = totalCountSql.Replace("@Where", "1=2"); pageSql = pageSql.Replace("@Where", "1=2"); } totalCount = (int)DBHelper.GetScalar(totalCountSql, parameters); DataTable dt = DBHelper.GetTable(pageSql, parameters); dataGridView1.DataSource = dt; } } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } /// <summary> /// 自動(dòng)編號(hào) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e) { Rectangle rectangle = new Rectangle ( e.RowBounds.Location.X, e.RowBounds.Location.Y, dataGridView1.RowHeadersWidth - 4, e.RowBounds.Height ); TextRenderer.DrawText ( e.Graphics, (e.RowIndex + 1).ToString(), dataGridView1.RowHeadersDefaultCellStyle.Font, rectangle, dataGridView1.RowHeadersDefaultCellStyle.ForeColor, TextFormatFlags.VerticalCenter | TextFormatFlags.Right ); } /// <summary> /// 查詢 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnSearch_Click(object sender, EventArgs e) { try { pageControl1.PageIndex = 0; pageControl1.SetPage(); } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } /// <summary> /// 查看條件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnCondition_Click(object sender, EventArgs e) { string strWhere = conditionControl1.GetWhereCondition(out SqlParameter[] parameters); if (parameters != null) { foreach (SqlParameter param in parameters) { strWhere += "," + param.ParameterName + "=" + param.SqlValue; } } MessageBox.Show(strWhere, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } /// <summary> /// 關(guān)閉 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnClose_Click(object sender, EventArgs e) { Close(); } /// <summary> /// 雙擊返回字典 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { try { if (FieldResults != null) { Dictionary<string, object> dictResult = new Dictionary<string, object>(); for (int i = 0; i < FieldResults.Length; i++) { if (dataGridView1.Columns.Contains(FieldResults[i])) { dictResult.Add(FieldResults[i], dataGridView1.Rows[e.RowIndex].Cells[FieldResults[i]].Value); } } if (dictResult.Count > 0) { ReturnResultEvent(dictResult); } else { ReturnResultEvent(null); } Close(); } } catch(Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } 8、以上，通用查詢器的功能就全部實(shí)現(xiàn)了，下面來調(diào)用一下： 新建一個(gè)WinForm窗體，命名為：Main。
9、Main的代碼實(shí)現(xiàn)： 
/// <summary> /// 調(diào)用通用查詢器 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { GeneralQuery query = new GeneralQuery { FieldNames = new string[] { "MO_NO", "MO_DD", "MRP_NO", "QTY", "BIL_NO" }, FieldTypes = new string[] { "varchar", "datetime", "varchar", "decimal", "varchar" }, FieldTexts = new string[] { "制令單號(hào)", "制令單日期", "成品編號(hào)", "生產(chǎn)數(shù)量", "來源單號(hào)" }, FieldResults = new string[] { "MO_NO" }, TotalCountSql = new StringBuilder() }; query.TotalCountSql.Append("SELECT COUNT(1) FROM MF_MO WHERE @Where"); query.PageSql = new StringBuilder(); query.PageSql.Append ( "SELECT TOP (@PageSize) MO_NO,MO_DD,MRP_NO,QTY,BIL_NO " + "FROM MF_MO A " + "WHERE @Where AND NOT EXISTS (SELECT 1 FROM (SELECT TOP ((@PageIndex - 1) * @PageSize) MO_NO FROM MF_MO WHERE @Where ORDER BY MO_NO) B WHERE A.MO_NO=B.MO_NO) " + "ORDER BY MO_NO" ); query.ReturnResultEvent += Query_ReturnResultEvent; query.ShowDialog(); } /// <summary> /// 委托函數(shù) /// </summary> /// <param name="dicts"></param> private void Query_ReturnResultEvent(Dictionary<string, object> dicts) { if(dicts!=null) { foreach(KeyValuePair<string,object> dict in dicts) { if(dict.Key=="MO_NO") { MessageBox.Show(string.Format("MO_NO傳回的值是：{0}", dict.Value.ToString()), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } } 10、效果：
好了，分享就到此結(jié)束了，希望對(duì)有此需要的人有一些幫助。 |
|
|
