Limiting resultset using ContactRows.CustomSearch


Hello, just wondering if it is possible to limit a search to say, top 50 hits or even better, get a subset
with a startpoint and number of rows to return.

The code we are currently using is:



(Sorry for this perhaps basic question, but I'm not really familiar with DevNet, I usually go through our local SuperOffice man for that, but he is currently unavailable)

thanks for your time

best regards
Tom Hellström

Re: Limiting resultset using ContactRows.CustomSearch

Hi Tom,

There is no way to optimize that search using Row, Entities, or Services - your stuck having to write OSQL. The magic is in the ExecuteReader parameters.

        private void SelectTopContacts(int topCount, string startsWith)

        {

            int contactId = 0;

            ContactTableInfo contactTable = TablesInfo.GetContactTableInfo();

            Select select = S.NewSelect();

            SoCommand command = null;

            SoConnection conn = null;

            SoDataReader reader = null;

 

 

            select.ReturnFields.Add(contactTable.ContactId);

            select.Restriction = contactTable.Name.Like(startsWith + "%");

            try

            {

 

                using (conn = ConnectionFactory.GetConnection())

                {

                    using (command = conn.CreateCommand())

                    {

                        command.SqlCommand = select;

 

                        conn.Open();

 

                        //uses paging - every page consists of topCountN

                        //entries and we only want the first page

                        using (reader = command.ExecuteReader(topCount, 1))

                        {

                            while (reader.Read())

                            {

                                //do stuff

                                contactId = (int)reader[contactTable.ContactId];

                            }

                        }

                    }

                }

            }

            catch (Exception)

            {

                //errormessage

            }

        }




Hope this helps.
Von: Tony Yates 18. Feb 2009

Re: Limiting resultset using ContactRows.CustomSearch


Thanks for the code, works very well!


cheers
///. Tom H

Von: Tom Hellström 6. Mrz 2009

RE: Limiting resultset using ContactRows.CustomSearch

Ancient code, but I needed this while ago. Additional note though, page numbering starts from zero.

Von: Mikko Tillikainen 1. Mrz 2021