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

作者:袖梨 2022-06-25
 代码如下 复制代码

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();
           
        }

 

    }
}

相关文章

精彩推荐