Limit the records

Question

I have this function that display records from the database to flowlayoutpanel

flowLayoutPanel1.Controls.Clear();
        using (SqlConnection myDatabaseConnection = new SqlConnection(myConnectionString.ConnectionString))
        {
            myDatabaseConnection.Open();
            string a = "Select EmpID, Name from EmpTable";
            using (SqlCommand SqlCommand = new SqlCommand(" "+ a +" ", myDatabaseConnection))
            {
                int i = 0;
                SqlDataReader DR1 = SqlCommand.ExecuteReader();
                while (DR1.Read())
                {
                    i++;
                    BookUserControl usercontrol = new BookUserControl();
                    usercontrol.Tag = i;
                    usercontrol.EmpID = DR1["EmpID"].ToString();
                    usercontrol.Name = (string)DR1["Name"];
                    flowLayoutPanel1.Controls.Add(usercontrol);
                }
            }
        }

How i will limit the number of records that will be display in the flowlayoutpanel? I know there is select top . But how I will do this, for example a number of 10 records will be display and when a nextbutton is click the next 10 records will be display and when a previousbutton is click the previous 10 records will be display.

@SquarePonge - My answer did not work? I am curious, let me know. — unlimit, Jul 25 '13 at 10:23

unlimit · Accepted Answer · 2013-07-25T07:16:12.050

In SQL Server 2008, you can use Row_Number():

Select * from    
(Select row_number() over (order by EmpID) r,  EmpID, Name from EmpTable) X
where X.r between @start and @end

Supply the values of @start and @end and you are all set. You may or may not display the value of r. To show from 1-10, set @start = 1 and @end = 10 and so on and so forth. In this query the data is ordered on EmpID.

Modified code:

string a = "Select * from (Select row_number() over (order by EmpID) r,  EmpID, Name from EmpTable) X where X.r between @start and @end";
        using (SqlCommand SqlCommand = new SqlCommand(" "+ a +" ", myDatabaseConnection))
        {
            SqlCommand.Parameters.Add("@start").Value = 1;
            SqlCommand.Parameters.Add("@end").Value = 10;
            int i = 0;
            SqlDataReader DR1 = SqlCommand.ExecuteReader();
            while (DR1.Read())
            {
                i++;
                BookUserControl usercontrol = new BookUserControl();
                usercontrol.Tag = i;
                usercontrol.EmpID = DR1["EmpID"].ToString();
                usercontrol.Name = (string)DR1["Name"];
                flowLayoutPanel1.Controls.Add(usercontrol);
            }
        }

See http://msdn.microsoft.com/en-us/library/ms186734(v=sql.90).aspx

score 0 · Answer 2 · answered Jul 25 '13 at 06:58

0

yes there is SELECT TOP in SQl

use it like this

Select TOP(10) EmpID, Name from EmpTable

i am updating your cod

flowLayoutPanel1.Controls.Clear();
        using (SqlConnection myDatabaseConnection = new SqlConnection(myConnectionString.ConnectionString))
        {
            myDatabaseConnection.Open();
            string a = "SELECT TOP(10) EmpID, Name FROM EmpTable";
            using (SqlCommand SqlCommand = new SqlCommand(" "+ a +" ", myDatabaseConnection))
            {
                int i = 0;
                SqlDataReader DR1 = SqlCommand.ExecuteReader();
                while (DR1.Read())
                {
                    i++;
                    BookUserControl usercontrol = new BookUserControl();
                    usercontrol.Tag = i;
                    usercontrol.EmpID = DR1["EmpID"].ToString();
                    usercontrol.Name = (string)DR1["Name"];
                    flowLayoutPanel1.Controls.Add(usercontrol);
                }
            }
        }

now use this cod and this will work exactly you want

best of luck

answered Jul 25 '13 at 06:58

sourabh devpura

605
8
25

How about the next top 10? previous top 10? – Square Ponge Jul 25 '13 at 07:03
That won't work! That will just repeat the same top 10 when he clicks next. What he's after is SQL paging or perhaps load all the results into a List<> then use .Take(10) etc. – Dr Schizo Jul 25 '13 at 07:08
2

this is the anser only for the top(n) record – sourabh devpura Jul 25 '13 at 07:11
1

if you want to create a system like NEXT and PREVIOUS – sourabh devpura Jul 25 '13 at 07:12
1

i am posting a new answer for this – sourabh devpura Jul 25 '13 at 07:13
What you proposed won't work for a next and previous as you are not keeping track of row number? You need to define it as a stored procedure (most likely) where you pass it current row_number() so the procedure knows what rows to return next. Yours will return the first 10 rows all the time. – Dr Schizo Jul 25 '13 at 07:14

score 0 · Answer 3 · answered Jul 25 '13 at 07:14

You the most elegant is to use the ROW_NUMBER function

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To

best of luck

Limit the records

3 Answers3