数据库存储过程 分页与SQL调用
CREATE PROCEDURE [dbo].[Pagination]
@Columns VARCHAR(500), -- The columns to be displayed, divide by comma @Tablename VARCHAR(100), -- The name of the table to be searched @OrderColumnName VARCHAR(100), -- The name of the column to be used in order
@Order VARCHAR(50), -- The order method, ASC or DESC
@Where VARCHAR(100), -- The where condition, if there is not conditon use 1=1
@PageIndex INT, -- Current page index @PageSize INT, -- The size of the page
@PageCount INT OUTPUT -- The total page count,define as output parameter AS BEGIN
DECLARE @SqlRecordCount NVARCHAR(100) -- The SQL Statement to get the total count of the records
DECLARE @SqlSelect NVARCHAR(1000) -- The SQL SELECT statment
SET @SqlRecordCount = N'SELECT @RecordCount = COUNT(*) FROM ' + @Tablename + ' WHERE ' +@Where DECLARE @RecordCount INT
EXEC sp_executesql @SqlRecordCount, N'@RecordCount INT
OUTPUT',@RecordCount OUTPUT -- Transfer the parameter dynamic
IF(@RecordCount % @PageSize = 0)
SET @PageCount = @RecordCount / @PageSize ELSE
SET @PageCount = @RecordCount / @PageSize + 1
SET @SqlSelect = N'SELECT ' + @Columns + ' FROM(SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderColumnName
+' ' + @Order + ') AS tempid, * FROM '
+ @Tablename + ' WHERE ' + @Where + ') AS tempTableName WHERE tempid BETWEEN ' + STR((@PageIndex - 1)*@PageSize + 1) + ' AND ' + STR(@PageIndex * @PageSize) EXEC(@SqlSelect) END
程序有3个控件
BindingNavigator: 就是DataGridView控件上面的那个,在工程里名字: bindngrDemo DataGridView: dgvDemo
BindingSource: 这个其实可以不要 bindseDemo
using System;
using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text;
using System.Windows.Forms; using System.Collections; using System.Data.SqlClient; using System.Configuration;
namespace FeiTianSeo {
public partial class Test1 : Form {
static int pageSize = 20; static int pageCount = 0; public Test1() {
InitializeComponent(); }
public static string connectionString =
ConfigurationManager.ConnectionStrings[\"conn\"].ToString();
private void Test1_Load(object sender, EventArgs e) {
LoadData(1, dgvDemo);
// Set the status of the BindingNavigator control if (pageCount == 0 || pageCount == 1)
{
bindngrDemo.MoveFirstItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = false; bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MovePreviousItem.Enabled = false; } else {
bindngrDemo.MoveFirstItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = true; bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MovePreviousItem.Enabled = false; bindngrDemo.PositionItem.Text = \"1\";
bindngrDemo.CountItem.Text = \"共{\" + pageCount.ToString() + \页\"; }
}
private static SqlCommand GenerateSelectCommand(string Columns, string tableName, string orderColumnName, string order, string whereCondition, int pageIndex, int pageSize, SqlConnection conn) {
SqlCommand sqlcmd = new SqlCommand(\"Pagination\", conn);调用存储过程 计算求分页数 sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue(\"@Columns\", Columns); sqlcmd.Parameters.AddWithValue(\"@Tablename\", tableName);
sqlcmd.Parameters.AddWithValue(\"@OrderColumnName\", orderColumnName); sqlcmd.Parameters.AddWithValue(\"@Order\", order);
sqlcmd.Parameters.AddWithValue(\"@Where\", whereCondition); sqlcmd.Parameters.AddWithValue(\"@PageIndex\", pageIndex); sqlcmd.Parameters.AddWithValue(\"@PageSize\", pageSize);
SqlParameter pageCount = new SqlParameter(\"@PageCount\", SqlDbType.Int); pageCount.Direction = ParameterDirection.Output; sqlcmd.Parameters.Add(pageCount);
sqlcmd.UpdatedRowSource = UpdateRowSource.None; return sqlcmd; }
private static void LoadData(int pageIndex, DataGridView dgvDemo) {
//string strConn = \"server = (local); Database = pubs; Integrated Security = SSPI\"; try {
using (SqlConnection conn = new SqlConnection(connectionString)) {
conn.Open();
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = GenerateSelectCommand(\"id, UserName, linkAdress\", \"Links\", \"id\", \"ASC\", \"1=1\", pageIndex, pageSize, conn); DataTable ds = new DataTable(); sqlDa.Fill(ds); 调用存储过程 计算求分页数
pageCount = (int)sqlDa.SelectCommand.Parameters[\"@PageCount\"].Value; BindingSource bindseDemo = new BindingSource(); bindseDemo.DataSource = ds; dgvDemo.DataSource = bindseDemo; sqlDa.Dispose();
// Use SqlDataAdapter Dataset to fetch the data /*
DataSet ds = new DataSet();
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = GenerateSelectCommand(\"fname\\"ASC\ sqlDa.Fill(ds);
pageCount = (int)sqlDa.SelectCommand.Parameters[\"@PageCount\"].Value; dgvDemo.DataSource = ds.Tables[0]; sqlDa.Dispose(); */
conn.Close(); } }
catch (Exception ex) {
MessageBox.Show(ex.Message, \"Information:\", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void button1_Click(object sender, EventArgs e) {
//string sqlstr = \"select * from Links\";
//SqlConnection conn = new SqlConnection(connectionString); //DataSet ds = new DataSet();
//SqlDataAdapter ada = new SqlDataAdapter(sqlstr, conn); //ada.Fill(ds);
//this.dgvDemo.DataSource = ds.Tables[0];
}
private void bindingNavigatorMoveNextItem_Click_1(object sender, EventArgs e) {
int currentPage = Convert.ToInt32(bindngrDemo.PositionItem.Text); if (currentPage < pageCount) {
int page = currentPage + 1;
bindngrDemo.PositionItem.Text = page.ToString(); LoadData(page, dgvDemo); if (page == pageCount) {
bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MoveLastItem.Enabled = false; }
if (page >= 2) {
bindngrDemo.MovePreviousItem.Enabled = true; bindngrDemo.MoveFirstItem.Enabled = true; } } else {
MessageBox.Show(\"This is the last page\", \"Information\", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void bindingNavigatorMoveLastItem_Click_1(object sender, EventArgs e) {
LoadData(pageCount, dgvDemo);
bindngrDemo.PositionItem.Text = pageCount.ToString(); bindngrDemo.MoveLastItem.Enabled = false; bindngrDemo.MoveNextItem.Enabled = false; bindngrDemo.MovePreviousItem.Enabled = true; bindngrDemo.MoveFirstItem.Enabled = true; }
private void bindingNavigatorMovePreviousItem_Click_1(object sender, EventArgs e) {
int currentPage = Convert.ToInt32(bindngrDemo.PositionItem.Text); if (currentPage >= 2)
{
int page = currentPage - 1;
bindngrDemo.PositionItem.Text = page.ToString(); LoadData(page, dgvDemo); if (page == 1) {
bindngrDemo.MovePreviousItem.Enabled = false; bindngrDemo.MoveFirstItem.Enabled = false; }
if (page <= pageCount) {
bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MoveLastItem.Enabled = true; } } else {
MessageBox.Show(\"This is the first page\", \"Information\", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void bindingNavigatorMoveFirstItem_Click_1(object sender, EventArgs e) {
LoadData(1, dgvDemo);
bindngrDemo.PositionItem.Text = \"1\"; bindngrDemo.MoveFirstItem.Enabled = false; bindngrDemo.MovePreviousItem.Enabled = false; bindngrDemo.MoveNextItem.Enabled = true; bindngrDemo.MoveLastItem.Enabled = true; } } }
因篇幅问题不能全部显示,请点此查看更多更全内容
Copyright © 2019- oldu.cn 版权所有 浙ICP备2024123271号-1
违法及侵权请联系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务