【机房重构】-组合查询

淡淡的烟草味﹌ 2022-04-03 17:52 334阅读 0赞

# 父窗体设计 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1Y2t5c3Rhcl85OQ_size_16_color_FFFFFF_t_70]

# 代码实现的功能： #

*  模板方法
 *  调用封装的clear类
 *  判断控件集合信息是否填写完成（foreach ）
 *  选择与或的时候，出来下一条信息（switch—case）
 *  存储过程

## DAL层 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1Y2t5c3Rhcl85OQ_size_16_color_FFFFFF_t_70 1]

## 存储过程 ##

![在这里插入图片描述][20181225104853422.png]

USE [jifang]
    GO
    /****** Object:  StoredProcedure [dbo].[PROC_GroupCheck]    Script Date: 2018/12/25 10:47:17 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[PROC_GroupCheck]
    	@CmbName1 varchar(50),
    	@CmbName2 varchar(50),
    	@CmbName3 varchar(50),
    	@CmbOper1 varChar(10),
    	@CmbOper2 varChar(10),
    	@CmbOper3 varChar(10),
    	@CmbGroup1 varchar(50),
    	@CmbGroup2 varchar(50),
    	@GetDataTable varchar(50),
    	@txtInfo1 varchar(50),
    	@txtInfo2 varchar(50),
    	@txtInfo3 varchar(50)
    AS
    	declare @TempSql varchar(500)--临时存放Sql语句
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    		SET @TempSql='Select * from '+@GetDataTable+' Where'+CHAR(32)+@CmbName1+@CmbOper1 +CHAR(39)+@txtInfo1 +CHAR(39)
    	if (@CmbGroup1!='')
    	BEGIN
    		SET @TempSql =@TempSql +CHAR(32) +@CmbGroup1 +CHAR(32)+@CmbName2 +@CmbOper2 +CHAR(39)+@txtInfo2 +CHAR(39)
    		if (@CmbGroup2!='')
    		begin
    		SET @TempSql =@TempSql +CHAR(32)+@CmbGroup2 +CHAR(32)+@CmbName3 +@CmbOper3 +CHAR(39)+@txtInfo3 +CHAR(39)
    	end
     
        END
        exec(@TempSql)
    END

## 父窗体代码 ##

public partial class GroupInquiry_UI : Form
        {
            public GroupInquiry_UI()
            {
                InitializeComponent();
            }
    
            #region 调用封装的clear类，控件初始化
            private void btnClear_Click(object sender, EventArgs e)
            {
                //Clear这个类是之前封装的，现在直接用！Clear(this, panel1)是Clear中的一个重载！
                Clear clear = new Clear(this, panel1);
                DataTable Table = null;
                dataGridView1.DataSource = Table;
    
                //控件的初始化
                CmbName2.Enabled = false;
                CmbName3.Enabled = false;
                CmbMark2.Enabled = false;
                CmbMark3.Enabled = false;
                txtInfo2.Enabled = false;
                txtInfo3.Enabled = false;
                CmbRelation1.Enabled = false;
                CmbRelation2.Enabled = false;
            }
            #endregion
    
            #region 查询
            private void btnSearch_Click(object sender, EventArgs e)
            {          
                foreach (Control cl in panel1.Controls)
                {
                    if ((cl is ComboBox || cl is TextBox) && cl.Enabled == true)
                    {
                        if (cl.Text.Trim() == "")
                        {
                            MessageBox.Show("请将您的信息填写完整！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return;//退出过程
                        }
                    }
                }
             
    
                Entity.GroupInquiry_Info groupinquiry = new Entity.GroupInquiry_Info();//实例化实体
                Facade.GroupInquiry_Facade groupFine = new Facade.GroupInquiry_Facade();
                //给实体赋值
                groupinquiry.CmbName1 = ToName(CmbName1.Text.Trim());
                groupinquiry.CmbName2 = ToName(CmbName2.Text.Trim());
                groupinquiry.CmbName3 = ToName(CmbName3.Text.Trim());
    
                groupinquiry.CmbOper1 = CmbMark1.Text.Trim();
                groupinquiry.CmbOper2 = CmbMark2.Text.Trim();
                groupinquiry.CmbOper3 = CmbMark3.Text.Trim();
    
                groupinquiry.CmbGroup1 = ToName(CmbRelation1.Text.Trim());
                groupinquiry.CmbGroup2 = ToName(CmbRelation2.Text.Trim());
    
                groupinquiry.txtInfo1 = txtInfo1.Text.Trim();
                groupinquiry.txtInfo2 = txtInfo2.Text.Trim();
                groupinquiry.txtInfo3 = txtInfo3.Text.Trim();
    
                groupinquiry.GetDataTable = Getdbtable();//从数据库中获取表格（选择调用那个数据库）
    
                DataTable result = new DataTable();
                result = groupFine.GroupCheck(groupinquiry);
    
                if (result.Rows.Count == 0)//没有查询到
                {
                    MessageBox.Show("没有复合条件的记录，请重新选择条件");
                }
                else
                {
                    //MessageBox.Show("查找成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    ToDgv(groupinquiry);//查询到以后调用子窗体中重写的方法显示数据
                }
            }
            #endregion
    
            #region 关闭
            private void btnQuit_Click(object sender, EventArgs e)
            {
                this.Close();
            }
            #endregion
    
            #region  自己构造的虚方法
            public virtual string ToName(string combo)//转换中文字符为英文字符,在子窗体进行重写！
            {
                return "";
            }
            protected virtual string Getdbtable() //定义一个数据库中不同表的名字的虚方法，在子窗体进行重写
            {
                return "";
            }
            protected virtual void ToDgv(Entity.GroupInquiry_Info enGroupFind)//显示数据
            {
    
            }
            #endregion
    
            #region CmbRelation1选择与或的时候，出来下一条信息
            private void CmbRelation1_SelectedIndexChanged(object sender, EventArgs e)
            {
                string relationship = CmbRelation1.Text.Trim();
                switch (relationship)
                {
                    case "与":
                        CmbName2.Enabled = true;
                        CmbMark2.Enabled = true;
                        txtInfo2.Enabled = true;
                        break;
                    case "或":
                        CmbName2.Enabled = true;
                        CmbMark2.Enabled = true;
                        txtInfo2.Enabled = true;
                        break;
                }
            }
            #endregion
    
            #region 当第一行所有的内容添加完毕，此时关系下拉框被激活
            private void txtContent1_TextChanged(object sender, EventArgs e)
            {
                if (CmbName1.Text != "" && CmbMark1.Text != "" && txtInfo1.Text != "")
                {
                    CmbRelation1.Enabled = true;
                }
            }
            #endregion
    
            #region 当第二行所有的内容添加完毕，此时关系下拉框被激活
            private void txtContent2_TextChanged(object sender, EventArgs e)
            {
                if (CmbName2.Text != "" && CmbMark2.Text != "" && txtInfo2.Text != "")
                {
                    CmbRelation2.Enabled = true;
                }
            }
            #endregion
    
            #region CmbRelation2选择与或的时候，出来下一条信息
            private void CmbRelation2_SelectedIndexChanged(object sender, EventArgs e)
            {
                string relationship = CmbRelation1.Text.Trim();
                switch (relationship)
                {
                    case "与":
                        CmbName3.Enabled = true;
                        CmbMark3.Enabled = true;
                        txtInfo3.Enabled = true;
                        break;
                    case "或":
                        CmbName3.Enabled = true;
                        CmbMark3.Enabled = true;
                        txtInfo3.Enabled = true;
                        break;
                }
            }
            #endregion          
    
            private void GroupInquiry_UI_Load(object sender, EventArgs e)
            {
                #region 操作符
                CmbMark1.Items.Add(">");
                CmbMark1.Items.Add("<");
                CmbMark1.Items.Add("=");
                CmbMark1.Items.Add("<>");
                CmbMark2.Items.Add(">");
                CmbMark2.Items.Add("<");
                CmbMark2.Items.Add("=");
                CmbMark2.Items.Add("<>");
                CmbMark3.Items.Add(">");
                CmbMark3.Items.Add("<");
                CmbMark3.Items.Add("=");
                CmbMark3.Items.Add("<>");
                #endregion
    
    
                #region 设置控件不可用状态
                CmbMark2.Enabled = false;
                CmbMark3.Enabled = false;
    
                CmbName2.Enabled = false;
                CmbName3.Enabled = false;
    
                CmbRelation1.Enabled = false;
                CmbRelation2.Enabled = false;
                txtInfo2.Enabled = false;
                txtInfo3.Enabled = false;
                #endregion
    
                #region 设置复选框只可以选择不可以输入
                this.CmbMark1.DropDownStyle = ComboBoxStyle.DropDownList;
                this.CmbMark2.DropDownStyle = ComboBoxStyle.DropDownList;
                this.CmbMark3.DropDownStyle = ComboBoxStyle.DropDownList;
    
                this.CmbName1.DropDownStyle = ComboBoxStyle.DropDownList;
                this.CmbName2.DropDownStyle = ComboBoxStyle.DropDownList;
                this.CmbName3.DropDownStyle = ComboBoxStyle.DropDownList;
    
                this.CmbRelation1.DropDownStyle = ComboBoxStyle.DropDownList;
                this.CmbRelation2.DropDownStyle = ComboBoxStyle.DropDownList;
                #endregion
            }
    
            #region 封装清空类
            public class Clear
            {
                public Clear(GroupInquiry_UI form, Panel panal) //如果有panel控件，此处也可以添加一个panel清除
                {
                    foreach (Control ctl in form.panel1.Controls)
                    {
                        //清空combobox
                        if (ctl is ComboBox)
                        {
                            ComboBox cmb = ctl as ComboBox;
                            cmb.SelectedIndex = -1;
                        }
                        //清空textbox
                        else if (ctl is TextBox)
                        {
                            TextBox txt = ctl as TextBox;
                            txt.Text = string.Empty;
                        }
                    }
                }
            }
            #endregion
    
            #region 清空，控件初始化
            private void btnClear_Click_1(object sender, EventArgs e)
            {
                Clear clear = new Clear(this, panel1);
                DataTable Table = null;
                dataGridView1.DataSource = Table;
    
                //控件的初始化
                CmbMark2.Enabled = false;
                CmbMark3.Enabled = false;
    
                CmbName2.Enabled = false;
                CmbName3.Enabled = false;
    
                CmbRelation2.Enabled = false;
                txtInfo2.Enabled = false;
                txtInfo3.Enabled = false;
    
            }
            #endregion
    
            #region 构造一个没有任何返回值的虚方法，来根据CmbName的内容，出现相应的下拉内容！
            public virtual void addcontent(ComboBox name, ComboBox mark)
            {
               
            }
            #endregion

## 如何生成子窗体 ##

**1. U层：添加——新建项**

![在这里插入图片描述][20181225102447843.png]

**2. Windows Forms——继承的窗体**

![在这里插入图片描述][20181225102534424.png]

**3. 确定——指定要从中继承的组件——确定**

![在这里插入图片描述][20181225103332656.png]

## 继承窗体 ##

public partial class AdminGroupInquiry_UI : UI.Admin.GroupInquiry_UI
        {
            public AdminGroupInquiry_UI()
            {
                InitializeComponent();
            }
        
            #region 重写了英文转换成数据库中所识别英文的方法
            public override string ToName(string combo)//重写虚方法，将自己要的字段转化成数据库字段
            {
                switch (combo)
                {
                    case "卡号":
                        return "stuID";
                    case "姓名":
                        return "stuName";
                    case "性别":
                        return "sex";
                    case "年龄":
                        return "age";
                    case "学院":
                        return "department";
                    case "专业":
                        return "major";
                    case "班级":
                        return "grade";
                    case "与":
                        return "and";
                    case "或":
                        return "or";
    
                    default:
                        return "";
                }
            }
            #endregion
    
            #region 重写了获取数据库中表的名字的方法
            protected override string Getdbtable() //Select语句需求的数据库
            {
                return "StuCard_Info";
            }
            #endregion
          
            #region 重写显示查询到的数据库中的数据
            protected override void ToDgv(Entity.GroupInquiry_Info enGroupFind)//重写方法，显示结果
            {
                dataGridView1.DataSource = "";
                DataTable dt = new DataTable();
                Facade.GroupInquiry_Facade gf = new Facade.GroupInquiry_Facade();//去拿到数据库的数据
                dt = gf.GroupCheck(enGroupFind);
                if (dt.Rows.Count == 0)
                {
                    MessageBox.Show("没有记录，请重新查询");
                }
                else
                {
                    //子类中的需要的东西，父窗体只负责显示
                    dataGridView1.DataSource = dt;
                    dataGridView1.Refresh();
                }
            }
            #endregion
    
            #region 重写根据CmbName的值，显示不同的cbomark的值
            public override void addcontent(ComboBox name, ComboBox cbomark)
            {
                switch (name.Text)
                {
                    case "姓名":
                    case "性别":
                    case "学院":
                    case "专业":
                        cbomark.Text = "";
                        cbomark.Items.Clear();
                        cbomark.Items.Add("=");
                        cbomark.Items.Add("<>");
                        break;
                    case "卡号":
                    case "班级":
                    case "年龄":
                        cbomark.Text = "";
                        cbomark.Items.Clear();
                        cbomark.Items.Add("=");
                        cbomark.Items.Add("<>");
                        cbomark.Items.Add("<");
                        cbomark.Items.Add(">");
                        break;
                }
            }
            #endregion
            private void AdminGroupInquiry_UI_Load(object sender, EventArgs e)
            {           
                CmbName1.Items.Add("卡号");
                CmbName1.Items.Add("姓名");
                CmbName1.Items.Add("性别");
                CmbName1.Items.Add("专业");
                CmbName1.Items.Add("年龄");
                CmbName1.Items.Add("学院");
                CmbName1.Items.Add("班级");
    
                CmbName2.Items.Add("卡号");
                CmbName2.Items.Add("姓名");
                CmbName2.Items.Add("性别");
                CmbName2.Items.Add("专业");
                CmbName2.Items.Add("年龄");
                CmbName2.Items.Add("学院");
                CmbName2.Items.Add("班级");
    
                CmbName3.Items.Add("卡号");
                CmbName3.Items.Add("姓名");
                CmbName3.Items.Add("性别");
                CmbName3.Items.Add("专业");
                CmbName3.Items.Add("年龄");
                CmbName3.Items.Add("学院");
                CmbName3.Items.Add("班级");
            }

--------------------

![SouthEast][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1Y2t5c3Rhcl85OQ_size_16_color_FFFFFF_t_70]: /images/20220403/0ad0c5da777842f0ba49788cf44ccd00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1Y2t5c3Rhcl85OQ_size_16_color_FFFFFF_t_70 1]: /images/20220403/24b18ef27390400d93f4716e9a1792c5.png
[20181225104853422.png]: /images/20220403/5a0b94e14ca445c8a0ed0f079013c016.png
[20181225102447843.png]: /images/20220403/125ba7e25c62433da986431c06b7f9f7.png
[20181225102534424.png]: /images/20220403/ed826eb028354bda9dcdbd60fc841c24.png
[20181225103332656.png]: /images/20220403/2a93b34af241422dbbbd514e5ccc3794.png
[SouthEast]: /images/20220403/316d3f50a44741ffbf698b45ce7f4691.png