WinForm DataGridView分頁功能

標籤：des   winform   datagridview   分頁控制項   blog   http   使用   os   

WinForm 裡面的DataGridView不像WebForm裡面的GridView那樣有內建的分頁功能，需要自己寫代碼來實現分頁，效果如： 分頁控制項 

 

.CS:

  1 using System;  2 using System.Collections.Generic;  3 using System.ComponentModel;  4 using System.Data;  5 using System.Drawing;  6 using System.Linq;  7 using System.Text;  8 using System.Windows.Forms;  9 using System.Data.SqlClient; 10  11 namespace allenPageTest 12 { 13     public partial class Form2 : Form 14     { 15         public Form2() 16         { 17             InitializeComponent(); 18         } 19  20         private void Form2_Load(object sender, EventArgs e) 21         { 22             BindDataWithPage(1); 23         } 24  25         //總記錄數 26         public int RecordCount = 0; 27         private string strConn = @"server=.;database=test;uid=sa;pwd=1234";          28         private string strProcedure = "PageTest "; 29  30  31         /// <summary> 32         /// 綁定第Index頁的資料 33         /// </summary> 34         /// <param name="Index"></param> 35         private void BindDataWithPage(int Index) 36         { 37             allenPage1.PageIndex = Index; 38             //winFormPager1.PageSize = 10; 39             DataTable dt = GetData(strConn, strProcedure, Index, allenPage1.PageSize); 40  41             dataGridView1.DataSource = dt; 42  43             //擷取並設定總記錄數 44             allenPage1.RecordCount = RecordCount; 45         } 46  47  48         /// <summary> 49         /// 擷取資料來源 50         /// </summary> 51         /// <param name="conn">連線物件</param> 52         /// <param name="strProcedure">預存程序名稱</param> 53         /// <param name="pageIndex">頁碼</param> 54         /// <param name="pageSize">每一頁顯示的行數</param> 55         /// <returns></returns> 56         private DataTable GetData(string conn, string strProcedure, int pageIndex, int pageSize) 57         { 58  59             using (SqlConnection connection = new SqlConnection(conn)) 60             { 61                 SqlCommand command = new SqlCommand(strProcedure, connection); 62                 command.CommandType = CommandType.StoredProcedure;//採用預存程序                  63                 command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = "TableName";//對應的資料表名 64                 command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "Index";//主鍵ID 65                 command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = "*";//要查詢的欄位，*為全部欄位 66                 command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = " 1=1 ";//查詢條件 67                 command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();//當前頁碼 68                 command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();//每一頁顯示的行數 69                 command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = " Column Name asc";//排序的欄位 70                 //開啟串連 71                 if (connection.State != ConnectionState.Open)  72                 {  73                     connection.Open();  74                 } 75                 try 76                 { 77                     //填充資料 78                     SqlDataAdapter da = new SqlDataAdapter(command); 79                     DataSet ds = new DataSet(); 80                     da.Fill(ds); 81                     //擷取總記錄數 82                     RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]); 83                     //返回資料集 84                     return ds.Tables[0]; 85  86                 } 87                 catch (SqlException err) 88                 { 89                     MessageBox.Show(err.Message); 90                     return null; ; 91                 } 92                 finally 93                 { 94                     connection.Close(); 95                 } 96             } 97         } 98  99         private void allenPage1_PageIndexChanged(object sender, EventArgs e)100         {101             BindDataWithPage(allenPage1.PageIndex);102         }103     }104 }

預存程序：

 1 IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME=‘PageTest‘) 2 DROP PROC PageTest 3 GO 4 CREATE PROCEDURE [dbo].[PageTest] 5     @Table VARCHAR(1000), --表名,多表是請使用 tA a inner join tB b On a.AID = b.AID 6     @TIndex NVARCHAR(100),    --主鍵  7     @Column NVARCHAR(2000) = ‘*‘,--要查詢的欄位，全部欄位就為* 8     @Sql NVARCHAR(3000) = ‘‘,--Where條件 9     @PageIndex INT = 1,    --開始頁碼10     @PageSize INT = 10,        --每頁查詢資料的行數11     @Sort NVARCHAR(200) = ‘‘ --排序的欄位12 13 AS14 15  16 17 DECLARE @strWhere VARCHAR(2000)18 DECLARE @strsql NVARCHAR(3900)19 IF @Sql IS NOT NULL AND len(LTRIM(RTRIM(@Sql)))>020   BEGIN21    SET @strWhere = ‘ WHERE ‘ + @Sql + ‘ ‘22   END23 ELSE24   BEGIN25    SET @strWhere = ‘‘26   END27         28 IF (charindex(LTRIM(RTRIM(@TIndex)),@Sort)=0)29 BEGIN30     IF(@Sort=‘‘)31         SET @Sort = @TIndex + ‘ DESC ‘32     ELSE33         SET @Sort = @Sort+ ‘ , ‘[email protected] + ‘ DESC ‘34 END35 IF @PageIndex < 136   SET @PageIndex = 137 38         IF @PageIndex = 1  39         BEGIN 40           SET @strsql = ‘SELECT TOP ‘ + str(@PageSize) +‘ ‘[email protected]+ ‘  FROM ‘ + @Table + ‘ ‘ + @strWhere + ‘ ORDER BY  ‘+ @Sort41         END 42         ELSE43           BEGIN44          45             DECLARE @START_ID NVARCHAR(50)46             DECLARE @END_ID NVARCHAR(50)47             SET @START_ID = convert(NVARCHAR(50),(@PageIndex - 1) * @PageSize + 1)48             SET @END_ID = convert(NVARCHAR(50),@PageIndex * @PageSize)49             SET @strsql =  ‘ SELECT ‘[email protected]+ ‘50            FROM (SELECT ROW_NUMBER() OVER(ORDER BY ‘[email protected]+‘) AS RowNum, 51              ‘[email protected]+ ‘52               FROM ‘[email protected] +‘ WITH(NOLOCK) ‘ + @strWhere +‘) AS D53            WHERE RowNum BETWEEN ‘[email protected]_ID+‘ AND ‘ [email protected]_ID +‘ ORDER BY ‘[email protected]54           END55 EXEC(@strsql)56 PRINT @strsql57     SET @strsql = ‘SELECT  Count(1) as TotalRecords FROM ‘ + @Table +‘ WITH(NOLOCK) ‘ + @strWhere  58 PRINT @strsql59 EXEC(@strsql)

還有一種預存程序寫法，僅供參考：

 1 CREATE PROCEDURE pro_DataPageRowNumber 2 @SQL nvarchar(2000),--主句 3 @Order nvarchar(20),--排序 4 @PageIndex int,--當前頁 5 @PageSize int,--每頁顯示數 6 @TotalRow int output--記錄總數 7 AS 8     SET NOCOUNT ON; 9     declare @ExceSQL nvarchar(4000)--主句10     declare @startRow as int--開始行11     set @startRow=(@PageIndex-1)*@PageSize+112     declare @lastRow int--結束行13     set @[email protected]*@PageIndex14     declare @RowNumber nvarchar(100)15     set @RowNumber=‘,Row_NUMBER() OVER(ORDER BY ‘[email protected]+‘) as RowNumber from ‘16     set @SQL=Replace(@SQL,‘ from ‘,@RowNumber)   17     set @ExceSQL=‘select @TotalRow=max(RowNumber) from (‘[email protected]+‘) as tmp‘    18     execute  sp_executesql @ExceSQl,N‘@TotalRow in output‘,@TotalRow output19     set @ExceSQL=‘select * from(‘[email protected]+‘) as tmp where RowNumber  between‘+ Convert(nvarchar,@startRow)20     +‘ and ‘+Convert(nvarchar,@lastRow)21     execute(@ExceSQL)22 23 GO

分頁的控制項是自己寫的一個使用者控制項，產生之後是一個DLL檔案，直接引用在項目裡面即可，有需要的可以留下郵箱。