コード例 #1
0
        public QueryObject <ShopWithOwner> QueryShopWithOwner(ShopRequest request)
        {
            var sql =
                @"select t.*, u.DisplayName as OwnerDisplayName, u.Mobile as OwnerMobileNumber from shops t inner join users u on t.ShopId = u.ShopId where
u.IsMobileAvailable = 1
and (@Name is null or t.Name = @Name)
and (@State is null or t.State = @State)
and (@ShopId is null or t.ShopId = @ShopId)
and (@NameWith is null or t.Name like '%'+ @NameWith + '%')
and (@DisplayNameWith is null or t.DisplayName like '%'+ @DisplayNameWith + '%')
and (@ShopIds is null or t.ShopId in (@ShopIds))";

            return(new QueryObject <ShopWithOwner>(_databaseInstance, sql, request));
        }
コード例 #2
0
        public DataTables <Shop> NewQuery(ShopRequest request)
        {
            SqlParameter[] param =
            {
                new SqlParameter("@Name",              request.Name),
                new SqlParameter("@NameWith",          request.NameWith),
                new SqlParameter("@DisplayNameWith",   request.DisplayNameWith),
                new SqlParameter("@State",             request.State),
                new SqlParameter("@pageIndex",         request.PageIndex),
                new SqlParameter("@pageSize",          request.PageSize),
                new SqlParameter("@IsMobileAvailable", request.IsMobileAvailable)
            };
            StoreProcedure sp = new StoreProcedure("P_getShops", param);

            return(_databaseInstance.GetTables <Shop>(sp));
        }
コード例 #3
0
        public QueryObject <Shop> Query(ShopRequest request)
        {
            var sql =
                @"select t.* from shops t where
(@Name is null or t.Name = @Name)
and (@State is null or t.State = @State)
and (@IsBuildIn is null or t.BuildIn = @IsBuildIn)
and (@IsMobileAvailable is null 
        or ( @IsMobileAvailable = 1 and exists (select * from users u where u.shopId = t.shopId and u.IsMobileAvailable = 1))
        or ( @IsMobileAvailable = 0  and  not exists (select * from users u where u.shopId = t.shopId and u.IsMobileAvailable = 1))
    )
and (@ShopId is null or t.ShopId = @ShopId)
and (@NameWith is null or t.Name like '%'+ @NameWith + '%')
and (@DisplayNameWith is null or t.DisplayName like '%'+ @DisplayNameWith + '%')
and (@ShopIds is null or t.ShopId in (@ShopIds))";

            return(new QueryObject <Shop>(_databaseInstance, sql, request));
        }