ASP.NET MVC+EasyUi 实现二级联动以及多条件查询

说明:本文中的XJ,Dm_xslb,Py_xs,Py_zy类,见之前的文章:ASP.NET MVC+EasyUi 分页显示数据表格,SQLHelper类与连接数据库的代码之前文章:ASP.NET MVC+Easyui 实现验证码登录
除此之外,本章再添加两个类:XJHelper.cs和Py_xs_zy.cs

XJHelper.cs

	public class XJHelper
    {
        public List<XJ> xjs { get; set; }
        public int count { get; set; }
    }

Py_xs_zy.cs

	public class Py_xs_zy
    {
        //院系编号
        public string xsbh { get; set; }
        //专业代码
        public string zydm { get; set; }

        /// <summary>
        /// 院系
        /// </summary>
        public Py_xs py_xs { get; set; }

        /// <summary>
        /// 专业
        /// </summary>
        public Py_zy py_zy { get; set; }
    }

1.Service层

  1. Py_xsService.cs
	public class Py_xsService
    {
    	//查询学院信息列表
        public List<Py_xs> FindAll()
        {
            List<Py_xs> py_Xses = new List<Py_xs>();
            string sql = " select xsbh,xsmc" +
                         " from py_xs";
            SqlDataReader reader = SQLHelper.ExecuteReader(sql, null);
            while (reader.Read())
            {
                Py_xs py_xs = new Py_xs();
                py_xs.xsbh = reader["xsbh"].ToString();
                py_xs.xsmc = reader["xsmc"].ToString();
                py_Xses.Add(py_xs);
            }
            reader.Close();
            return py_Xses;
        }
    }
  1. Py_zyService.cs
	public class Py_zyService
    {
    	//查询专业信息列表
        public List<Py_zy> FindAll()
        {
            List<Py_zy> py_Zies = new List<Py_zy>();
            string sql = " select zydm,zymc" +
                         " from py_zy";
            SqlDataReader reader = SQLHelper.ExecuteReader(sql, null);
            while (reader.Read())
            {
                Py_zy py_zy = new Py_zy();
                py_zy.zydm = reader["zydm"].ToString();
                py_zy.zymc = reader["zymc"].ToString();
                py_Zies.Add(py_zy);
            }
            reader.Close();
            return py_Zies;
        }
    }
  1. Dm_xslbService.cs
	public class Dm_xslbService
    {
    //查询所有学生类别
        public List<Dm_xslb> FindAll()
        {
            List<Dm_xslb> dm_Xslbs = new List<Dm_xslb>();
            string sql = " select xslbm,xslb" +
                         " from dm_xslb";
            SqlDataReader reader = SQLHelper.ExecuteReader(sql, null);
            while (reader.Read())
            {
                Dm_xslb dm_xslb = new Dm_xslb();
                dm_xslb.xslbm = reader["xslbm"].ToString();
                dm_xslb.xslb = reader["xslb"].ToString();
                dm_Xslbs.Add(dm_xslb);
            }
            reader.Close();
            return dm_Xslbs;
        }
    }
  1. Py_xs_zyService.cs
	public class Py_xs_zyService
	{
		//根据院系编号获取专业列表
        public List<Py_zy> FindByXs(string xsbh)
        {
            List<Py_zy> py_zies = new List<Py_zy>();
            string sql = " select xsbh,zydm" +
                         " from py_xs_zy" +
                         " where xsbh=@xsbh";
            SqlParameter[] pms = { new SqlParameter("xsbh", xsbh) };
            SqlDataReader reader = SQLHelper.ExecuteReader(sql, pms);
            while (reader.Read())
            {
                string zydm = reader["zydm"].ToString();
                Py_zy py_zy = new Py_zyService().FindByZydm(zydm);
                py_zies.Add(py_zy);
            }
            reader.Close();
            return py_zies;
        }
	}
  1. XJService.cs
	public class XJService
    {
    	private Py_rkjsService py_rkjsService = new Py_rkjsService();
        private Py_xsService py_xsService = new Py_xsService();
        private Py_zyService py_zyService = new Py_zyService();
        private Dm_xslbService dm_xslbService = new Dm_xslbService();
    	//多条件分页查询
        public XJHelper SearchByPage(XJ xj, int start, int end)
        {
            List<XJ> xjs = new List<XJ>();
            string sqlData = " select *" +
                         " from(select row_number() over(order by xh asc) as num,*" +
                         " from xj " +
                         " where 1=1 ";
            string sqlCount = " SELECT count(1) count" +
                         " FROM xj " +
                         " where 1=1 ";
            //用count记录需要多少个参数
            int totalCount = 0;
            int dataCount = 2;
            if (xj.xh != null)
            {
                totalCount++;
                dataCount++;
            }
            if (xj.xm != null)
            {
                totalCount++;
                dataCount++;
            }
            if (xj.zydm != null)
            {
                totalCount++;
                dataCount++;
            }
            if (xj.yxsh != null)
            {
                totalCount++;
                dataCount++;
            }
            if (xj.xslb != null)
            {
                totalCount++;
                dataCount++;
            }
            if (xj.nj != null)
            {
                totalCount++;
                dataCount++;
            }
            SqlParameter[] pmsCount = new SqlParameter[totalCount];
            SqlParameter[] pmsData = new SqlParameter[dataCount];
            //用index下标记录参数下标
            int dataIndex = 0;
            int totalIndex = 0;
            if (xj.xh != null)
            {
                //拼接sql语句,并给pms赋值,最后index++,方便下一个赋值
                sqlData += "and xj.xh like '%'+@xh+'%' ";
                pmsData[dataIndex] = new SqlParameter("xh", xj.xh);
                dataIndex++;
                sqlCount += "and xj.xh like '%'+@xh+'%' ";
                pmsCount[totalIndex] = new SqlParameter("xh", xj.xh);
                totalIndex++;
            }
            if (xj.xm != null)
            {
                sqlData += "and xj.xm like '%'+@xm+'%' ";
                pmsData[dataIndex] = new SqlParameter("xm", xj.xm);
                dataIndex++;
                sqlCount += "and xj.xm like '%'+@xm+'%' ";
                pmsCount[totalIndex] = new SqlParameter("xm", xj.xm);
                totalIndex++;
            }
            if (xj.zydm != null)
            {
                sqlData += "and xj.zydm=@zydm ";
                pmsData[dataIndex] = new SqlParameter("zydm", xj.zydm);
                dataIndex++;
                sqlCount += "and xj.zydm=@zydm ";
                pmsCount[totalIndex] = new SqlParameter("zydm", xj.zydm);
                totalIndex++;
            }
            if (xj.yxsh != null)
            {
                sqlData += "and xj.yxsh=@yxsh ";
                pmsData[dataIndex] = new SqlParameter("yxsh", xj.yxsh);
                dataIndex++;
                sqlCount += "and xj.yxsh=@yxsh ";
                pmsCount[totalIndex] = new SqlParameter("yxsh", xj.yxsh);
                totalIndex++;
            }
            if (xj.xslb != null)
            {
                sqlData += "and xj.xslb=@xslb ";
                pmsData[dataIndex] = new SqlParameter("xslb", xj.xslb);
                dataIndex++;
                sqlCount += "and xj.xslb=@xslb ";
                pmsCount[totalIndex] = new SqlParameter("xslb", xj.xslb);
                totalIndex++;
            }
            if (xj.nj != null)
            {
                sqlData += "and xj.nj=@nj ";
                pmsData[dataIndex] = new SqlParameter("nj", xj.nj);
                dataIndex++;
                sqlCount += "and xj.nj=@nj ";
                pmsCount[totalIndex] = new SqlParameter("nj", xj.nj);
            }
            sqlData += " ) as t" +
                       " where t.num >= @start and t.num <= @end";
            pmsData[dataIndex] = new SqlParameter("start", start);
            dataIndex++;
            pmsData[dataIndex] = new SqlParameter("end", end);
            SqlDataReader reader = SQLHelper.ExecuteReader(sqlData, pmsData);
            SqlDataReader countReader = SQLHelper.ExecuteReader(sqlCount, pmsCount);
            while (reader.Read())
            {
                XJ xj2 = new XJ();
                xj2.id = Convert.ToInt32(reader["id"]);
                xj2.xh = reader["xh"].ToString();
                xj2.xm = reader["xm"].ToString();
                xj2.xbm = Convert.ToInt32(reader["xbm"]);
                xj2.nj = reader["nj"].ToString();
                xj2.rxrq = (DateTime)reader["rxrq"];
                xj2.yxsh = reader["yxsh"].ToString();
                xj2.dsbh = reader["dsbh"].ToString();
                xj2.zydm = reader["zydm"].ToString();
                xj2.xslb = reader["xslb"].ToString();
                Py_xs py_xs = py_xsService.FindByXsbh(xj2.yxsh);
                xj2.py_xs = py_xs;
                Py_rkjs py_rkjs = py_rkjsService.FindByJsbh(xj2.dsbh);
                xj2.py_rkjs = py_rkjs;
                Py_zy py_zy = py_zyService.FindByZydm(xj2.zydm);
                xj2.py_zy = py_zy;
                Dm_xslb dm_xslb = dm_xslbService.FindByXslbm(xj2.xslb);
                xj2.dm_xslb = dm_xslb;
                xjs.Add(xj2);
            }
            int count = 0;
            if (countReader.Read())
            {
                count = Convert.ToInt32(countReader["count"]);
            }
            XJHelper xjHelper = new XJHelper();
            xjHelper.count = count;
            xjHelper.xjs = xjs;
            reader.Close();
            countReader.Close();
            return xjHelper;
        }
    }

2.视图层

(1)页面代码

<p>
    <!--搜索栏start-->
    <div id="searchDiv" style="padding:3px">
        <span>学号:</span>
        <input id="xh" class="easyui-textbox" style="line-height:20px;border:1px solid #ccc;width:100px;">

        <span>姓名:</span>
        <input id="xm" class="easyui-textbox" style="line-height:20px;border:1px solid #ccc;width:100px;">

        <span>年级:</span>
        <select class="easyui-combobox" id="nj" style="width:100px;"></select>

        <!--以下循环/XJ/Index封装的ViewBag数据-->
        <span>院系:</span>
        <select class="easyui-combobox" id="yxsh" style="width:120px;"></select>

        <span>专业:</span>
        <select class="easyui-combobox" id="zydm" style="width:120px;"></select>

        <span>类别:</span>
        <select class="easyui-combobox" id="xslb" style="width:120px;"></select>

        <a href="#" class="easyui-linkbutton" iconCls="icon-search" onclick="doSearch()">搜索</a>
    </div>
    <!--搜索栏end-->
</p>

(2)JavaScript代码:加载下拉框数据以及实现二级联动

<script type="text/javascript">
    $(function () {
        var relation_id_nj = 0;
        var relation_id_yxsh = 0;
        var relation_id_zydm = 0;
        var relation_id_xslb = 0;
        //加载年级搜索下拉框
        $('#nj').combobox({
            valueField: 'nj',
            textField: 'nj',
            url: '/XJ/FindNjList',
            value: '--全部--',
            onShowPanel: function () {
                var data = $(this).combobox('getData');
                if (relation_id_nj == 0) {
                    data.unshift({ 'nj': '0', 'nj': '--全部--' });
                }
                $("#nj").combobox("loadData", data);
                relation_id_nj = 1;
            }
        });
        //加载院系搜索下拉框
        $('#yxsh').combobox({
            valueField: 'xsbh',
            textField: 'xsmc',
            url: '/Py_xs/FindAll',
            value:'--全部--',
            onShowPanel: function () {
                var data = $(this).combobox('getData');
                if (relation_id_yxsh == 0) {
                    data.unshift({ 'xsbh': '0', 'xsmc': '--全部--' });
                }
                $("#yxsh").combobox("loadData", data);
                relation_id_yxsh = 1;
            },
            onSelect: function (rec) {
                $('#zydm').combobox('setValue', '--全部--');
                var url = '/XJ/FindByXs?xsbh=' + rec.xsbh;
                if (rec.xsbh == 0) {
                    relation_id_zydm = 0;
                } 
                $('#zydm').combobox('reload', url);
            }
        });
        //加载专业搜索下拉框
        $('#zydm').combobox({
            valueField: 'zydm',
            textField: 'zymc',
            url: '/Py_zy/FindAll',
            value: '--全部--',
            onShowPanel: function () {
                var data = $(this).combobox('getData');
                if (relation_id_zydm == 0) {
                    data.unshift({ 'zydm': '0', 'zymc': '--全部--' });
                }
                $("#zydm").combobox("loadData", data);
                relation_id_zydm = 1;
            }
        });
        //加载学生类别搜索下拉框
        $('#xslb').combobox({
            valueField: 'xslbm',
            textField: 'xslb',
            url: '/Dm_xslb/FindAll',
            value: '--全部--',
            onShowPanel: function () {
                var data = $(this).combobox('getData');
                if (relation_id_xslb == 0) {
                    data.unshift({ 'xslbm': '0', 'xslb': '--全部--' });
                }
                $("#xslb").combobox("loadData", data);
                relation_id_xslb = 1;
            }
        });
     });
</script>

(3)JavaScript代码:实现搜索功能
注意:下拉框的值是通过.combobox(“getValue”)获取的

<script type="text/javascript">

    var url;
    //查询
    function doSearch() {
        //获取查询条件框中的值
        var xh = $('#xh').val();
        var xm = $('#xm').val();
        var nj = $('#nj').combobox("getValue");
        var zydm = $('#zydm').combobox("getValue");
        var xslb = $('#xslb').combobox("getValue");
        var yxsh = $('#yxsh').combobox("getValue");
        //编写url
        url = "/XJ/Search?1=1";
        //以下为拼接url的值,当有查询条件时才进行拼接
        if (xh != '' && xh != null) {
            url += "&xh=" + xh;
        }
        if (xm != '' && xm != null) {
            url += "&xm=" + xm;
        }
        if (nj != 0 && nj != "--全部--") {
            url += "&nj=" + nj;
        }
        if (zydm != 0 && zydm != "--全部--") {
            url += "&zydm=" + zydm;
        }
        if (xslb != 0 && xslb != "--全部--") {
            url += "&xslb=" + xslb;
        }
        if (yxsh != 0 && yxsh != "--全部--") {
            url += "&yxsh=" + yxsh;
        }
        //调用拼接完成后的url,并将后台传递的数据重新加载至数据表格
        var options = $("#dg").datagrid("options");//获取 option设置对象
        options.url = url;//设置url
        $("#dg").datagrid("load");//重新加载
    }
</script>

3.控制层

  1. Py_xsController.cs
	public class Py_xsController : Controller
    {
    	private Py_xsService py_xsService = new Py_xsService();
    	//查询所有信息列表,供其他页面使用
        public ActionResult FindAll()
        {
            List<Py_xs> py_xses = py_xsService.FindAll();
            return Json(py_xses, JsonRequestBehavior.AllowGet);
        }
    }
  1. Py_zyController.cs
	public class Py_zyController : Controller
    {
    	private Py_zyService py_zyService = new Py_zyService();
    	//查询所有专业信息列表,供其他页面使用
        public ActionResult FindAll()
        {
            List<Py_zy> py_Zies = py_zyService.FindAll();
            return Json(py_Zies, JsonRequestBehavior.AllowGet);
        }
    }
  1. Dm_xslbController.cs
	public class Dm_xslbController : Controller
    {
    	private Dm_xslbService dm_xslbService = new Dm_xslbService();
    	//返回学生类别列表,供其他页面使用
        public ActionResult FindAll()
        {
            List<Dm_xslb> dm_Xslbs = dm_xslbService.FindAll();
            return Json(dm_Xslbs, JsonRequestBehavior.AllowGet);
        }
    }
  1. XJController.cs
	public class XJController : Controller
    {
    	XJService xjService = new XJService();
    	Py_zyService py_ZyService = new Py_zyService();
        Py_xs_zyService Py_Xs_ZyService = new Py_xs_zyService();
    	
    	//返回年级列表
        public ActionResult FindNjList()
        {
            List<XJ> xjList = xjService.FindNjList();
            return Json(xjList, JsonRequestBehavior.AllowGet);
        }
        
		//根据学院新查询专业列表
        public ActionResult FindByXs(string xsbh)
        {
            List<Py_zy> py_zies = new List<Py_zy>();
            if (xsbh==null)
            {
                py_zies = py_ZyService.FindAll();
            }
            else
            {
                if (xsbh.Equals("0"))
                {
                    py_zies = py_ZyService.FindAll();
                }
                else
                {
                    py_zies = Py_Xs_ZyService.FindByXs(xsbh);
                }
            }
            return Json(py_zies, JsonRequestBehavior.AllowGet);
        }
        
    	//多条件查询
        public ActionResult Search()
        {
            List<XJ> xjs = new List<XJ>();
            //获取页面传递的查询条件参数
            string xh = Request.Params["xh"];
            string xm = Request.Params["xm"];
            string zydm = Request.Params["zydm"];
            string xslb = Request.Params["xslb"];
            string yxsh = Request.Params["yxsh"];
            string nj = Request.Params["nj"];
            int pageIndex = Convert.ToInt32(Request.Params["page"]);//easyUI默认传当前页码,固定参数为page
            int pageSize = Convert.ToInt32(Request.Params["rows"]);//easyUI默认传每页数据量,固定参数为rows
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            int count = 0;
            //若查询条件都为空,直接返回所有列表
            if (xh == null && xm == null && zydm == null && xslb == null && yxsh == null && nj == null)
            {
                xjs = xjService.FindAllByPage(start, end);
                count= xjService.Count();
            }
            //若查询条件中有一个不为空,则调用查询方法
            else
            {
                XJ xj = new XJ();
                xj.xh = xh;
                xj.xm = xm;
                xj.zydm = zydm;
                xj.xslb = xslb;
                xj.yxsh = yxsh;
                xj.nj = nj;
                XJHelper xjHelper = xjService.SearchByPage(xj,start,end);
                xjs = xjHelper.xjs;
                count = xjHelper.count;
            }
            //最后将查询结果列表用Json数据返回
            return Json(new { rows = xjs, total = count }, JsonRequestBehavior.AllowGet);
        }
    }
发布了49 篇原创文章 · 获赞 2 · 访问量 1018
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 游动-白 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览