C#简易电影记录管理系统:开发六[数据搜索]

本期概述

前几期中,我们实现了用户对系统数据记录的基本操作-增删改查, 由于数据量不是很多,我们可以轻易的找到我们所需要操作的记录；但是一旦数据量多起来,是否还能方便的找到我们想要操作的数据呢?这一期我们一起来实现一个功能-数据搜索.

原理

主要原理是使用SQL模糊查询语句 select * from 表名 where 字段名 like ‘%输入的内容%’ 对库内相应的字段进行模糊查询;

通过获取文本框输入的内容与数据库中各字段下的内容进行一一匹配,如果输入的内容被某个字段下的单元格的内容所包含或者相同,则显示该列内容.

这里使用了单个字段模糊搜索相关信息的查询语句 select * from 表名 where “选择的单个字段” like ‘%输入的内容%’ 以及

全字段模糊搜索信息的查询语句 select * from 表名 where (字段名1 like ‘%输入的内容%’)or(字段名2 like ‘%输入的内容%’)….;

界面设计

首先,我们需要在之前的电影记录的管理界面FrmManager 增加一个查询文本框(name:txtDataforQuery) 和一个combox下列条(name:cmbforfieldSelecting)

代码如下(这里代码仅限于数据搜索功能,如果需要查看其他功能代码请看这里)

private void txtDataforQuery_TextChanged(object sender, EventArgs e)
{
    string sql = "";
    if (txtDataforQuery.Text.Trim() == "")
    {
        //执行查询语句
        sql = "select * from Record";
    }
    else if (seletedValue == "全局搜索" || cmbforTypeSelecting.Text == "全局搜索")
    {
        //全字段搜索
        sql = "select * from Record where(Id like'%" + txtDataforQuery.Text.Trim() + "%')or(Movie_Name like'%" + txtDataforQuery.Text.Trim() + "%')or(Movie_Director like'%" + txtDataforQuery.Text.Trim() + "%')or(Date_Released like'%" + txtDataforQuery.Text.Trim() + "%')";
    }
    else if (seletedValue == "Id" || seletedValue == "Movie_Name" || seletedValue == "Movie_Director" || seletedValue == "Date_Released")
    {
        //通过相应的字段进行搜索
        sql = "select * from Record where " + seletedValue + " like '%" + txtDataforQuery.Text.Trim() + "%'";
    }

    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(sql, conn);
    DataTable dt = new DataTable();
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    sda.Fill(dt);
    dgvManager.DataSource = dt;
}

这里所做的数据搜索,是通过文本框的TextChanged 事件来自动获取输入的文字同时与数据库中相应字段下的数据相匹配,如果输入的数据被数据库包含或者相同则显示出来.

//当系统加载的时候自动显示DataGridView的数据
private void FrmManager_Load(object sender, EventArgs e)
{
    //调用刷新数据的方法
    Refresh();
    cmbforTypeSelecting.Text = "全局搜索";
    cmbforTypeSelecting.Items.Add("全局搜索");
    cmbforTypeSelecting.Items.Add("编号");
    cmbforTypeSelecting.Items.Add("电影名称");
    cmbforTypeSelecting.Items.Add("导演");
    cmbforTypeSelecting.Items.Add("发行时间");
}

string seletedValue;

private void txtDataforQuery_TextChanged(object sender, EventArgs e)
{
    string sql = "";
    if (txtDataforQuery.Text.Trim() == "")
    {
        //执行查询语句
        sql = "select * from Record";
    }
    else if (seletedValue == "全局搜索" || cmbforTypeSelecting.Text == "全局搜索")
    {
        //全字段搜索
        sql = "select * from Record where(Id like'%" + txtDataforQuery.Text.Trim() + "%')or(Movie_Name like'%" + txtDataforQuery.Text.Trim() + "%')or(Movie_Director like'%" + txtDataforQuery.Text.Trim() + "%')or(Date_Released like'%" + txtDataforQuery.Text.Trim() + "%')";
    }
    else if (seletedValue == "Id" || seletedValue == "Movie_Name" || seletedValue == "Movie_Director" || seletedValue == "Date_Released")
    {
        //通过相应的字段进行搜索
        sql = "select * from Record where " + seletedValue + " like '%" + txtDataforQuery.Text.Trim() + "%'";
    }

    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(sql, conn);
    DataTable dt = new DataTable();
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    sda.Fill(dt);
    dgvManager.DataSource = dt;
}

private void cmbforTypeSelecting_SelectedIndexChanged(object sender, EventArgs e)
{
    string strSelected = cmbforTypeSelecting.Text;
    switch (strSelected)
    {
        case "全局搜索":
            seletedValue = "全局搜索";
            break;
        case "编号":
            seletedValue = "Id";
            break;
        case "电影名称":
            seletedValue = "Movie_Name";
            break;
        case "导演":
            seletedValue = "Movie_Director";
            break;
        case "发行时间":
            seletedValue = "Date_Released";
            break;
        default:
            seletedValue = "全局搜索";
            break;
    }
}

同理,我们在日志查询界面也做一个日志搜索.

界面新增按钮如下输入文本框name:txtlogQuery 搜索按钮name:btnlogQuery

代码如下(如想看本界面的其他功能请看这里)

//日志查询
private void btnlogQuery_Click(object sender, EventArgs e)
{
    //初始化 showSql语句
    string showSql = "";
    ///如果文本框txtQuery 输入的内容为空值
    if (txtlogQuery.Text.Trim() == "")
    {
        //显示所有数据
        showSql = "select * from Log";
    }
    else
    {
        //否则 进行 全局搜索 将文本框里的内容与 各字段 进行 匹配
        showSql = "select * from Log where(Id like'%" + txtlogQuery.Text.Trim() +
        "%')or(User_Name like'%" + txtlogQuery.Text.Trim() + "%')or(Situation like'%"
        + txtlogQuery.Text.Trim() + "%')or(Time like'%" + txtlogQuery.Text.Trim() + "%')";
    }
    SqlConnection conn = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(showSql, conn);
    DataTable dt = new DataTable();
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    sda.Fill(dt);
    dgvLog.DataSource = dt;
}