大数量查询分页显示 微软的解决办法-ASP教程,ASP应用

微软的解决办法

using system;

using system.data;

using system.data.sqlclient;

using system.drawing;

using system.windows.forms;

public class pagingsample: form

{

// form controls.

button prevbtn = new button();

button nextbtn = new button();

static datagrid mygrid = new datagrid();

static label pagelbl = new label();

// paging variables.

static int pagesize = 10; // size of viewed page.

static int totalpages = 0; // total pages.

static int currentpage = 0; // current page.

static string firstvisiblecustomer = ""; // first customer on page to determine location for move previous.

static string lastvisiblecustomer = ""; // last customer on page to determine location for move next.

// dataset to bind to datagrid.

static datatable custtable;

// initialize connection to database and dataadapter.

static sqlconnection nwindconn = new sqlconnection("data source=localhost;integrated security=sspi;initial catalog=northwind");

static sqldataadapter custda = new sqldataadapter("", nwindconn);

static sqlcommand selcmd = custda.selectcommand;

public static void getdata(string direction)

{

// create sql statement to return a page of records.

selcmd.parameters.clear();

switch (direction)

{

case "next":

selcmd.commandtext = "select top " + pagesize + " customerid, companyname from customers " +

"where customerid > @customerid order by customerid";

selcmd.parameters.add("@customerid", sqldbtype.varchar, 5).value = lastvisiblecustomer;

break;

case "previous":

selcmd.commandtext = "select top " + pagesize + " customerid, companyname from customers " +

"where customerid < @customerid order by customerid desc";

selcmd.parameters.add("@customerid", sqldbtype.varchar, 5).value = firstvisiblecustomer;

break;

default:

selcmd.commandtext = "select top " + pagesize + " customerid, companyname from customers order by customerid";

// determine total pages.

sqlcommand totcmd = new sqlcommand("select count(*) from customers", nwindconn);

nwindconn.open();

int totalrecords = (int)totcmd.executescalar();

nwindconn.close();

totalpages = (int)math.ceiling((double)totalrecords / pagesize);

break;

}

// fill a temporary table with query results.

datatable tmptable = new datatable("customers");

int recordsaffected = custda.fill(tmptable);

// if table does not exist, create it.

if (custtable == null)

custtable = tmptable.clone();

// refresh table if at least one record returned.

if (recordsaffected > 0)

{

switch (direction)

{

case "next":

currentpage++;

break;

case "previous":

currentpage–;

break;

default:

currentpage = 1;

break;

}

pagelbl.text = "page " + currentpage + " of " + totalpages;

// clear rows and add new results.

custtable.rows.clear();

foreach (datarow myrow in tmptable.rows)

custtable.importrow(myrow);

// preserve first and last primary key values.

datarow[] ordrows = custtable.select("", "customerid asc");

firstvisiblecustomer = ordrows[0][0].tostring();

lastvisiblecustomer = ordrows[custtable.rows.count – 1][0].tostring();

}

}

public pagingsample()

{

// initialize controls and add to form.

this.clientsize = new size(360, 274);

this.text = "northwind data";

mygrid.location = new point(10,10);

mygrid.size = new size(340, 220);

mygrid.allowsorting = true;

mygrid.captiontext = "northwind customers";

mygrid.readonly = true;

mygrid.allownavigation = false;

mygrid.preferredcolumnwidth = 150;

prevbtn.text = "<<";

prevbtn.size = new size(48, 24);

prevbtn.location = new point(92, 240);

prevbtn.click += new eventhandler(prev_onclick);

nextbtn.text = ">>";

nextbtn.size = new size(48, 24);

nextbtn.location = new point(160, 240);

pagelbl.text = "no records returned.";

pagelbl.size = new size(130, 16);

pagelbl.location = new point(218, 244);

this.controls.add(mygrid);

this.controls.add(prevbtn);

this.controls.add(nextbtn);

this.controls.add(pagelbl);

nextbtn.click += new eventhandler(next_onclick);

// populate dataset with first page of records and bind to grid.

getdata("default");

dataview custdv = new dataview(custtable, "", "customerid", dataviewrowstate.currentrows);

mygrid.setdatabinding(custdv, "");

}

public static void prev_onclick(object sender, eventargs args)

{

getdata("previous");

}

public static void next_onclick(object sender, eventargs args)

{

getdata("next");

}

}

public class sample

{

static void main()

{

application.run(new pagingsample());

}

}