Ejemplo n.º 1
0
        public T Single <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao, new()
        {
            var attr = GetTableAttr <T>();

            var sql = new StringBuilder();

            sql.Append($"SELECT {BuildSelectSqlColumn(attr)} FROM {attr.Name}");

            var condition = new ConditionBuilder();

            condition.Build(whereBy.Body);

            if (!string.IsNullOrEmpty(condition.Condition))
            {
                sql.Append(" WHERE " + condition.Condition);
            }

            if (!string.IsNullOrEmpty(attr.Sort))
            {
                sql.Append(" ORDER BY " + attr.Sort);
            }

            var instance = _dapper.QueryFirstOrDefault <T>(sql.ToString(), condition.DapperArguments);

            instance?.Inital();

            return(instance);
        }
Ejemplo n.º 2
0
        public List <T> Query <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao, new()
        {
            var attr = GetTableAttr <T>();

            var sql = new StringBuilder();

            sql.Append($"SELECT {BuildSelectSqlColumn(attr)} FROM {attr.Name}");

            var condition = new ConditionBuilder();

            condition.Build(whereBy.Body);

            if (!string.IsNullOrEmpty(condition.Condition))
            {
                sql.Append(" WHERE " + condition.Condition);
            }

            if (!string.IsNullOrEmpty(attr.Sort))
            {
                sql.Append(" ORDER BY " + attr.Sort);
            }

            var list = _dapper.Query <T>(sql.ToString(), condition.DapperArguments).ToList();

            if (list.Count > 0)
            {
                list.ForEach(x => x.Inital());
            }

            return(list);
        }
Ejemplo n.º 3
0
        public int Update <T>(T instance, Expression <Func <T, bool> > whereBy) where T : class, IDao
        {
            var listCol = new List <string>();

            var sqlPara = new DynamicParameters(new { });

            foreach (var pi in instance.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
            {
                var attrCol = GetColumnAttr(pi);

                if (attrCol != null)
                {
                    var value = pi.GetValue(instance, null);

                    listCol.Add($"{attrCol.Name} = @{attrCol.Name}");
                    sqlPara.Add(attrCol.Name, value == null && pi.PropertyType == typeof(string) ? string.Empty : value);
                }
            }

            var attr = GetTableAttr <T>();

            if (listCol.Count > 0 && sqlPara.ParameterNames.Any())
            {
                var sql = new StringBuilder();

                sql.Append($"UPDATE {attr.Name} SET {string.Join(", ", listCol.ToArray())}");

                var condition = new ConditionBuilder();
                condition.Build(whereBy.Body);

                if (!string.IsNullOrEmpty(condition.Condition))
                {
                    sql.Append(" WHERE " + condition.Condition);
                }

                foreach (var p in condition.DapperArguments)
                {
                    sqlPara.Add(p.Key, p.Value);
                }

                return(_dapper.Execute(sql.ToString(), sqlPara));
            }

            return(-1);
        }
Ejemplo n.º 4
0
        public int Count <T>(Expression <Func <T, bool> > whereBy) where T : class, IDao
        {
            var attr = GetTableAttr <T>();

            var sql = new StringBuilder();

            sql.Append($"SELECT COUNT(*) FROM {attr.Name}");

            var condition = new ConditionBuilder();

            condition.Build(whereBy.Body);

            if (!string.IsNullOrEmpty(condition.Condition))
            {
                sql.Append(" WHERE " + condition.Condition);
            }

            return(_dapper.ExecuteScalar <int>(sql.ToString(), condition.DapperArguments));
        }
Ejemplo n.º 5
0
        public List <T> Query <T>(IPager pager, Expression <Func <T, bool> > whereBy, string orderBy = null)
            where T : class, IDao, new()
        {
            var attr = GetTableAttr <T>();

            // Generate WhereBy Clause
            var condition = new ConditionBuilder();

            condition.Build(whereBy.Body);

            // Generate OrderBy Clause
            var strOrderBy = !string.IsNullOrEmpty(attr.Sort) ? attr.Sort : attr.Key;

            if (!string.IsNullOrEmpty(orderBy))
            {
                strOrderBy = orderBy;
            }

            // Get TotalCount First
            var countSql = $"SELECT COUNT({attr.Key}) AS TotalCount FROM {attr.Name} WHERE {condition.Condition}";

            pager.SetTotalCount(_dapper.ExecuteScalar <int>(countSql, condition.DapperArguments));

            // Build Sql and Execute
            var innerSql = $"SELECT ROW_NUMBER() OVER(ORDER BY {strOrderBy}) AS RowNo, * FROM {attr.Name} WHERE {condition.Condition}";

            string sql =
                $"SELECT {BuildSelectSqlColumn(attr)} FROM ({innerSql}) AS t WHERE t.RowNo BETWEEN {pager.CurrentPage * pager.PagingSize + 1} AND {(pager.CurrentPage + 1) * pager.PagingSize}";

            var list = _dapper.Query <T>(sql, condition.DapperArguments).ToList();

            if (list.Count > 0)
            {
                list.ForEach(x => x.Inital());
            }

            return(list);
        }