Approved

Head of Department

Candidate of Technical Science,

Professor of Russian Academy

of Natural Sciences

___________ V.V.Serbin

«_____» ______________________ 2016

DIPLOMA PROJECT

“Creating the application for automation the area of parasitic diseases (trematodiases) in Kazakhstan: development of a database on the research results of saiga and sheep”

Major 5B070300 - Information System

«___» _________ 2016 ___________

Almaty 2016

International Information Technology University

Faculty of Information Technologies

Department of IS

Major 5B070300 - Information Systems

Chapter Twelve Diploma Project Assignment

Student

Kerimbaev Madi Muratovich

Diploma paper (project) topic

Creating the application for automation the area of parasitic diseases (trematodiases) in Kazakhstan: development of a database on the research results of saiga and sheep

Approved by IITU order № ___ dated

«____» _______ 2016

Diploma paper submission date: __________________

Diploma paper initial data:

MS Office, Microsoft Office PowerPoint

Details of computations and explanations (list of issues due to be addressed):

- analysis of the conditions;

- learning of the existing programs;

- choosing technologies and tools in order to realize the project

- explaining of the methodology of the development;

- completing of the economic and labor parts;

- practical value of the project and conclusion;

CD containing the digital version of diploma paper and attachments

- Diploma project

- Reviews on diploma project

- Presentation

Consultations on diploma project (with related project chapters name)

Date received ____________

Research advisor:

Kurenkeeva Dariyash Turdalievna ________________

Received by:

Kerimbaev M.M ________________

Almaty 2016

International Information Technology University

DIPLOMA PROJECT WRITING SCHEDULE

Group IS 124K Course 4

Student: Kerimbaev Madi Muratovich

Topic: Creating the application for automation the area of parasitic diseases (trematodiases) in Kazakhstan: development of a database on the research results of saiga and sheep

Student:

АҢ ДАТПА

Borland Delphi 7.0 тілін қ олданып, Access Берілгендер Базасын Басқ ару Жү йесінде берілгендер базасы жасалынды. Ол ү й жануарларының гельминтоздармен заладануының эпизоотикалық жағ дайын кө рсетеді, жә не Жамбыл ғ ылыми-зерттеу ветеринария станциясының ғ ылыми деректеріне негізделген. Базадағ а ақ паратты қ олдануғ а ың ғ айлы интерфейсте 11 кө рсеткіш бойынша қ адағ алауғ а болады. Екі кө рсеткіштің берілгендері автоматты тү рде стандартты математикалық тә сілдермен ө ң деледі. Басқ а да ары қ арай графикалық жә не басқ а да ө ң деулер ү шін программада таң далғ ан берілгендер массивтерін MS Excel кө шіру мү мкіндігі қ арастырылғ ан.

АННОТАЦИЯ

Разработана база данных на СУБД Access с использованием языка Borland Delphi 7.0. Она отражает эпизоотическую ситуацию по гельминтозам домашних животных и содержит данные исследований Жамбылской научно-исследовательской ветеринарной станции. Информация отображается в виде удобного пользовательского интерфейса по одиннадцати показателям, результаты по двум из которых автоматически обрабатываются стандартными математическими методами. В программе предусмотрена возможность переноса отобранного массива данных в MS Excel для дальнейшей графической и прочей обработки.

ABSTRACT

A database on the Access database using language Borland Delphi 7.0. It reflects the epizootic situation on helminthiasis domestic animals and provides data research Zhambyl research veterinary station. The information is displayed in a user-friendly user interface for the eleven indicators, the results on two of which are automatically processed by standart mathematical methods. The program provides an opportunity to transfer selected array of data in MS Excel for further processing and other graphics.

СONTENT

LIST OF TERMS AND ABBREVIATIONS…………………………………….…11

INTRODUCTION………………………………………………………………......12 1. LITERATURE REVIEW……………………………………………………..…..14

1.1 Programming Technology in Delphi 8……………………………………….....14

1.1.1 Using Delphi together with ADO and MS Access ………………………...…14

1.2 Basic programming languages ​ ​ used in the development of the database.............16

1.2.1 Property SQL..17

1.2.2 TQuery and Parameters..19

1.2.3 Passing parameters through TdataSource. 20

1.2.4 Implementation of joining multiple tables..21

1.3 Class DataSet…………………………………………………………………....23

1.3.1 Navigation (Navigate through the records)…………………………………...25

1.3.2 Fields………………………………………………………………………….25

1.4 Working with Data…………………………………………………………..….27

1.4.1 Update (Refresh)……………………………………………………………...28

1.4.2 Management TDBGrid at runtime…………………………………………....29

2.ECONOMIC RATIONALE OF THE PROJECT…………………………….….31

2.1 Economic efficiency………………………………............................................31

2.2 Calculating of the developed program cost……………………………….…….32

2.3 Calculation of material costs……………………………………………..……..32

2.4 Calculation of the developer’s salary………………………………………..….33

3. DEVELOPING THE PROGRAM THAT CREATES A DATABASE…………34

3.1 Structure of the developed database……………………………………………34

3.2 The main editing window………………………………………………………35

3.3 Main Window…………………………………………………………………..38

3.4 Data output in Excel…………………………………………………………….40

3.5 Programming the mathematical processing of information in the database……42

3.5.1 Programmable mathematical operations……………………………...............42

3.5.2 Code calculation of averages………………………………………………….46

3.5.2.1 Coefficient of variation..43

3.5.3 The standard deviation. 44

3.5.4 Accuracy. 44

4. PROTECTING PROGRAMS FROM UNAUTHORIZED ACCESS…………...47

4.1 Protection programs written in Delphi 7 PLO………………………………….47

4.2 Burglary protection Access databases……………………………………….….53

5. SAFETY PRECAUTIONS WHEN WORKING ON THE COMPUTER.. 59

5.1 Observing of Erg-Exercises………………………………………………......60

CONCLUSION.. 62

REFERENCES. 64

Appendix A $R *.dfm………………………………………………………............66

Appendix B $R *.dfm……………………………………………………………....75

LIST OF TERMS AND ABBREVIATIONS

FileMon - File Monitor

API- Application programming interface

DBMS – Database management systems

ER – Entity–relationship

HTML – Hyper Text Markup Language

IIS – Internet Information Services

IITU – International Information Technologies University

IRR-Internal rate of return

MVC – Model–view–controller

MS – Microsoft

NET – a software framework developed by Microsoft that runs

primarily on Microsoft Windows

OS – Operating System

SQL – Structured Query Language

UML – Unified Modeling Language

XML – Extensible Markup Language

XP – Extreme Programming

XAMPP – is APACHE distribution containing MySQL, PHP andPerl

INTRODUCTION

Zhambyl Research Veterinary Station (Nieves) conducts applied research aimed at developing better tools and methods of diagnosis, treatment and prevention of infectious diseases, invasive and non-contagious animal disease etiology, and environmental protection.In carrying out research in Nieves found that in all types of farms of Zhambyl region livestock with rather high rates infected with worms of various taxonomic ranks, including trematodes, which form in animals and cause a variety of associations significant economic damage to the region's economy. The highest infestation press on pastures around the settlements has a population of farm animals small farms that do not have the ability to adhere to transhumance technology.Over the years, research in the enterprise has accumulated a wealth of information on the contamination of farm animals with helminths. Data processing in the routine work with different data occupies most of the time resource to researchers, therefore automation of this work is undeniable urgency.So in front of us was given the task to develop the software automate the processing of scientific data Nieves with intuitive user interface. Of course, there are standard staisticheskih processing program within the available for the ordinary user's operating system, for example, MS Excel. However, to accomplish the task given the possibility of the system is insufficient. To perform this work found it possible to stop the choice on OOP Delphi, because it fully satisfies the task. It is impossible today without the databases present the work of the majority of financial, industrial, commercial and other organizations. Streams of information circulating in the world that surrounds us, is huge. In time, they tend to increase. Without the database, we would have long ago drowned in information avalanche. Databases allow information to structure, store and retrieve optimal for the user.Since the use of the database is the foundation on which is built the existence of different organizations, close attention to the developers of database applications produce tools with which these applications can be created. Put forward to them the requirements in general terms can be defined as: " speed, simplicity, efficiency and reliability." At the heart of this avowed Delphi popularity is the fact that Delphi, like any other programming system, it satisfies the above requirements. Indeed, applications developed with Delphi quickly and developer interaction with Delphi interactive environment does not cause internal rejection, but rather gives you a feeling of comfort. Delphi-applications are effective, if the developer complies with certain rules (and often - if not in compliance). These applications are reliable and operation have predictable behavior.Delphi only seems easy, because many " pitfalls" are hidden from the developer. However, the more you study it, the more it becomes clear the depth of which is both a matter of respect, and frightening. Only with time comes understanding that writing is really powerful and functional applications require constant study of Delphi.Under the database (DB) understand the repository of structured data, and the data must be consistent, minimum redundancy and integrity.Typically, the database is created to store and access data, containing information about a certain subject area, that is a field of human activity or the area of ​ ​ the real world. Every database should be a system of data on the subject area. Databases related to the same domain, in various cases include more or less detailed information about it. The level of detail is determined by a number of factors, primarily the purpose of the use of information from the database and the complexity of the production (business) processes that exist within the subject area in specific conditions.Relational databases are connected between a set of database tables (LDP).The relationship between the tables may be reflected in the data structure, and can only be inferred, that is present at unformalized level.Each database table is represented as a set of rows and columns, where the rows correspond to the object instance, a particular event or phenomenon, and columns - attributes (attributes, characteristics, parameters) of objects, events, phenomena.Databases are considered to be a major asset Delphi. Although the language was not created specifically for this area, but the implementation working with data are quite large. Even specialized languages ​ ​ to work with the database (such as MS Visual FoxPro) is clearly inferior to the simplicity and power of this type of programming applications.

1. LITERATURE REVIEW

Delphi - an imperative, structured, object-oriented programming language with a strict statically typed variables.

1.1 Programming technology in Delphi

The main area of use - writing application software.

1.1.1 Using Delphi together with ADO and MS Access This project used MS Access database that have been linked through ADO components. Using Delphi together with ADO and MS Access is not accidental. For easier understanding was prepared a small sample - It is a primitive database Access 2000, consists of two tables, the following structure (Figure 1.1):

Figure 1.1 - Example of tables Access

The first table - imaginary phone subscribers - it has four fields - auto-incrementing the subscriber code, name, telephone number and access code for long-distance lines - by default this is set to 8.

The second table is used to store information about the imaginary long-distance calls of subscribers - is stored the date and time of call, duration, dialed the city code and the number dialed.

Table of subscribers connected with calls AbonentID field table relation one to many [1].

In this part of the work will be only the first of the tables.

ADO always work on the principle of client / server - even if the local table is opened on your computer. This can sometimes be confusing if you do not understand what is happening.

Let the program opens only one table - Abonents - and allows the user to add new subscribers and make corrections.

Once a table is opened Abonents than the data itself, retrieved from a database and more metadata. Metadata - it is " data about data" ie names of the tables, fields, and most importantly, information about which of the fields are key.

This information (received metadata) - allow engine ADO (ADO cursor engine) to convert a user's SQL commands sent to the server.

Suppose the user has added subscriber.

On the server will be sent about this team:

INSERT INTO Abonents... a list of field names... my values ​ ​...

And now a user has corrected the name of one of the subscribers.

On the server that something will go like this:

Update Abonent Set Name = 'Sidorov' where AbonentID = 5.

Now it becomes clear why the needs of the field with a primary key - otherwise ADO engine would not have a clue - what he found on the server line, which should be changed.

Cursor engine knows that the primary key tables Abonents - This field AbonentID, through metadata, which were obtained when the table is opened.

And if the primary key is not present, then the cursor engine attempts to find a string, including Where the section all the others, are not key fields, and if this is not one line is changed and a few (say in the database were namesakes with the same phone number) - you get a warning about that changed a few lines instead of one.

So far, everything is fine, but suppose you - is not the only user of a database, and someone else had to change the data to you. What will happen?

After all, if a team

Update Abonent Set Name = 'Sidorov' where AbonentID = 5.

Simply record the version of your changes on top of another. But first a few words - that kind of dynamic properties such. They are dynamic because they can be changed after the ADO recordset is already open, access them by name through the Properties property, like so:

ADODataSet1.Properties [ 'property name'] Value:. = < New value of the property>

So, the first property is called Update Criteria - it can take the following values ​ ​ (Table 1.).

The server returns a Cursor Engine in response to a number - the number of rows that were affected by the command (number of rows affected).

If this table is not equal to one - the system considers that there was a conflict associated with optimistic locking contention when updating and displays an error message «Row can not be located for updating, etc."

Table 1.1 - Properties Update Criteria

Database developers SQL Server is often used so-called triggers - specific SQL commands that can be triggered when you add or edit data in a table. As a result, the program can get the value of number of rows affected is not following commands, and then the team running the trigger. And if the team worked and successfully changed the record, but the trigger is executed some other team that did not change any entries, then the value comes number of rows affected is equal to zero, and an error message pops up that really was not. You can offer this output option - all triggers are always the first line write the command SET NOCOUNT ON - this will cause SQL Server does not count the number of rows affected by a trigger commands - and you thus'll get the correct value of rows affected [2].

The next property is called Update Resync. Sometimes there are situations when the server itself adds something to the newly entered data. The most common case - in line has a field of type Identity (auto-incremented) - whose value is set by the server. Other cases - fields with default values ​ ​ (if such a field does not assign - its value will be set by the server), field type TimeStamp (at each change of line in this field, the server records the time of the change up to milliseconds). In addition, the record that you add or update can be triggered to change the trigger.

Look at our plate Abonents. She has a field with a default value.

If it works this scenario:

- The user adds a new subscriber, without specifying the access code for long distance line

- Server enter in the Code field of the new record value of 8, which our program knows

- The user remembers that he forgot to introduce non-standard access code for long-distance line, and edit the newly-added record, inscribed in the Code field value of 9

Since the value of the Update Criteria defaults to adCriteriaUpdCols, it will be sent to the server type the command:

Update Abonents Set Code = '9' where AbonentID = 15 AND Code IS NULL

That's right - the key field is transmitted and the original value of the field that you want to change, but that the initial value must be equal to 8! The program does not know that the server has entered the default - as a result we get the error message " Can not find the row for renovation. Some values ​ ​ may have been changed since the last reading time. " Some Delphi programmers offer to update the entire data set after each change - it is not very elegant way - because you need to update only one line!

For this purpose the intended property and Update Resync () (Tab. 2.1.1.1). Here is a list of its possible values:

Table 1.2 - Property Update Resync

So, the default value is accepted adResyncAutoIncrement - that means that after adding each entry ADO engine issue select @@ IDENTITY team - and update the value of the corresponding field on the client, displaying the ID value newly added rows.

As a result, immediately after the addition of new subscriber cursor engine first finds the value AbonentId which received the newly added line, according to the line server, using the value obtained AbonentId - so we immediately see all the changes that the server has made on our new line, and reopen the data set is not it needs.

1.2 Basic programming languages ​ ​ used to develop database

SQL reduction means Structured Query Language - Structured Query Language, and is usually pronounced either as " Sequel" or " Ess Qu El" But how would you like to any pronounced, SQL -. Is a powerful language database, which is easily accessible from the Delphi, but which It differs from Delphi native language. Delphi can use SQL statements to view the tables to perform a join between tables, create a one-to-many [2], or perform almost any action that can make your main database. Delphi tools comes with the Local SQL, so that you can execute SQL queries when working with local tables, without access to the SQL server.

Delphi provides support for " pass through SQL", this means that you can make SQL statements and send them directly to Oracle servers, Sybase, Inrterbase and others. " Pass through SQL" - is a powerful mechanism for two reasons:

- Most servers can handle SQL queries very quickly, which means that using the SQL statement to the remote database, you will receive an answer very quickly.

- It is possible to make SQL queries, which will force the server to perform specialized tasks that are not available through the native Delphi language.

You can create a SQL query using a TQuery component as follows:

- Assign Alias ​ ​ (Alias) DatabaseName.

- Use the SQL property to enter an SQL query like

" Select * from Country".

- Set the Active property to True

If the call goes to the local data, instead of an alias, you can specify the full path to the directory where the tables reside.

1.2.1 SQL property

Property SQL - probably the most important part of the TQuery. Access to this property is either via the Object Inspector during construction of the project (design time), or programmatically at run time (run time) [3].

Interestingly, of course, access to the SQL property at runtime to dynamically change the request. For example, if you need to perform three SQL query, it is not necessary to place three TQuery component on the form. Instead, you can place one and just change the SQL property three times. The most effective, simple and powerful way - make it through parameterized queries that will be explained in the next section. However, first we examine the basic features of the SQL property, and then look at more advanced topics, such as queries with parameters.

SQL property is the TStrings type, which means that this number of rows stored in the list. The list also acts as the array, but, in fact, is a special class with its own unique features. The next few paragraphs are considered the most commonly used properties.

When you programmatically use the TQuery, it is recommended to close the current request, and to clear the list of strings to the SQL property:

Query1.Close;

Query1.SQL.Clear;

Please note that you can always " safe" call Close. Even if the request has already been closed, an exception will be generated.

The next step - to add new rows to the query:

Query1.SQL.Add ('Select * from Country');

Query1.SQL.Add ('where Name =' 'Argentina' '');

The Add method is used to add one or more rows to the SQL query. The total amount is limited only by the amount of memory on your machine. Чтобы Delphi has fulfilled the request and returned pointer containing the result as a table, you can call the method:

Query1.Open;

Demonstration program THREESQL shows this process. The program THREESQL uses especially local SQL, which allows you to use search patterns insensitive (case insensitive). For example, the following SQL query:

Select * form Country where Name like 'C%'

returns the DataSet, containing all the records where the field Name begins with 'C' letter [4].

This query allows you to see all the countries in the title of which is found

the letter 'C': Figure 1.2:

Figure 1.2 - THREESQL program shows how to make multiple requests from a single object TQuery.

Select * from Country where Name like '% C%';

Here's a query that finds all the country, the name of which ends with 'ia':

Select * from Country where Name like '% ia';

One of the useful features of the SQL features - the ability to read files containing the text of the query directly from the disk. This feature is shown in THREESQL program.

Here's how it works. In the directory examples in this lesson, there is a file with the extension SQL. It contains the text of a SQL query. THREESQL program has a button called Load, which allows you to choose one of these files and run the SQL query that is stored in this file.

Load button has the following method to the OnClick event:

procedure TForm1.LoadClick (Sender: TObject);

begin

if OpenDialog1.Execute then

with Query1 do begin

Close; SQL.LoadFromFile(OpenDialog1.FileName);

Open;

end;

end;

LoadClick method first loads OpenDialog component and allows the user to select a file from SQL extension. If a file is selected, the current request is closed, the chosen file is loaded from disk into Saint of the SQL, the query is executed and the result is shown to the user.

1.2.2 TQuery and Options

Delphi allows you to create " flexible" form of inquiry, called a parameterized query. These requests allow to substitute the value of the variable instead of individual words in the expressions " where" or " insert". This variable can be changed at almost any time. (If you are using local SQL, you can make the change almost any word in the statement SQL, but the same is not supported by most servers.) [3]

Before you start to use parameterized queries, consider again one of the above-mentioned proposals simple SQL:

Select * from Country where Name like 'C%'

You can make this statement in a parameterized query by replacing the right side of the variable NameStr:

select * from County where Name like: NameStr

In this proposal, SQL, NameStr not a predetermined constant and may vary either during design or at run time. SQL parser (a program that parses the text of the request) realizes that he is dealing with a parameter, but not constant because the parameter is preceded by a colon ": NameStr". This is a colon tells Delphi about the need to replace the variable NameStr some value which will be known later.

Note the word NameStr was selected completely random. Use can be any valid variable name, just as selected variable identifier in the program.

There are two ways to assign a value to a parameterized query SQL. One way is to use a property Params TQuery object. The second - to use the DataSource property to obtain information from another DataSet [5]. Here are the key features to achieve these goals:

property Params [Index: Word];

function ParamByName (const Value: string);

property DataSource;

If you substitute the value of the parameter in a parameterized query through the Params property, it is usually necessary to make four steps:

- Close TQuery

- Prepare the object TQuery, calling the Prepare method

- Assign the required values ​ ​ property Params

- Open TQuery

The second step is performed in the event that the query text for the first time,

it can be further lowered.

That code snippet shows how this can be accomplished substantially:

Query1.Close;

Query1.Prepare;

Query1.Params[0].AsString: = ‘Argentina’;

Query1.Open;

This code may seem a little mysterious. To understand it requires careful line analysis. The easiest way to start from the third row, as the Params property is the " heart" of the process.

Params - is an indexed property that has syntax like the Fields property for TDataSet. For example, you can access the first variable in the SQL statement, addressing the zero element in the array Params:

Params [0].AsString: = ' " Argentina" ';

If a parameterized SQL query looks like this:

select * from Country where Name =: NameStr

the end result (ie what is actually executed) - the following sentence is SQL:

select * from Country where Name = " Argentina"

Everything that happened, it is variable: NameStr was set to " Argentina" through the Params property. Thus, you have completed the construction of a simple SQL statement.

If the request contains more than one parameter, then it is possible to dostupatsya changing index of the Params property

Params [1].AsString: = 'SomeValue';

or by using the access parameter name

. ParamByName ('NameStr') AsString: = ' " Argentina" ';

So, parameterized SQL queries using variables that always start with colon, to define the place where the settings will be handed over.

Before using the Params variable, you can first call the Prepare. This call causes Delphi to parse your SQL query and prepare Params property so that it was " ready to take" appropriate number of variables. You can assign a value to a variable Params without calling the Prepare, but it will run more slowly.

After you called the Prepare, and after being awarded the Params necessary variable values, you must call the Open, to finish the bind variables and get the desired DataSet. In this case, the DataSet is to include records where the field " Name" is " Argentina". [6]

1.2.3 Passing parameters through TdataSource

TQuery object has the DataSource property that can be used to create a link

to another DataSet. It does not matter whether another DataSet object TTable,

TQuery or some other descendant of TDataSet. All that is needed to establish a

connection - is to make sure that the DataSet that has an associated DataSource.

Suppose you want to create a link between ORDERS and CUSTOMERS tables

so that each time you are viewing a specific record of the customer, will

be visible only to the orders associated with it.

Consider the following parameterized query:

Select * from Orders where CustNo =: CustNo

In this query: CustNo - binding variable, which must be set from some source. Delphi allows you to use TQuery.DataSource field to specify a different DataSet, which will provide this information automatically. In other words, instead of having to use the Params and " manually" property to assign values ​ ​ to a variable, the variable value can simply be taken automatically from another table. In addition, Delphi is always first tries to perform a parameterized query by using the DataSource property [21], and then (if some value was not found) will try to get the value of a variable from the property Params. Upon receipt of data from the DataSource is believed that after the colon is the name of the field from the DataSource. If you change the current record in the main DataSet query will be automatically recalculated.

1.2.4 Implementation of joining multiple tables.

You have seen that the CUSTOMERS and ORDERS tables are connected with respect to one-to-many based on the CustNo. ORDERS and ITEMS tables are also connected against a one-to-many relationship, only through the field OrderNo.

More specifically, every order that exists in the ORDERS table will have multiple entries in the ITEMS table associated with this order.Records from the ITEMS table determine the type and number of items associated with this order.

Example.

Someone Ivanov FP May 1, 1995. I ordered the following:

- Nut 4-coal - 50 pieces

- The valve - 1 piece

A certain Sydorchuk YG December 8, 1994. I ordered:

- M / KR580 IK80 scheme - 10 pieces

- The transistor KT315 - 15 pieces

- Wire Coil - 1 piece

In a situation like this, sometimes the easiest way to " connect" the data from the ORDERS and ITEMS tables so that the resulting DataSet contain information from both tables.

The merger of these two tables is called " Connect" and is one of the fundamental actions that you can perform on a set of two or moretables. [14]

Taking ORDERS and ITEMS tables subdirectory DEMOS \ DATA, they can combine them in such a way that the field CustNo, OrderNo and SaleDate from ORDERS table will be " merged" with PartNo fields and Qty of the ITEMS table and form a new the DataSet, containing all five fields. Grid containing the resulting DataSet is shown in Figure 1.3:

Figure 1.3 - Connection ORDERS and ITEMS tables can be done so as to form a new DataSet containing the fields from each table.

There is a significant difference between the cursors and the associated connection table. However, they have two things in common:

- And those and others use two or more tables

- Each table is associated with another one or more of the same fields.

Connection ORDERS and ITEMS tables can be performed only SQL query that looks like this:

select

O.CustNo, O.OrderNo, O.SaleDate, I.PartNo, I.Qty

from Orders O, Items I

where O.OrderNo = I.OrderNo

This query consists of four different parts:

- Select statement defines what you want to receive - the cursor containing some form of DataSet.

- Then comes the list of fields that you want to include in the dataset. This list includes field CustNo, OrderNo, SaleDate, PartNo and Qty. The first three fields of the ORDERS table, and two others - from the ITEMS table.

- Expression from announcing that we are working with two tables, one called the ORDERS, and the other ITEMS. For brevity, the query uses of SQL individuals that allows you to reference the table ORDERS letter O, and a table ITEMS letter I

-Where clause is vital because it determines the field of communication for the two tables. Some servers may return a DataSet, but almost always the result set of records will not be so. To obtain the desired result, you must ensure that key sya enovyrazhenie where. There are several major components (objects) that ut Bud and spolzovat constantly to access the database. These objects may be divided into three groups:

- Nonvisual: TTable, TQuery, TDataSet, TField

- Visual: TDBGrid, TDBEdit

- Binders: TDataSource

The first group includes non-visual classes that are used to manipulate tables and queries. This group focuses around the component type TTable, TQuery, TDataSet and TField. In the Component Palette, these objects are located on the Data Access page.

- The second important group of classes - visual that shows data to the user and allow him to view and modify them. This group of classes includes components such as TDBGrid, TDBEdit, TDBImage and TDBComboBox. In the Component Palette, these objects are located on the Data Controls page.

- There is also a third type, which is used to link the two previous types of objects. The third type refers only to non-visual component TDataSource.

1.3 Class TDataSet

TDataSet class - one of the most important database objects. To start working with it, you should take a look at the following hierarchy:

TDataSet

|

TDBDataSet

|

| - TTable

| - TQuery

| - TStoredProc

TDataSet contains abstract methods where there should be a direct data management. TDBDataSet knows how to deal with passwords, and what to do to connect you to a specific table. TTable knows (ie has copied all the abstract methods), how to deal with the table, its indexes, etc [9].

TQuery has certain methods for the SQL query.

The TD ataSet - a tool that will be used to open the table, and move through it. There is no need to directly create an object such as the TDataSet. Instead, enough to use TTable, TQuery or other descendants TDataSet (for example, TQBE). A full understanding of the system, and the exact value of the TDataSet, will become clearer as you read this chapter. [18]

At the most fundamental level, the Dataset is just a set of records, as shown in Figure 1.4:

Figure 1.4 -. Each dataset consists of a series of records (each containing N fields) and a pointer to the current record.

In most cases the dataset will have a direct, one-to-one correspondence with the physical table that exists on the disk. However, in other cases, you can perform a query or other action, return dataset, which contains a subset of any single table records or the union (join) between multiple tables. The text of the DataSet and TTable terms are sometimes used interchangeably.

Typically, the program uses objects of type TTable or TQuery, so in the next few chapters, we assume the existence of TTable object type called Table1.

1.3.1 Navigation (Navigate through the records)

Following an extensive set of methods and properties of TDataSet provides everything you need to access any particular entry in the table:

procedure First;

procedure Last;

procedure Next;

proce dure Prior;

property BOF: Boolean read FBOF;

property EOF: Boolean read FEOF;

procedure MoveBy (Distance: Integer);

We give a brief overview of their functionality:

- Call Table1. First moves you to the first record in the table.

- Table1.Last moves to the last record.

- Table1.Next moves one record forward.

- Table1.Prior moves on one entry Back.

- You can check the properties of BOF or EOF

MoveBy procedure moves to N records forward or backward in the table. There is no functional difference between the request and the calling Table1.Next Table1.MoveBy (1). Similarly, Table1.Prior call has the same result as the call Table1.MoveBy (-1).

1.3.2 Fields

In most cases, when the need to gain access to the program recording individual fields, may use one of the following properties or methods, all of which belong TDataSet:

property Fields [Index: Integer];

function FieldByName (const FieldName: string): TField;

property FieldCount;

FieldCount property returns the number of fields in the current record structure. If you need to programmatically read the names of the fields that use the Fields property to access them:

var

S: String;

begin

S: = Fields.FieldName;

end;

S: = Fields.FieldName;

The index passed in Fields (starting from scratch), and determines the number of the field to which received ene access, ie, the first field - a zero, the second one, and so on.

If you need to read the current contents of a particular field of a particular record, you can use the Fields property or FieldsByName method. To find the value of the first field recording, read the first element of the array Fields:

S: = Fields.AsString;

Assume that the first field in the record contains a customer number, then the code shown above type would return the string " 1021", " 1031" or " 2058". If you need to access this variable as a numeric value, then you may use instead AsInteger AsString. Similarly, Fields property include AsBoolean, AsFloat and AsDate.

In printsepe can use the Fiel dsByName instead properties of the Fields:

The S: = FieldsByName (' CustNo '). AsString;

As shown in the examples above and FieldsByName, Fields and return the same data. Two different syntax is used solely to provide developers a flexible and user-friendly set of tools for programmatic access to the contents of the DataSet.In Delphi, there are other tools that allow you to get the same information, but it is the easiest way to access the field names in Time The the Run.

Fields property allows you to access not only the names of the fields, but also the contents of the fields. When Example NADI for the second button sat:

procedure TForm1.ValuesClick (Sender: TObject);

var

i: Integer;

begin

ListBox2.Clear;

for i: = 0 Table1.FieldCou to nt - 1 do

ListBox2.Items.Add (Table1.Fields [i].AsString);

end;

This code adds the contents of each field in the second listbox. Note that the meter is changed again from zero to FieldCount - 1.

Property Fields lets you choose the type of result of writing Fields [N].AsString. This and several related methods provide a simple and flexible way to access the data associated with a specific field. Here is a list of available methods that you can find in the description of class TField:

property AsBoolean

a pro perty AsFloat

property AsInteger

property AsString

property AsDateTime

Every time (when it makes sense), Delphi will be able to make the conversion. For example, Delphi can convert the Boolean field to Integer or Float, or Integer field to String. But not convert String to Integer, although it is capable of converting Float to Integer. BLOB and Memo field - special events, and we will discuss later. If you want to work with a Date or DateTime fields, you can use AsString and AsFloat to access them [37].

As explained above, FieldByName property allows you to access the contents of a particular field simply by specifying the name of the field:

S: = Table1.FieldByName ('CustNo') AsString;.

This is – a convenient technology that has several advantages when used appropriately way.

1.4 Working with Data

The following methods allow you to change the data associated with the TTable:

procedure the Append;

procedure the Insert;

procedure the Cancel;

procedure Delete;

procedure Edit;

procedure Post;

All of these methods - Part TDataSet, they are inherited and used TTable and TQuery.

Whenever you want to change the data, you must first translate the DataSet in edit mode. As you will see, most of the visual components do this automatically, and when you use them, it will not completely take care of it. However, if you want to change the TTable software, you will have to use the above function [28].

There is a typical sequence, which you could use when you change the current record fields:

Table1.Edit;

. Table1.FieldByName ('CustName') AsString: = 'Fred';

Table1.Post;

The first line translates the database to edit mode. The next line sets the value of the 'Fred' field 'CustName'. Finally, the data is written to disk when you call the Post [16].

Using this approach, you always work with the records. The very fact of moving to the next record automatically saves your data to disk. For example, the following code will have the same effect as the code shown above, plus it will move you to the next record:

Table1.Edit;

. Table1.FieldByName ('CustNo') AsInteger: = 1234;

Table1.Next;

The general rule to follow - whenever you move from the current record, the data you have entered will be recorded automatically.This means that calls First, Next, Prior and Last always perform Post, if you are in edit mode. If you work with the data on the server and the transaction, then the rules set forth herein do not apply. However, the transaction - this is a separate issue with their own special rules, you will see it when you read about them in the following classes [34].

However, even if you are not working with the transaction, you can still cancel the results of your editing at any time, as long as not caused directly or indirectly by Post method. For example, if you moved the table in edit mode, and change the data in one or more fields, you can always return the recording to its original state by calling the Cancel.

There are two methods called Append and Insert, which you can use whenever you want to add a new record in the DataSet. Obviously it makes more sense to use the Append to DataSets that are not indexed, but Delphi does not generate an exception if you use the Append to an indexed table. In fact, you can always use the Append, and the Insert.

1.4.1 Update (Refresh)

As you already know, any table that you open always " subject to change". In short, you have to be regarded as a changing table rather than as a static entity. Even if you - the only person using the TTable this, and even if you are on a network, there is always the possibility that a program with which you work, can have two different ways to change the data in the table. As a result, you should always know whether you need to update the table view on the screen.

Refresh function associated with the Open function in the sense that it reads the data, or some of the data associated with the table. For example, when you open the table, Delphi reads the data directly from the database file. Similarly, when you regenerate table, Delphi reads data directly from the table. So you can use this function to pereprochitat table if you think that it could change. Faster and more efficient to call the Refresh, you call Close, and then Open.

Keep in mind, however, that the TTable update can sometimes lead to unexpected results. For example, if a user is viewing a record that has been removed, it disappears from the screen at the moment when it is called Refresh. Similarly, if some other users to edit the data, calling Refresh will lead to a dynamic change of data. Of course it is unlikely that a user will modify or delete a record while the other looks at it, but it is possible.

Often it is useful to note the current position in the table so that you can quickly return to this place in the future. Delphi provides this functionality through three methods, which use the concept of bookmarks.

function GetBookmark: TBookmark;

(Sets a bookmark in the table)

procedure GotoBookmark (Bookmark: TBookmark);

(moves on tab)

procedure FreeBookmark (Bookmark: TBookmark);

(Frees memory)

As you can see, the call returns a variable of type GetBookmark TBookmark. TBookmark contains sufficient information to Delphi could find the location to which this TBookmark. Therefore, you can simply pass this TBookmark GotoBookmark function, and will be immediately returned to the location associated with this tab.

Please note that the call GetBookmark allocates memory for TBookmark, so you should call FreeBookmark before the end of your program, and before each attempt to reuse Tbookmark (in GetBookMark).

1.4.2 Management TDBGrid at runtime

DBGrid object can be completely reconfigured at runtime. You can hide and show columns, change the order of display of columns and their widths.

Property Options object a DBGrid (Table 1.3) is to change its view. Options property can have the following possible values:

Table 1.3 - Property Options DBGrid object.

As announced in this structure:

= DBGridOption (dgEditing, dgAlwaysShowEditor, dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgRowSelect, dgAlwaysShowSelecti on, dgConfirmDelete, dgCancelOnExit, dgMultiSelect);

For example, you can set options in the Runtime writing code like this:

A DBGrid 1. the Options: = [ dgTitles, dgIndicator ];

If you need to enable or disable the option, it can be done with the help of logical operations. For example, the following code will be added to the current set dgTitles parameters:

DBGrid1.Options: = DBGrid1.Options + [dgTitles];

Let ShowTitles have a Boolean variable, then the following code to enable or disable the option of one-click:

Proced ure TForm1.Button3Click (the Sender: a TObject);

begin

if ShowTitles then

DBGrid1.Options: = DBGrid1.Options + [dgTitles]

else

DBGrid1.Options: = DBGrid1.Options - [dgTitles];

ShowTitles: = not ShowTitles;

end;

If you need to change the position of the column in Runtime, we can but just change the code (first field in the record has a zero index):

. Query1.FieldByName ('CustNo') Index: = 1;

Query1CustNo.Index: = 2;

By default, CustNo field in the Customer table is the first. Key in the first row of this field moves to the second position, and the next line moves to a third position. Numbering field starts from zero, because the assignment property Index 1 makes the second field in the record. The first field is 0 Index.

When ienya field index, the other fields are automatically changed in the record.

If you need to change the width of columns in Runtime, only need to change the property of the respective DisplayWidth TField.

. Query1.FieldByName ('CustNo') DisplayWidth: = 12;

Query1CustNo.DisplayWidth: = 12;

The value 12 refers to the number of characters that can be displayed in a visible element.

DBGR_RT program shows how to work with the DBGrid in Runtime. The program is simple enough, except for two small parts, which are described below. The first part shows you how to create a check box in Runtime, and the second shows how to change the order of items in a listbox at Runtime.

When you create a form (event OnCreate) ListBox filled with field names, then create an array of the CheckBox, the corresponding fields in the table. First, all the checkboxes are selected, and all the fields are visible in the table. The program learns through TTable1 field names and assigns them to the Caption property of the corresponding CheckBox. In addition, the OnClick event handler is assigned to all the checkboxes procedure ChBClick, and which turns on / off the field in the DBGrid.

procedure TForm1.FormCreate (Sender: TObject);

var

i: Word;

R: Array [0..49] of TCheckBox;

begin

{Fill ListBox}

ListBox1.Clear;

for i: = 0 to Table1.FieldCount-1 do

ListBox1.Items.Add (Table1.Fields [i].FieldName);

The Make CheckBoxe { s}

for i: = 0 to Table1.FieldCount-1 do begin

R [I]: = TCheckBox.Create (Self);

R [I].Parent: = ScrollBox1;

R [I].Caption: = Table1.Fields [i].FieldName;

R [I].Left: = 10;

R [I].Top: = I * CheckBox1.Height + 5;

R [I].Width: = 200;

R [I].Checked: = True;

R [I].OnClick: = ChBClick;

end;

end;

Most of the code in this example, perform relatively simple tasks, such as naming and regulations check boxes. here is two Key line:

R [I]: = TCheckBox.Create (Self);

R [I].Parent: = ScrollBox1;

The first line creates a CheckBox to specify Owner (owner). The second line assigns the Parent (Parent) for the CheckBox. To understand the differences between parent and owner, see the relevant properties-to help of online.

The program contains a ListBox, which shows the current order of the fields in the DataSet. To change the order of fields in the DataSet (and, consequently, in the DBGrid) used two buttons. When you press one of the buttons selected in ListBox'e field moves one position up or down. In synchronization with this change and the order of fields in DBGrid. The code shown below, the Index field for changes Table1, thus changing, position in field DBGrid. These changes apply only to the visual representation of DataSet. Physically, the data on the disc are not changed.

procedure TForm1.downButtonClick (Sender: TObject);

var

i: Integer;

begin

with ListBox1 do

if (ItemIndex < Items.Count-1) and (ItemIndex < > - 1) then begin

i: = ItemIndex;

{Move ListBox item}

Items.Move (i, i + 1);

ItemIndex: = i + 1;

{Move Field}

Table1.Fields [i].Index: = i + 1;

end;

end;

The last line in the example is just one that actually changes the index of the column that the user wants to move. Two lines of code just before it moves the current row in the ListBox to a new position.

Conclusion ofthe main advantage of delphi

Thus, the database are considered the main advantage Delphi. Although the language was not created specifically for this area, but the implementation of the work with the data gives gneogranichennye opportunities. Even specialized languages ​ ​ to work with the database (such as MS Visual FoxPro) is clearly inferior to the simplicity and power of this type of programming applications. Delphi hides all the complexity and at the same time gives the greatest power. There has never been such a problem that the user would not be able to implement in Delphi in a short period of time. And most importantly, that all this is implemented very convenient and easy to understand.