none
Getting only a few records at a time.

    Question

  • Using a Select * from. Is there an option where I get only so many records. This is for a console application. I wanted some thing where it will display only 3 records at a time. And then I have the option of viewing the next 3 and then the next 3 records and so on.

    Any one have any ideas.

    Merry Christmas EVERY ONE and thanks to all the members on msdn forums for helping me with my code.

    Joe Moody

    http://www.starfiresoft.com


    Pro-Forums
    Delta Force Barracks
    On the Frontlines

    Wednesday, December 25, 2013 10:05 AM

Answers

  • Below is an example that should work with SQL CE without modifying the query.  The assumption is that the result set doesn't change and rows will be returned in the same order every time the query is executed.  I think these assumptions will hold true with a single-user SQL CE application.

    static void clientPaginationExample()
    {
    
        SqlCeCommand cm = new SqlCeCommand("SELECT * FROM users", cn);
    
        int recordOffset = 0;
        int rowsToDisplay = 3;
        string action = "N"; //N=next page, P=prev page, Q=quit
    
        while (action != "Q")
        {
    
            SqlCeDataReader rd = getPositionedReader(cm, recordOffset);
                    
            int rowsDisplayed = displayResults(rd, rowsToDisplay);
    
            action = Convert.ToString(Char.ToUpper(Door.ReadKey())); 
    
            if (action == "P")
            {
                recordOffset = recordOffset - rowsToDisplay > 0 ? recordOffset - rowsToDisplay : 0;
            }
            else if (action == "N")
            { 
                recordOffset = recordOffset + rowsDisplayed; 
            }
    
        }
    
        cn.Close();
    
    }
    
    static SqlCeDataReader getPositionedReader(SqlCeCommand cm, int recordOffset)
    {
    
        var reader = cm.ExecuteReader();
    
        for (int i = 0; i < recordOffset; ++i )
        {
            if (!reader.Read()) break;
        }
    
        return reader;
    
    }
    
    static int displayResults(SqlCeDataReader rd, int rowsToDisplay)
    {
    
        int rowsDisplayed = 0;
    
        while ((rowsDisplayed < rowsToDisplay) && (rd.Read()))
        {
            ++rowsDisplayed;
            Door.CursorRight(10);
            Door.WriteLn("|0C___________________________________________________");
            Door.CursorRight(10);
            Door.WriteLn("|0FId |04: |03" + rd["Id"].ToString() + " |0FName |04: |03" + rd["username"].ToString());
            Door.CursorRight(10);
            Door.WriteLn("|0FStatus |04: |03" + rd["status"] + "   |0FDate Jointed |04: |03" + rd["joindate"]);
            Door.CursorRight(10);
            Door.WriteLn("|0C___________________________________________________");
        }
    
        rd.Close();
    
        return rowsDisplayed;
    
    }


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Joesoft11a Thursday, December 26, 2013 6:15 PM
    Thursday, December 26, 2013 3:34 PM

All replies

  • With forward-only pagination in a connected console application, you could execute the query and display the desired number of rows in a loop, pausing for user response between each iteration.  This is the only method that doesn't require changing the SQL statement to handle pagination.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Wednesday, December 25, 2013 2:28 PM removed non-CE options
    Wednesday, December 25, 2013 2:10 PM
  • Please look for "Paging in SQL Server" there are many resources available for this on the internet.

    If you have SQL Server 2012 you can use the OFFSET..FETCH clause which look like this:
    SELECT * FROM MyTable ORDER BY MyOrderingColumn
    OFFSET 100 ROWS
    NEXT 50 ROWS

    The above statement displays rows 100 to 150.

    If your version is older than 2012 but is at least 2005 then you can use the ranking functions such as RANK() or ROW_NUMBER(). It's a bit more complicated but still relatively easy to do. For example:

    SELECT *
    FROM
    (SELECT *, ROW_NUMBER() OVER (ORDER BY MyOrderingColumn ASC) AS RowRank
     FROM MyTable
    ) AS tmp
    WHERE RowRank BETWEEN 100 AND 150

    Note that you must use a subquery to be able to access the calculated "RowRank" column.

    This is totally in a nutshell. Like I said, there's plenty of resources on this on the internet.


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    • Proposed as answer by ErikEJMVP, Moderator Thursday, December 26, 2013 12:15 PM
    • Unproposed as answer by Joesoft11a Thursday, December 26, 2013 3:21 PM
    • Proposed as answer by Tom Phillips Thursday, December 26, 2013 3:39 PM
    • Unproposed as answer by Tom Phillips Thursday, December 26, 2013 3:40 PM
    Wednesday, December 25, 2013 2:53 PM
  • My assumption is that Joesoft11a is using SQL CE since he posted to this forum.  Features such as ROW_NUMBER and FETCH...OFFSET are therefore not available.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, December 25, 2013 4:43 PM
  • Dan, Thank you. I didn't under stand why they posted the wrong information in the wrong forum. I'm clearly posted this is the SQL CE Forum. Any way Yes I know. Those commands don't and won't work. I asked about pagination and couldn't get any help with it. There has to be some option to use that will work.

    Joe M.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Wednesday, December 25, 2013 11:22 PM
  • Dan, Thank you. I didn't under stand why they posted the wrong information in the wrong forum. I'm clearly posted this is the SQL CE Forum.

    Many of us monitor multiple SQL forums and it's easy to overlook the context of the question.  I included options not available in CE in my initial response but quickly caught my mistake.

    There has to be some option to use that will work.

    If adding a WHERE clause to the query is an option, you can specify criteria greater than the last key displayed to get the next rows and then stop once the desired number of rows are displayed.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, December 26, 2013 1:17 AM
  • I'd just like to apologize for my reply earlier.

    Just like Joe said I monitored multiple SQL forums and didn't notice the context.

    As for your question, I'd like to ask: Is your ordering always based on the same column? Or can it change depending on user preferences?


    Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

    Thursday, December 26, 2013 6:59 AM
  • You can use OFFSET FETCH with SQL Server Compact 4.0

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, December 26, 2013 8:14 AM
    Moderator
  • Thanks. It doesn't matter as long as I can get it to work. The idea is to display 3 records at a time. Then the user should be able to go backward or forward in the list. Let me show you what I have. This will give you some idea on how I have it displaying the info.

     SqlCeCommand cm = new SqlCeCommand("SELECT * FROM users", cn);
                    //cm.Parameters.AddWithValue("@tooid", num);
                    //cm.Parameters.AddWithValue("@fromid", Id);
    
                    SqlCeDataReader rd = cm.ExecuteReader();
                    
                    try
                    {
                        while (rd.Read())
                        {
                                Door.CursorRight(10);
                                Door.WriteLn("|0C___________________________________________________");
                                Door.CursorRight(10);
                                Door.WriteLn("|0FId |04: |03" + rd["Id"].ToString() + " |0FName |04: |03" + rd["username"].ToString());
                                Door.CursorRight(10);
                                Door.WriteLn("|0FStatus |04: |03" + rd["status"] + "   |0FDate Jointed |04: |03" + rd["joindate"]);
                                Door.CursorRight(10);
                                Door.WriteLn("|0C___________________________________________________");                        
                        }
                    }
                    catch (Exception e)
                    {
                        throw new Exception(e.Message);
                    }
                    cn.Close();
    

    This is inside of a do loop. and at the bottom I have it waiting for user input. or the user can quit and return to the main menu.

    Door.WriteLn();
                    Door.Write("|07Page |0F<|04||0F> |0FQ|09)|0BQuit |07Choice |04: |0C");
                    cc = Char.ToUpper(Door.ReadKey());

    Above is my prompt. This waits for user input. and I use a switch to tell the software what to do. Any way if you want to know what Door.writeLn() is, Think of it like this "Console.Writeline()". This is just a library I use for the kind of programming I do.Any help would be great. Thanks and have a safe Xmas.

    Joe.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Thursday, December 26, 2013 8:42 AM
  • Below is an example that should work with SQL CE without modifying the query.  The assumption is that the result set doesn't change and rows will be returned in the same order every time the query is executed.  I think these assumptions will hold true with a single-user SQL CE application.

    static void clientPaginationExample()
    {
    
        SqlCeCommand cm = new SqlCeCommand("SELECT * FROM users", cn);
    
        int recordOffset = 0;
        int rowsToDisplay = 3;
        string action = "N"; //N=next page, P=prev page, Q=quit
    
        while (action != "Q")
        {
    
            SqlCeDataReader rd = getPositionedReader(cm, recordOffset);
                    
            int rowsDisplayed = displayResults(rd, rowsToDisplay);
    
            action = Convert.ToString(Char.ToUpper(Door.ReadKey())); 
    
            if (action == "P")
            {
                recordOffset = recordOffset - rowsToDisplay > 0 ? recordOffset - rowsToDisplay : 0;
            }
            else if (action == "N")
            { 
                recordOffset = recordOffset + rowsDisplayed; 
            }
    
        }
    
        cn.Close();
    
    }
    
    static SqlCeDataReader getPositionedReader(SqlCeCommand cm, int recordOffset)
    {
    
        var reader = cm.ExecuteReader();
    
        for (int i = 0; i < recordOffset; ++i )
        {
            if (!reader.Read()) break;
        }
    
        return reader;
    
    }
    
    static int displayResults(SqlCeDataReader rd, int rowsToDisplay)
    {
    
        int rowsDisplayed = 0;
    
        while ((rowsDisplayed < rowsToDisplay) && (rd.Read()))
        {
            ++rowsDisplayed;
            Door.CursorRight(10);
            Door.WriteLn("|0C___________________________________________________");
            Door.CursorRight(10);
            Door.WriteLn("|0FId |04: |03" + rd["Id"].ToString() + " |0FName |04: |03" + rd["username"].ToString());
            Door.CursorRight(10);
            Door.WriteLn("|0FStatus |04: |03" + rd["status"] + "   |0FDate Jointed |04: |03" + rd["joindate"]);
            Door.CursorRight(10);
            Door.WriteLn("|0C___________________________________________________");
        }
    
        rd.Close();
    
        return rowsDisplayed;
    
    }


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Joesoft11a Thursday, December 26, 2013 6:15 PM
    Thursday, December 26, 2013 3:34 PM
  • Tom, Thanks for the info. How ever SQL CE 4.0 only works on ASP.NET and not in applications. Some day it will be. not for programming. Only SQL CE 3.5 is the only database that's will work in console and windows form.

    Joe M


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Thursday, December 26, 2013 6:18 PM
  • Sql ce 4 Works just fine in desktop applications, both console and WPF/Winforms

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, December 26, 2013 6:25 PM
    Moderator
  • Sql ce 4 Works just fine in desktop applications, both console and WPF/Winforms

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thanks but, Sorry I did try that and I read that SQL CE 4.0 was not available for console or windows applications. Unless they updated it and I never got the update. If you do a search in this forum and in the C# forum. You will find the results. All so do a search on google all so. Let me try making a new project and see if I can add SQL CE 4.0 to it.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Thursday, December 26, 2013 7:27 PM
  • Sql ce 4 Works just fine in desktop applications, both console and WPF/Winforms

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Any way I tried adding a new data connection to a project and as you can see from the image below that 4.0 is not available. and I do have SQL CE 4.0 installed. I was told it was only for ASP.NET. and that was it.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Thursday, December 26, 2013 7:40 PM
  • Only three Words: Visual Studio 2012

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, December 26, 2013 7:56 PM
    Moderator
  • Tom: what Joe is pointing out is actually the case for VS 2010, even with SP1 installed, but not for VS 2012

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, December 26, 2013 8:21 PM
    Moderator
  • I didn't know that.  Thanks for the information.

    Thursday, December 26, 2013 9:06 PM
  • Sorry Tom, Maybe I didn't explain that right. Yes it doesn't work for VS 2010. and I wish I could run it. How ever it's only for Windows 7 or above and I only have Windows Vista. I hope some day I can get a better PC with Windows 7 or 8 on it. More then likely not until the middle of next year.


    http://www.df-barracks.com Delta Force Barracks
    http://www.starfiresoft.com Starfire Software

    Thursday, December 26, 2013 9:15 PM