大数量查询分页显示 微软的解决办法
程序员文章站
2024-02-02 10:08:28
微软的解决办法
using system;
using system.data;
using system.da...
微软的解决办法
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());
}
}
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());
}
}