C#个人重构之组合查询

【人参果】

技术点：

1、模板模式

2、存储过程

3、控件之间的逻辑

【山巅远眺】

学习思路：

1、设置父窗体，想象什么写在父窗体上，什么写在子窗体上。

【披荆斩棘】

父窗体控件

代码：

        #region 全局的变量，整个系统都可以用

        public string filed1 = "";
        public string filed2 = "";
        public string filed3 = "";
        public string sombol1 = "";
        public string sombol2 = "";
        public string sombol3 = "";
        public string txt1 = "";
        public string txt2 = "";
        public string txt3 = "";
        public string D1 = "";
        public string D2 = "";

        #endregion

父窗体加载过程中：默认控件的属性

     private void UIFormClass_Load(object sender, EventArgs e)
        {

            #region 下拉菜单显示（操作符）

            cmbOperatorSymbol1.Items.Add(">");
            cmbOperatorSymbol1.Items.Add("<");
            cmbOperatorSymbol1.Items.Add("=");
            cmbOperatorSymbol1.Items.Add("<>");
            cmbOperatorSymbol2.Items.Add(">");
            cmbOperatorSymbol2.Items.Add("<");
            cmbOperatorSymbol2.Items.Add("=");
            cmbOperatorSymbol2.Items.Add("<>");
            cmbOperatorSymbol3.Items.Add(">");
            cmbOperatorSymbol3.Items.Add("<");
            cmbOperatorSymbol3.Items.Add("=");
            cmbOperatorSymbol3.Items.Add("<>");

            #endregion

            #region 下拉菜单显示（组合关系）
            cmbSyntheticRelation1.Items.Add("");
            cmbSyntheticRelation1.Items.Add("与");
            cmbSyntheticRelation1.Items.Add("或");
            cmbSyntheticRelation2.Items.Add("");
            cmbSyntheticRelation2.Items.Add("与");
            cmbSyntheticRelation2.Items.Add("或");
            #endregion
            
            #region 窗体加载后，默认后两组窗体不可用

            cmbFiled2.Enabled = false;
            cmbFiled3.Enabled = false;

            cmbOperatorSymbol2.Enabled = false;
            cmbOperatorSymbol3.Enabled = false;

            cmbSyntheticRelation2.Enabled = false;

            txtQueryCondition2.Enabled = false;
            txtQueryCondition3.Enabled = false;
  
            #endregion

            #region MyRegion
            dateTimePicker1.Visible = false;
            dateTimePicker2.Visible = false;
            dateTimePicker3.Visible = false;
            dateTimePicker1.Enabled = false;
            dateTimePicker2.Enabled = false;
            dateTimePicker3.Enabled = false;
            #endregion

第一个与或关系控制：

        private void cmbSyntheticRelation1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbSyntheticRelation1.Text=="")
            {
                

                cmbFiled2.Text = ""; 
                cmbFiled2.Enabled = false;
                cmbOperatorSymbol2.Text = ""; 
                cmbOperatorSymbol2.Enabled = false;
                txtQueryCondition2.Text = "";
                txtQueryCondition2.Visible = true;
                txtQueryCondition2.Enabled = false;

                cmbSyntheticRelation2.Text = "";
                cmbSyntheticRelation2.Enabled = false;
                dateTimePicker2.Visible = false;
                dateTimePicker2.Enabled = false;

                cmbFiled3.Text = ""; 
                cmbFiled3.Enabled = false;
                cmbOperatorSymbol3.Text = ""; 
                cmbOperatorSymbol3.Enabled = false;
                txtQueryCondition3.Text = "";
                txtQueryCondition3.Visible = true;
                txtQueryCondition3.Enabled = false;
                dateTimePicker3.Visible = false;
                dateTimePicker3.Enabled = false;
            }
            else
            {
                cmbFiled2.Enabled = true;
                cmbOperatorSymbol2.Enabled = true;
                txtQueryCondition2.Enabled = true;
                cmbSyntheticRelation2.Enabled = true;

                if (cmbFiled2.Text == "上线日期：" || cmbFiled2.Text == "下线日期：")
                {
                    txtQueryCondition2.Visible = false;
                    txtQueryCondition2.Enabled = false;
                    dateTimePicker2.Visible = true;
                    dateTimePicker2.Enabled = true;
                    return;
                }  
                if (cmbFiled3.Text == "上线日期：" || cmbFiled3.Text == "下线日期：")
                {
                    txtQueryCondition3.Visible = false;
                    txtQueryCondition3.Enabled = false;
                    dateTimePicker3.Visible = true;
                    dateTimePicker3.Enabled = false;
                    return;
                }
            }
        }

第二个与或关系控制：

        private void cmbSyntheticRelation2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbSyntheticRelation2.Text == "")
            {
                cmbFiled3.Text = "";
                cmbFiled3.Enabled = false;
                cmbOperatorSymbol3.Text = "";
                cmbOperatorSymbol3.Enabled = false;

                txtQueryCondition3.Text = "";
                txtQueryCondition3.Visible = true;
                txtQueryCondition3.Enabled = false;
                dateTimePicker3.Visible = false;
                dateTimePicker3.Enabled = false;
            }
            else
            {
                cmbFiled3.Enabled = true;
                cmbOperatorSymbol3.Enabled = true;
                if (cmbFiled3.Text == "上线日期：" || cmbFiled3.Text == "下线日期：")
                {
                    txtQueryCondition3.Enabled = false;
                    dateTimePicker3.Visible = true;
                    dateTimePicker3.Enabled = true;
                }
                else
                {
                    txtQueryCondition3.Enabled = true;
                    dateTimePicker3.Visible = false;
                    dateTimePicker3.Enabled = false;
                }            
            }
        }

条件选择控制（一共三个，代码一样）：

private void cmbFiled1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbFiled1.Text == "上线日期：" || cmbFiled1.Text == "下线日期：")
            {
                dateTimePicker1.Visible = true;
                dateTimePicker1.Enabled = true;
                txtQueryCondition1.Visible = false;
                txtQueryCondition1.Enabled = false;
                cmbOperatorSymbol1.Items.Clear();
                cmbOperatorSymbol1.Items.Add(">");
                cmbOperatorSymbol1.Items.Add("<");
                cmbOperatorSymbol1.Items.Add("=");
                cmbOperatorSymbol1.Items.Add("<>");

            }
            else
            {
                cmbOperatorSymbol1.Items.Clear();
                cmbOperatorSymbol1.Items.Add("=");
                cmbOperatorSymbol1.Items.Add("<>");

                txtQueryCondition1.Visible = true;
                txtQueryCondition1.Enabled = true;
                dateTimePicker1.Visible = false;
                dateTimePicker1.Enabled = false;
            }
        }

子窗体：获取值，赋值给实体，传给B层

            #region 给实体赋值
            Entity.EntityAll EntityAll = new Entity.EntityAll();
            EntityAll.Filed1 = filed1;
            EntityAll.Filed2 = filed2;
            EntityAll.Filed3 = filed3;
            EntityAll.OperatorSymbol1 = sombol1;
            EntityAll.OperatorSymbol2 = sombol2;
            EntityAll.OperatorSymbol3 = sombol3;
            EntityAll.QueryCondition1 = txt1;
            EntityAll.QueryCondition2 = txt2;
            EntityAll.QueryCondition3 = txt3;
            EntityAll.SyntheticRelation1 = D1;
            EntityAll.SyntheticRelation2 = D2;
            EntityAll.TableName = "LineRecord_Info";   
            #endregion

            #region 去B层一直往下走，返回信息
            Facade.QueryFacade facadeCombinationQuery = new Facade.QueryFacade();
            DataTable result = facadeCombinationQuery.combinationQuery(EntityAll);
            CP ct = new CP();
            List<Entity.LineRecordInfo> List = new List<Entity.LineRecordInfo>();
            List = ct.convertToList<Entity.LineRecordInfo>(result);
            dataGridView1.DataSource = List;
            #endregion

D层：

        #region 组合查询 
        public DataTable combinationQuery(Entity.EntityAll EntityAll)
        {
            SqlHelper.SqlHelperAll sqlHelerCombinationQuery = new SqlHelper.SqlHelperAll();
            string sql = "EntityAll_Info";
            SqlParameter[] sqlParams = {   new SqlParameter("@Filed1",EntityAll.Filed1),
                                           new SqlParameter("@Filed2",EntityAll.Filed2),
                                           new SqlParameter("@Filed3",EntityAll.Filed3),
                                           new SqlParameter("@OperatorSymbol1",EntityAll.OperatorSymbol1),
                                           new SqlParameter("@OperatorSymbol2",EntityAll.OperatorSymbol2),
                                           new SqlParameter("@OperatorSymbol3",EntityAll.OperatorSymbol3),
                                           new SqlParameter("@QueryCondition1",EntityAll.QueryCondition1),
                                           new SqlParameter("@QueryCondition2",EntityAll.QueryCondition2),
                                           new SqlParameter("@QueryCondition3",EntityAll.QueryCondition3),
                                           new SqlParameter("@SyntheticRelation1",EntityAll.SyntheticRelation1),
                                           new SqlParameter("@SyntheticRelation2",EntityAll.SyntheticRelation2),
                                           new SqlParameter("@TableName",EntityAll.TableName),            
                                       };
            DataTable result = sqlHelerCombinationQuery.ExecuteNomQuery(sql,CommandType.StoredProcedure,sqlParams);
            return result;
        }
        #endregion

【仙人指路】

太上老君：鸡蛋清不愧是三清之首，帮我调出了存储过程中的空格。

原始天尊：大家的博客，思路很多！

通天教主：世代庄少主的不懈努力！

【筋斗云】