Exemple #1
0
        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <typeparam name="T">查询对象</typeparam>
        /// <param name="con">数据库链接</param>
        /// <param name="fieldParam">查询字段,查询全部为null,查询某个字段,如:new {name="",id=0,status=0}</param>
        /// <param name="where">查询条件where sql语句</param>
        /// <param name="whereParam">查询条件参数值,如:new {name="danny",status=1}</param>
        /// <param name="orderBy">排序字段 ,正序为true,降序为false,如:new {sort_id=false,id=true}</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="pageindex">当前页</param>
        /// <param name="totalCount">返回总条数</param>
        /// <returns></returns>
        public static List <T> GetList <T>(this SqlConnection con, object fieldParam, object where, object whereParam, object orderBy, int pagesize, int pageindex, out int totalCount)
        {
            var table_name = typeof(T).Name;

            #region 查询字段
            var           fields = "*";
            StringBuilder sql    = new StringBuilder();
            if (fieldParam != null)
            {
                fields = "";
                var t          = fieldParam.GetType();
                var properties = t.GetProperties();
                for (int i = 0; i < properties.Length; i++)
                {
                    if (i > 0)
                    {
                        fields += ",";
                    }
                    fields += properties[i].Name;
                }
            }

            sql.Append($"select {fields} from [{table_name}]  ");
            #endregion
            #region 查询条件
            if (where != null)
            {
                sql.Append($" where {where.ToString()}");
                //var t = whereParam.GetType();
                //var properties = t.GetProperties();
                //for (int i = 0; i < properties.Length; i++)
                //{
                //    if (i > 0)
                //        sql.Append($" and  ");
                //    sql.Append($" {properties[i].Name}=@{properties[i].Name}");
                //}
            }
            #endregion
            #region 排序字段
            var order = "";
            if (orderBy != null)
            {
                var t          = orderBy.GetType();
                var properties = t.GetProperties();
                for (int i = 0; i < properties.Length; i++)
                {
                    if (i > 0)
                    {
                        order += ",";
                    }
                    order += $" {properties[i].Name} {((Utils.ObjToBool(properties[i].GetValue(orderBy), false)) ? "asc" : "desc")}";
                }
            }
            else
            {
                var t          = typeof(T);
                var properties = t.GetProperties();
                order += $" {properties[0].Name}  asc";
            }

            #endregion
            var safeSql = GetAntiXssSql(sql.ToString());

            totalCount = con.Query <int>(PagingHelper.CreateCountingSql(safeSql), whereParam).First();
            var pagingSql = PagingHelper.CreatePagingSql(totalCount, pagesize, pageindex, safeSql, order);
            return(con.Query <T>(pagingSql, whereParam).ToList());
        }