三、数据API-3

预备

 返回格式需要包括：
// Code     状态码（200，400等）
// Mgs  提示信息（邮箱格式不正确；数据返回成功等）
// Result     返回数据

　　

一、WebAPI与传统MVC的区别是 MVC是 ActionResule /WebAPI则是自定义接口返回的格式

        /// <summary>
        /// 院校库
        /// </summary>
        /// <returns></returns>
        [Route("GetUniversityLibByPager")]
        [HttpPost]
        public WebApiResponseCommonDTO GetUniversityLibByPager()
        {
            var hs = new WebApiResponseCommonDTO();
            string[] category = new string[] { "985", "211", "双一流", "全国重点" };
            string[] nature = new string[] { "公立", "私立" };
            try
            {
                var arrProvinceInfo = _ProvinceInfo.Get().Select(o => o.YD_ProvinceName).ToList();
                var arrUniversityInfo = _UniversityInfo.GetUtypeList().ToList();

                var data = new List<object>();
                data.Add(new
                {
                    Category = category,
                    Nature = nature,
                    ProvinceInfo = arrProvinceInfo,
                    UniversityInfo = arrUniversityInfo
                });
                hs.Result = data;
                hs.Code = true;
                hs.Msg = "成功";
            }
            catch (InvalidCastException ex)
            {
                Log4NetHelper.WriteError("------获取院校库失败------" + ex.ToString(), DevAuthorNameEnum.SongShan);
                hs.Code = false;
                hs.Msg = "失败";
            }
            return hs;
        }

　　

二、EF的方式读取到数据从var变量中取值放在MajorAndClassInfo对象中

var MajorInfo = yd_Umm.GetUniversityMapMajorAndClassInfo(query.Id);//这是数据库读取的
    if (MajorInfo.Count() > 0)
    {
        universitydto.MajorInfo = MajorInfo.Select(w => new MajorAndClassInfo //遍历数据库每一行   将每一行的列值从新指定
        {
            YD_MajorId = w.YD_MajorId,
            YD_MajorClassName = w.YD_MajorClassName,
            YD_MajorTitle = w.YD_MajorTitle,
            //YD_MajorTitle = yd_MajorInfo.GetSingle(w.YD_MajorId).YD_MajorName
        }).ToList();
    }

　　

 三、order by方式

List<UniversalInfo> list =new List<UniversalInfo>();
            list = (from n in dbEntities.UniversalInfo
                where n.UniTypeId == unitypeid && n.IsFlag == 1
                orderby n.CreateTime descending
                select new
                {
                    UniInfoId = n.UniTypeId,
                  
                    UniInfoTitle = n.UniInfoTitle,
                  
                    CreateTime = n.CreateTime
                }).ToList().Select(a=>new UniversalInfo
                {
                    UniTypeId = a.UniInfoId,
                    UniInfoTitle = a.UniInfoTitle,
                    CreateTime = a.CreateTime
                }).ToList();
            return list;

　　

 四、基于EF的linq 查询Select多字段

 var Pro = _profession.Get().Where(o => o.YD_ProfessionTitle == ProfessionTitle).ToList().Select(o=> new{ o.YD_ProfessionTitle,o.YD_Icofont,o.YD_Introduce }).Distinct();    //多字段要new，因为通过名字查询 ==非like 所以 Distinct()一下
   var classname = _yd_Class.Get(x => x.Id == Guid.NewGuid()).FirstOrDefault().YD_ClassName; //获取班级    列在后面

　　

五、接触写sql的地方

List<SqlParameter> ilist = new List<SqlParameter>(); 
            ilist.Add(new SqlParameter("@Param1", "1"));
            ilist.Add(new SqlParameter("@Param2", "2"));
            ilist.Add(new SqlParameter("@Param3", "3"));
            ilist.Add(new SqlParameter("@Param4", "4"));
            ilist.Add(new SqlParameter("@ParamLike","%5%"));//Like的写法
            SqlParameter[] param = ilist.ToArray();



List<string> listWhere = new List<string>();
            List<SqlParameter> listParameters = new List<SqlParameter>();
            if (cbName.Checked)
            {
                listWhere.Add("Name like @name");
                listParameters.Add(new SqlParameter("name","%"+txtQueryName.Text+"%"));
            }
            if (cbMobile.Checked)
            {
                listWhere.Add("MobilePhone like @mobile");
                listParameters.Add(new SqlParameter("mobile", "%" + txtQueryMobile.Text + "%"));
            }
           string sql = "select * from T_Customers
";
            
            if (listWhere.Count > 0)
            {
                string sqlWhere = string.Join(" and ", listWhere.ToArray());
                sql =sql+" where "+sqlWhere;
            }
            dataGridView1.DataSource = SqlHelper.ExecuteDataTable(sql, listParameters.ToArray());