Ahmed Rabie El Bohoty..... IT : Share Info..Share Help

public int CountEmployees()
{
// Create the Command and the Connection.
string sql = "SELECT COUNT(*) FROM Employees";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
con.Open();
// Execute the command and use the return value for the
// VirtualItemCount property.
int i = (int)cmd.ExecuteScalar();
con.Close();
return i;
}
      ملحوظة
لو انا كنت شغال فى نفس ال class يعنى مكنتش عامل data Acess layer ممكن استخدم ال VirtualItemCount property.
 حيث تقوم بارجاع عدد ال Records
     public int CountEmployees()
        {
            string sql = "SELECT COUNT(*) FROM Employees";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            con.Open();
            // Execute the command and use the return value for the
            // VirtualItemCount property.
            Datagrid1.VirtualItemCount = (int)cmd.ExecuteScalar();
            con.Close();
        }
                    
This example uses the COUNT () aggregate function to calculate the number of records in the table and returns that information using the ExecuteScalar() method of the Command object.


Note in web Confg file add

         providerName="System.Datas.SqlClient" />
  

Third Step : A Stored Procedure to Get Paged Records
ü      The next part of the solution is a little trickier (حل بخدعة). Instead of retrieving a collection with all the employee records, the GetStudent () method must retrieve records for the current page only.

ü      To accomplish this feat, this example uses a stored procedure named GetStudentPage. This stored procedure copies all the employee records into a temporary table that has one additional column—a unique auto incrementing ID that will number each row. Next, the stored procedure retrieves a selection from that table that corresponds to the requested page of data, 7using the supplied @Start and @Count parameters.

ALTER PROCEDURE GetStudentPage
@Start int, @Count int
AS
-- create a temporary table with the columns we are interested in
--Colums Names For Temp Table Must be Like Colums Names in The acutal DataBase.
CREATE TABLE #TempStudents
(
ID int IDENTITY PRIMARY KEY,
s_id int,
s_name nvarchar(20),
s_age int,
)
-- fill the temp table with all the employees
INSERT INTO #TempStudents
(
s_id,s_name,s_age
)
SELECT
s_id,s_name,s_age
FROM
student ORDER BY s_id ASC
-- declare two variables to calculate the range of records
-- to extract for the specified page
DECLARE @FromID int
DECLARE @ToID int
-- calculate the first and last ID of the range of records we need
SET @FromID = @Start
SET @ToID = @Start + @Count - 1
-- select the page of records
SELECT * FROM #TempStudents WHERE ID >= @FromID AND ID <= @ToID


Fourth Step to finalize the custom paging:
Ω The Paged Selection Method Ω
The final step is to create an overload of the GetEmployees () method that performs paging. This method receives two arguments—the index of the row that starts the page (starting at 0) and the page size (maximum number of rows). You specify the parameter names you want to use for these two details through the StartRowIndexParameterName and MaximumRowsParameterName properties.
If not set, the defaults are startRowIndex and maximumRows.

public ArrayList GetStudents(int startRowIndex, int maximumRows)
    {
        ArrayList all = new ArrayList();
        SqlCommand cmd = new SqlCommand("GetEmployeePage", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.Int, 4));
        cmd.Parameters["@Start"].Value = startRowIndex + 1;
        cmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int, 4));
        cmd.Parameters["@Count"].Value = maximumRows;

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Studet s = new Studet((int)dr[0], (string)dr[2], (int)dr[1]);
            all.Add(s);
        }
        con.Close();
        return all;
    }
Note:
When you run this page, you’ll see that the output is the same as the output generated by the previous page using automatic pagination, and the pager controls work the same way.