组合查询是我们在开发机房收费系统中公认比较难实现的模块，但是我告诉你有一种方法可以让组合查询变得很简单，你想知道吗？听我慢慢道来。
在准备开发这个功能的时候，我发现它的复用程度很高：界面一样，控件布局一样，查询方式也一样，只不过是我查询的具体内容之间有些差别。仔细一想，这不就跟模板方法一样吗。
模板方法：定义一个操作中的算法的骨架（组合查询父窗体），而将一些步骤延迟到子类中（子窗体独有的属性）。
知道了这些之后，组合查询功能就有了，我们首先要建立一个组合查询父窗体模板：
简单代码实现：
U层
/// <summary>
/// 定义一个个虚方法 GetDbTableName 在子窗体重写
/// </summary>
/// <returns></returns>
public virtual string GetDbTableName() { return ""; }
//定义一个字典，匹配数据库中字段名
protected Dictionary<string, string> dic = new Dictionary<string, string>()
{
{"卡号","CardNo" },
{"学号","StudentNo" },
{"学生姓名","StudentName" },
……
……
{"且","and" },
{"或","or" },
{"","" }
};
public combineInquiry()
{
InitializeComponent();
//设置dateTime控件的最大最小时间限制
dateTimePicker1.MinDate = new DateTime(2018, 1, 1);
dateTimePicker1.MaxDate = DateTime.Today;
dateTimePicker2.MinDate = new DateTime(2018, 1, 1);
dateTimePicker2.MaxDate = DateTime.Today;
dateTimePicker3.MinDate = new DateTime(2018, 1, 1);
dateTimePicker3.MaxDate = DateTime.Today;
}
private void combineInquiry_Load(object sender, EventArgs e)
{
//控件的初始化
cboField2.Enabled = false;
cboField3.Enabled = false;
cboMark2.Enabled = false;
cboMark3.Enabled = false;
txtContent2.Enabled = false;
txtContent3.Enabled = false;
cboConbination1.Enabled = false;
cboConbination2.Enabled = false;
//组合关系
cboConbination1.Items.Add("或");
cboConbination1.Items.Add("且");
cboConbination2.Items.Add("或");
cboConbination2.Items.Add("且");
//将时间控件设为不可用 不可见
dateTimePicker1.Enabled = false;
dateTimePicker1.Visible = false;
dateTimePicker2.Enabled = false;
dateTimePicker2.Visible = false;
dateTimePicker3.Enabled = false;
dateTimePicker3.Visible = false;
}
//查询功能
private void btnInquiry_Click(object sender, EventArgs e)
{
//判空
foreach (Control ctl in panel1.Controls)
{
if ((ctl is ComboBox||ctl is TextBox)&&ctl.Enabled==true)
{
if (ctl.Text.Trim()=="")
{
MessageBox.Show("请将信息填写完整！","温馨提示");
return;
}
}
}
//实例化外观层 实体层
FacadeGroupFindTemplate facadeBoolGroupFindTemplate = new FacadeGroupFindTemplate();
GroupFindTemplate_Info groupFindTemplate = new GroupFindTemplate_Info();
//给实体层传值
groupFindTemplate.Dbtablename = GetDbTableName();
groupFindTemplate.cboField1 = dic[cboField1.Text.Trim()];
groupFindTemplate.cboField2 = dic[cboField2.Text.Trim()];
groupFindTemplate.cboField3 = dic[cboField3.Text.Trim()];
groupFindTemplate.cboMark1 = cboMark1.Text.Trim();
groupFindTemplate.cboMark2 = cboMark2.Text.Trim();
groupFindTemplate.cboMark3 = cboMark3.Text.Trim();
if (txtContent1.Enabled==true)
{
groupFindTemplate.Content1 = txtContent1.Text.Trim();
groupFindTemplate.Content2 = txtContent2.Text.Trim();
groupFindTemplate.Content3 = txtContent3.Text.Trim();
}
else
{
groupFindTemplate.Content1 = dateTimePicker1.Text.Trim();
groupFindTemplate.Content2 = dateTimePicker2.Text.Trim();
groupFindTemplate.Content3 = dateTimePicker3.Text.Trim();
}
groupFindTemplate.Relationship1 = dic[cboConbination1.Text.Trim()];
groupFindTemplate.Relationship2 =dic[cboConbination2.Text.Trim()];
//判断是否查找成功
bool result = false;
result = facadeBoolGroupFindTemplate.boolGroupFindTemplate(groupFindTemplate);
if (result==true)
{
DataTable dt = facadeBoolGroupFindTemplate.groupFindTemplate(groupFindTemplate);
dataGridView1.DataSource = dt;
dataGridView1.AllowUserToAddRows = false;
//MessageBox.Show("查找成功！", "温馨提示");//直接在datagridview显示，减少弹框，提高用户体验度
}
else
{
MessageBox.Show("不存在您查找的信息","温馨提示");
dataGridView1.DataSource = null;
}
}
/// <summary>
/// 清空+控件初始化
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnClear_Click(object sender, EventArgs e)
{
//清空容器panel里的所有内容
foreach (Control control in panel1.Controls)
{
if (control is TextBox)
{
control.Text = string.Empty;
}
else if (control is ComboBox)
{
ComboBox cbo = control as ComboBox;
cbo.SelectedIndex = -1;
}
}
//清空组合关系框内容
cboConbination1.SelectedIndex = -1;
cboConbination2.SelectedIndex = -1;
DataTable table = null;
dataGridView1.DataSource = table;
//控件的初始化
cboField2.Enabled = false;
cboField3.Enabled = false;
cboMark2.Enabled = false;
cboMark3.Enabled = false;
txtContent2.Enabled = false;
txtContent3.Enabled = false;
cboConbination1.Enabled = false;
cboConbination2.Enabled = false;
}
/// <summary>
/// 如果选择了第一个组合关系，下一行信息可选
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cboConbination1_SelectedIndexChanged(object sender, EventArgs e)
{
string relationship = cboConbination1.Text.Trim();
switch (relationship)
{
//使用case事件代替ifelse语句，减少代码量
case "且":
cboField2.Enabled = true;
cboMark2.Enabled = true;
txtContent2.Enabled = true;
break;
case "或":
cboField2.Enabled = true;
cboMark2.Enabled = true;
txtContent2.Enabled = true;
break;
}
/// <summary>
/// 当第一行所有的内容添加完毕，第一个关系下拉框被激活
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtContent1_TextChanged(object sender, EventArgs e)
{
if (cboField1.Text != "" && cboMark1.Text != "" && txtContent1.Text != "")
{
cboConbination1.Enabled = true;
}
}
/// <summary>
/// 当第二行所有的内容添加完毕，第二个关系下拉框被激活
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtContent2_TextChanged(object sender, EventArgs e)
{
if (cboField2.Text != "" && cboMark2.Text != "" && txtContent2.Text != "")
{
cboConbination2.Enabled = true;
}
}
/// <summary>
/// 构造一个没有返回值的虚方法，子窗体根据自身内容来实例化
/// </summary>
/// <param name="field"></param>
/// <param name="mark"></param>
public virtual void addContent(ComboBox field, ComboBox mark){ }
/// <summary>
/// 根据field的内容 来添加Mark中的符号 （这里只给出一个，因为后面两个和第一个一样）
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cboField1_SelectedIndexChanged(object sender, EventArgs e)
{
addContent(cboField1,cboMark1);
if (cboField1.Text=="注册日期"|| cboField1.Text == "注册时间" || cboField1.Text == "登录日期" || cboField1.Text == "登录时间" || cboField1.Text == "上机日期" ||cboField1.Text == "下机日期"||cboField1.Text=="注销日期")
{
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.Visible = true;
dateTimePicker1.Enabled = true;
txtContent1.Visible = false;
txtContent1.Enabled = false;
dateTimePicker1.CustomFormat = "yyyy-MM-dd";
}
else
{
txtContent1.Visible = true;
txtContent1.Enabled = true;
dateTimePicker1.Visible = false;
dateTimePicker1.Enabled = false;
return;
}
/// <summary>
/// 导出为Excel表，调用封装好的exportExcel方法
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExcel_Click(object sender, EventArgs e)
{
string name = "Mark";
exportExcel export = new exportExcel();
export.RExcel(name,dataGridView1);
}
/// <summary>
/// 如果是时间控件，在第一行所有内容都填满之后，第一个组合关系可使用
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
if (cboField1.Text != "" && cboMark1.Text != "" && dateTimePicker1.Text != "")
{
cboConbination1.Enabled = true;
}
}
private void dateTimePicker2_ValueChanged_1(object sender, EventArgs e)
{
if (cboField2.Text != "" && cboMark2.Text != "" && dateTimePicker2.Text != "")
{
cboConbination2.Enabled = true;
}
}
D层也很简答，因为我用了存储过程。
public class DALGroupFindTemplate:IDALGroupFindTemplate
{
SQLHelper sqlHelper = new SQLHelper();
public DataTable GroupFindTemplate(GroupFindTemplate_Info groupfind)
{
SqlParameter[] sqlParams = { new SqlParameter("@cboField1", groupfind.cboField1),
new SqlParameter("@cboField2", groupfind.cboField2),
new SqlParameter("@cboField3", groupfind.cboField3),
new SqlParameter("@cboMark1",groupfind.cboMark1),
new SqlParameter("@cboMark2",groupfind.cboMark2),
new SqlParameter("@cboMark3",groupfind.cboMark3),
new SqlParameter("@txtContent1",groupfind.Content1),
new SqlParameter("@txtContent2",groupfind.Content2),
new SqlParameter("@txtContent3",groupfind.Content3),
new SqlParameter("@Relationship1",groupfind.Relationship1),
new SqlParameter("@Relationship2",groupfind.Relationship2),
new SqlParameter("@Dbtablename",groupfind.Dbtablename)
};
string sql = "PROC_GroupCheck";
DataTable dt = sqlHelper.ExecuteQuery(sql, sqlParams, CommandType.StoredProcedure);
return dt;
}
OK，父窗体搞定，下面开始解决子窗体。以学生基本信息查询为例，只需要在U层做简单改动即可。
/// <summary>
/// 重写父窗体addContent方法，匹配对应的字段名和操作符
/// </summary>
/// <param name="field"></param>
/// <param name="mark"></param>
public override void addContent(ComboBox field, ComboBox mark)
{
switch (field.Text)
{
case "学生姓名":
case "性别":
case "系别":
case "备注":
case "状态":
case "是否结账":
mark.Text = "";
mark.Items.Clear();
mark.Items.Add("=");
mark.Items.Add("<>");
break;
case "卡号":
case "学号":
case "余额":
case "操作员ID":
case "年级":
case "班级":
case "注册日期":
mark.Text = "";
mark.Items.Clear();
mark.Items.Add("=");
mark.Items.Add("<>");
mark.Items.Add(">");
mark.Items.Add("<");
break;
}
}
/// <summary>
/// 返回要查询的表名
/// </summary>
/// <returns></returns>
public override string GetDbTableName()
{
return "Student_Info";
}
/// <summary>
/// 在datagridview表中加入对应的标头
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnInquiry_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count>0)
{
dataGridView1.Columns[0].HeaderText = "卡号";
dataGridView1.Columns[1].HeaderText = "学号";
dataGridView1.Columns[2].HeaderText = "学生姓名";
dataGridView1.Columns[3].HeaderText = "性别";
dataGridView1.Columns[4].HeaderText = "系别";
dataGridView1.Columns[5].HeaderText = "年级";
dataGridView1.Columns[6].HeaderText = "班级";
dataGridView1.Columns[7].HeaderText = "余额";
dataGridView1.Columns[8].HeaderText = "备注";
dataGridView1.Columns[9].HeaderText = "操作员ID";
dataGridView1.Columns[10].HeaderText = "是否结账";
dataGridView1.Columns[11].HeaderText = "注册日期";
dataGridView1.Columns[12].HeaderText = "注册时间";
dataGridView1.Columns[13].HeaderText = "状态";
}
}
/// <summary>
/// 添加字段
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void frmOpStuMaintenance_Load(object sender, EventArgs e)
{
//定义一个数组，然后每个combo加载，简单粗暴
string[] Field = { "卡号","学号","学生姓名","性别","系别","年级", "班级", "余额", "备注", "操作员ID", "是否结账","注册日期", "状态" };
cboField1.Items.AddRange(Field);
cboField2.Items.AddRange(Field);
cboField3.Items.AddRange(Field);
}
}
模板方法到这里就已经结束了，用了模板方法之后是不是有一种一劳永逸的感觉？哈哈。
扫一扫
970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
