mvnForum - مشاهدة مشاركة - New solution in Gridview paging

مرحبا بكم Guest | دخول

قناة الجزيرة بالعربية * * * ِ Aljazeera (ُEnglish) * * * Iqaa TV قناة إقرأ * * * FM قناة القرآن الكريم * * * FM قناة السيره النبويه * * * إذاعة القران الكريم السعودية * * * FM قناة الأناشيد

يجب التسجيل ببيانات الاسم الحقيقية ولا يسمح باستخدم أسماء وهمية أو مستعاره .. كما يجب وضع الصورة الشخصية للأعضاء الذكور وتعفى الأعضاء الإناث من وضع الصورة فقط -يجب توفيق الأوضاع الأعضاء الحاليين قبل الأول من أكتوبر 2010 - إقرأ قوانين المنتدى على هذا الرابط

mvnForum » قائمة المنتديات » منتدى: Games Programming Forum » مشاركة: New solution in Gridview paging

انتقــال سريع »

حالة المشاركة: مفتوحة
مجموع الردود في هذا المشاركة: 2

[إضافة إلى لمفضلة] [شاهد هذه المشاركة] [موضوع جديد]

الكاتب

تمت مشاهدة هذه المشاركة 268 مرات وتم 1 رد

hayssam_said
Member

Egypt
تاريخ الاشتراك: 27/11/2006
عدد الردود: 72
حالة الإيصال: غير متصل

New solution in Gridview paging

إضافة رد
رد مع اقتباس

السلام عليكم ورحمة الله و بركاته
من فضل الله . ربنا من عليه بحل لموضوع paging
انا جربت الحل ده علي تيبل فيه 61.000 record
ووجدت ان مفيس اي مشكله في الجرد و كمان السرعه اكبر بكتير جداً جداً
storedProcedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[Test_GetClientsByPage]
@SelectedID as int,
@MoveState as nvarchar (5) ,@TotalRecords as int Output
as
--Set no count on

set nocount on
--Determin which move
IF @MoveState='First'
Select top 5 * From BookChapters
else if @MoveState = 'Prv'
select * from (Select top 5 *
From
BookChapters
Where
BookChapter_ID < @SelectedID order by BookChapter_ID desc)
as BookChapters order by BookChapter_ID
else if @MoveState='Last'
select * from (
Select top 5 *
From
BookChapters
Order by BookChapter_ID desc) as BookChapters order by BookChapter_ID
else if @MoveState='Next'
Select top 5 *
From
BookChapters
Where
BookChapter_ID > @SelectedID

--Return the total number of records available as an output parameter
Select
@TotalRecords = Count(*)
From
BookChapters

* Code in page

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{

// Declare local variables.
int CurrentPage;
int totalRecords;
int totalPages;
int SelectedID;
string MoveState;
private void HandleMoveButtons(params bool[] TorF)
{
btnFirst.Enabled =TorF[0];
BtnPrv.Enabled = TorF[1];
BtnNext.Enabled = TorF[2];
btnLast.Enabled= TorF[3];
}
//public enum MoveState
//{
// First ,
// Last ,
// Prv ,
// Next
//}
//MoveState D = MoveState.First;

private void GetData()
{

#region Declare local data members

SqlConnection connClients = new SqlConnection();
SqlCommand cmdClients = new SqlCommand();
SqlDataAdapter Da = new SqlDataAdapter();
SqlParameter parmClients;
SqlDataReader drClients;
DataTable dtClients = new DataTable();
DataRow drClient;

#endregion Declare local data members

#region Build and bind the data source

// Define the DataTable.
dtClients.Columns.Add("BookChapter_ID", System.Type.GetType("System.Int32"));
dtClients.Columns.Add("Book_ID", System.Type.GetType("System.Int32"));
dtClients.Columns.Add("ChapterTitle", System.Type.GetType("System.String"));

// Create a connection to the database.
try
{

// Connect using the connection string in the web.config.
connClients.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Mubark2005ConnectionString"].ConnectionString.ToString();
connClients.Open();

// Configure the command object.
//Test_GetClientsByPage
cmdClients.CommandText = "Test_GetClientsByPage";
cmdClients.CommandType = CommandType.StoredProcedure;
cmdClients.Connection = connClients;

parmClients = new SqlParameter("@MoveState", SqlDbType.NVarChar);
parmClients.Direction = ParameterDirection.Input;
parmClients.Value = MoveState;
//MoveState D = MoveState.Next;
cmdClients.Parameters.Add(parmClients);

parmClients = new SqlParameter("@SelectedID", SqlDbType.Int);
parmClients.Direction = ParameterDirection.Input;
if (!Page.IsPostBack)
{
parmClients.Value = 1;
}
else
{
//parmClients.Value = dgClients.Rows[4].Cells[0].Text;
//SelectedID
parmClients.Value = SelectedID;

}
cmdClients.Parameters.Add(parmClients);

// Create the total rows parameter.
parmClients = new SqlParameter("@TotalRecords", SqlDbType.Int);
parmClients.Direction = ParameterDirection.Output;
cmdClients.Parameters.Add(parmClients);

// Process the command.
// It should return to us the client rows into a DataReader.
// There will also be an output parameter of the total number of clients in the db.
// We need to close the DataReader prior to retrieving the output parameter.

//Da.SelectCommand = cmdClients;
//Da.Fill(dtClients);

drClients = cmdClients.ExecuteReader();

//Iterate through the DataReader.
while (drClients.Read())
{

// Create a new DataRow to populate.
drClient = dtClients.NewRow();

// Populate the DataRow.
drClient["BookChapter_ID"] = drClients["BookChapter_ID"];
drClient["Book_ID"] = drClients["Book_ID"];
drClient["ChapterTitle"] = drClients["ChapterTitle"];

// Add the DataRow.
dtClients.Rows.Add(drClient);
}

//Close the DataReader.
drClients.Close();

// Retrieve the output parameter.
totalRecords = (int)cmdClients.Parameters["@TotalRecords"].Value;

// Bind the DataGrid.
dgClients.DataSource = dtClients;
dgClients.DataBind();

// Configure the footer.
//lblCurrentPage.Text = dgClients.PageIndex.ToString();

if ((totalRecords % 5) == 0)
{
totalPages = totalRecords / 5;
}
else
{

totalPages = (totalRecords / 5) + 1;
}

//Hndel MoveButtons =================
//HandleMoveButtons(false, false, true, true);
switch (MoveState)
{
//case "First":
// HandleMoveButtons(false, false, true, true);
// break;
case "Last":
HandleMoveButtons(true, true, false, false);
dgClients.PageIndex = totalPages;
break;
case "Next":
HandleMoveButtons(true, true, true, true);
break;
case "Prv":
HandleMoveButtons(true, true, true, true);
break;

}
if (dgClients.PageIndex == 1)
{
HandleMoveButtons(false, false, true, true);
}
if (totalPages == dgClients.PageIndex)
{
HandleMoveButtons(true, true, false, false);
}

//===================================
LblCount.Text = "( Page Num " + dgClients.PageIndex + " of " + totalPages.ToString() + ")";
}

catch (Exception ex)
{

// Nothing here for now.
}

finally
{

// Close and dispose of the database connection.
cmdClients.Dispose();
connClients.Close();
connClients.Dispose();
}

#endregion Build and bind the data source
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
MoveState = "First";
dgClients.PageIndex = 1;
CurrentPage = 1;
GetData();

}

}
protected void BtnNext_Click(object sender, EventArgs e)
{
dgClients.PageIndex += 1;
SelectedID = int.Parse(dgClients.Rows[4].Cells[0].Text);
MoveState = "Next";
GetData();
}
protected void btnFirst_Click(object sender, EventArgs e)
{
dgClients.PageIndex = 1;
MoveState = "First";
GetData();
}
protected void btnLast_Click(object sender, EventArgs e)
{
//dgClients.PageIndex = totalPages;
SelectedID = int.Parse(dgClients.Rows[4].Cells[0].Text);
MoveState = "Last";
GetData();

}
protected void BtnPrv_Click(object sender, EventArgs e)
{
dgClients.PageIndex -= 1;
//Previous
SelectedID = int.Parse(dgClients.Rows[0].Cells[0].Text);
MoveState = "Prv";
GetData();

//GetData();
}
}

[21/10/2008 08:00:39 ص]		[رابط]

anwer
Advanced Member

Egypt
تاريخ الاشتراك: 11/07/2006
عدد الردود: 166
حالة الإيصال: غير متصل

Re: New solution in Gridview paging

إضافة رد
رد مع اقتباس

You can also consider this solution


CREATE PROCEDURE [dbo].[GetUsers]
    @PageIndex      int,
    @PageSize       int
AS
BEGIN
    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId int
    )

    -- Insert into our temp table
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT UserId FROM	dbo.Users 

    SELECT  *
    FROM	dbo.Users u, 
			#PageIndexForUsers p
    WHERE  u.UserId = p.UserId
			AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    
    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers

END

----------------------------------------
ٌٌRegards,
Anwer Matter

[21/10/2008 11:33:44 م]		[رابط]

[مشاهدة إصدار الطباعة] [موضوع جديد]