C# Oracle分頁完整代碼

/**
*
*開發環境：VS2008
*.NET版本：.Net Framework2.0
*
*/

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using MySql.Data.MySqlClient;

using Oracle.DataAccess.Client;

namespace分页测试

{

publicpartialclassForm1 : Form

    {

       int nextrec = 0;

       int nnrec = 0;

public Form1()

        {

            InitializeComponent();

        }

 

privatevoid Form1_Load(object sender, EventArgs e)

        {

try

            {

string constr = "data source=172.17.1.12/oracle1;user id=scott;password=123456";

 

OracleConnection conn = newOracleConnection(constr);

                conn.Open();

OracleCommand cmd = conn.CreateCommand();

                nextrec = 0;

                cmd.CommandText = @"select * from(select name,sex,phone,rownum rn from test) test where rn<=" + 6;

DataSet ds = newDataSet();

OracleDataAdapter da = newOracleDataAdapter(); 

                da.SelectCommand = cmd;

                da.Fill(ds);

                dataGridView1.DataSource = ds.Tables[0];

//获取数据库记录总数

OracleCommand tatolcmd = conn.CreateCommand();

                tatolcmd.CommandText = "select count(name) from test";

int reccount = Convert.ToInt32(tatolcmd.ExecuteScalar());

                label1.Text ="数据总记录为："+ reccount.ToString();

 

            }

catch (Exception ex)

          {

MessageBox.Show(ex.Message);

            }

        }

 

privatevoid button2_Click(object sender, EventArgs e)

        {

try

            {

string constr = "data source=172.17.1.12/oracle1;user id=scott;password=123456";

 

OracleConnection conn = newOracleConnection(constr);

                conn.Open();

OracleCommand cmd = conn.CreateCommand();

                nextrec += 6;   //第二頁從第6個數據開始顯示

                nnrec = nextrec + 5;  //每頁顯示6條數據

 

//获取数据库记录总数

OracleCommand tatolcmd = conn.CreateCommand();

                tatolcmd.CommandText = "select count(name) from test";

int reccount = Convert.ToInt32(tatolcmd.ExecuteScalar());

                label1.Text = "数据总数为：" + reccount.ToString();

if (nnrec > reccount+5)

                {

MessageBox.Show("数据显示结束.");

 

                    button2.Enabled = false;

                }

else

                {

                    cmd.CommandText = @"select * from(select name,sex,phone,rownum rn from test) test where rn>=" + nextrec + "and rn<=" + nnrec;

DataSet ds = newDataSet();

OracleDataAdapter da = newOracleDataAdapter();

                    da.SelectCommand = cmd;

                    da.Fill(ds);

                    dataGridView1.DataSource = ds.Tables[0]

                    button1.Enabled = true;

                }

       }

catch (Exception ex)

            {

MessageBox.Show(ex.Message);

            }

        }

 

privatevoid button1_Click(object sender, EventArgs e)

        {

try

            {

string constr = "data source=172.17.1.12/oracle1;user id=scott;password=123456";

 

OracleConnection conn = newOracleConnection(constr);

                conn.Open();

OracleCommand cmd = conn.CreateCommand();

                nextrec -=6;    //向上返回6條數據

                nnrec -=6;    //每頁顯示6條數據

 

//获取数据库记录总数

OracleCommand tatolcmd = conn.CreateCommand();

                tatolcmd.CommandText = "select count(name) from test";

int reccount = Convert.ToInt32(tatolcmd.ExecuteScalar());

                label1.Text = "数据总数为：" + reccount.ToString();

if (nextrec <= -1)

                {

MessageBox.Show("数据显示结束.");

 

                    button2.Enabled = true ;

                    button1.Enabled = false;

                }

else

                {

                    cmd.CommandText = @"select * from(select name,sex,phone,rownum rn from test) test where rn>=" + nextrec + "and rn<=" + nnrec;

DataSet ds = newDataSet();

OracleDataAdapter da = newOracleDataAdapter();

                    da.SelectCommand = cmd;

                    da.Fill(ds);

                    dataGridView1.DataSource = ds.Tables[0];

                    button2.Enabled = true;

                }

            }

catch (Exception ex)

            {

MessageBox.Show(ex.Message);

            }

        }

    }

}