data module 4 . adoquery2 . parameters[0]。 Value: = User name;
data module 4 . adoquery2 . open；
It is always safe to call the Close method when setting SQL properties for TQuery or TADOquery sections. If the TQuery or TADOquery section has been closed, calling the close method will have no effect. When setting a new SQL command statement for an SQL attribute in an application, you must call the Clear method to clear the existing SQL command statement in the SQL attribute. If you don't call the clear method, you can call the Add method to set the SQL command statement in the SQL attribute. Then the newly set SQL command statement will be appended to the existing SQL command statement, and unexpected query results will often appear when the program runs, or even the program cannot run.
It is important to pay special attention here. In general, the SQL attribute of TQuery or TADOquery component can only contain a complete SQL statement, and it is not allowed to be set to multiple SQL statements. Of course, some database servers also support setting multiple SQL statements in the SQL properties of TQuery or TADOquery components. As long as the database server allows, we can set multiple SQL statements for SQL properties at programming time.
After setting the attribute value of SQL attribute for TQuery or TADOquery component, that is, after writing an appropriate SQL program, there are many ways to execute the SQL program.
In the design process, after setting the SQL property of TQuery or TADOquery component, set the value of its Active property to True, so that the SQL program in the SQL property can be executed. If there are data browsing components connected to TQuery or TADOquery components (such as TDDGridTDBEdit) in the application, the execution results of SQL programs will be displayed in these data browsing components.
During the running of the application, you can execute the SQL properties of the SQL program by calling the Open method or the ExecSQL method of the TQuery or TADOquery component. The Open method is different from the ExecSQL method. Open method can only be used to execute SQL query statement (Select command) and return a query result set, while ExecSQL method can also be used to execute other commonly used SQL statements (such as INSERT, UPDATE, DELETE and other commands), such as:
Query 1。 Open (this will return a query result set).
If the Open method is called without the query result, an error will occur. At this point, you should call the ExecSQL method instead of the Open method. For example:
Query 1。 ExecSQL (no result returned)
Of course, when designing an application, programmers can't be sure whether the SQL statements in TQuery or TADOquery components will return a query result. In this case, try … except that modules should be used to design programs. Call the Open method in the Try part and the ExceSQL method in the Except part to ensure the correct operation of the program.
For example:
attempt
Query 1。 open
Except for ...
Query 1。 ExecSQL
end
Two types of data can be obtained through Tquery or TADOquery components:
U "activity" data
This kind of data is just like the data obtained through the Table component. Users can edit and modify these data through the data browsing component. When the Post method is called or the focus leaves the current data browsing component, the user's modifications to the data will be automatically written back to the database.
U Inactive data (read-only data)
Users cannot modify data through the data browsing component. By default, the query result data obtained through the TQuery component is read-only. In order to obtain "activity" data, you must set the RequestLive property value of the Tquery or TADOquery component to True in the application, but in any case, you cannot obtain "activity" data (by setting the subordinate value of RequestLive to True). In order to obtain "activity" data, in addition to setting the RequestLive property in the TQuery section to True, the corresponding SQL command must also meet the following conditions.
In the case of local SQL statement query, in order to get an updatable data set, the restrictions of SQL statements are as follows:
N A query can only involve one table.
The nSQL statement cannot contain the ORDERBY command.
NSQL statements cannot contain aggregate operators SUM or AVG.
N After selection, there cannot be a calculated field in the field list.
N In the WHERE part of the Select statement, only comparison operations between field values and constants can be included. These comparison operators are: Like,>,<,>=,<=. There can be union AND intersection operations between comparison operations: and and OR.
When querying the database tables in the database server through SQL statements:
N A query can only involve one table.
The nSQL statement cannot contain the ORDERBY command.
NSQL statements cannot contain aggregate operators SUM or AVG operations.
In addition, if you query a table in Sybase database, there can only be one index in the queried table.
If the TQuery or TADOquery component is required to return an "active" query result dataset in the application, but the SQL command statement does not meet the above constraints, then BDE can only return a read-only dataset for the SQL query of the local database. For SQL queries in the database server, only the wrong code can be returned. When a Tquery or TADOquery component returns an "active" query result dataset, the value of its CanModIfy property is set to True.
3.4 introduction to MSSQL server
SQLServer is a background database management system with powerful functions and simple operation, which is more and more popular among database users. More and more development tools provide interfaces with SQLServer. SQLServer is a relational database management system, which was originally developed by Microsoft, Sybase and Ashton-Tate. The first OS/2 version was released by 1988. After the introduction of WindowsNT, Microsoft and Sybase parted ways in the development of SQLServer. Microsoft transplanted SQLServer to WindowsNT system, focusing on the development and promotion of Windows NT version of SQL Server.
SQLServer2000 is the latest version of SQLServer database management system introduced by Microsoft. This version inherits the advantages of SQLServer7.0, and at the same time adds many more advanced functions than it, which has the advantages of convenient use, good expansibility and high integration with related software. It can be used across a variety of platforms, from laptops running MicrosoftWindows98 to large multiprocessor servers running MicrosoftWindows2000. MSSQLServer can be applied not only to the management of large and medium-sized databases, but also to the development of desktop databases. In fact, the basic structure of SQLServer database processing adopts relational database mode. Nevertheless, I believe it is easy to find that the database processing mode in SQLServer adopts the object-oriented operation mode and spirit, that is to say, all functions of SQLServer can be realized based on some objects already established in the system, which is a rather OO (object-oriented) architecture.
SQLServer Enterprise Manager is the main management tool of SQLServer, which provides a user interface conforming to MMC standards, enabling users to:
Define SQLServer instance groups.
Register a single server into a group.
Configure all SQLServer options for each registered server.
Create and manage all SQLServer databases, objects, logins, users and permissions in each registered server.
Define and execute all SQLServer management tasks on each registered server.
By calling SQL query analyzer, SQL statements, batches and scripts are designed and tested interactively.
Wake up and call various wizards defined for SQLServer.
Chapter III Design and Analysis of Book Management System
4. 1 application requirements analysis
The library management system needs to meet the needs of three aspects, namely, book borrowers, library staff and library managers. The demand of book borrowers is to inquire about the collection of books, personal borrowing situation and personal information modification; Library staff operate the borrowing and returning requirements of book borrowers, and at the same time form a borrowing or returning report for borrowers to check and confirm; The functions of librarians are the most complicated, including the management and maintenance of staff, book borrowers and books, the view and maintenance of system status, and the generation of book return reports.
Borrowers can go directly to the library to look up books. If they log in to the system according to their library card number and password, they can also check their borrowing situation and maintain some personal information. In general, book borrowers should only inquire and maintain their own borrowing situation and personal information. If he inquires and maintains the borrowing situation and personal information of other borrowers, he must know the library card number and password of other borrowers. These are hard to get, especially passwords, so it not only meets the requirements of book borrowers, but also protects personal privacy.
Library staff have the right to modify the borrowing and returning records of books, so more consideration should be given to staff logging in this module. In this module, the library staff can add book borrowing records or book returning records for the borrowers, and print and generate corresponding reports for users to check and confirm.
Librarians have a large amount of information and the highest requirements for data security and confidentiality. This function realizes the management and statistics of book information, borrower information and overall lending information, as well as the viewing and maintenance of personnel and management information. Librarians can browse, query, add, delete, modify and count the basic information of books; Browse, query, count, add, delete and modify the basic information of book borrowers, browse, query and count the borrowing information of the library, but you can't add, delete and modify the borrowing information. This part of the functions should be performed by library staff. However, when deleting the basic information records of book borrowers, the borrowing records of book borrowers should be deleted in cascade. In addition, it should also have the function of generating and printing the book return report.
In this system, because there is no printer equipment for testing, report printing is changed to report preview in advance.
Design the operation authority and login mode of different users.
Book query open to all users
The borrower maintains the personal information of the borrower.
The borrower inquires about personal loan information.
Maintain the borrower's personal password
Operate the database and generate reports according to the borrowing situation.
Operate the database and generate reports according to the return of books.
Inquire and count all kinds of information
Maintain book information
Maintain employee and administrator information.
Maintain borrower information
Integrity of processing information
Generate a report for expired books.
Figure 4-2 Summary of Database Application Requirements of Library Management System
According to the above demand analysis, some details are omitted (such as not considering the user's login; Record maintenance), get the following three layers of data flow chart.
4.2 Division of system function modules
The functional block diagram of the system is shown in Figure 4- 10.
4.3 System Database Design
conceptual design
In the conceptual design stage, designers treat data, handle requirements and constraints from the user's point of view, and generate a conceptual model that reflects the user's point of view. Then the conceptual model is transformed into a logical model. The separation of conceptual design and design process makes the task of each stage relatively simple, greatly reducing the complexity of design, and is not limited by a specific DBMS.
The conceptual design of database based on ER method can be divided into three steps: first, design local ER patterns, then synthesize each local ER pattern into a global pattern, and finally optimize the global ER pattern to get the final pattern, that is, the conceptual pattern.
(1) Design local ER mode
Definition of entities and attributes:
Books (book number, title, author, publishing house, publication date, remarks, price, quantity, etc.)
Borrower (library card number, name, gender, ID card, contact number and password)
Identity (ID number, ID description, maximum borrowing times)
Book category (book category number, category description)
The "connection" of ER model is used to describe the association between entities. A complete way is to investigate whether there is a connection between any two entity types in the local structure according to the results of demand analysis. If there is a connection, further determine whether it is 1:N, M:N, or 1: 1 and so on. It is also necessary to examine whether there is a connection within an entity type, whether there is a connection between two entity types, whether there is a connection between multiple entity types and so on. Contact definition as shown in figure 4-5. The explanation is as follows:
U A borrower (user) can only have one identity, and one identity can be owned by multiple borrowers;
U A book can only belong to one book category (category), and a book category can contain multiple books;
A user can borrow many different books, and a book can also be borrowed by many different users.
(2) Design the global ER mode
After designing all the local er patterns, the next step is to synthesize them into a single global conceptual structure. The global conceptual structure should not only support all local ER patterns, but also reasonably represent a complete and consistent database conceptual structure.
1) Determine the entity type of male * * *
In order to provide a basis for the merger of multiple local er patterns, we must first determine the public entity type in each local structure. In this step, we only identify the public entity type according to the entity type name and key. In general, an entity type with the same name is regarded as a candidate for a public entity type, and an entity type with the same key is regarded as another candidate for a public entity type.
2) merging of local er patterns
The principles of merger are: first, merger in pairs; First, merge those local structures that are related in the real world; The merger starts with the public entity type and ends with the independent local structure.
3) Eliminate conflicts
Conflicts are divided into three categories: attribute conflicts, structural conflicts and naming conflicts.
The purpose of designing global ER pattern is not to formally merge several local ER patterns into one ER pattern, but to eliminate conflicts and make it the same conceptual model that all users can understand and accept.
3) Optimization of global ER mode
After obtaining the global er schema, in order to improve the efficiency of the database system, it is necessary to further optimize the ER schema according to the processing requirements. A good global ER model should not only accurately and comprehensively reflect the functional requirements of users, but also meet the following conditions: the number of entity types should be as small as possible; Entity types contain as few attributes as possible; There is no redundancy between entity types.
To sum up, the global ER mode of "Book Management System" is shown in Figure 4- 13.
Logical design of relational database
Because the result of conceptual design is er diagram, and DBMS generally adopts relational DBMS (MSSQLServer I use is a relational DBMS), the logical design process of database is the process of transforming ER diagram into relational schema. Because of the advantages of relational model, logical design can make full use of the normalization theory of relational database to formalize the design process. The design result is the definition of a set of relational patterns.
(1) Export the initial relational schema
Book category (book number #, book name, book category #, author, publisher, publication date, remarks, price, quantity) User (book card number #, name, gender, ID number #, ID card, contact number, password) ID (ID number #, identity description, maximum borrowing times)
Figure 4- 14 relational schema set
(2) generating sub-patterns
A subpattern is a description of the data part used by the user. In addition to pointing out the data used by users, it is also necessary to point out the relationship between data and corresponding data in conceptual model, that is, the corresponding relationship between conceptual model and submodel.
Sub-borrowing method (library card number, name, book number, title, borrowing date)
Figure 4- 15 partial submodule
(3) According to the problems in the design, I also added two relational models when writing the system:
1, ownertemp: used for staff to temporarily store the information of borrowing and returning books when handling borrowing and returning books, so as to print reports.
2.keyer: used to store the user names, passwords and permissions of staff and librarians, so that staff or librarians can verify the identity of users when they enter the corresponding functional modules.
4.3.3 Realization of database
The logical design of database I chose MicrosoftSQLServer2000 (Enterprise Edition) database. First, seven basic database tables are created, as shown in Table 4- 1-4-7, and then the relationship between tables is established according to the global ER diagram, as shown in Figure 4-8.
Table 4- 1 Structure of Borrower's Basic Information Table (User)
Table 4-2 Structure of Book Information Table (Book)
Table 4-3 Structure of Book Category Information Table (Category)
Table 4-4 Structure of Borrower's Identity Information Table (ID)
Table 4-5 Structure of Loan Information Table (Owner)
Table 4-6 Borrowing Structure of Temporary Storage Information Table (Ownertemp)
Note: an index field is added to the owner table and the ownertemp table to uniquely identify a book borrowing record and set as an identifier, and the identifier seed is 1.
Table 4-7 Structure of Employee and Administrator Information Table (Keyer)
Figure 4-8 Relationship diagram between database tables
Chapter V Application Program Design of Library Management System
5. 1 system form module
5.2 Settings of Data Module Form
When writing database applications, many components and forms often access the same data source at the same time. Setting up a data source for each component or form will be a very time-consuming work, and it will take some effort to ensure that these data sources are indeed the same. Then, can these data sources be managed centrally, preferably as a unified module, and the module can be introduced when necessary, without directly operating the data sources themselves? DataModule is the best answer to this question. Simply put, data module is a form of centralized management of data sources, which can be introduced at any time where necessary.
But when I was developing this system, I started to use the data module, but I encountered some problems in the process of using it. Moreover, considering that this system uses many TADOQuery controls, if data controls are used, it may bring management troubles, such as confusing the functions of various data controls. Others think that using dynamically generated ADOQuery can save more resources. Therefore, in my own system, the first module I started to do, "Borrower Personal Module", also used the data module slightly. But in the next two modules, most of them are realized by dynamically generating ADOQuery. And because SQL statements are dynamically added, there are not many controls in datamodule.
5.3 Implementation of startup screen
The purpose of starting the screen is to leave a good impression on users and deepen the affinity of the software. It has no practical function. Add the image and time components to the Form 1 The form of the startup screen is omitted, and the main source code is as follows:
5.4 Implementation of User Login Form
This form provides three different users (ordinary users, employees and administrators) with the choice of entering different modules to meet the needs of different users. The source code is relatively simple, abbreviated.
5.5 Implementation of User Password Authentication Form
The purpose of this form is to let the staff or librarian log in according to the user name and password, and check the "Permission" field in the Keyer table according to the user name to distinguish whether to enter the librarian module or the staff module. The form interface and source code are as follows
5.6 Implementation of Borrower Service Module
The function of the borrower service form is mainly to query books, view personal borrowing and modify some personal information. The interface diagram is as follows:
5.6. 1 Realization of book query function
In this system, anyone has the right to use the query function without any restrictions. The interface is as follows:
Because there are many query functions, such as complete matching search and partial matching fuzzy search by fields such as book number and book title, and logical AND or logical or multi-condition search by multiple conditions. The implementation method is similar, so only the code of multi-condition search is given, as follows:
5.6.2 Realization of the Borrower's Login Function
The realization of this function is roughly the same as that of staff and managers, and it is simpler. Look up the library card number and password from the user table to see if they are consistent with the user input. If they are consistent, users can check their own borrowing situation and maintain some of their own information. The source code and the borrower login interface have been omitted.
5.6.3 Realization of Borrower's Loan Situation Function
When the borrower logs in to the system correctly, this function will be activated, and users can check their own borrowing situation. In this system, ListView is generally used to display information, and DBgrid is only used in a few cases, because I think ListView is better to implement and can completely separate information data from users.
Here, we can query the borrowing situation according to the different requirements of borrowers, including checking all the borrowing departments, the borrowing situation of a book, and querying according to the borrowing days. Among them, the query based on the number of borrowing days is more representative, and there are two ways: way one and way two. The source code of this function is given below.
Query Method 1 By Borrowing Days
Query method 2 by borrowing days
5.6.4 Realization of the function of maintaining the borrower's personal data
This function realizes the modification of some information of the current borrower, but the information such as library card number and identity category is not allowed to be modified, which is the function of librarian module. In this interface, click the modify button to open the modify form (Form8), and click the modify password button to open the grouping box 8, where you can modify the password. The key source code is as follows.
The following is the source code of personal information modification:
The following is the source code of password modification:
5.7 Implementation of Lending/Returning Module of Employee Handbook
5.7. 1 personnel realize the book borrowing function.
In this function, the staff input the card number of the borrower and the book number of the book to be borrowed, and then click the borrow button to borrow the book. Considering that we may only know the book title but not the book number in actual operation, a conversion function is added at the bottom of this interface, which can convert the book title into the book number and then borrow books.
After the loan is completed, the system will generate a loan report for the borrower to check and confirm. The printing effect of the borrowing report is shown in the figure below, which is relatively simple to implement and omits the implementation process.
5.7.2 The staff realizes the function of returning books.
In this function, the staff returns books according to the borrower's book number and the returned book number. And according to the possible situation of returning books in reality, we only know the title of the book and don't know the book number, so we add the function of returning books according to the title. This function is an improved method to convert the book name into the book number in the book borrowing function, so that it is not necessary to convert before borrowing as in the borrowing function. After the return is completed, a return report will be printed for users to check and confirm.
5.8 Realization of Librarian Module
5.8. 1 Realize the book management function of the librarian
In this function, you can enter the book number in (* book number), click the Find button, and the information will be displayed in each corresponding component, or you can find the desired record through the book name. The information of the first record will be displayed in each corresponding component, or you can click a record in the ListView component below, and the information of the selected record will also be displayed in each corresponding component. In the warehousing function, you can insert a new book record, as long as it is not the same book number and the field marked with * is not empty. Deleting will delete the book records in those book tables, and if it is lent, it can also delete the records in the owner table according to the user's requirements. Because the functions of book modification and book storage are the same as those of personnel record modification and personnel record addition, only the source code of deletion function is given below, as follows.
5.8.2 Realization of the management functions of librarians, staff and administrators.
In this function, you can add employees or administrators, or modify their passwords and permissions.
In this function, if the records in ListView are selected, the information will be displayed in the corresponding components on the right, and the administrator can also modify these records or add new records. You can also click the Delete button to delete the selected record. The deletion function is the same as the deletion of book records, and only the implementation process of adding and modifying is given below.
5.8.3 Realize the function of the librarian to uniformly modify the book category and save records.
In this form, you can delete, add and modify the categories of books. The function of this module is the same as that of deleting, adding and modifying book records, but it can also be used to make statistics by book category, and the total number of books, the number of books in stock, the number of books lent and the number of overdue books can also be counted according to the book table and the owner table. This paper gives several functions and processes to count the total number of books, the number of books in stock, the number of books lent and the number of books overdue.
5.8.4 Realization of Library Librarian's Lending Management Function
Inquiring about the borrower can find the borrower's information according to the borrower's library card number, name or ID number, and can also realize fuzzy search. The realization of this function is generally the same as the previous book search, so I won't explain it in detail.
5.8.5 Realize the management function of library maintenance borrowers.
This function can view, add, delete and modify the borrower's information. The implementation process of the refresh button is given here.
5.8.6 Realization of Library Identity Maintenance Function
This part is to manage the identity of the borrower, which can be added, deleted and modified. Similarly, when one or more records are selected in listview, the information will be displayed in the corresponding right component. The implementation process of this function is similar to that described above.
5.8.7 Realization of Library Lending Statistics Function
This function makes statistics according to the identity of the borrower, and obtains the total number of borrowers in a certain identity, the number of borrowers in that identity and the number of borrowers. The implementation process is given below.
5.8.8 Realization of overdue records of statistical lending in libraries
The printed overdue loan collection report is shown in the following figure:
This report can display the information that the borrowing information exceeds the limited time in ascending order by the library card number. The main SQL statements are as follows:
5.9 Realization of System Information Display