Exemplo n.º 1
0
        public void SQLTest()
        {
            var dbConnection = new DatabaseConnection();
            ISessionFactory sessionFactory = dbConnection.GetSessionFactory();

            IList<DaeHeurigen> returnList;
            string name = "";
            int id = 2;
            int skip = 0;
            int limit = 20;

            using (var session = sessionFactory.OpenSession())
            {
                DaeHeurigen myHeurigen = null;

                try
                {
                    var query = session.CreateCriteria<DaeHeurigen>("h")
                    .CreateCriteria("h.Ratings", "r", JoinType.LeftOuterJoin)
                    .SetProjection(Projections.ProjectionList()

                        .Add(Projections.Property("h.Name").WithAlias(() => myHeurigen.Name))
                        .Add(Projections.Property("h.Postcode").WithAlias(() => myHeurigen.Postcode))
                        .Add(Projections.Property("h.City").WithAlias(() => myHeurigen.City))
                        .Add(Projections.Property("h.Street").WithAlias(() => myHeurigen.Street))
                        .Add(Projections.Property("h.Telephone").WithAlias(() => myHeurigen.Telephone))
                        .Add(Projections.Property("h.Mail").WithAlias(() => myHeurigen.Mail))
                        .Add(Projections.Property("h.HomepageUrl").WithAlias(() => myHeurigen.HomepageUrl))
                        .Add(Projections.Property("h.Description").WithAlias(() => myHeurigen.Description))
                        .Add(Projections.Property("h.Latitude").WithAlias(() => myHeurigen.Latitude))
                        .Add(Projections.Property("h.Longitude").WithAlias(() => myHeurigen.Longitude))
                        .Add(Projections.Property("h.WarmFood").WithAlias(() => myHeurigen.WarmFood))
                        .Add(Projections.Property("h.Logo").WithAlias(() => myHeurigen.Logo))
                        .Add(Projections.Avg("r.RatingStars").WithAlias(() => myHeurigen.AverageRating))
                        .Add(Projections.GroupProperty("h.Id").WithAlias(() => myHeurigen.Id)))
                        .SetResultTransformer(Transformers.AliasToBean<DaeHeurigen>());

                    if (!string.IsNullOrEmpty(name))
                    {
                        query.Add(Restrictions.Like("h.Name", name, MatchMode.Anywhere));
                    }
                    if (id != 0)
                    {
                        query.Add(Restrictions.Eq("h.Id", id));
                    }

                    query.SetFirstResult(skip);
                    query.SetMaxResults(limit);

                    returnList = query.List<DaeHeurigen>();
                }
                catch (Exception)
                {

                    throw;
                }

            }
            Assert.IsNotNull(returnList);
        }
Exemplo n.º 2
0
        public void SQLRatingTest()
        {
            var dbConnection = new DatabaseConnection();
            ISessionFactory sessionFactory = dbConnection.GetSessionFactory();

            IList<DaeRating> returnList;

            using (var session = sessionFactory.OpenSession())
            {
                var rquery = session.CreateCriteria<DaeRating>()
                    .Add(Restrictions.Eq("FkHeurigenId", 1));

                returnList = rquery.List<DaeRating>();
            }
            Assert.IsTrue(returnList.Any());
        }
Exemplo n.º 3
0
        public void MySQLJoinTest()
        {
            var dbConnection = new DatabaseConnection();
            ISessionFactory sessionFactory = dbConnection.GetSessionFactory();

            IList<DaeHeurigen> returnList;
            using (var session = sessionFactory.OpenSession())
            {
                DaeHeurigen myHeurigen = null;

                var query = session.CreateCriteria<DaeHeurigen>("h")
                    .CreateCriteria("h.Ratings", "r", JoinType.InnerJoin)
                    .SetProjection(Projections.ProjectionList()

                        .Add(Projections.GroupProperty("h.Id").WithAlias(() => myHeurigen.Id))
                        .Add(Projections.Property("h.Name").WithAlias(() => myHeurigen.Name))
                        .Add(Projections.Property("h.Postcode").WithAlias(() => myHeurigen.Postcode))
                        .Add(Projections.Property("h.City").WithAlias(() => myHeurigen.City))
                        .Add(Projections.Property("h.Street").WithAlias(() => myHeurigen.Street))
                        .Add(Projections.Property("h.Telephone").WithAlias(() => myHeurigen.Telephone))
                        .Add(Projections.Property("h.Mail").WithAlias(() => myHeurigen.Mail))
                        .Add(Projections.Property("h.HomepageUrl").WithAlias(() => myHeurigen.HomepageUrl))
                        .Add(Projections.Property("h.Description").WithAlias(() => myHeurigen.Description))
                        .Add(Projections.Property("h.Latitude").WithAlias(() => myHeurigen.Latitude))
                        .Add(Projections.Property("h.Longitude").WithAlias(() => myHeurigen.Longitude))
                        .Add(Projections.Property("h.WarmFood").WithAlias(() => myHeurigen.WarmFood))

                        .Add(Projections.Avg("r.RatingStars").WithAlias(() => myHeurigen.AverageRating))

                        .Add(Projections.Property("h.Logo").WithAlias(() => myHeurigen.Logo)))
                        .SetResultTransformer(Transformers.AliasToBean<DaeHeurigen>());

                returnList = query.List<DaeHeurigen>();

            }
            Assert.IsNotNull(returnList);
        }