|
Using TTable and TDataSet In this
chapter, you learn some of the basics about accessing database tables using
the TTable object. In particular, the
chapter covers the fundamental information you need to access tables without
using SQL. An examination of using SQL to access tables begins in the next
chapter. In the
examples given here, you will be explicitly working with local Paradox
tables, but nearly everything explained in this chapter applies equally to
dBASE files or to files located on a SQL server such as InterBase or Oracle.
I decided to run these examples against Paradox tables because I wanted to
keep this chapter as simple as possible. Large portions of this book work
exclusively with SQL databases, but this chapter sticks to the world of local
tables. Looking
a little more deeply at the content of this chapter, you can expect to find
information on the following:
Here is
a second way to categorize some of the objects discussed in this chapter:
This
latter view of the major database components breaks them down into two major
categories. The nonvisual components enable you to open, close, edit, and
otherwise manipulate tables, records, and fields. The visual components
display the tables to the user so he or she can view or edit them. The
powerful TDataSource object forms a link between the
visual and nonvisual database controls. You might want to think of the
nonvisual controls as being the intelligent heart of the BCB database tools,
while the visual controls are the less intelligent outward show. The
nonvisual controls manipulate the data; the visual controls display it. The
overriding purpose of this chapter is to give you a good overview of the
basic facts about using a BCB database class called TDataSet. TDataSet is the driving force behind both
the TTable and TQuery objects. It is the root class from which they are both descended. A
third component, called TStoredProc, is also descended from TDataSet. It will be discussed in more depth in
Chapters 15, "Working with the Local InterBase Server," and 16,
"Advanced InterBase Concepts," both of which deal with InterBase. Specific
information about other database issues will be presented in subsequent
chapters. For instance, the TQuery object will be treated in depth
in the next chapter, and more detailed explanations of TDBGrid, TField, TStringField, and TIntegerField are found in Chapter 11. Understanding
the TDataSet Class In the
last chapter you were introduced to the Database Explorer, the SQL Monitor,
and TDataModule. It is now time to start digging
into some of the technical details of the TTable object. Learning something about these details will go a long way
toward helping you understand the structure of the database tools supplied by
BCB. TTable and TQuery inherit most of their functionality from TDataSet. As a result, the TDataSet class is one of the most
important database objects. To get started working with it, you need to
concentrate on the hierarchy shown in Figure 9.1. TDataSet contains the abstractions needed
to directly manipulate a table. TDBDataSet
knows how to handle passwords and other tasks directly associated with
linking to a specific table. TTable knows how to handle indices and
the specific chores associated with linking two tables in a one-to-many
relationship. As you
will see in the next chapter, TQuery has a deeply rooted and complete
knowledge of how to process SQL statements. The TStoredProc object is on the same level of the hierarchy as TTable and TQuery. It is used to process the stored procedures in a SQL database. The
methods of the TDataSet object enable you to open and
navigate a table. Of course, you will never directly instantiate an object of
type TDataSet. Instead, you will usually be
working with TTable, TQuery, or some other descendant of TDataSet. The exact way this system works, and the precise significance of TDataSet, will become clear as you read through this
chapter. On the
most fundamental level, a dataset is nothing more than a set of records, each
containing x number of fields and a pointer to the current record. On many
occasions, a dataset has a direct, one-to-one correspondence with a physical
table that exists on disk. However, at other times, you may perform a query
or other action that returns a dataset that contains either a subset of one
table or a join between multiple tables. The text that follows, however,
sometimes uses the terms dataset and table interchangeably if it helps to
simplify the explanation of a particular concept. You will
normally instantiate an object of type TTable or TQuery in order to access the
functionality of TDataSet. Because of this relationship,
the code in the next few sections will always assume the existence of an
instance of class TTable. Remember, however, that the
functions under discussion are part of TDataSet, unless the text specifically states otherwise. In other words, much
of what I say here applies to both TQuery and TTable, since both of these objects descend from TDataSet. It's now
time for you to begin a direct exploration of TDataSet. As you become familiar with its capabilities, you will begin to
understand exactly how BCB accesses the raw data saved to disk as a database.
The key point to remember is that nearly every time a BCB programmer opens a
table, he or she will be using a class such as TTable or TQuery, both of which are merely thin
wrappers around TDataSet. Opening and
Closing Datasets The
simplest thing you can do with a TDataSet is
open or close it. This is therefore an appropriate starting point for an
exploration of datasets. In the sections that follow, you will drill down
deeper and learn more about the thorough access to databases provided by BCB.
If you
are writing code rather than working through the Object Inspector, there are
two different ways to open or close a dataset. You can write the following
line of code: Table1->Open(); Or, if
you prefer, you can set the Active property equal to True: Table1->Active = True; There is
no difference between the effect produced by these two statements. The RTL
call to Open, however, ends up setting Active to True, so it
may be ever so slightly more efficient to use the Active property directly. Just as
there are two ways to open a table, there are also two ways to close a table.
The simplest way is to call Close: Table1->Close(); Or, if
you want, you can write the following: Table1->Active = False; Once
again, there is no substantial difference between these two calls. You should
note, however, that Close and Open are functions, while Active is a property. In this
section, you have learned about two methods: void __fastcall Open(void); void __fastcall Close(void); You also
learned about one property: __property System::Boolean Active; It is
definitely worthwhile opening up DB.HPP,
finding the class declaration for TDataSet, and
examining the methods shown here as well as some of the other ones included
in this large object. Remember, most of the rest of this chapter is dedicated
to an examination of TDataSet. Navigational
Routines After
opening a dataset, the next step is to learn how to move about inside it. The
following rich set of methods and properties from TDataSet provides all the tools you need to access any particular record
inside a dataset: void __fastcall First(void); void __fastcall Last(void); void __fastcall Next(void); void __fastcall Prior(void); property System::Boolean Bof; property System::Boolean Eof; System::Integer_fastcall MoveBy(System::Integer
Distance); Experienced programmers will find these functions very easy to use. Here is a quick overview of their functionality:
Most of
these properties and methods are demonstrated in the sample program found on
the CD-ROM accompanying this book as Navy.dpr. You can open this example directly, or construct it piece by piece
by following the description that follows. To get
started using these navigational routines, you should perform the following
steps: 1. Place a TTable, TDataSource, and TDBGrid on a form. If you
are having trouble completing these steps, refer to Navy.dpr. If you
run a program that contains a TDBGrid control, you will find that you
can iterate through the records in a dataset by manipulating the scrollbars
on the edges of the grid. You can gain the same functionality by using the TDBNavigator component. However, there are times when you
want to move through a table programmatically, without the use of the
built-in visual tools. The next few paragraphs explain how this process
works. Place
two buttons on a form and label them Next and Prior, as shown in Figure 9.2.
void __fastcall TForm1::bbNextClick(TObject *Sender)
{
BioLife->Next(); } Perform
the same action with the Prior button, so that the function associated with
it looks like this: void __fastcall TForm1::bbPriorClick(TObject
*Sender) {
BioLife->Prior(); } Now run
the program and click the two buttons. You will find that they easily let you
iterate through the records in a dataset. Now drop
down two more buttons and label them First and Last, as shown in Figure 9.3.
void __fastcall TForm1::bbFirstClick(TObject
*Sender) {
BioLife->First(); } void __fastcall TForm1::bbLastClick(TObject *Sender)
{
BioLife->Last(); } Nothing
could be more straightforward than these navigational functions. First takes you to the beginning of a dataset, Last takes you to the end, and the Next and Prior functions move you one record forward or backward. Checking for
the End or Beginning of a Dataset TDataSet's Bof is a read-only Boolean property used to check whether or not you are at the beginning of a dataset. The Bof property returns True on three occasions:
The
first two items listed should be obvious. Specifically, when you open a
dataset, BCB places you on the first record, and when you call First, BCB again moves you to the beginning of the
dataset. The third item, however, requires a little more explanation: After
you have called Prior enough times to get to the
beginning of a file, and have then tried one more time and found that the
call failed, Bof will return True. The
following code shows a common method for using Prior to get to the beginning of a file: void __fastcall TForm1::bLastToFirstClick(TObject
*Sender) {
ListBox1->Items->Clear();
tblAnimals->Last(); while
(!tblAnimals->Bof) {
ListBox1->Items->Add(tblAnimals->Fields[0]->AsString);
tblAnimals->Prior(); } } The code
shown here is from the BofAndEof program found on the CD-ROM that comes with
this book. In this case the ListBox Add method is called on the current record and then on every other record
between the end and beginning rows of the dataset. The loop continues until a
call to Table->Prior fails to move you back any
further in the table. At that point Bof returns True and the program breaks out of the loop. If your
dataset is connected to a data source, you can optimize a loop like the one
shown previously by setting DataSource1.Enabled to False before beginning the loop, and then resetting it to True after the loop is finished. These two lines
of code enable you to iterate through the table without having to update the
visual tools at the same time. Everything
said previously about Bof also applies to Eof. In other words, the code that follows
provides a simple means of iterating over all the records in a dataset: void __fastcall TForm1::bFirstToLastClick(TObject
*Sender) {
ListBox1->Clear();
tblAnimals->First(); while
(!tblAnimals->Eof) {
ListBox1->Items->Add(tblAnimals->Fields[0]->AsString);
tblAnimals->Next(); } } The
classic error in cases like this is to enter into a while or repeat loop but to forget to call Table->Next: do {
ListBox1->Items->Add(tblAnimals->Fields[0]->AsString); } while (!tblAnimals->Eof); If you
accidentally wrote code like this, your machine would appear to lock up. You
could break out of the loop only by pressing Ctrl+Alt+ Eof returns True in the following three cases:
NOTE: Iterating through tables as
described in the last few paragraphs is a common process on local databases,
but it is not as popular a technique to use with SQL servers. In particular,
most client/server databases expect you to process individual records, or
sets of records, but not to treat a table as a series of contiguous rows.
This is rather a fine point to put on the discussion at this stage of the
game, but it is something to keep in mind if you intend to move directly into
processing large amounts of data on a server. If you must perform this kind
of operation on a real server, it is probably best to do so from inside a
stored procedure. The last
navigational routine that I want to cover is called MoveBy. MoveBy enables you to move x number of
records forward or backward in a dataset. If you want to move two records
forward, you would write the following: tblAnimals->MoveBy(2); And if
you wanted to move two records backward, you would write this: tblAnimals->MoveBy(-2); When
using this function, you should always remember that when you are working on
a network, datasets are fluid entities, and the record that was five records
back a moment ago may now be back four records, or six records, or who knows
how many records. In other words, when you are on a network, someone on
another machine may delete or add records to your database at any time. If
that happens, MoveBy might not work exactly as you
expect. One solution to this "fluidity" problem is to use the
Bookmark functions mentioned later in this chapter. NOTE: Prior and Next are simple one-line functions that call MoveBy. If you have the source, look up TDataSet.Next in DB.PAS. The TDataSet object is a beautifully written wrapper
around the core functionality in the BDE. After
reading the last two sections, you should have a good feeling for how to move
around in a dataset. The navigational commands you have been learning about
are very easy to use. Take the few moments necessary to be sure you
understand them, because they are likely to be part of your day-to-day BCB
programming experience. Fields On most
occasions when you want to programmatically access the individual fields of a
record, you can use one of the following properties or methods, all of which
belong to TDataSet: __property TField *Fields[System::Integer Index]; __property System::Integer FieldCount; __property System::Variant Value; TField *__fastcall FieldByName(const
System::AnsiString FieldName); The Fields property is one of the most important single
syntactical elements of the VCL. It consists of an array of TField objects, each of which is automatically
assigned to a separate field in a table. For instance, if a table called Address had five fields called Name, Address, City, State and Zip, BCB would automatically create five field
objects when you accessed the Address table. You don't have to do
anything to create these field objects; they are given to you automatically. The Fields array for the hypothetical Address table mentioned in the last paragraph would
have five members. Fields[0] would access the Name field, Fields[1] would access the Address field, and so on. The TField object is declared in DB.HPP. This is an extremely powerful, and fairly
complex object, which is worth your while to study in depth. For instance,
here are some of the methods of the TField object:
The TField object also has a number of useful
descendant classes with names such as TStringField and TIntegerField. These child objects are discussed in
Chapter 17, "Printing: QuickReport and Related Technologies." The FieldCount property returns an integer that specifies
the number of fields in the current record structure. If you wanted a
programmatic way to read the names of these fields, you could use the Fields property: { AnsiString
S(Table->Fields[0]->FieldName); } If a
record had a first field called CustNo, the
preceding code would put the string "CustNo" in the variable S. If you wanted to access the name
of the second field in the example, you could write this: S = Table->Fields[1]->FieldName; In
short, the index passed to Fields is zero-based, and it specifies
the number of the field you want to access, where the first field is number
zero, the second is referenced by the number one, and so on. If you
want to find out the current contents of a particular field from a particular
record, you can use the Fields or FieldByName property, or you could access the entire table as an array of fields.
To find the value of the first field of a record, index into the first
element of the Fields array: S = Table->Fields[0]->AsString; Assuming
that the first field in a record contains a customer number, the code shown
would return a string such as `1021', `1031', or `2058'. If you wanted to access this
variable as an integer value, you could use AsInteger in place of AsString. Similar properties of Fields include AsBoolean, AsFloat, and AsDate. If you
want, you can use the FieldByName function instead of the Fields property: S =
Table->FieldByName("CustNo")->AsString; As used
in the examples shown here, both FieldByName
and Fields return the same data. The two
different syntaxes are used solely to provide programmers with a flexible and
convenient set of tools for programmatically accessing the contents of a
dataset. When in doubt, use FieldByName because it won't be affected if
you change the order of the fields in your table. NOTE: I'll add a note here for S := Table1[`CustNo']; This is obviously a considerable improvement over the FieldByName method. However, this syntax is not supported in BCB. It might
be helpful to take a few moments to discuss a set of routines from the
FieldObject program found on the disk that accompanies this book. These routines
illustrate the most common ways to access the value of a field. The vValueClick routine shows the default means for setting
or getting the value of a field: void __fastcall TForm1::bValueClick(TObject *Sender)
{
ShowMessage(tblOrders->FieldByName("TaxRate")->Value);
} This is
the standard way to get at a field. You use the AsString or AsInteger properties for conversion, but
when you just want to get at the type of a field, you can use the Value property. However, the issue of conversion can
sometimes raise its head at strange moments, so it is helpful to note that
properties such as AsInteger and AsString exist. In the TField object, the Value property is declared as being of type Variant, which means it will handle most type conversions for you
automatically. This is illustrated in the previous example, where the TaxRate field, which is of type Float, is converted for you automatically to a
string. Note, however, that descendants of TField, such as TStringField, may explicitly declare the Value field as an AnsiString: __property
System::AnsiString Value;
NOTE: Remember that Object Pascal treats
properties and methods with the same name differently than C++. In
particular, there is no function or method overloading in Object Pascal. Here are
some other illustrations of how to use the TField object: void __fastcall TForm1::bAsStringClick(TObject
*Sender) {
ShowMessage(tblOrders->FieldByName("TaxRate")->AsString);
} void __fastcall TForm1::bAsIntegerClick(TObject
*Sender) { int i =
tblOrders->FieldByName("TaxRate")->AsInteger;
ShowMessage(static_cast<AnsiString>(i)); } void __fastcall TForm1::bAsFloatClick(TObject
*Sender) { float f =
tblOrders->FieldByName("TaxRate")->AsFloat; ShowMessage(static_cast<AnsiString>(f));
} Three of
the four routines shown here from the FieldObject program produce the same
output. The odd man out in this group is the bAsIntegerClick method, which displays the value as an Integer, rather than as a floating-point number. For
instance, if the value of the TaxRate field in the current record was
4.5, three of the methods shown here would display the string "4.5". The bAsIntegerClick method, however, would display the string as
simply "4". The difference here is simply
due to the fact that the AsInteger property automatically converts a
floating point number to an int. More
Information on the Fields Property The
Fielder program that ships on this book's CD-ROM demonstrates some simple
ways to use the Fields property of TDataSet. If you want to construct the program
dynamically, place a TTable, two buttons, and two list boxes
on a form, as shown in Figure 9.4. Hook up the TTable object to the CUSTOMER table that ships with BCB. Double-click
the Fields button and create a method that
looks like this: void __fastcall TForm1::bbFieldsClick(TObject
*Sender) { int i;
ListBox1->Clear(); for (i = 0;
i < tblCustomer->FieldCount; i++)
ListBox1->Items->Add(tblCustomer->Fields[i]->FieldName); } This
method starts by clearing the current contents of the first list box, and
then it iterates through each of the fields, adding their names one by one to
the list box. Notice that the for loop shown here counts from 0 to one less than FieldCount. If you don't remember to stop one short of the value in FieldCount, you will get a "List Index Out of
Bounds" error, because you will be attempting to read the name of a
field that does not exist. If you
enter the code correctly, you will fill the list box with the names of all the
fields in the current record structure. BCB provides other means to get at
the same information, but this is a simple, programmatic way for you to
access these names at runtime. In the
Fielder example, you can associate the following code with the second button
you placed on the program's form: void __fastcall TForm1::bbCurRecordClick(TObject
*Sender) { int i;
ListBox2->Clear(); for (i = 0;
i < tblCustomer->FieldCount; i++)
ListBox2->Items->Add(tblCustomer->Fields[i]->AsString); } This
code adds the contents of each of the fields to the second list box. Notice
that once again, it is necessary to iterate from zero to one less than FieldCount. The key point here is that the indices to Fields is zero-based. NOTE: Much of the functionality of TField can be achieved with visual tools. In
particular, you can manipulate fields with the Fields Editor, which you can
access by clicking once with the right mouse button on the top of a TTable or TQuery object.
This subject is explored in more depth in Chapter 9. However, good
programmers know how to use both the methods of TDataSet and the Fields Editor. Furthermore, the Fields Editor can be used to
best advantage if you understand how to enhance its functionality with some of
the code you are learning about in this chapter. In this
section and the one previous to it you learned how to access the fields of a
record. In the next section you will see how to use this knowledge when you
want to append, insert, or edit records in a dataset. Changing Data The
following methods enable you to change the data associated with a table: void __fastcall Post(void); void __fastcall Insert(void); void __fastcall Edit(void); void __fastcall Append(void); void __fastcall Delete(void); void __fastcall Cancel(void); All
these routines are part of TDataSet, and they are inherited and used
frequently by TTable and TQuery. The
preceding list is hardly exhaustive. For instance, here are a few more
related methods of TDataSet: void
__fastcall AppendRecord(const TVarRec *Values, const System::Integer
Values_Size); void __fastcall ClearFields(void); void
__fastcall InsertRecord(const TVarRec *Values, const System::Integer Values_Size); void __fastcall FetchAll(void); void __fastcall UpdateRecord(void); void __fastcall ApplyUpdates(void); void __fastcall CommitUpdates(void); void __fastcall CancelUpdates(void); The
point here is not that you need to learn all these methods right away.
Rather, I would concentrate on the first list, the one that include Post, Insert, and so
on. This first list is meant to be a fairly careful selection of the most
commonly used methods. Many programmers will never need to use any other
methods than those in the first list. But it is helpful to know that the TDataSet object has considerable depth, and if you
need customized functionality, you can generally find it in the TDataSet object itself. NOTE: There are, of course, times when
some programmers might need to do something to a database that is not covered
in the VCL. In those cases, your next resort after TDataSet might well be the BDE itself. In particular, you should use the
Borland Database Engine, found in BDE.HPP, or in
a separate package available from Borland. Whenever
you want to change the data associated with a record, you must first put the
dataset you are using into edit mode. As you will see, most of the visual
controls do this automatically. However, if you want to change a table
programmatically, you need to use the functions listed previously. Here is
a typical sequence you might use to change a field of a given record: Table1->Edit(); Table1->FieldByName("ShipToContact")->Value
= "Lyle Mayes"; Table1->Post(); The
first line shown places the database in edit mode. The next line assigns the
string "Lyle
Mayes" to the
field labeled "ShipToContact". Finally, the data is written to
disk when you call Post. See the SimpleEdit program on
the CD-ROM accompanying this book for an example of this code in action. When
looking at that programming, you might note that I have double-clicked the
grid control in order to edit its properties. Working with the grid control
will be explained in more depth later in this chapter. The very
act of moving on to the next record automatically posts your data to disk.
For instance, the following code has the same effect as the code shown
previously, plus it moves you on to the next record: Table1->Edit(); Table1->FieldByName("ShipToContact")->Value
= "Lyle Mayes"; Table->Next(); Calls to
First, Next, Prior, and Last all perform Posts, as long as you are in edit
mode. If you are working with server data and transactions, the rules explained
here do not apply. However, transactions are a separate matter with their own
special rules, as explained in Chapter 15, "Working with the Local
InterBase Server." (Both local tables and SQL servers support
transactions, but I cover this subject in the chapter on the InterBase server
because it is usually considered an intermediate or advanced database topic.)
Even if
you are not working with transactions, you can still undo your work at any
time, as long as you have not yet either directly or indirectly called Post. For instance, if you have put a table into
edit mode, and have changed the data in one or more fields, you can always
change the record back to its original state by calling Cancel. For instance, you can edit every field of a
record, and then call the following line to return to the state you were in
before you began editing: Table->Cancel(); Here is
an excerpt from the SimpleEdit program that shows three button response
methods that enable you to enter data and then cancel or post your changes.
This code is still not robust enough for a shipping programming, but it gives
you a good idea of how to proceed if you don't want to include much error
checking. void __fastcall TForm1::bEditClick(TObject *Sender) { AnsiString
S; if (InputQuery("Enter Contact
Dialog", "Enter Name", S)) {
DMod->tblOrders->Edit();
DMod->tblOrders->FieldByName("ShipToContact")->Value
= S; } } void __fastcall TForm1::bCancelClick(TObject
*Sender) {
DMod->tblOrders->Cancel(); } void __fastcall TForm1::bPostClick(TObject *Sender) {
DMod->tblOrders->Post(); } I say
this code is not really robust. For instance, I do not check to see if the
dataset is in edit or insert mode before calling Post. This kind of error checking is covered later in this chapter, and
several times in the next few chapters. Despite its lack of error checking,
the simple methods shown here should give you a good idea of how to proceed
in your own programs. NOTE: The InputQuery method shown in the bEditClick method is used to retrieve a
string from the user. The first parameter is the title of the dialog used to
retrieve the string, the second the prompt asking for a string, and the third
the string to be retrieved. InputQuery is a Boolean VCL function. It is
usually best to zero out the string passed in the third parameter before
showing it to the user. There
are also cancel, edit, and insert buttons on the TDBNavigator control. You can use this control to work with
a dataset without having to write any code. There
are two methods, called Append and Insert, which you can use whenever you want to add another record to a
dataset. It obviously makes more sense to use Append on datasets that are not indexed, but BCB won't throw an exception if
you use it on an indexed dataset. In fact, it is always safe to use either Append or Insert
whenever you are working with a valid dataset. On your
disk you will find a simple program called Inserts, which shows how to use
the Insert and Delete commands. To create the program by hand, first use a TTable, TDataSource,
and TDBGrid to open up the COUNTRY table from the demos subdirectory. Then
place two buttons on the program's form and call them Insert and Delete. When
you are done, you should have a program like the one shown in Figure 9.5. NOTE: To spice up the Inserts program,
you can drop a panel on the top of the form and then add the buttons to the
panel. Set the panel's Align property to alTop, and set the TDBGrid's Align property to alClient. If you run the program, you can then
maximize and resize the form without damaging the relationship between the
various visual components. The next
step is to associate code with the Insert button: void __fastcall TForm1::FInsertClick(TObject
*Sender) {
FCountry->Insert();
FCountry->FieldByName("Name")->Value =
"Erehwon";
FCountry->FieldByName("Capital")->Value =
"Nowhere";
FCountry->FieldByName("Continent")->Value =
"Imagination";
FCountry->FieldByName("Area")->Value = 0; FCountry->FieldByName("Population")->Value
= 0;
FCountry->Post(); } The
function shown here first puts the table into insert mode, which means that a
new record filled with blank fields is automatically inserted into the
current position of the dataset. After
inserting the record, the next job is to assign strings to one or more of its
fields. There are, of course, several different ways to enter this data.
Using the current program, you could simply type the information into the
current record in the grid. Or, if you wanted, you could place standard edit
controls on the form and then set each field equal to the value the user has
typed into the edit control: Table1->FieldByName(`Name')->Value =
Edit1.Text; If you
place a table in edit mode, or if its TDataSource object has AutoEdit set to True, you can use data-aware controls to insert information directly into
a record. The
intent of this chapter, however, is to show you how to enter data
programmatically. Therefore you are presented with an example in which
information is hardwired into the code segment of the program: FCountry->FieldByName("Name")->Value
= "Erehwon"; NOTE: One of the interesting (or perhaps
"frustrating" would be a more appropriate word) byproducts of this
technique is that pressing the Insert button twice in a row automatically
triggers a "Key Violation" exception. To remedy this situation, you
must either delete the current record or manually change the Name and Capital fields of the newly created record. void __fastcall TForm1::FInsertClick(TObject
*Sender) {
FCountry->Insert();
FCountry->FieldByName("Name")->AsString =
"Erehwon";
FCountry->FieldByName("Capital")->AsString =
"Nowhere"; FCountry->FieldByName("Continent")->AsString
= "Imagination";
FCountry->FieldByName("Area")->AsInteger = 0;
FCountry->FieldByName("Population")->AsInteger = 0;
FCountry->Post(); } Instead
of calls to AsString and AsInteger, you can just use Value. Looking
at the code shown previously, you will see that the mere act of inserting a
record and of filling out its fields is not enough to change the physical
data that resides on disk. If you want the information to be written to disk,
you must call Post. After
calling Insert, if you change your mind and
decide to abandon the current record, you should call Cancel. As long as you do this before you call Post, everything you have entered is discarded,
and the dataset is restored to the condition it was in before you called Insert. One last
related property that you should keep in mind is called CanModify. A table might be set to ReadOnly, in which case CanModify would return False. Otherwise CanModify returns True and you can enter edit or insert mode at will. CanModify is itself a read-only property. If you want
to set a dataset to read-only, you should use the ReadOnly property, not CanModify. In this
section, you have learned how to use the Insert, Delete, Edit, Post, Cancel, and Append commands. Most of the actions
associated with these commands are fairly intuitive, though it can take a
little thought to see how they interact with the Fields property. Using SetKey
or FindKey to Search through a File If you
want to search for a value in a dataset, you can call on five TDataSet methods, called FindKey, FindNearest, SetKey, GotoNearest, and GotoKey. These routines assume that the field you are searching on is
indexed. You should note that the BCB includes two methods for searching for
values in a table. The SetKey, GotoNearest, and GotoKey methods comprise one technique,
and FindKey and FindNearest comprise a second technique. I discuss both techniques in the next
few paragraphs. This
book's CD-ROM contains a demonstration program called Search that shows how
to use these calls. You should open up this program and run it once to see
how it works, or else follow the steps shown here to create the program
yourself. NOTE: There is actually a third method
for searching for values in a table. I demonstrate that technique in the
upcoming section from this chapter called "Filtering with the OnFilterRecord Event." The technique in question uses
a series of routines called FindFirst, FindLast, FindNext and FindPrior. Unlike the routines used in this section, FindFirst and the like are not tied to the index fields of your table. To
create the Search program, place TTable and TDataSource objects on a data module, and TDBGrid, TButton, TLabel, and TEdit controls on a form. Arrange the visual controls so the result looks
like the image shown in Figure 9.6. Be sure to set the caption of the button
to Search, and then to wire the database controls so you can view the Customer table in the grid control.
In this
program, I observe more carefully the rules of object-oriented programming.
In particular, I create a data module, store the non-visual database tools on
it, and then create methods for manipulating the data within the data module
itself, rather than inside the object for the main form. This has only
theoretical benefits in a simple program like Search. However, in a more
complex program, this shows the way to create a single data module--with some
built in rules and functionality--that can be reused in multiple programs. It
also shows how to create programs that are easy to maintain, and easy to
understand. In
particular, the main form contains a method response routine for the Search
button that looks like this: void __fastcall TForm1::bSearchClick(TObject
*Sender) {
DMod->Search(Edit1->Text); } In its
turn, the data module has a search method that looks like this: void TDMod::Search(AnsiString S) {
tblCustomer->SetKey();
tblCustomer->Fields[0]->AsString = S; tblCustomer->GotoKey();
} As you
can see, the TDMod object protects its data and
exposes its functionality to the outside world through a single easy-to-call
method. This way, TDMod could totally change the internal
technique it has for searching through a table without forcing you to modify
the TForm1 object in any way. The
first call in this function sets Table1 into
search mode. BCB needs to be told to switch into search mode simply because
you use the Fields property in a special way when
BCB is in search mode. Specifically, you can index into the Fields property and assign it to the value you want
to find. In the
example shown here, the CustNo field is the first column in the
database, so you set the Fields index to zero. To actually carry
out the search, simply call Table->GotoKey(). GotoKey is a Boolean function, so you could write code that looks like this: if (!Table->GotoKey)
DatabaseError("Error searching for data!"); The DatabaseError routine might raise a database exception of
some kind. If you
are not sure of the value you are looking for, call Table->GotoNearest. GotoNearest
will take you to the numerical or string value closest to the one you
specify. The FindKey and FindNearest routines perform the same function GotoKey or GotoNearest, but they are much easier to use.
Here, for instance, is the technique for using FindKey: tblCustomer->FindKey(OPENARRAY(TVarRec, (S))); In this
case, S is assumed to be a valid AnsiString containing a CustNo for which you want to search. Here's
how FindNearest looks: tblCustomer->FindNearest(OPENARRAY(TVarRec,
(S))); When
using FindKey or FindNearest there is no need to first call SetKey or to use the FieldByName property. (Needless to say,
internally FindKey and FindNearest end up calling SetKey and FieldByName, before making calls to either GotoKey or GotoNearest.) FindKey and FindNearest take an array of values in their sole parameter. You would pass
multiple parameters to FindKey or FindNearest if you have a table that was indexed on multiple fields. In this
case, the Customer table has a primary index on one
field, called CustNo. But if it had multiple fields in
the primary index, you could specify one or more of the values for these
fields in this parameter: tblCustomer->FindNearest(OPENARRAY(TVarRec, (S1,
S2, S3))); This is
one of those cases where Object Pascal provides a built in, easy-to-use
method for working with a dynamically created array of values, while there is
no such native type in C++. C++, of course, is nothing if not flexible, and
it is rare that you cannot find a way to make the language conform to your
desires. For instance, in this case, one simple way for C++ to accommodate
the VCL's request for a dynamically constructed array of values is by using
the OPENARRAY macro and its associated template
class from Sysdefs.h. If you
are not searching on the primary index of a file, you must use a secondary
index and specify the name of the index you are using in the IndexName property for the current table. For
instance, the Customer table has a secondary index
called ByCompany on the field labeled Company. You would have to set the IndexName property to the name of that index if you
wanted to search on that field. You could then use the following code when you
searched on the Company field: void TDMod::CompanySearch(AnsiString S) {
tblCustomer->IndexName = "ByCompany";
tblCustomer->FindNearest(OPENARRAY(TVarRec, (S))); } Remember:
This search will fail unless you first assign the correct value to the IndexName property. Furthermore, you should note that IndexName is a property of TTable and would therefore not automatically be included in any direct
descendant of TDataSet or TDBDataSet that you might create yourself. In particular, it's not part of TQuery. Indeed, none of the functions discussed in
this section are part of TQuery. The
previous code is flawed, or at least could be flawed in some cases, in that
any attempt to search on the CustNo field after a call to CompanySearch would fail, because the table would no
longer be indexed on the CustNo field. As a result, you might
want to save the old index in a temporary variable so it can be restored at
the end of the function: void TDMod::CompanySearch(AnsiString S) { AnsiString
Temp(tblCustomer->IndexName);
tblCustomer->IndexName = "ByCompany";
tblCustomer->FindNearest(OPENARRAY(TVarRec, (S)));
tblCustomer->IndexName = Temp; } A neat
trick you can use with the FindNearest method involves performing an
incremental search across a table. Start a new project and get things rolling
quickly by simply dragging the Country table off the Database Explorer
and onto Form1. You will end up with a TTable, TDataSource,
and TDBGrid on the form, with the TTable hooked up to the Country table. Put a panel on the top of the form and set its Align property to alTop. Set the Align property of the TDBGrid for the Country table to alClient. Place a TEdit component on the panel and create an OnChange event with the following code attached to
it: void __fastcall TForm1::Edit1Change(TObject *Sender)
{
tblCountry->FindNearest(OPENARRAY(TVarRec, (Edit1->Text))); } Run the
program. When you type into the edit control, you will automatically begin
incrementally searching through the table. For instance, if you type C, you will go to the record for The
incremental search example is available on disk as the IncrementalSearch
program. It's perhaps worth pointing out that this program is interesting in
part because it shows how you can use the built-in features of BCB to easily
implement additional features that were never planned by the developers. For
instance, there is no Incremental Search component in BCB. However, if you
need to build one, the tools come readily to hand. Filtering the
Records in a Dataset with ApplyRange The next
two sections cover two different ways to filter data. Of these two
techniques, the second is preferable on several counts; so if you are in a
hurry, you can skip this section. The key difference between the two methods
is that the one explained in this section uses keyed fields, while the next
technique will work on any type of field. The technique used in the next
section is very highly optimized, but the conservatives in the crowd might
find the technique outlined in this section appealing because it relies on
indexes, which are a tried and true database technology, guaranteed to
execute in a highly optimized manner. The ApplyRange function lets you set a filter that limits
the range of the records you view. For instance, in the Customers database, the CustNo field ranges from just over 1,000 to a little under 10,000. If you
wanted to see only those records that had a customer number between 2000 and
3000, you would use the ApplyRange method from TTable and two related routines. When using this
method, you must work with a field that is indexed. (As explained in the next
chapter, you can perform this same type of operation on a nonindexed field by
using a TQuery object rather than TTable object. You can also use the technique
explained in the next section of this chapter for searching on non-keyed
fields.) Here are
the four methods of TTable that make up the suite of
routines you will use when setting up filters: void __fastcall ApplyRange(void); void __fastcall SetRangeEnd(void); void __fastcall SetRangeStart(void); void __fastcall CancelRange(void); To use
these functions, perform the following steps: 1. Call SetRangeStart and then use the Fields property to designate the start of the range. The
Ranger program, which is located on the CD-ROM that comes with this book,
shows you explicitly how to use these functions. To create the program, drop
a TTable, TDataSource, and TDBGrid onto a form. Wire them up so that
you can view the CUSTOMERS table from the demos
subdirectory. You need to set Table->Active to True. Next, drop two labels on the form and set their captions to The SetRangeStart and SetRangeEnd functions enable you to declare the first and last members in the
range of records you want to see. To get started using the functions, first
double-click the button labeled SetRange, and
then create a function that looks like this: void __fastcall TForm1::bbSetRangeClick(TObject
*Sender) {
DMod->SetRange(StrToInt(eStart->Text), eEnd->Text.ToInt()); } This
code shows two different ways to translate a string value into an integer
value. The first technique calls a stand-alone VCL method named StrToInt, the second technique uses a method of the AnsiString class. Once
again, the TForm1::bbSetRangeClick function merely calls a method of
TDMod that does all the real work: void TDMod::SetRange(int AStart, int AnEnd) {
tblCustomer->SetRangeStart();
tblCustomer->Fields[0]->AsInteger = AStart;
tblCustomer->SetRangeEnd();
tblCustomer->Fields[0]->AsInteger = AnEnd;
tblCustomer->ApplyRange(); } The TDMod::SetRange function first calls SetRangeStart, which puts the table into range mode and
blanks out the records seen in the TDBGrid
control. Once in range mode, the program next expects you to specify the
beginning range, which in this case you grabbed from the first edit control.
Setting the end range for the program involves following a similar pattern.
First you call SetRangeEnd, and then you pass it an
appropriate value culled from the second Edit control back on the main form. Note
that you can use the Fields property to specify the actual
range you want to use: Table->Fields[0]->AsInteger = AStart; This use
of the Fields property is obviously a special
case, since the syntax shown here is usually used to set the value of a
field, not to define a range. This special case comes into effect only after
you have put Table1 into range mode by calling SetRangeStart. The
final step in the function just shown is the call to ApplyRange. This is the routine that actually puts your request into effect.
When the call to ApplyRange ends, the table is no longer in
range mode, and the Fields property returns to its normal
functionality. If you
want to undo the results of a call to ApplyRange, you can call the CancelRange function: Table->CancelRange(); A
typical run of the program might involve the user typing in the number So far,
you have learned how to filter the data from a table so that you view only a
particular range of records. The steps involved are threefold: 1. Call SetRangeStart and specify the beginning value in the range
of records you want to see. BCB also
provides a shorthand method calling the SetRangeStart, SetRangeEnd,
and ApplyRange methods. This technique is
featured in both the Ranger and Ranger2 programs. The conceit behind the
Ranger2 program is to place a Windows 95 track bar control at the top of the
form, and to enable the user to move the thumb on the track bar in order to
select a range of values to view: void __fastcall TForm1::TBarChange(TObject *Sender) { int i =
TBar->Position * 1000; int j = i +
1000;
DMod->SetRange(i, j); } TBarChange is called whenever the thumb on
the track bar moves. The valid range of values on the track bar are 1 to 10.
The code multiplies current position on the track bar times 1,000 to get the
start of the range, and adds 1,000 to this value to get the end range. The
theory here is that the track bar measures the range in increments of 1,000. The TBarChange method calls the SetRange method of TDMod: void TDMod::SetRange(int AStart, int AFinish) { VMS Desenvolvimentos
Diversas Dicas, Apostilas, Arquivos Fontes,
Tutoriais, Vídeo Aula, Download de Arquivos Relacionado a Programação em C++
Builder.
|