📄 cb199910hh_f.asp.htm
字号:
<HTML><HEAD><TITLE>Quick Data Sorts</TITLE>
<META content="text/html; charset=gb-2312" http-equiv=Content-Type>
<BODY bgColor=white MARGINWIDTH="0" MARGINHEIGHT="0">
<TABLE border=0 cellPadding=0 cellSpacing=0 width="100%">
<TBODY>
<TR vAlign=top>
<TD width="100%">
<P class=ColumnTitle><b>Columns & Rows</b></P>
<P class=ColumnSubtitle>ClientDataSet / SQL / Database / Client/Server</P>
<P class=Byline>By Harold Howe</P>
<P class=StoryTitle>Quick Data Sorts</P>
<P class=StorySubtitle>Using ClientDataSet to Order Data</P>
<P class=BodyText> </P>
<P class=BodyText>Sorting the result set of a query is a common task in
database applications. You can often sort data by using an ORDER BY clause
in your SQL SELECT statement. Sorting data with SQL works well when you
know how you want to order the data before you execute the query. However,
if you need to sort the same data in several ways, using an ORDER BY
clause in your query might create some performance problems. </P>
<P class=BodyText> </P>
<P class=Subheads>A Straightforward Approach</P>
<P class=BodyText>For example, let's imagine you want to give users of
your program the power to sort the data in the Orders database table that
comes with C++Builder and Delphi. Furthermore, you want to sort the data
in a grid when users click on a column heading. You could sort the data by
adding an ORDER BY clause to your query. The following code demonstrates
how you could sort the data in a grid using this technique: </P>
<P class=BodyText> </P>
<P class=Code><B>void</B> <B
style="mso-bidi-font-weight: normal">__fastcall</B>
TForm1::DBGrid1TitleClick(TColumn *Column) </P>
<P class=Code>{</P>
<P class=Code> <B> if </B>(!Query1->Active) </P>
<P class=Code> <B> return</B>;</P>
<P class=Code> </P>
<P class=Code> Query1->DisableControls();</P>
<P class=Code> <B> try</B></P>
<P class=Code> { </P>
<P class=Code> Query1->Active =
<B>false</B>;</P>
<P class=Code> Query1->SQL->Clear();</P>
<P class=Code> Query1->SQL->Add("SELECT *
FROM Orders"); </P>
<P class=Code> Query1->SQL->Add("ORDER BY " +
Column->Field->FieldName); </P>
<P class=Code> Query1->Active = <B>true</B>;</P>
<P class=Code> } </P>
<P class=Code> <B> __finally</B></P>
<P class=Code> { </P>
<P class=Code> Query1->EnableControls();</P>
<P class=Code> } </P>
<P class=Code>}</P>
<P class=BodyText> </P>
<P class=BodyText>When users clicks on a column heading, this event fires.
The code closes the old query, configures the <I>SQL</I> property of the
query with a new ORDER BY clause, and re-executes the query. </P>
<P class=BodyText> </P>
<P class=BodyText>The code works well on a desktop database, but users
would see a noticeable delay if the back-end database were running
remotely on a server. This technique has a couple of other problems too.
First, because the data is completely reloaded, users lose their place in
the grid when they click on a column. If they scroll down, the grid zooms
back to the top when they click on one of the columns. Another problem is
that it could mess up a query control that has <I>RequestLive</I> set to
<B>true</B>. The BDE sometimes has problems returning an editable result
set when the SQL contains an ORDER BY clause. Lastly, this technique won't
work well if you're using cached updates. </P>
<P class=BodyText> </P>
<P class=Subheads>A Better Way</P>
<P class=BodyText>The purpose of this article is to demonstrate a better
way to sort your data on the client side without having to re-fetch the
data from the database. The article explains how to use the index features
of ClientDataSet to sort your data on the client side. By using an index,
you can sort your data in a variety of ways. Furthermore, sorting with an
index is faster than re-querying the database. </P>
<P class=BodyText> </P>
<P class=BodyText>Note: The ClientDataSet component is a feature of the
Client/Server and Enterprise versions of C++Builder. The techniques
discussed in this article cannot be used with the Standard or Professional
versions. </P>
<P class=BodyText> </P>
<P class=Subheads>Introducing ClientDataSet</P>
<P class=BodyText>The ClientDataSet component is the foundation of the
MIDAS multi-tier framework in the Enterprise versions of C++Builder and
Delphi. It's located on the MIDAS tab of the Component palette. Although
ClientDataSet is an integral part of MIDAS, we won't be using any MIDAS in
this article, and we won't be creating remote data modules or MIDAS
servers. All of our code will be contained in a single project. </P>
<P class=BodyText> </P>
<P class=BodyText>ClientDataSet is a dataset control that functions much
like a Table or Query component. You can connect it to a DBGrid through a
DataSource component, and the data in the ClientDataSet will automatically
appear in the grid. Unlike Table and Query, ClientDataSet doesn't fetch
its data directly from a database. Rather, it gets it through a data
provider, which is specified in the <I>ProviderName</I> property of the
ClientDataSet. </P>
<P class=BodyText> </P>
<P class=BodyText>In this article, we'll use the Provider component as the
provider for the ClientDataSet. The Provider component allows you to
connect a ClientDataSet to a source dataset. The source dataset can be a
Query, Table, or StoredProc component. When you set the <I>Active</I>
property of ClientDataSet to <B>true</B>, it goes through its data
provider and activates the source dataset. The ClientDataSet fetches data
from the source dataset and stores the records locally in memory. </P>
<P class=BodyText> </P>
<P class=Subheads>ClientDataSet by Example</P>
<P class=BodyText>The sorting examples in this article make heavy use of
the ClientDataSet-Provider-DataSet bridge. You may want to build a simple
program that tests this connection: </P>
<P class=BodyText>1) Create a new
project. </P>
<P class=BodyText>2) Place a Query
component onto the main form, and set its <I>DatabaseName</I> property to
BCDEMOS. </P>
<P class=BodyText>3) Edit the
Query<I> </I>component's<I style="mso-bidi-font-style: normal"> SQL</I>
property, entering a SELECT statement that fetches all records from the
Orders table: SELECT * FROM Orders</P>
<P class=BodyText>4) Place a Provider
component onto the form, and set its <I>DataSet</I> property to Query1.
</P>
<P class=BodyText>5) Place a
ClientDataSet on the form, and set its <I>ProviderName</I> property to the
name of the Provider component you just created (<I>Provider1</I> by
default). </P>
<P class=BodyText>6) Place a button
on the form, and create an <I>OnClick</I> event handler for it. Add code
that sets the <I>Active</I> property of the ClientDataSet to
<B>true</B>:</P>
<P class=BodyText> </P>
<P class=Code style="TEXT-INDENT: 1.45pt"><B>void</B> <B>__fastcall</B>
TForm1::Button1Click(TObject *Sender) </P>
<P class=Code style="TEXT-INDENT: 1.45pt">{</P>
<P class=Code
style="TEXT-INDENT: 1.45pt"> ClientDataSet1->Active =
<B>true</B>;</P>
<P class=Code style="TEXT-INDENT: 1.45pt">}</P>
<P class=BodyText> </P>
<P class=BodyText>7) Place a
DataSource on the form, and set its <I>DataSet</I> property to
<I>ClientDataSet1</I> (not to the Query component). </P>
<P class=BodyText>8) Place a DBGrid
on the form, and set its <I>DataSource</I> property to DataSource1. </P>
<P class=BodyText> </P>
<P class=BodyText>Now run the program and click the button on the form.
When you activate the ClientDataSet, it goes through its provider and
activates the source dataset, which is the Query component. After the
Query component is opened, the ClientDataSet fetches the records from the
query and stores them locally in memory. Once all of the rows are fetched,
the Query component is closed. </P>
<P class=BodyText> </P>
<P class=BodyText>If you're new to ClientDataSet, here are some key points
to remember: </P>
<UL>
<LI>ClientDataSet doesn't use the BDE.
<LI>The low-level code for ClientDataSet resides in DBCLIENT.DLL, which
you'll need to deploy with your program.
<LI>The records in a ClientDataSet are stored in memory; they're not
part of any database table.
<LI>Any changes you make to the ClientDataSet don't affect the records
in the source dataset. The ClientDataSet is disconnected from the source
dataset.
<LI>ClientDataSet doesn't use a database connection, and it doesn't
interact with the Database component.
<LI>You can edit, append, and post data to the ClientDataSet. These
changes don't affect the source database until you call
<I>ApplyUpdates</I>. If you don't call <I>ApplyUpdates</I>, the database
doesn't change. </LI></UL>
<P class=BodyText> </P>
<P class=Subheads>Creating an Index on a ClientDataSet</P>
<P class=BodyText>ClientDataSet components resemble real database tables
in many ways. In fact, a ClientDataSet functions somewhat like an
in-memory table. One of the ways that ClientDataSet resembles a real table
is indexing; you can create an index on a ClientDataSet just as you create
an index on a database table. The index may contain uniqueness or primary
key constraints, and you can configure an index to be ascending,
descending, or case-insensitive. You configure the indexes of a
ClientDataSet the same way you configure the indexes of a Table component.
</P>
<P class=BodyText> </P>
<P class=BodyText>A ClientDataSet may contain several indexes, but only
one can be active. The <I
style="mso-bidi-font-style: normal">IndexName</I> property of
ClientDataSet determines the current index. When you select an index, that
index is enforced, i.e. the records in the ClientDataSet are sorted
according to that index. We'll use this sorting action to perform
client-side data sorting. </P>
<P class=BodyText> </P>
<P class=BodyText>You can create an index for a ClientDataSet in one of
three ways: You can use the <I
style="mso-bidi-font-style: normal">IndexDefs</I> property at design time,
you can use the <I>IndexDefs</I> property at run time, or you can call the
<I>AddIndex</I> method at run time. Each technique has its advantages, as
the following three examples demonstrate. </P>
<P class=BodyText> </P>
<P class=Subheads>Creating an Index at Design Time</P>
<P class=BodyText>The property editor for the <I>IndexDefs</I> property
allows you to create an index at design time. You can activate the
property editor by clicking the ellipsis button (...) in the <I
style="mso-bidi-font-style: normal">IndexDefs</I> property from the Object
Inspector. This activates a property editor that allows you to add and
delete index definitions to the ClientDataSet. </P>
<P class=BodyText> </P>
<P class=BodyText>To add an index, click the Add button in the property
editor mini-form. Clicking the Add button creates a single <I
style="mso-bidi-font-style: normal">TIndexDef</I> object, and adds it to
the <I style="mso-bidi-font-style: normal">IndexDefs</I> collection. After
creating the <I>IndexDef</I> object, you can set its properties in the
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -