Commercial Database Software Development (Part.II)
A. Client Server Issues
To setup a network for development, we should know what are the component of a network. The topology and technology being used. Firstly, the OS has to be installed keeping in kind the purpose for which the application is being develop and deployed. The next step is to decide the model to be used. A tip here to choose Domain model if more users and sophistication on central administration is required and the Workgroup model if less number of users are going to use the application. For the domain model we need to install the DNS server.
B. Stored Procedures
A stored procedures is a routine written in T-SQL by using the DML, which acts on rows of a table in a database. SQL does not support IF statements and functions which manipulate strings, formatting functions, whereas T-SQL supports all of them. Stored procedures are stored in SQL Server databases. We can use stored procedures to build business rules into the database. After stores procedures have been stored to the database, users and applications can be call them as if they were another SQL statement or a built-in T-SQL function. The main advantage of using stored procedures is performance. Stored procedures execute on the database server, close to he data. Store procedures, which are nothing but data manipulation code, execute faster than passing SQL statements from VB. A stores procedures can scan thousand of records, perform calculations, and return a single number to an application. All calculations occur on the database server and data is not moved across the network. For this reason, stored procedures are faster than equivalent SQL statement. After stored procedures are defined, they became part of the database and appear as database objects, like tables or views.
This makes it easier to access the stored procedures statements and manipulated them. Once they are tested, stored procedures do not required compiling as is the case with VB code. We can set security on the stored procedures so that only callers with appropriate permissions can be execute the logic. Using stored procedures we can encapsulated the business logic. Hiding database structure changes from an application. We can also change the structure of the underlying tables, by modifying the stored procedure without affecting applications that use the stored procedure. By providing a stored procedure for editing the transactions, we can ensure the integrity of the data. Stored procedures are used to query data into the database, save data into the database, and update multiple tables.
We can use the SQL Server Query Analyzer or the Enterprise Manager to write, debug, and execute stored procedures against a database. To create a stored procedure, we enter the definition of the procedure into the database. We create a new stores procedure by using the CREATE PROCEDURE statement and save it to the database. This step does not actually execute the stored procedure. To execute a procedure saved into the database, we must use the EXECUTE statement.
C. Developing the Standard Modules
The next step is o develop the standard modules, standard modules are sections of the code, which you will access repeatedly across the application. Declaration of Constant and chosen procedures and functions are written here.
D. Designing the Database using SQL Server / any RDBMS
Database design is one aspect of the application development where domain expertise matter most, it is important to remember that once a database design is arrived at for a particular set of specifications, it Is difficult to change or alter the design, frequently .So, better do a lot of reviews to arrive at the final working design. ADO is commonly used to access the SQL Server database. ADO is mainly used to:
- open a connection
- Close connection
- Retrieve data from the server
- Save data
All the above can be implemented in an elegant way by writing a class. Developing a class is technique, which is a topic by itself and can be got from the book.
E. Transactions
While database can efficiently hold large amount of information that can be queried, all of that data and all of that querying power is useless if the data is incorrect or nonsensical. Database provide a plethora of techniques for ensuring the integrity and consistency: primary key and unique constraints can be employed to ensure entity integrity; foreign key constraints aid in ensuring relational integrity, and transactions help ensure that the database’s data remains consistent.
While INSERT, UPDATE, and DELETE statements are the most granular for modifying a database’s underlying data, at times we want to treat multiple INSERT, UPDATE and/or DELETE statements as one atomic operations. That is, in certain situations, rather than having each INSERT, UPDATE, and DELETE statements stands on its own, we want the set of statements to be, together, an invisible unit. When issuing this set of statements we want either the entire set of statements to succeed, or all to fail – there should be no ‘in-between’ state.
The canonical transactional example is transferring money from one account to another. A money transfer account at the bank requires two steps; if we want to transfer $500 from our checking account to our saving account, the following steps must be processed:
First, $500 must be deducted from our checking account,
Next, $500 must be added to the savings account.
In terms of SQL syntax, this would involve two UPDATE statements – One subtracting $500 from the balance of the checking account and the other incrementing the savings account balance by $500. it Is vital, however, that these two steps are treated as one atomic unit. What we want to avoid is to have step 1 complete, subtracting $500 from our checking account, but before step 2 can run, crediting our savings account, image that the database server crashes. (Well, this scenario is something the bank might not get too upset over!!) it is important that either both of these steps complete in total or neither complete.
Database transactions are what ensure atomicity, one of the key feature of any database system. Microsoft SQL Server, as well as any professional grade database product, has support for transactions. In this article we’ll examine how to wrap multiple SQL statements within an atomic database transaction using the sqlTransaction class in the System.Data.SqlClient namespace. A database transaction is a series of statements that either succeed or fail as a whole. In a journal voucher transaction, user enters two accounts creditor account and purchase account.
Creditor account Credit $1000 (-)
Purchase account Debit $1000 (+)
We should update both the accounts if one of the following accounts updation fails. It would be better if both statements fail. Microsoft SQL Server itself support transactions though the BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
F. Application Interface
As the developer you have to create to create a menu, which will include all access to open all the forms you plan to design. When you start with programming entries, you will start seeing the application taking shape.
Validations can broadly be divided into two categories. Validations in the front end and those set up on the server side. The server side validations are implemented using stored procedures.
The next step is to write report. Firstly, you will have to decide on output format. Depending on the format, write the correct “SELECT” statements (Query Statements). Use the indexes properly to increase the performance of the report generation.
Conclusion:
This article is intended to give you a brief idea of the issues related to application development for a client server environment. I hope that it has given you a basic idea of the issues involved. If you have any questions you can always refer the book which does an excellent job of explaining the steps to develop the application in detail.
This review is based on the book titles:
"Develop an Accounting Package using VB"
(Client Server edition)
vkinfotek.com
review by Micheal, SLC, US
