WinForm數據源分頁技術

1、編寫分頁存儲過程

USE [Contacts]
GO

create procedure [dbo].[GetPageData]
(@startIndex int,
@endIndex int
)
as
begin
with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY contact.id )AS Row, contact.id,name,phone,email,groupname from contact inner join contactgroup on contact.groupid=contactgroup.id )
SELECT * FROM temptbl where Row between @startIndex and @endIndex
end
GO

2、設計窗體

3、編寫代碼

public partial class FormPaging : Form
{

//每頁顯示的記錄數
int pageSize = 3;

//當前頁碼
int page=1;

public FormPaging()
{
InitializeComponent();
}

//獲取總的記錄數
int GetRecordCount()
{
string sql = "select count(*) from contact";
return Convert.ToInt32(SqlDbHelper.ExecuteScalar(sql));
}
void Fill(int page)
{
string sql = "GetPageData";//分頁存儲過程名稱
int startIndex = (page - 1) * pageSize + 1;
int endIndex = page * pageSize;
SqlParameter[] sp ={
new SqlParameter("@startIndex",startIndex),
new SqlParameter("@endIndex", endIndex)
};
DataTable dt = SqlDbHelper.ExecuteDataTable(sql, CommandType.StoredProcedure, sp);
dgvContactList.DataSource = dt;
}
private void FormPaging_Load(object sender, EventArgs e)
{
BindCombox();
Fill(page);
}

//用頁數填充下拉框

private void BindCombox()
{
int total = GetRecordCount();

//計算總的頁數
int totalPage = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
for (int i = 1; i <=totalPage; i++)
{
comboBox1.Items.Add(i);
}
}

 //根據用戶選擇的當前頁碼，綁定DataGridView控制項

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
page = Convert.ToInt32(comboBox1.Text);
Fill(page);
}
}