用 C# Builder 开发 DB2 Web 应用程序的 5 项性能法则

using System;
namespace Effeciency
{
	/// <summary>
	/// This class is used to time operations. In our example
	/// we will time the speed of certain database operations
	/// to compare them later. 
	/// </summary>
	public class TimeDiff
	{
		DateTime StartTime;
		DateTime EndTime;
		public TimeDiff() {}
		public void Start()
		{
			StartTime = DateTime.Now;
		}
		public void Stop()
		{
			EndTime = DateTime.Now;
		}
		public string TimeDifferenceText
		{
			get
			{
				TimeSpan TimeDifference = EndTime - StartTime;
				return TimeDifference.ToString();
			}
		}
	}
}

CREATE TABLE "GLENN   "."LOTSOFRECORDS"  (
		  "KEYCOL" INTEGER NOT NULL , 
		  "COL1" CHAR(50) , 
		  "COL2" CHAR(50) , 
		  "COL3" CHAR(50) , 
		  "COL4" CHAR(50) , 
		  "COL5" CHAR(50) , 
		  "COL6" CHAR(50) , 
		  "COL7" CHAR(50) , 
		  "COL8" CHAR(50) , 
        	  "COL9" CHAR(50) , 
		  "COL10" CHAR(50) )   
		 IN "USERSPACE1" ; 
COMMENT ON TABLE "GLENN   "."LOTSOFRECORDS" IS 'Table designed to Contain Lots of Records';
-- 表 "GLENN   "."LOTSOFRECORDS" 主键的 DDL 语句
ALTER TABLE "GLENN   "."LOTSOFRECORDS" 
	ADD CONSTRAINT "CC1058255334652" PRIMARY KEY
		("KEYCOL");

select * from GLENN.LOTSOFRECORDS

select 
  KEYCOL, COL1, COL2, COL7 
from 
  GLENN.LOTSOFRECORDS

SELECT 
  PRODUCTCODE, PRODUCTNAME, DESCRIPTION, UNITPRICE
FROM 
  GLENN.PRODUCTLIST 
WHERE 
  UNITPRICE > 20.00

TimeDiff diff = new TimeDiff();
private DataSet GetProductsDataSet() {
	diff.Start();
	string connString = ConfigurationSettings.AppSettings["database"];
	BdpConnection conn = new BdpConnection(connString);
	BdpDataAdapter da = new BdpDataAdapter("select KEYCOL, " +
		"COL1, COL2, COL7 FROM GLENN.LOTSOFRECORDS "+
		"ORDER BY KEYCOL ASC", conn);
	DataSet ds = new DataSet();
	da.Fill(ds, "Table1");
	diff.Stop();
	return ds;
}
private void BindToTheData()
{
		dataGrid1.DataSource = GetProductsDataSet();
		dataGrid1.DataMember = "Table1";
		dataGrid1.DataBind();
		label1.Text = diff.TimeDifferenceText;
}
private void Page_Load(object sender, System.EventArgs e)
{
	if (!IsPostBack) {
		BindToTheData();
	}
}
private void dataGrid1_PageIndexChanged(object source,
 System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
	//Change the active page of the data
	dataGrid1.CurrentPageIndex = e.NewPageIndex;
	BindToTheData();
}

SELECT * FROM 
  (SELECT KEYCOL, COL1, COL2, COL7, rownumber() 
   over(ORDER BY KEYCOL ASC) AS rn 
   FROM GLENN.LOTSOFRECORDS 
   ORDER BY KEYCOL ASC) AS a1 
   WHERE a1.rn BETWEEN 100 AND 120

private DataSet GetDataByPage(int PageNo, int PageSize, out int NumberOfPages)
{
	int startRecord = (PageNo - 1) * PageSize + 1;
	int endRecord = startRecord + PageSize - 1;
	string connString = ConfigurationSettings.AppSettings["database"];
	BdpConnection conn = new BdpConnection(connString);
	conn.Open();
	//Get the number of Pages
	string sRecordCount = "select count(*) from GLENN.LOTSOFRECORDS";
	BdpCommand cmdGetRecordCount = new BdpCommand(sRecordCount, conn);
	int intRecordCount = (int)cmdGetRecordCount.ExecuteScalar();
	NumberOfPages = intRecordCount / PageSize;
	if (intRecordCount % PageSize > 0)
	  NumberOfPages++;
	//Get the data specifically for the page
	string sSQL =
		"SELECT * FROM " +
		"  (SELECT KEYCOL, COL1, COL2, COL7, rownumber() " +
		"   over(ORDER BY KEYCOL ASC) AS rn " +
		"   FROM GLENN.LOTSOFRECORDS " +
		"   ORDER BY KEYCOL ASC) AS a1 " +
		"   WHERE a1.rn BETWEEN ? AND ?";
	BdpCommand cmdSel = new BdpCommand(sSQL, conn);
	BdpParameter prmStart =
	  cmdSel.Parameters.Add("StartRecord", BdpType.Int32);
	prmStart.Value = startRecord;
	BdpParameter prmEnd =
	  cmdSel.Parameters.Add("EndRecord", BdpType.Int32);
	prmEnd.Value = endRecord;
	BdpDataAdapter da = new BdpDataAdapter(cmdSel, conn);
	DataSet ds = new DataSet();
	da.Fill(ds, "Table1");
	diff.Stop();
	return ds;
}
private void LoadSingleDataPage(int pageNo)
{
	//Display the Page contents
	int PageCount;
	DataSet dsData = GetDataByPage(pageNo+1,
		dataGrid1.PageSize, out PageCount);
	dataGrid1.VirtualItemCount = PageCount * dataGrid1.PageSize;
	dataGrid1.CurrentPageIndex = pageNo;
	dataGrid1.DataSource = dsData;
	dataGrid1.DataBind();
}
private void Page_Load(object sender, System.EventArgs e)
{
	if (!IsPostBack)
	{
		LoadSingleDataPage(0);
	}
}
private void dataGrid1_PageIndexChanged(object source,
 System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
	LoadSingleDataPage(e.NewPageIndex);
}

 CREATE TABLE "GLENN   "."PRODUCTLIST"  (
		  "PRODUCTCODE" VARCHAR(20) NOT NULL , 
		  "PRODUCTNAME" VARCHAR(50) NOT NULL , 
		  "DESCRIPTION" VARCHAR(255) , 
		  "UNITPRICE" DOUBLE NOT NULL , 
		  "CATEGORYCODE" INTEGER , 
		  "IMAGEURL" CHAR(150) )   
		 IN "USERSPACE1" ; 
COMMENT ON TABLE "GLENN   "."PRODUCTLIST" IS 'A list of Products in the Shopping Cart';
-- DDL Statements for primary key on Table "GLENN "."PRODUCTLIST"
ALTER TABLE "GLENN   "."PRODUCTLIST" 
	ADD CONSTRAINT "CC1053568050795" PRIMARY KEY
		("PRODUCTCODE");

SELECT PRODUCTLIST.PRODUCTCODE, PRODUCTLIST.PRODUCTNAME, PRODUCTLIST.DESCRIPTION,
 PRODUCTLIST.UNITPRICE, PRODUCTLIST.IMAGEURL
           FROM GLENN.PRODUCTLIST AS PRODUCTLIST
           WHERE PRODUCTLIST.CATEGORYCODE = 2;

CREATE PROCEDURE GLENN.GETPRODUCTSINCATEGORY ( IN CATCODE INTEGER )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure 
------------------------------------------------------------------------
P1: BEGIN
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
        SELECT PRODUCTLIST.PRODUCTCODE, PRODUCTLIST.PRODUCTNAME, PRODUCTLIST.DESCRIPTION,
 PRODUCTLIST.UNITPRICE, PRODUCTLIST.IMAGEURL
           FROM GLENN.PRODUCTLIST AS PRODUCTLIST
           WHERE PRODUCTLIST.CATEGORYCODE = CATCODE;
    -- Cursor left open for client application
    OPEN cursor1;
END P1  

private void GetProductsViaStoredProcedure(int CategoryCode) {
	cmdGetDataViaStoredProc.Parameters[0].Value = CategoryCode;
	BdpDataAdapter da = new BdpDataAdapter(cmdGetDataViaStoredProc);
	da.Fill(dsProducts, "Products");
	dataGrid1.DataBind();
}

<%@ Page language="c#" Debug="true" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" 
Inherits="MyNewWebApplication.WebForm1" %> 
<%@ OutputCache Duration="300" VaryByParam="None" VaryByCustom="browser" %>

private void Page_Load(object sender, System.EventArgs e)
{
	TimeDiff diff = new TimeDiff();
	diff.Start();
	string retrievalMethod;
	DataSet CategoriesDataSet;
	if (Cache["Categories"] == null) {
		retrievalMethod = "Database";
		connShopping.Open();
		CategoriesDataSet = new DataSet();
		daCategories.Fill(CategoriesDataSet, "Categories");
		Cache.Insert("Categories", CategoriesDataSet);
		connShopping.Close();
	} else {
		retrievalMethod = "Cache";
		CategoriesDataSet = (DataSet)Cache["Categories"];
	}
	diff.Stop();
	lblDetails.Text = "Retrieval from the " + retrievalMethod +
		" in " + diff.TimeDifferenceText + " seconds";
	dataGrid1.DataSource = CategoriesDataSet;
	dataGrid1.DataMember = "Categories";
	dataGrid1.DataBind();
}

Cache.Insert("Categories", CategoriesDataSet, null,
	System.Web.Caching.Cache.NoAbsoluteExpiration,
	TimeSpan.FromHours(6));