asp.net C# 存储过程分页代码

 代码如下 复制代码

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.Data.SqlClient;

namespace opdata
{
    public partial class Form1 : Form
    {
        private static String connectstring = "server=192.168.15.175;database='wentest';user id='sa';password='19831221'";
        private SqlConnection allconn; //连接对象
        private int allpage; //计算得出的总页数
        private int currpage; //当前页数
        private int allrows;//所有条数
        private int psize;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            listView1.Items.Clear();
            SqlConnection conn = new SqlConnection(connectstring);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select top 100 * from adonet", conn);
                SqlDataReader sread = cmd.ExecuteReader();
                while (sread.Read())
                {
                    ListViewItem lv = new ListViewItem(sread.GetString(0));
                    lv.SubItems.Add(sread.GetString(1));
                    lv.SubItems.Add(sread.GetString(2));
                    lv.SubItems.Add(sread.GetDateTime(3).ToString());
                    listView1.Items.Add(lv);
               
                }
                sread.Close();
           
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.ToString());
            }
            finally
            {

                conn.Close();

            }
           

        }

        private void setListview(ListView lview)
        {
            lview.View = View.Details; //表格方式
            lview.GridLines = true;
            lview.FullRowSelect = true;
            ColumnHeader header1 = new ColumnHeader();
            ColumnHeader header2 = new ColumnHeader();
            ColumnHeader header3 = new ColumnHeader();
            ColumnHeader header4 = new ColumnHeader();
            header1.Text = "编号";
            header2.Text = "名称";
            header3.Text = "地址";
            header4.Text = "日期";
            header1.Width = 80;
            header2.Width = 100;
            header3.Width = 280;
            header4.Width = 140;
            header1.TextAlign = HorizontalAlignment.Center;
            header2.TextAlign = HorizontalAlignment.Center;
            header3.TextAlign = HorizontalAlignment.Center;
            header4.TextAlign = HorizontalAlignment.Center;
            lview.Columns.Add(header1);
            lview.Columns.Add(header2);
            lview.Columns.Add(header3);
            lview.Columns.Add(header4);
            lview.Items.Clear();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            //初始化
            allconn = new SqlConnection(connectstring);
            allrows = getpageall(allconn); //取得所有条数
            currpage = 1;//设为第一页
            psize = 50;
            allpage = getpage(allrows, psize); //取得页数
            setListview(listView1);
            //MessageBox.Show(allpage.ToString());
            showlist(allconn, listView1, 1);
            //statusStrip1.
            toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize .ToString()+ "条记录，共有" +allpage.ToString()+ "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
            toolStripStatusLabel2.Text = "当前时间：" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connectstring);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select count(*) from adonet", conn);
                MessageBox.Show(cmd.ExecuteScalar().ToString());
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.ToString());
            }
            finally
            {

                conn.Close();

            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connectstring);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "myfy_one"; //存储过程名
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter param = cmd.CreateParameter();
                param.Direction = ParameterDirection.Input; //输入参数
                param.ParameterName = "@size";
                param.DbType = DbType.Int32;
                param.Value = 100;

                SqlParameter param1 = cmd.CreateParameter();
                param1.Direction = ParameterDirection.Input;
                param1.ParameterName = "@number";
                param1.DbType = DbType.Int32;
                param1.Value = 3;

                cmd.Parameters.Add(param);
                cmd.Parameters.Add(param1);

                SqlDataReader sread = cmd.ExecuteReader();
                while (sread.Read())
                {
                    ListViewItem lv = new ListViewItem(sread.GetString(0));
                    lv.SubItems.Add(sread.GetString(1));
                    lv.SubItems.Add(sread.GetString(2));
                    lv.SubItems.Add(sread.GetDateTime(3).ToString());
                    listView1.Items.Add(lv);

                }
                sread.Close();
           
            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.ToString());
            }
            finally
            {

                conn.Close();

            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connectstring);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("sall",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@allnum",SqlDbType.Int,4);
                cmd.Parameters["@allnum"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                MessageBox.Show(cmd.Parameters["@allnum"].Value.ToString());

            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.ToString());
            }
            finally
            {

                conn.Close();

            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            currpage = 1;
            showlist(allconn, listView1, currpage);
            toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
        }

        private int getpageall(SqlConnection gconn) //取得条数
        {
            int rnum=0;
            try
            {
                gconn.Open();
                SqlCommand cmd = new SqlCommand("sall", gconn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@allnum", SqlDbType.Int, 4);
                cmd.Parameters["@allnum"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                //MessageBox.Show(cmd.Parameters["@allnum"].Value.ToString());
                rnum = (int)cmd.Parameters["@allnum"].Value;

            }
            catch (SqlException ee)
            {
                MessageBox.Show(ee.ToString());
            }
            finally
            {

                gconn.Close();

            }
            return rnum;
           
        }

        private int getpage(int rows,int size) //取得页数
        {
            int page;
            if (rows % size == 0 ) //若是能整除
            {
                page = rows / size;
            }
            else {

                page = rows / size +1;
            }
            return page;

        }

        private void showlist(SqlConnection sconn,ListView lvw,int cur) //显示记录到listview1
        {
            lvw.Items.Clear();
            if (allrows != 0) //有记录才显示
            {
                try
                {
                    sconn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = sconn;
                    cmd.CommandText = "myfy_one"; //存储过程名
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter param = cmd.CreateParameter();
                    param.Direction = ParameterDirection.Input; //输入参数
                    param.ParameterName = "@size";
                    param.DbType = DbType.Int32;
                    param.Value = psize; //分页大小
                    SqlParameter param1 = cmd.CreateParameter();
                    param1.Direction = ParameterDirection.Input;
                    param1.ParameterName = "@number";
                    param1.DbType = DbType.Int32;
                    param1.Value = cur;  //当前页
                    cmd.Parameters.Add(param);
                    cmd.Parameters.Add(param1);
                    SqlDataReader sread = cmd.ExecuteReader();
                    while (sread.Read())
                    {
                        ListViewItem lv = new ListViewItem(sread.GetString(0));
                        lv.SubItems.Add(sread.GetString(1));
                        lv.SubItems.Add(sread.GetString(2));
                        lv.SubItems.Add(sread.GetDateTime(3).ToString());
                        lvw.Items.Add(lv);

                    }
                    sread.Close();

                }
                catch (SqlException ee)
                {
                    MessageBox.Show(ee.ToString());
                }
                finally
                {

                    sconn.Close();

                }
            }
       
        }

        private void button8_Click(object sender, EventArgs e)
        {
            currpage = allpage;
            showlist(allconn,listView1, currpage);
            toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (currpage > 1)
            {
                currpage = currpage - 1;
                showlist(allconn, listView1, currpage);
                toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (currpage < allpage)
            {
                currpage = currpage + 1;
                showlist(allconn, listView1, currpage);
                toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
            }
        }

        private void timer1_Tick(object sender, EventArgs e)
        {

            toolStripStatusLabel2.Text = "当前时间：" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
        }

        private void button9_Click(object sender, EventArgs e)
        {
            int size = System.Int32.Parse( textBox1.Text );
            if (size < 1)
            {
                currpage = 1;
            }
            if (size > allpage)
            {
                currpage = allpage;
            }
            currpage = size;
            showlist(allconn, listView1, currpage);
            toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
           
        }

 

    }
}
时间： 2024-09-27 19:52:50

asp.net C# 存储过程分页代码的相关文章

asp.net利用存储过程分页代码

下面是存储过程(sqlserver2000下通过)   --最通用的分页存储过程 -- 获取指定页的数据  CREATE PROCEDURE Pagination  @tblName   varchar(255),       -- 表名  @strGetFields varchar(1000) = ''*'',  -- 需要返回的列  @fldName varchar(255)='''',      -- 排序的字段名  @PageSize   int = 10,          -- 页尺

asp.net sql存储过程分页代码

use [data_smf] go /****** 对象:  storedprocedure [dbo].[catsearch]    脚本日期: 01/23/2011 04:34:30 ******/ set ansi_nulls on go set quoted_identifier on go -- ============================================= -- author:        <author,,name> -- create date:

高效mssql存储过程分页代码(1/6)

在一般的小数据分页是我们就用简单的分页功能就可以了,但如果上百万级数据分页了,那么我就不得不考虑到高效mssql存储过程分页代码哦. declare @TotalCount int declare @TotalPageCount int exec P_viewPage_A 'type1','*','id','','id asc',1,0,4,3,@TotalCount output,@TotalPageCount output select * from type1 Create PROC P_

sqlserver 通用存储过程分页代码

分页存储过程大致有下列几种 1. 利用Not in 和select top 2. 利用id大于多少和select top 3. 利用sql中的游标 4.临时表 可以参看网上的以下链接 C#中常用的分页存储过程小结 http://read.newbooks.com.cn/info/174545.html 在2005中我们的选择就多了,可以利用新语法CTE(公用表表达式),关于CTE的介绍大家可以参看博客园中一位仁兄的系列教程 http://www.cnblogs.com/nokiaguy/arch

Asp.NET 的aspnetpager分页代码

asp教程.net 的aspnetpager分页代码  @ page language="c#" autoeventwireup="true" codefile="default.aspx.cs" inherits="test_default" stylesheettheme="default" %> <%...@ register assembly="aspnetpager"

asp.net repeater 数字分页代码

asp教程.net repeater 数字分页代码 public static string sort { set; get; }     private static string strsql;     sqlconnection con = new sqlconnection("server=localhost;database=moldsystem;uid=sa;pwd=sa");//这个是全局变量         private pageddatasource pds()  

一个asp.net MVC 的分页代码

哈哈,这个博客的处女文啦~~ 首先声明,这个分页代码并不是出自我手哈,借用了网上的一段代码,然后加了个css,变的好看一些啦~~ 原作者忘记是谁了,实在找不到了--万分抱歉啊~~ 效果如下:     1 using System; 2  using System.Collections.Generic; 3  using System.Linq; 4  using System.Web; 5  using System.Web.Mvc; 6  using System.Web.Routing;

asp 存储过程分页代码第1/2页

存储过程采用的是select top 加 not in的方式完成,速度也算是相当快了 我测试过了百万级数据量一般查询在1秒一下,贴出来大家交流下,看有没有什么好的建议. 简单几句话就可以实现分页功能,请看代码: 最简单使用方法(适用于任何数据表): test.asp 复制代码 代码如下: <!--#include file="conn.asp"--> <!--#include file="Page.asp"--> <% Set My =

asp 存储过程分页代码第1/2页_应用技巧

存储过程采用的是select top 加 not in的方式完成,速度也算是相当快了 我测试过了百万级数据量一般查询在1秒一下,贴出来大家交流下,看有没有什么好的建议. 简单几句话就可以实现分页功能,请看代码: 最简单使用方法(适用于任何数据表): test.asp 复制代码 代码如下: <!--#include file="conn.asp"--> <!--#include file="Page.asp"--> <% Set My =