房号范围模糊查询

房号范围模糊查询

··························································

开发工具：Visual Studio 2015

关键技术：C#MVC、JavaScript

作者：邵亮华

撰写时间：2019-02-24

··························································

 

一般情况下，范围查询都有两个可输入文本值的文本框，那么在控制器编写数据绑定代码时就得提供两个参数接收页面传过来的值（求范围查询就是求在这个区间内的数据集）

RoomNum1、RoomNum2直接通过页面输入房号传递来的参数，进行数据查询整一条房间信息，通过ToList()形成数组（原理相当于查询房间ID，弊端就是如果房号的数据错误就无法达到想要的效果，而且输入的时候RoomNum1<RoomNum2才能搜索，否则无法执行，所以HTML的js提供解决方法）

控制器

public ActionResult SelectNoRentRoomGLAll(BsgridPage bsgridPage, string RoomNum1, string RoomNum2, Int16 RoomStateID)

        {

var listNoRentAll = (from tbRoomWX in myModels.RGL_RoomWX

             join tbStateCause in myModels.base_StateCause on tbRoomWX.StateCauseID

            equals tbStateCause.StateCauseID

             join tbRoomState in myModels.base_RoomState on tbStateCause.RoomStateID equals tbRoomState.RoomStateID

              join tbRoom in myModels.system_Room on tbRoomWX.RoomID equals tbRoom.RoomID

                                select new RoomWXinfor

                                {

                                    RoomWXID = tbRoomWX.RoomWXID,

                                    RoomStateID = tbStateCause.RoomStateID,

                                    RoomStateMC = tbRoomState.RoomStateMC,

                                    StateCauseID = tbRoomWX.StateCauseID,

                                    StateCause= tbStateCause.StateCause,

                                    RoomID = tbRoomWX.RoomID,

                                    RoomNum = tbRoom.RoomNum,

                                }).ToList();

房号的范围查询

            if (!string.IsNullOrEmpty(RoomNum1) && !string.IsNullOrEmpty(RoomNum2))

            {

 直接通过页面输入房号传递来的参数，进行数据查询整一条房间信息，通过ToList()形成区间数组

如先通过传递的房号值从数据库查询房间的某条切确的房间信息，申明两个变量roomID1、roomID2，最后在多表查询Linq语句的数据集筛选出范围：

如果roomID1=3，roomID2=6

listNoRentAll的多表查询数据集 =1  2  [3  4  5  6]  7  8  9

（由于RoomNum是string类型，无法使用"< 、="，只能通过相房号的信息查询房间ID，弊端就是如果房号的数据错误就无法达到想要的效果；而且如果输入的值超出范围，数据库没有这个数据就会报错）

                var roomID1 = myModels.system_Room.Where(M => M.RoomNum == RoomNum1).ToList();

                var roomID2 = myModels.system_Room.Where(M => M.RoomNum == RoomNum2).ToList();

                listNoRentAll = listNoRentAll.Where(m => m.RoomID >= roomID1[0].RoomID && m.RoomID <= roomID2[0].RoomID).ToList();

            }

 

            if (RoomStateID > 0)

            {

                listNoRentAll = listNoRentAll.Where(m => m.RoomStateID == RoomStateID).ToList();

            }

            int count = listNoRentAll.Count();

            List<RoomWXinfor> listRoomWXinfor = listNoRentAll.OrderByDescending(m => m.RoomID)

                .Skip(bsgridPage.GetStartIndex())

                .Take(bsgridPage.pageSize).ToList();

            Bsgrid<RoomWXinfor> bsgrid = new Bsgrid<RoomWXinfor>

            {

                success = true,

                totalRows = count,

                curPage=bsgridPage.curPage,

                data= listRoomWXinfor

            };           

            return Json(bsgrid, JsonRequestBehavior.AllowGet);

        }

 

HTML

由于这的范围查询方法存在一些缺陷：比如

1、不是直接通过房号搜索，而是通过房号ID进行查询、在控制器的条件查询语句

2、RoomNum1<RoomNum2才能查询

但是在页面的js提供解决方法：

 

在页面设计时文本框用正则表达式限制只能输入数字

<input type="text" class="form-control" id="RoomNum_1" name="RoomNum"  onkeyup="this.value=this.value.replace(/\D/g,'')"/>

 

在页面的查询语句判断如果 （参数RoomNum1）>（参数RoomNum2）就将它们的值互相调换过来

先声明一个null的变量temp作为容器便可以腾出空间进行值得调换，再将RoomNum_2的值赋给temp，然后将RoomNum_1的值赋给RoomNum_2，最后temp的值赋给RoomNum_1，就可以达到值互相调换的效果

 

        function searchRoomWX() {

            var RoomNum_1 = $("#RoomNum_1").val();

            var RoomNum_2 = $("#RoomNum_2").val();

            if (parseInt(RoomNum_1) > parseInt(RoomNum_2)) {

                var temp = "";          //null的变量temp

                temp = RoomNum_2;      //temp== RoomNum_2，RoomNum_2==null

                RoomNum_2 = RoomNum_1; //RoomNum_2== RoomNum_1，RoomNum_1==null

                RoomNum_1 = temp;      //RoomNum_1== RoomNum_2

            }

            var RoomStateID = $("#RoomState").val();

            if (RoomStateID == "" || RoomStateID == undefined) {

                RoomStateID = 0;

            }

控制器的参数与页面的参数拼接查询

   tbRoomWXInfor.search("RoomNum1=" + RoomNum_1 + "&RoomNum2=" + RoomNum_2 + "&RoomStateID=" + RoomStateID);

        }

如图：