SQLserver模糊查询

SQLserver模糊查询

          考试的后台监控中，查询考生的登录情况，模糊查询考生信息。

          对于根据查询出来的该考试、考场或者班级的所有考生信息，从中进行模糊查询，可以模糊匹配学号、姓名、状态、学院或者专业信息。

          首先，前台将用户输入的字符串传到后台。

          代码如下

<script>
            //获取用户输入的字符串
           var value = $('#txtSearch').searchbox('getValue');
           function doSearch(value,name) {
               var value = $('#txtSearch').searchbox('getValue');
               var ExamID = $('#ExamID').val();
               var ExamPlaceID = $('#ExamPlaceID').val();
               var StartDate = $('#StartDate').val();
                var StartTime =$('#StartTime').val();
 
               $('#Chapter1').datagrid({
                   url: '/Monitore/FuzzyQueryStudentInfo?strLike=' + value +"&ExamID=" + ExamID + "&ExamPlaceID=" + ExamPlaceID+ "&StartDate=" + StartDate + "&StartTime=" +StartTime
               });
               $("#Chapter1").datagrid("reload");//重新加载表信息datagrid
            }
       </script>

          这里，strLike即用户输入的字符串，其他的参数规定了查询的范围，即页面当前显示的学生信息。

          Controller代码如下，包含了分页

public JsonResult FuzzyQueryStudentInfo()
        {
           string t = Request.QueryString["strLike"];
           string ExamID = Request.QueryString["ExamID"];
           string ExamPlaceID = Request.QueryString["ExamPlaceID"];
           string StartDate = Request.QueryString["StartDate"];
           string StartTime = Request.QueryString["StartTime"];
           ExamDetailsViewModel studentinfo = new ExamDetailsViewModel
            {
               ExamID = ExamID,
               ExamPlaceID = ExamPlaceID,
               StartDate = StartDate,
               StartTime = StartTime
            };
 
           int pageSize = Request["rows"] == null ? 1 :int.Parse(Request["rows"]);
           int pageIndex = Request["page"] == null ? 10 :int.Parse(Request["page"]);
           int total = 0;
 
           string strLike = Request["strLike"].ToString().Trim();
 
           List<v_studentexaminfo> ListExamInformation = newList<v_studentexaminfo>();
           ListExamInformation =IexaminformationBll.FuzzyQueryStudentInfo(studentinfo, strLike, pageSize,pageIndex, out total).ToList();
           var data = new
            {
               total,
               rows = ListExamInformation
            };
           return Json(data, JsonRequestBehavior.AllowGet);
        }

          最后，使用模糊匹配like进行多字段的查询。

          代码如下

public List<v_studentexaminfo> FuzzyQueryStudentInfo(ExamDetailsViewModel studentinfo, string strLike, int pageSize, int pageIndex, out int total)
        {
            List<v_studentexaminfo> configonclassList = new List<v_studentexaminfo>();
            try
            {
                MySqlConnection conn = MySQLHelper.GetConnection;

                string sql = "select * from (SELECT * FROM v_studentexaminfo WHERE ExamMainID=" + "'" + studentinfo.ExamID + "'" + " and ExamPlaceID=" + "'" + studentinfo.ExamPlaceID + "'" + " and StartDate=" + "'" + studentinfo.StartDate + "'" + " and StartTime=" + "'" + studentinfo.StartTime + "'" + ") aa where StudentNo LIKE'%" + strLike + "%' or StudentName LIKE'%" + strLike + "%' or State LIKE'%" + strLike + "%' or Colleage LIKE'%" + strLike + "%' or Major LIKE'%" + strLike + "%'";
                int begin = pageSize * (pageIndex - 1);
                DataTable dt = MySQLHelper.ExecuteDataTable(sql);
                total = dt.Rows.Count;
                sql = sql + " limit " + begin + "," + pageSize;

                DataTable dt1 = MySQLHelper.ExecuteDataTable(sql);
                List<v_studentexaminfo> examdetailsinfo = ModelConvertHelper<v_studentexaminfo>.ConvertToModel(dt1).ToList();

                return examdetailsinfo;
            }
            catch (Exception)
            {
                throw;
            }
        }