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);
        }
    }
View Code

    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;
        }
View Code

    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
    }
View Code

    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);
            }
        }
View Code

    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);
                    }
                }
            }
        }
View Code

    10、效果:

 

     好了,分享就到此结束了,希望对有此需要的人有一些帮助。

posted @ 2019-11-25 23:25  缥缈的尘埃  阅读(...)  评论(...编辑  收藏