CODEWITHSUNDEEP
c#sqlsql-server-2008ado.net
Square Ponge
Square Ponge

Reputation: 762

Limit the records

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.

Upvotes: 1

Views: 1202

Answers (3)

unlimit
unlimit

Reputation: 3752

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

Upvotes: 1

sourabh devpura
sourabh devpura

Reputation: 625

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

Upvotes: 0

sourabh devpura
sourabh devpura

Reputation: 625

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

Upvotes: 0

Related Questions