C# ServiceQueryOptions.GetSqlQueryTop方法代码示例

        public List<TransitPlace> GetFavoritePlaces(string ticket, ServiceQueryOptions serviceoptions)
        {
            using (SnCore.Data.Hibernate.Session.OpenConnection())
            {
                ISession session = SnCore.Data.Hibernate.Session.Current;

                ISQLQuery q = session.CreateSQLQuery(
                    "CREATE TABLE #fav (	[Id] [int],	[Score] [int] )\n" +
                    "INSERT INTO #fav ( [Id], [Score] ) " +
                    " SELECT Place_Id, 1 FROM AccountPlaceFavorite " +
                    "CREATE TABLE #pl (	[Id] [int],	[Score] [int] )\n" +
                    "INSERT INTO #pl ( [Id], [Score] )" +
                    " SELECT Id, SUM(Score) AS 'Score' FROM #fav " +
                    " GROUP BY Id\n" +
                    "SELECT " + (serviceoptions != null ? serviceoptions.GetSqlQueryTop() : string.Empty) +
                    " {Place.*} FROM {Place} INNER JOIN #pl" +
                    " ON #pl.Id = Place.Place_Id" +
                    " ORDER BY [Score] DESC\n" +
                    "DROP TABLE #pl\n" +
                    "DROP TABLE #fav ")
                    .AddEntity("Place", typeof(Place));

                //if (serviceoptions != null)
                //{
                //    q.SetMaxResults(serviceoptions.PageSize);
                //    q.SetFirstResult(serviceoptions.FirstResult);
                //}

                IList<Place> list = WebServiceQueryOptions<Place>.Apply(serviceoptions, q.List<Place>());
                List<TransitPlace> result = new List<TransitPlace>(list.Count);

                ManagedSecurityContext sec = new ManagedSecurityContext(session, ticket);
                foreach (Place p in list)
                {
                    result.Add(new ManagedPlace(session, p).GetTransitInstance(sec));
                }

                return result;
            }
        }

        protected IList<Place> InternalSearchPlaces(ISession session, string s, ServiceQueryOptions options)
        {
            // search for a place matching the name exactly
            IList<Place> places = session.CreateCriteria(typeof(Place))
                .Add(Expression.Eq("Name", Renderer.SqlEncode(s)))
                .List<Place>();

            // search for everything else
            if (places.Count == 0)
            {

                int maxsearchresults = ManagedConfiguration.GetValue(session, "SnCore.MaxSearchResults", 128);
                ISQLQuery query = session.CreateSQLQuery(

                        "CREATE TABLE #Results ( Place_Id int, RANK int )\n" +
                        "CREATE TABLE #Unique_Results ( Place_Id int, RANK int )\n" +

                        "INSERT #Results\n" +
                        "SELECT place.Place_Id, ft.[RANK] FROM Place place\n" +
                        "INNER JOIN FREETEXTTABLE (Place, ([Name], [Street], [Zip], [CrossStreet], [Description], [Phone], [Fax], [Email]), '" +
                            Renderer.SqlEncode(s) + "', " +
                            maxsearchresults.ToString() + ") AS ft ON place.Place_Id = ft.[KEY]\n" +

                        "INSERT #Results\n" +
                        "SELECT place.Place_Id, ft.[RANK] FROM Place place, PlaceName placename\n" +
                        "INNER JOIN FREETEXTTABLE (PlaceName, ([Name]), '" + Renderer.SqlEncode(s) + "', " +
                            maxsearchresults.ToString() + ") AS ft ON placename.PlaceName_Id = ft.[KEY] \n" +
                        "WHERE placename.Place_Id = place.Place_Id\n" +

                        "INSERT #Results\n" +
                        "SELECT place.Place_Id, ft.[RANK] FROM Place place, PlacePropertyValue placepropertyvalue\n" +
                        "INNER JOIN FREETEXTTABLE (PlacePropertyValue, ([Value]), '" + Renderer.SqlEncode(s) + "', " +
                            maxsearchresults.ToString() + ") AS ft ON placepropertyvalue.PlacePropertyValue_Id = ft.[KEY] \n" +
                        "WHERE placepropertyvalue.Place_Id = place.Place_Id\n" +

                        "INSERT #Results\n" +
                        "SELECT place.Place_Id, 0 FROM Place place\n" +
                        "INNER JOIN City city ON place.City_Id = city.City_Id " +
                        "WHERE City.Name = '" + Renderer.SqlEncode(s) + "'\n" +

                        "INSERT #Results\n" +
                        "SELECT place.Place_Id, 0 FROM Place place\n" +
                        "INNER JOIN Neighborhood neighborhood ON place.Neighborhood_Id = neighborhood.Neighborhood_Id " +
                        "WHERE Neighborhood.Name = '" + Renderer.SqlEncode(s) + "'\n" +

                        "INSERT #Unique_Results\n" +
                        "SELECT DISTINCT Place_Id, SUM(RANK)\n" +
                        "FROM #Results GROUP BY Place_Id\n" +
                        "ORDER BY SUM(RANK) DESC\n" +

                        "SELECT " + (options != null ? options.GetSqlQueryTop() : string.Empty) +
                        "{Place.*} FROM {Place}, #Unique_Results\n" +
                        "WHERE Place.Place_Id = #Unique_Results.Place_Id\n" +
                        "ORDER BY #Unique_Results.RANK DESC\n" +

                        "DROP TABLE #Results\n" +
                        "DROP TABLE #Unique_Results")
                        .AddEntity("Place", typeof(Place));

                places = query.List<Place>();
            }

            return WebServiceQueryOptions<Place>.Apply(options, places);
        }

        protected IList<Account> InternalSearchAccounts(ISession session, string s, ServiceQueryOptions options)
        {
            // search for an account matching the name exactly
            IList<Account> accounts = session.CreateCriteria(typeof(Account)).Add(
                Expression.Or(
                    Expression.Eq("Name", Renderer.SqlEncode(s)),
                    Expression.Eq("City", Renderer.SqlEncode(s))
                )).List<Account>();

            // search for everything else
            if (accounts.Count == 0)
            {
                int maxsearchresults = ManagedConfiguration.GetValue(session, "SnCore.MaxSearchResults", 128);
                ISQLQuery query = session.CreateSQLQuery(

                        "CREATE TABLE #Results ( Account_Id int, RANK int )\n" +
                        "CREATE TABLE #Unique_Results ( Account_Id int, RANK int )\n" +

                        "INSERT #Results\n" +
                        "SELECT account.Account_Id, ft.[RANK] FROM Account account\n" +
                        "INNER JOIN FREETEXTTABLE (Account, [Name], '" + Renderer.SqlEncode(s) + "', " +
                            maxsearchresults.ToString() + ") AS ft ON account.Account_Id = ft.[KEY]\n" +

                        //"INSERT #Results\n" +
                    //"SELECT account.Account_Id, ft.[RANK] FROM Account account, AccountSurveyAnswer accountsurveyanswer\n" +
                    //"INNER JOIN FREETEXTTABLE (AccountSurveyAnswer, ([Answer]), '" + Renderer.SqlEncode(s) + "', " +
                    //    maxsearchresults.ToString() + ") AS ft ON accountsurveyanswer.AccountSurveyAnswer_Id = ft.[KEY] \n" +
                    //"WHERE accountsurveyanswer.Account_Id = account.Account_Id\n" +

                        "INSERT #Results\n" +
                        "SELECT account.Account_Id, ft.[RANK] FROM Account account, AccountPropertyValue accountpropertyvalue\n" +
                        "INNER JOIN FREETEXTTABLE (AccountPropertyValue, ([Value]), '" + Renderer.SqlEncode(s) + "', " +
                            maxsearchresults.ToString() + ") AS ft ON accountpropertyvalue.AccountPropertyValue_Id = ft.[KEY] \n" +
                        "WHERE accountpropertyvalue.Account_Id = account.Account_Id\n" +

                        "INSERT #Unique_Results\n" +
                        "SELECT DISTINCT Account_Id, SUM(RANK)\n" +
                        "FROM #Results GROUP BY Account_Id\n" +
                        "ORDER BY SUM(RANK) DESC\n" +

                        "SELECT " + (options != null ? options.GetSqlQueryTop() : string.Empty) +
                        "{Account.*} FROM {Account}, #Unique_Results\n" +
                        "WHERE Account.Account_Id = #Unique_Results.Account_Id\n" +
                        "ORDER BY #Unique_Results.RANK DESC\n" +

                        "DROP TABLE #Results\n" +
                        "DROP TABLE #Unique_Results\n")
                        .AddEntity("Account", typeof(Account));

                accounts = query.List<Account>();
            }

            return WebServiceQueryOptions<Account>.Apply(options, accounts);
        }