winform 調用預存程序顯示分頁列表

直接貼代碼吧

SqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ADMEWinForm
{
    class SqlHelper
    {
        private static readonly string strCon = System.Configuration.ConfigurationSettings.AppSettings["connstr"].ToString();

        #region 執行個體化對象
        /// <summary>
        /// 執行個體化對象
        /// </summary>
        SqlConnection con = new SqlConnection(strCon);
        private DataSet ds;
        private SqlCommand cmd;
        private SqlDataAdapter sda;
        private SqlDataReader myReader;
        private SqlTransaction transaction;

        #endregion

        #region 檢測串連是否開啟
        /// <summary>
        /// 檢測串連的方法CheckConnection(),若串連是關閉的則開啟SqlConnection串連
        /// </summary>
        public void CheckConnection()
        {
            if (this.con.State == ConnectionState.Closed)
            {
                this.con.Open();
            }
        }
        #endregion

        #region 執行SQL語句或預存程序的方法ExecuteDataSetProc()
        public DataSet ExecuteDataSetProc(string strSQL, int pagesize, int pageindex)
        {
            CheckConnection();
            DataSet ds1 = new DataSet();
            try
            {
                cmd = new SqlCommand(strSQL, con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] para = { new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int) };
                para[0].Value = pagesize;
                para[1].Value = pageindex;
                cmd.Parameters.AddRange(para);
                SqlDataAdapter dap = new SqlDataAdapter();
                dap.SelectCommand = cmd;
                dap.Fill(ds1, "TGongWen");
            }
            catch (Exception e)
            {
                e.Message.ToString();
            }
            finally
            {
                con.Close();
            }
            return ds1;
        }

        #endregion

        #region 執行SQL語句的方法ExecuteNonQuery()
        /// <summary>
        /// 執行SQL語句的方法ExecuteNonQuery()
        /// </summary>
        /// <param name="strSQL">要執行的SQL語句</param>
        public int ExecuteNonQuery(string strSQL)
        {
            int count = 0;
            CheckConnection();
            try
            {
                cmd = new SqlCommand(strSQL, con);
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return count;
        }
        #endregion

        #region 執行語句,返回該語句查詢出的資料行的總數
        /// <summary>
        /// 執行語句,返回該語句查詢出的資料行的總數
        /// </summary>
        /// <param name="strSQL">要執行的SQL語句</param>
        /// <returns>整型值－－資料總行數</returns>
        public int ReturnRowCount(string strSQL)
        {
            CheckConnection();
            try
            {

                cmd = new SqlCommand(strSQL, con);
                return Convert.ToInt32(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion
    }
}

是分頁列表顯示頁面，其對應的.cs 代碼如下：

Title

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;

namespace ADMEWinForm
{
    public partial class Form1 : Form
    {
        SqlHelper db = new SqlHelper();
        public int rowcount;
        public int NowPage = 1, PageCount = 1, PageSize = 20;
        public Form1()
        {
            InitializeComponent();
        }
        public void BindData()
        {
            setBtnStatus();

         //這裡是調用預存程序並傳入頁碼和當前頁面
            this.dataGridView1.DataSource = db.ExecuteDataSetProc("Pagination", PageSize, NowPage).Tables["TGongWen"];        
        }  
        private void Form1_Load(object sender, EventArgs e)
        {
            BindData();
        }
        //計算餘頁
        public int OverPage()
        {
            int pages = 0;
            if (rowcount % PageSize != 0)
                pages = 1;
            else
                pages = 0;
            return pages;
        }
        public void setBtnStatus()
        {
           rowcount= db.ReturnRowCount("select count(1) from TGongwen ");
           PageCount = rowcount / PageSize + OverPage();
            this.label2.Text="共"+PageCount+"頁 共"+rowcount+"條記錄";
            this.label1.Text = "當前是第" + NowPage + "頁";
            if (NowPage > 1)
            {
                this.button5.Enabled = true;
                this.button6.Enabled = true;
            }
            else
            {
                this.button5.Enabled = false;
                this.button6.Enabled = false;
            }
            if (NowPage == PageCount)
            {
                this.button7.Enabled = false;
                this.button8.Enabled = false;
            }
            else
            {
                this.button7.Enabled = true;
                this.button8.Enabled = true;
            }       
        }
        private void button5_Click(object sender, EventArgs e)
        {
            NowPage = 1;
            Form1_Load(sender,e);
        }
        private void button6_Click(object sender, EventArgs e)
        {
            NowPage -= 1;
            Form1_Load(sender,e);
        }
        private void button7_Click(object sender, EventArgs e)
        {
            NowPage += 1;
            Form1_Load(sender,e);
        }
        private void button8_Click(object sender, EventArgs e)
        {
            NowPage = PageCount;
            Form1_Load(sender,e);
        }
        private void button2_Click(object sender, EventArgs e)
        {
            string id = this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
            if (db.ExecuteNonQuery("delete from TGongwen where gid=" + id)>0)
            {
                MessageBox.Show("刪除成功");
            }
            Form1_Load(sender, e);
        }
        private void button3_Click(object sender, EventArgs e)
        {
            Manager m = new Manager();
            m.CmdType = "edit";
            m.Id = Convert.ToInt32(this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
            m.Show();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            Manager m = new Manager();
            m.CmdType = "add";
            m.Show();
        }    
    }
}

 

新增資料和修改資料頁面以及所對應的.cs 檔案

Manager

 

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;

namespace ADMEWinForm
{
    public partial class Manager : Form
    {
        SqlHelper db = new SqlHelper();
        public Manager()
        {
            InitializeComponent();
        }
        private string cmdType;

        public string CmdType
        {
            get { return cmdType; }
            set { cmdType = value; }
        }
        private int id;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        public void GetModelData()
        {
            DataSet ds = db.ReturnDataSet("select * from tgongwen where gid=" + Id);
            if (ds.Tables[0].Rows.Count > 0)
            {
                this.textBox1.Text = ds.Tables[0].Rows[0]["title"].ToString();
                this.textBox2.Text = ds.Tables[0].Rows[0]["neibuyonghu"].ToString();
                this.textBox3.Text = ds.Tables[0].Rows[0]["reader"].ToString();
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {     
            if (CmdType == "add")
            {
                if (add() > 0)
                {
                    MessageBox.Show("添加成功！");
                    button2_Click(sender, e);
                }
            }
            else
            {
                if (update() > 0)
                {
                    MessageBox.Show("修改成功！");
                    button2_Click(sender,e);
                }
            }

        }
        public int add()
        {
           return db.ExecuteNonQuery("insert into tgongwen (title,fariqi,neibuyonghu,reader)values('"+this.textBox1.Text+"','"+System.DateTime.Now.ToString()+"','"+this.textBox2.Text+"','"+this.textBox3.Text+"')");
        }
        public int update()
        {
            return db.ExecuteNonQuery("update tgongwen set title='"+this.textBox1.Text+"',neibuyonghu='"+this.textBox2.Text+"',reader='"+this.textBox3.Text+"' where gid="+Id );
        }
        private void button2_Click(object sender, EventArgs e)
        {
            Form1 f = new Form1();
            f.Show();
            this.Close();
        }
        private void Manager_Load(object sender, EventArgs e)
        {
            GetModelData();
        }
    }
}

 

對了，還有預存程序忘記貼了就在下面

到此結束。