C#通用查询器

    <connectionstrings>
        <add name ="connstring" connectionstring="server=.;database=db_test;uid=sa;pwd=********;"/>
    </connectionstrings>

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

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

    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
    }

        //委托及事件
        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);
            }
        }

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