日期:2014-05-16  浏览次数:21366 次

C#输入姓名或者拼音码(姓名的首字母,大小写均可)进行快速模糊查找

    第一步:在SQL Server中编写获取输入汉字的拼音码函数,详见SQL Server编写函数获取汉字的拼音码(首字母)。

    第二步:增加含有拼音码字段的人员记录。

private void button_Insert_Click(object sender, EventArgs e)
        {
            string NAME = textBox_name.Text.Trim();
            string SEX = comboBox_sex.Text;
            MyDBase DB = new MyDBase(DBUser.sserver,DBUser.DBName, DBUser.suser, DBUser.spasswd);
            string PYM = DB.ExecuteSQLScalarTOstring("select dbo.fun_getPY('" + textBox_name.Text.Trim() + "')");
            SQL = "INSERT INTO EXPERT (ENAME, SEX,PYM) VALUES ('" + NAME + "','" + SEX + "','" + PYM + "')";
            DB.ExecuteSQL(SQL);
            if (DB.ErrorCode())
            {
                MessageBox.Show(DB.ErrMessage(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            DB.DBClose();
        }
    第三步:利用输入的姓名或者拼音码进行模糊查找。

private void button_search_Click(object sender, EventArgs e)
        {
            string SQL; int count;
            MyDBase DB = new MyDBase(DBUser.sserver,DBUser.DBName, DBUser.suser, DBUser.spasswd);
            if (Regex.IsMatch(textBox_inputname.Text.Trim(), "^[a-zA-Z]+$")) //按拼音码查找。using System.Text.RegularExpressions;
            {
                SQL = "select * from VIEW_EXPERT where 姓名 in(select Ename from expert where  PYM like '%" + textBox_inputname.Text.Trim() + "%')";
                count = DB.ExecuteSQLScalar("Select count(*) from VIEW_EXPERT where 姓名 in(select Ename from expert where  PYM like '%" + textBox_inputname.Text.Trim() + "%')");
            }
            else //按姓名查找
            {
                SQL = "select * from view_expert where 姓名 like'%" + textBox_inputname.Text.Trim() + "%'";
                count = DB.ExecuteSQLScalar("Select count(*) from expert where Ename like'%" + textBox_inputname.Text.Trim() + "%' ");
            }
            DataSet ds = DB.GetRecordset(SQL);
            if (DB.ErrorCode())
            {
                MessageBox.Show(DB.ErrMessage(), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            dataGridView1.DataSource = ds.Tables[0];
            toolStripStatusLabel1.Text = "当前记录数:" + count;
            DB.DBClose();
        }