Example #1
0
        /// <summary>
        ///  获取gridtable数据源
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sable"></param>
        /// <param name="pars">查询参数</param>
        /// <param name="selectFields">查询字段</param>
        /// <param name="whereObj">参数条件</param>
        /// <returns></returns>
        public static JsonResultModel <T> GetWidgetsSource <T>(Sqlable sable, GridSearchParams pars, string selectFields = "*", object whereObj = null) where T : class
        {
            var    query     = System.Web.HttpContext.Current.Request.QueryString;
            Type   type      = typeof(T);
            string tableName = type.Name;

            sable = sable.Where(BuildQuery(query));
            string orderBy = null;

            if (!string.IsNullOrEmpty(pars.sortorder))
            {
                orderBy = (pars.sortdatafield + " " + pars.sortorder);
            }
            else
            {
                orderBy = "getdate()";
            }
            var rows   = sable.SelectToPageList <T>(selectFields, orderBy, pars.pagenum + 1, pars.pagesize, whereObj);
            var result = new JsonResultModel <T>()
            {
                TotalRows = sable.Count(whereObj),
                Rows      = rows
            };

            return(result);
        }
Example #2
0
        /// <summary>
        /// 接近Sql的编程模式
        /// </summary>
        private void SqlableDemo()
        {
            using (var db = SugarDao.GetInstance())
            {
                //---------Sqlable,创建多表查询---------//

                //多表查询
                List <School> dataList = db.Sqlable()
                                         .From("school", "s")
                                         .Join("student", "st", "st.id", "s.id", JoinType.INNER)
                                         .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT)
                                         .Where("s.id>100 and s.id<@id")
                                         .Where("1=1")//可以多个WHERE
                                         .OrderBy("id")
                                         .SelectToList <School /*新的Model我这里没有所以写的School*/>("st.*", new { id = 1 });

                //多表分页
                List <School> dataPageList = db.Sqlable()
                                             .From("school", "s")
                                             .Join("student", "st", "st.id", "s.id", JoinType.INNER)
                                             .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT)
                                             .Where("s.id>100 and s.id<100")
                                             .SelectToPageList <School>("st.*", "s.id", 1, 10);

                //多表分页WHERE加子查询
                List <School> dataPageList2 = db.Sqlable()
                                              .From("school", "s")
                                              .Join("student", "st", "st.id", "s.id", JoinType.INNER)
                                              .Join("student", "st2", "st2.id", "st.id", JoinType.LEFT)
                                              .Where("s.id>100 and s.id<100 and s.id in (select 1 )" /*这里面写子查询都可以*/)
                                              .SelectToPageList <School>("st.*", "s.id", 1, 10);



                //--------转成List Dynmaic 或者 Json-----//

                //不分页
                var list1 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToDynamic("*", new { id = 1 });
                var list2 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToJson("*", new { id = 1 });
                var list3 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToDataTable("*", new { id = 1 });

                //分页
                var list4 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 });
                var list5 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageTable("s.*", "l.id", 1, 10, new { id = 1 });
                var list6 = db.Sqlable().From("student", "s").Join("school", "l", "s.sch_id", "l.id and l.id=@id", JoinType.INNER).SelectToPageDynamic("s.*", "l.id", 1, 10, new { id = 1 });


                //--------拼接-----//
                Sqlable sable = db.Sqlable().From <Student>("s").Join <School>("l", "s.sch_id", "l.id", JoinType.INNER);
                string  name  = "a";
                int     id    = 1;
                if (!string.IsNullOrEmpty(name))
                {
                    sable = sable.Where("s.name=@name");
                }
                if (!string.IsNullOrEmpty(name))
                {
                    sable = sable.Where("s.id=@id or s.id=100");
                }
                if (id > 0)
                {
                    sable = sable.Where("l.id in (select top 10 id from school)");//where加子查询
                }
                var pars      = new { id = id, name = name };
                int pageCount = sable.Count(pars);
                var list7     = sable.SelectToPageList <Student>("s.*", "l.id desc", 1, 20, pars);
            }
        }