C#通用查询器
很多通用查询器，对查询条件中的AND及OR的支持度不是很好，要么全部是AND要么全部是OR。笔者通过一段时间的摸索，终于完成了一个自己较为满意的通用查询器，可以实现多条件的AND及OR，现将实现过程记录一下：
1、在App.config中添加数据库连接字符串。
<connectionStrings> <add name ="connString" connectionString="server=.;database=db_test;uid=sa;pwd=********;"/> </connectionStrings>
2、添加一个数据库操作帮助类，命名为DBHelper。
/// <summary> /// SQL数据库访问类 /// </summary> public static class DBHelper { private static SqlConnection conn = null; /// <summary> /// 连接对象 /// </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> /// 打开数据库连接 /// </summary> public static void Open() { if (conn.State != ConnectionState.Open) { conn.Open(); } } /// <summary> /// 关闭数据库连接 /// </summary> public static void Close() { if (conn.State == ConnectionState.Open) { conn.Close(); } } /// <summary> /// 创建一个新的命令对象 /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <param name="parameters">参数数组</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> /// 执行SQL命令，并输出影响的行数。 /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <param name="parameters">参数数组</param> /// <return>返回影响的行数</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> /// 执行增删改的方法 /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <param name="parameters">参数数组</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> /// 执行增删改的方法 /// </summary> /// <param name="commandText">要执行的sql语句</param> /// <param name="parameters">参数数组</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">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <param name="parameters">参数数组</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">要执行的sql语句或存储过程名称</param> /// <param name="parameters">参数数组</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">要执行的sql语句</param> /// <returns></returns> public static DataTable GetTable(string commandText) { return GetTable(commandText, CommandType.Text, null); } /// <summary> /// 获得SqlDataReader /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <param name="parameters">参数数组</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">要执行的sql语句或存储过程名称</param> /// <param name="parameters">参数数组</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, params SqlParameter[] parameters) { return GetReader(commandText, CommandType.Text, parameters); } /// <summary> /// 获得SqlDataReader /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText, CommandType commandType) { return GetReader(commandText, commandType, null); } /// <summary> /// 获得SqlDataReader /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <returns></returns> public static SqlDataReader GetReader(string commandText) { return GetReader(commandText, CommandType.Text, null); } /// <summary> /// 执行SQL命令，并返回一个值。 /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <param name="parameters">参数数组</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> /// 执行SQL命令，并返回一个值。 /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="parameters">参数数组</param> /// <returns></returns> public static object GetScalar(string commandText, params SqlParameter[] parameters) { return GetScalar(commandText, CommandType.Text, parameters); } /// <summary> /// 执行SQL命令，并返回一个值。 /// </summary> /// <param name="commandText">要执行的sql语句或存储过程名称</param> /// <param name="commandType">CommandType枚举值，表示执行sql语句还是存储过程。</param> /// <returns></returns> public static object GetScalar(string commandText, CommandType commandType) { return GetScalar(commandText, commandType, null); } /// <summary> /// 执行SQL命令，并返回一个值。 /// </summary> /// <returns></returns> public static object GetScalar(string commandText) { return GetScalar(commandText, CommandType.Text, null); } }
3、添加一个数据类型转换类，命名为SqlDbTypeHelper。
/// <summary> /// SqlDbType转换为C#数据类型 /// </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://自定义的数据类型 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数据类型转换为SqlDbType类型 /// </summary> /// <param name="sqlTypeString"></param> /// <returns></returns> public static SqlDbType SqlTypeStringToSqlDbType(string sqlTypeString) { SqlDbType dbType = SqlDbType.Variant; //默认为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、添加一个自定义控件，命名为：ConditionControl。
注：底下的是panel1，上面的控件名分别为：cmbLeft1、cmbFieldText1、cmbOperator1、txtValue1、cmbRight1、cmbRelation1、btnAdd、btnRemove。
5、ConditionControl的代码实现：
public partial class ConditionControl : UserControl { #region 字段 private int conditionCount = 1; //panel个数 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); } } } //字段数据类型 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 构造函数 /// <summary> /// 构造函数 /// </summary> public ConditionControl() { InitializeComponent(); } #endregion #region 设置其它下拉框数据源 /// <summary> /// 设置左括号下拉框数据源 /// </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> /// 设置字段文本下拉框数据源 /// </summary> /// <param name="comboBox"></param> private void SetFieldTextDataSource(ComboBox comboBox) { if (VerifyFieldMatch()) { comboBox.Items.AddRange(tempFieldTexts); } } /// <summary> /// 设置右括号下拉框数据源 /// </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> /// 设置关系符下拉框数据源 /// </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()) { //左括号 SetLeftDataSource(cmbLeft1); //字段文本 if (tempFieldTexts[0] == "") { SetFieldTextDataSource(cmbFieldText1); } else { //第一行设为"" 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); } //右括号 SetRightDataSource(cmbRight1); //关系符 SetRelationDataSource(cmbRelation1); } } #endregion #region 验证字段是否匹配 /// <summary> /// 验证字段是否匹配 /// </summary> /// <returns>通过返回true</returns> public bool VerifyFieldMatch() { if (tempFieldNames == null || tempFieldTypes == null || tempFieldTexts == null) { MessageBox.Show("字段的名称或数据类型或标题未赋值。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } else { if (tempFieldNames.Length != tempFieldTypes.Length || tempFieldNames.Length != tempFieldTexts.Length) { MessageBox.Show("字段的名称或数据类型或标题长度不一致。", "提示", 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 根据数据类型进行获取 /// <summary> /// 根据数据类型返回其所属类型 /// </summary> /// <param name="fieldType">字段类型</param> /// <returns>所属类型</returns> private string GetKindByFieldType(string fieldType) { switch (fieldType.ToLower()) { //值为""时返回"" case "": return ""; //二进制类型，无运算符。 case "binary": case "varbinary": case "image": return null; //文本类型，可用（= like > >= < <= <>）运算符。 case "char": case "nchar": case "varchar": case "nvarchar": case "text": case "ntext": return "text"; //数字、日期类型，只能用（= > >= < <= <>）运算符。 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类型，只能用（= <>）运算符。 case "bit": return "bool"; default: return null; } } /// <summary> /// 根据数据类型返回对应类型的字段值 /// </summary> /// <param name="fieldType">字段类型</param> /// <param name="value">字段值</param> /// <returns>对应类型的字段值</returns> private object GetValueByFieldType(string fieldType, string value) { switch (fieldType.ToLower()) { //值为""时返回"" case "": return ""; //二进制类型 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 设置运算符下拉框数据源 /// <summary> /// 设置运算符下拉框数据源(文本类型) /// </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> /// 设置运算符下拉框数据源(数字、日期类型) /// </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> /// 设置运算符下拉框数据源(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 字段文本选择改变时 /// <summary> /// 字段文本选择改变时 /// </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) { //验证字段是否符合 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 }; //左括号 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 }; //运算符 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 }; //右括号 Size sizeRight = new Size(cmbRight1.Width, cmbRight1.Height); ComboBox cmbRightI = new ComboBox { FormattingEnabled = true, Name = "cmbRight" + conditionCount.ToString(), Size = sizeRight, DropDownStyle = ComboBoxStyle.DropDownList }; //关系符 Size sizeRelation = new Size(cmbRelation1.Width, cmbRelation1.Height); ComboBox cmbRelationI = new ComboBox { FormattingEnabled = true, Name = "cmbRelation" + conditionCount.ToString(), Size = sizeRelation, DropDownStyle = ComboBoxStyle.DropDownList }; //字段文本注册事件 cmbFieldTextI.SelectedIndexChanged += new EventHandler(cmbFieldText_SelectedIndexChanged); //设置数据源 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; //验证字段是否符合 if (!VerifyFieldMatch()) { return string.Empty; } //遍历产生Where条件 StringBuilder sbWhere = new StringBuilder(); List<SqlParameter> lstParams = new List<SqlParameter>(); int leftCount = 0, rightCount = 0; for (int i = 1; i <= conditionCount; i++) { //所选字段序号及文本 int index = -1; string fieldText = ""; if (FindControl("panel" + i, "cmbFieldText" + i) is ComboBox cmbFieldText) { index = cmbFieldText.SelectedIndex; fieldText = cmbFieldText.Text; } //左括号 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]); } } //运算符 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参数化查询(防注入) 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); } } } //右括号 if (FindControl("panel" + i, "cmbRight" + i) is ComboBox cmbRight && cmbRight.Text != string.Empty) { rightCount++; if (rightCount > leftCount) { MessageBox.Show("左括号与右括号不匹配。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return string.Empty; } sbWhere.Append(")"); } //关系符 if (FindControl("panel" + i, "cmbRelation" + i) is ComboBox cmbRelation) { if (i < conditionCount) { sbWhere.Append(" " + cmbRelation.SelectedValue.ToString()); } } } //括号匹配 if (leftCount != rightCount) { MessageBox.Show("左括号与右括号不匹配。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return string.Empty; } //处理无效关键字及开头或末尾是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++) { //处理次数 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++; } } //处理字典无效关键字 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++; } } //无处理次数时退出 if (j == 0) { break; } } //返回值 if (lstParams.Count > 0) { parameters = lstParams.ToArray(); } return strWhere.Trim(); } #endregion }
6、新建一个WinForm窗体，命名为：GeneralQuery。加入3个panel，分别命名为：topPanel、middlePanel、bottomPanel。
topPanel拖入上面新建的ConditionControl
middlePanel拖入一个DataGridView
bottomPanel拖入一个自定义分页控件(详情请看：DataGridView使用自定义控件实现简单分页功能)
7、GeneralQuery的代码实现：
//委托及事件 public delegate void ReturnResult(Dictionary<string, object> dicts); public event ReturnResult ReturnResultEvent; //属性 public string[] FieldNames { get; set; } //字段名 public string[] FieldTypes { get; set; } //字段数据类型 public string[] FieldTexts { get; set; } //字段文本 public string[] FieldResults { get; set; } //要返回的字段结果 public StringBuilder TotalCountSql { get; set; } //总记录数SQL public StringBuilder PageSql { get; set; } //分页SQL(需包含@PageSize、@PageIndex，条件需包含@Where。) public int PageSize { get; set; } = 12; //每页显示记录数 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">每页显示记录数</param> /// <param name="pageIndex">页序号</param> /// <param name="totalCount">总记录数</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> /// 自动编号 /// </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> /// 关闭 /// </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、以上，通用查询器的功能就全部实现了，下面来调用一下： 新建一个WinForm窗体，命名为：Main。
9、Main的代码实现：
/// <summary> /// 调用通用查询器 /// </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[] { "制令单号", "制令单日期", "成品编号", "生产数量", "来源单号" }, 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> /// 委托函数 /// </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、效果：
好了，分享就到此结束了，希望对有此需要的人有一些帮助。
