Develop your database applications


Database Software, Accounting software, Stock Management softwareTools: Microsoft Tools – VS, C#, VB, SQL Server
In this ebook, I will run you through the important steps involved in developing client/server database application software. Most of the applications we see today, technically speaking, are client/server application softwares.

Before we go further, let us know more about client/server applications. Client/Server applications have become predominant in enterprise wide computing. This is because Client/Server computing provides an open and flexible environment where mix-and-match is the rule.

A Client/Server network is used to accomplish many business needs. They are:

a.

To record accounting transactions like purchases, sales, receipts, payments in a company. Note that, different users enter these transactions simultaneously using the client computer. The client computer may be running a windows application or a web application. The Client/Server application, should support recording of these transactions into a central Database server confirming to all security and database integrity requirements of an accounting application.

b.

To extract business intelligence reports from the data in the database server. c.

To enable users to share resources like printers and fax machines. As enterprise wide computing becomes more sophisticated, the CIO of a company has a challenging job involving maintenance of hardware and networks, upkeep issues like registry clean up, setting up and updating policies, regular backups, etc., ensuring that the Client/Server business application is up and running. A complex job like this requires an understanding and a grasp of all the issues like hardware, networks, network operating systems, protocols and more. Client / server application development can be broadly categorized as: Installing and setting up windows operating system for

development and production environment Installing and setting up database server – sql server

Front end design and development

Back end database design and development

Understanding the Client/Server architecture

On the client side of this architecture, we find a fairly simple Front-end application. On the server side, we find an intelligent engine. The server is designed to accept queries from the front-end application – usually in the form of calls to stored procedures and return the requested information. We will see now in detail what are the responsibilities of client and a server. Client Responsibilities

The client application is responsible for connection management, data capture, data retrieval, data presentation, and error management.

Connection Management: Client/Server applications establish a connection with the server to submit queries, retrieve result sets. In this architecture, the client makes a hard connection with the server over a local area network (LAN) or a wide area network (WAN) – even if the connection is 2

with a SQL Server, running on the same system. The client application’s connection is authenticated by the LAN and SQL server with a user-supplied or application-generated login ID and password. In some cases, Microsoft Windows provides a degree of connection security management. That is, using domain-managed security, client applications need not provide additional user names and passwords to SQL Server—once they are authenticated by windows. The client must also deal with connection problems caused by trouble on the server, on the network, in the application itself. Data Capture: The client presents forms for the user to fill in with data. The client validates data values before they are sent to the database. This pre-validation often involves cross-checking form fields against other fields, either on the form or in the database. Validation can take place as fields are filled in or completed, or as the form is committed. These validation criteria are often referred to as client-side business rules.

Data retrieval: The client submits queries to the database engine for processing and retrieves the result sets, as required. Another responsibility of the client is to manage data returned to the application.

Data Presentation: The client application is responsible for displaying results from queries, as needed. This task might involve filling a Grid or Listbox control. Error Management: The client is also responsible for trapping and dealing with the errors. Effective and comprehensive error management is the sign of a successful client application. The Server’s responsibilities

In any client/server implementation, the server is not just a data dumping ground. The server is also responsible for intelligent resource management, security management, data management, query management and database system management.

Resource management: The server is responsible for managing its own resources. These resources include RAM, connections, disk space, CPU time, threads, and a set of caches or queues. If the server has to compete for resources with other Windows 2003/ NT services, its job is made that much harder. For example, if the server must also act as a print server or domain controller, the SQL

Server operations will become slow.

Security management: The server prevents unauthorized access to itself and the database while permitting guarded access to those with valid permission.

Data management: The server is also responsible for the validity and integrity of the data sent to the database system from the client application.

Query management: The server processes SQL queries from the clients, which involves syntax and object checks and compilation of a query into a valid and efficient processing plan. Database system management: In managing the database system, the server manages all connections to the database. The server also maintains tables, indexes, procedures, rules, triggers, data types, list of valid users, and other database objects.

Let us list and discuss the important topics we should be familiar with concerning the windows operating system. Here my focus will be on discussing topics, which are more relevant and useful for a programmer who is into application software development.
Windows provides two models of network administration. They are workgroup (peer-peer) and domain model (client/server). The domain model, which involves usage of active directory services, is more prevalent and suitable for large networks and where centralized administration is must. Setup a domain model for a Client / Server Network

A domain model provides centralized administration because user’s information is stored centrally. In the workgroup model to access resources on two machines, it is necessary to have two user accounts one on each computer. The domain model provides a single logon process for users to gain access to network resources.

Active Directory Service is the service used to implement the domain model. With the ADS, all the information needed to use and manage printers, shared folders and other resources are stored in a centralized location and the process of locating and managing these resources is simplified. The term directory in ADS refers to a database, which stores information of network objects or resources. The ADS can be installed on the computer, which is running Server OS windows 2002 or 2003. The process of installing Active Directory on a Windows Server 2003 server computer begins by launching the Active Directory Installation Wizard often referred to by its executable file, DCPROMO. Using dcpromo.exe, we can install and remove Active Directory from a Windows Server 2003 computer. The computer on which Active Directory Services is installed is called domain controller. It is from this computer that the network administrator manages the domain. How to setup a DNS server

DNS is a service, which translates computer names (host names) to IP addresses. The DNS server is a machine on which DNS service is installed. The DNS service helps to identify the computers on the network. The Domain Name System (DNS) is the standard name resolution strategy used on Windows Server 2003 systems.

In a practical scenario, when an application is running on the network, the client frequently requests data from the SQL server residing on the server. Whenever a request is issued there is a necessity for the client’s identity. The DNS server helps to resolve this identity. At the application level, the DNS server is not involved in the scheme of things. The DNS server operates only at the OS level and helps to open a channel between the server and a client. Once the IP addresses are set and the application is installed, there is nothing to be done on a day-to-day basis. DNS is most commonly associated with the Internet. However, private networks use DNS extensively to resolve computer host names and to locate computers within their local networks. If a DNS solution is not available or installed when you set up domain controller, we won’t be able to create the domain. Therefore, you need to have a working DNS server before we can install Active Directory. If not, Active Directory Installation Wizard will offer to create one for you.

How to setup a DHCP Server

In a network, if the number of clients are more, setting up IP addresses manually, is not practical. For such cases, an additional service called the DHCP (Dynamic Host Control Protocol) service is provided by Windows operating system. This service allocates an IP address to each computer when the system boots. The advantage is that, when there are more number of computers on the network, the need to give one unique IP address to each one of the computer is eliminated. 4

for more, click on http://www.vkinfotek.com/Install and Configure the DHCP service

To implement DHCP, you must install and configure the DHCP service on at least one computer running Windows 2000 or 2003 server within the network. For DHCP to function properly, we must manually allocate the IP address on the server and set up the clients for dynamic address configuration. We will install the DHCP service on the first computer, referred to as SYS1 here on. Use the Add/Remove programs utility in control panel. Then click the Add Windows Components selection.

1.

Choose Networking services, select Dynamic Host Configuration protocol. 2.

Click Next, and you will be prompted for the Windows server 2003 source files. Required files will be copied to your hard disk.

3.

Click Finish to close the Windows Components Wizard.

How to setup a particular Network Topology

The topology of a network is the pattern used to connect the computers and other devices with the cables or other network medium. There are several basic types of network topologies, or structures in networks. A network can be connected by using any one of the following three topologies: 1.Bus

2.Star

3.Ring

we will discuss the star topology as this is the most common topology used. Star topology

In a star topology, each computer is connected to the hub using a separate cable. Most of the Ethernet LANs installed today, and many LANs using other protocols as well, use the star topology. Star LANs can use several different cable types, including various types of twisted-pair and fiber optic cable. The unshielded twisted pair (UTP) cables used on most Ethernet LANs are usually installed using a star topology. Each computer connects to the hub with its own cable, the hub propagates all signals entering through its ports out through all of its other ports. The main advantage of the star topology is that each computer has its own dedicated connection to the hub, providing the network a measure of fault tolerance.

Concept of Ip address

An important concept of networking is IP addressing. Currently a 32-bit network address is in use all over the world. This address contains 4 octets i.e, four numbers. Each octet can be any number from 0 to 255. Each octet is separated by a period. IP addresses must be unique for each computer in the network. IP addresses commonly fall within three classes: Class A, Class B, and Class C. Class assignments are based on network size and the availability of IP addresses. Each network device needs a unique IP address. The system administrator, or anyone who coordinates IP address assignment and configuration, should assign this address from the pool of addresses he defines. For example if the network contains 25 computers, he may define it as 1 to 25 (192.168.0.1 to 192.168.0.25). In Class A, the first octet refers to network and the next three octets refer to hosts. In Class B, the first two octets refer to network and the next two octets refer to the host. In Class C, the first three octets refer to the network and the last octet refers to host. The term network and 5

host are relevant, when we want to be part of the Internet. If the network we are setting up is not a part of the Internet, then we need to use the following IP addresses of Class C, and they are 192.168.0.0 to 192.168.255.255. These addresses are called private addresses. These addresses are designed to be used within an organization. The last octet in this IP address can be of our choice. Note that in DHCP server we have to provide the range of possible numbers, so that DHCP server will allocate a number with in this range automatically. For public addresses to be used on the internet, InterNIC an organization established to monitor and allocate public IP addresses accepts the requests for these IP addresses and allots the same on a first come first serve basis. SQL Server Database Server installation

After we are done with windows operating system installation and setting up we can now go ahead with sql server installation. The important topics in installation and setting up sql server database are:

How to Secure Databases in Sql Server

Databases have to be secured to ensure that only authorized users can access a SQL Server database. To secure the database the following steps have to be done using the Enterprise Manager. In sql server 2005, there are changes in executing the following steps. If you want to know exactly how it is done in sql server 2005 refer the book titled “Database Programming using visual basic 2005, c# 2005 and sql server 2005”.

1.Creating Logins.

2.Adding Logins to Server Roles.

3.Creating Database Roles.

4.Granting Permissions.

1.Creating Logins

In order for users to get to the data provided by SQL Server, they must first log in to SQL Server. This is the first layer of security that is implemented inside SQL Server itself. SQL Server logins control which individuals or group of individuals have permissions to connect to a SQL Server. There are two basic types of logins. There are logins (windows logins) that are created internally in SQL Server and logins that reference existing windows users or groups. The standard logins are created by the SQL administrator internally in SQL Server and are primarily for non-windows or remote users to use to log in.

2.Adding Logins to Server Roles

As an administrator, we need to allow other people to perform certain administrative activities on the SQL Server. By default, individuals with login access to SQL Server have no implied privileges on the server. If you require specific logins be allowed to perform certain administrative tasks on the server, you need to give users the permissions to do so.

Server roles are basically groups that exist at the server level. The server roles are built into SQL

Server and have specific permissions pre-assigned to them. By adding logins to these Server Roles, you allow those logins to perform the actions for which the role has permissions. For example, adding a login to the dbcreator server role allows those users to create databases in SQL Server. The set of roles that exist at the server level and the permissions assigned to them are all predefined. You cannot create your own server roles, nor can you change their permissions. Because of these restrictions, the server roles are called fixed server roles.

6

3.Creating Database Roles

In SQL Server, groups are called roles. Server roles exist at the server level and allow their members to perform server wide operations. SQL server also provides a number of built-in roles for each database it contains. The built-in database roles, better known as fixed database roles, provide a convenient mechanism for assigning special database permissions to users. SQL Server does allow you to create your own database roles.

4.Granting Permissions

Permissions need to be assigned for users to be able to execute statements and interact with the objects in a database. Permissions can be assigned using Enterprise Manager. In the enterprise manager, Expand the Databases, select the Northwind database and Click the Tables object in the Enterprise manager tree. Then all the tables will be displayed in the right pane. Click on the Categories table, properties dialog will be displayed. Click on the Permissions button, and then the object properties dialog box will be displayed. A checked box for permission implies that the permission is granted. A box with an X in it implies that the permission has been denied, and a cleared check box means that the permission is revoked.

Understanding the Windows Authentication Mode

Windows authentication is generally preferred because it provides an optimal level of integration with Windows 2003 server. User and group accounts from Windows are granted or denied access to SQL

Server. Windows 2003 authenticates the user when the user logs on to the network. Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user. Windows Authentication provides the following advantages over SQL Server Authentication.

Windows Authentication can grant group accounts to access SQL Server, thus minimizing the over head of login administration.

Users are authenticated by Windows 2003, resulting in a secure authentication over a network.

Users could be able to use the same user credentials for network and database access. Audited events can be tracked to a network user.

Understanding the SQL Authentication Mode

SQL Server Authentication is preferred in the following scenarios. 1. The user is not logging into a Windows domain.

2. Your network does not have a Windows 2003 domain.

3. It is not feasible to manage all Internet users on the windows domain. 4. You prefer to manage them separately from your normal Windows domain administration. 5. An application is acquired from a vendor that requires SQL Server Authentication. How to Create Logins in Sql Server

To add a Standard SQL Login using the Enterprise Manager, follow these steps. a.

Select your server in the Enterprise Manager tree.

b.

Expand the Security node, and select logins.

c.

Right-click Logins, and select New Logins from the pop-up menu.

7

d.

Ensure that the SQL Server Authentication option is selected.

e.

Enter the name of the SQL login in the Name field.

f.

Select the default database for the user.

g.

Click OK.

h.

Confirm the password for the login.

Understanding SQL Server Security

To develop a client application that establishes a connection to a data source, we must plan the way in which the connection is made. This includes determining the security mode of the designated data source, and whether it requires a user ID and Password. Security is necessary to protect the information contained in the database. We usually implement one of two primary security options. 1.

Standard security mode.

2.

Integrated security mode.

1.

Standard security mode

Standard security mode is the default security mode for SQL Server. Standard mode uses the SQL

Server security model for every connection to the database. It supports non-trusted environments, such as the internet. Note that users will not necessarily first connect to a Microsoft Windows 2003 or NT server for authentication. SQL Server will perform its own authentication in this situation. The system administrator or database owner can create userIDs, user names, and groups for each database on the server. When this mode is in use, the user must enter a user ID and password that has been established for the database.

2.

Integrated security mode

Integrated security mode allows SQL Server to use Microsoft Windows 2003 or Windows NT

authentication mechanisms to validate all connections to the database. We use integrated security in network environments in which all clients support trusted connections. Integrated security allows applications to take advantage of Windows server security capabilities. With integrated security, user maintain a single user ID and password for both Windows 2003 and SQL Server. If SQL Server is using Integrated Security, the values of user ID and password are ignored. Sql server uses the service security account to access windows resources. The service security account is the logon account that SQL Server uses to access Windows resources. The service security account is not a login for users connecting to SQL server.

Creating a Dedicated Service Security Account

Two options exist for the service security account. The first option is local system account and the second option is dedicated domain user account.

The local system account is a Windows 2003 operating system account with full administrator rights on the local computer. We use this account to install SQL server where we need not integrate SQL

Server with other server applications such as Exchange server.

The recommended option in production environments is to use a dedicated domain user account. The preferred way to use a dedicated domain user account is to create an account in a Windows Active 8

Directory domain that can be referenced by all the computers involved in a domain. We recommend creating a specific account just for SQL Server rather than share a general network system administrator account. This will help reduce the chance that a network-system administrator will one day delete the account or change the password, causing SQL Server to fail. We use Active Directory Users and Computers to create and configure a dedicated Windows 2003 user account. The following are the steps to create a service security account. 1. Ensure that, you are logged on to the domain controller as Administrator. 2. Click Start->Programs->Administrative tools and then click Active Directory users and computers. The Active Directory users and computers screen appears. 3. In the console tree, expand VKINFOTEK.com. Right click users, point to new and then click user.

4. The New Object – User dialog box appears.

5. In the First name text box, type SQL Service. In the User logon name text box, type SQL

Service, and then click next.

6. Type the password in the Password Text Box and Confirm Password text box and select the Password never expires check box, and then click Next.

7. Click the Finish button.

8. In the console tree, click users. The details pane displays a list of all users in the Users container. Verify that the SQL service domain user account has been created and close Active directory users and computers.

In a production setting data entered by the user is stored in a sql server database tables. These tables cab be created using designer or using the scripts. Using scripts is a more professional way of designing the database. Let us now see how it is done.

Creating Tables with SQL Scripts

If you are developing a database for mass deployment or repeatable installations, the benefitsof developing the database schema in scripts become obvious, as listed below. 1. All the code is in one location. Working with SQL scripts is similar to developing an application with Visual Basic or C#.

2. The most current version of the database may be installed without running change scripts or restoring a backup.

All the tables used in the application series books are created using scripts. Drawbacks

The T-SQL commands may be unfamiliar and the size of the script may become overwhelming. In a situation where foreign-key constraints are embedded within the table, the table-creation order is very strict. If the constraints are applied after the tables are created, the table-creation order is no longer a problem; however, the foreign keys are distanced from the tables in the script. The following CREATE TABLE DDL command creates the Customer table. The table name, including the name of the owner (dbo), is provided, followed by the table’s columns. The final code directs SQL

Server to create the table on the primary filegroup. Apart from the columns, the only information you normally supply when creating a table is the name.

CREATE TABLE dbo.Customer (

CustID INT NOT NULL PRIMARY KEY NONCLUSTERED,

CustName VARCHAR(30) UNIQUE NOT NULL,
City VARCHAR(50) NOT NULL

)

ON [Primary]

Creating Keys

Enforcing data integrity ensures that the data in the database is valid and correct. Keys play an important role in maintaining data integrity.

Creating Primary keys

The relational database depends on the primary key. The uses of the primary keys are: 1. To uniquely identify the row.

2. To serve as a useful object for a foreign key.

3. Primary keys can be single columns for fast joins and where clauses. 4. Primary keys never need updating.

5. Note that Primary keys should not contain data that dynamically change, such as a timestamp column, a date-created column, or a date-updated column.

Setting a column, or columns, as the primary key in Enterprise Manager is as simple as selecting the column and clicking the primary-key toolbar button. Enterprise manager creates primary keys with clustered indexes. This is a poor index choice and waste of the one clustered index available for a table.

In the following code, declaring the primary-key constraint in the Create Table statement is shown. CREATE TABLE dbo.Customer (

CustID INT NOT NULL PRIMARY KEY NONCLUSTERED,

CustName VARCHAR(30) UNIQUE NOT NULL,

City VARCHAR(50) NOT NULL

)

ON [Primary]

Two data types are excellent for primary keys: Identity columns and unique identifier columns. Using Identity columns

By far the most popular method for building primary keys involves using an identity column. Like an auto-number column or sequence column on other databases, the identity column generates consecutive integers as new rows are inserted into the database. Advantages of using Identity column for primary keys:

1. Integers are easier to manually recognize and edit than GUIDs. 2. Integers are small and fast

3. An identity column used as a primary key with a clustered index (a common, but poor practice) may be extremely fast when retrieving a single row with a single user. However, that configuration will cause lock-contention hot spots on the database. Identity – column values are created by SQL Server as the row is being inserted, as shown below. CREATE TABLE dbo.Customer (

CustID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
CustName VARCHAR(30) UNIQUE NOT NULL,

City VARCHAR(50) NOT NULL

)

ON [Primary]

Using GUIDS ( Global Unique Identifiers )

GUIDs is a 16-byte hexadecimal number that is essentially unique among all tables, all databases and all servers. The uniqueness is due to the GUID generator using several factors, including the computer NIC code, the MAC address, the CPU internal ID, and the current tick of the CPU clock. Advantages of using GUIDS

1. The randomness of the GUID helps reduce database hot spots by spreading new rows around the table or index and avoiding lock contention.

2. GUIDs discourage users from working with or assigning meaning to the primary keys. 3. GUIDs are forever. The table based on a typical integer-based identity column will hold only 2,147,483,648 rows.

4. Easier to program with GUIDs than with identity columns.

Below is the code for using Unique Identifier.

CREATE TABLE dbo.Customer (

CustID UNIQUEIDENTIFIER NOT NULL

ROWGUIDCOL DEFAULT (NEWID())

PRIMARY KEY NONCLUSTERED,

CustName VARCHAR(30) NOT NULL,

City VARCHAR(50) NOT NULL

)

ON [Primary]

Advantages of Using Indexes

1.

Improves the speed of execution of queries.

2.

Enforces uniqueness of data.

3.

Speeds joins between tables.

Disadvantages of Using Indexes:

1.

Takes disk space to store.

2.

Data modification takes longer as indexes have to be updated.

3.

Takes time to create an index.

Guidelines for choosing the attribute on which the index would be created: 1.

Columns that are frequently used in a SELECT list and a WHERE clause. 2.

Columns where data will be accesses in sequence by a range of values. 3.

Columns that will be used with the GROUP BY or ORDER BY clause to sort the data. Columns used in joins, such as FOREIGN KEY column, so that joins can execute faster. 11

Features of Indexes

1. Indexes accelerate queries that join tables, and perform sorting and grouping 2. Indexes can be used to enforce uniqueness of rows.

3. Indexes are useful on column in which the majority of data is unique. An index on columns containing large amount of duplicate data is not useful.

4. When you modify the data of an indexed column, the associated indexes are updated automatically.

5. Maintaining indexes requires time and resources. You should not create an index that would not be used frequently.

6. The clustered index should be created before nonclustered index. Clustered index changes the order of rows. The nonclustered index would need to be rebuilt if it is built before clustered index.

Typically, non clustered indexes are created on foreign keys.

Developing the application

The one important question every programmer needs to answer before commencing work on a software project is

“how to design the application?”

An application can be an n-tier application or a three-tier application. A programmer should also answer questions like where to place the domain logic, should I use a web or windows UI, how should I validate the data, and many more such questions.

All database applications, whether n-tier or three-tier have three logical layers and they are: 1. User Services Layer

2. Business Services layer

3. Data Services Layer

Three-tier architecture is a client/server architecture in which all the three layers reside separately, either on the same machine or on different machines.

The user interface (presentation layer) interacts with the user and accepts data and passes it to the business services (Business layer) which validates the data and sends it to the data services (Data Layer). While there will be only three logical layers in any application, the physical layers can be many depending on the usage. Many a times the word layers and tiers are used interchangeably. The presentation layer resides in the front as windows forms and web forms and handles input from input devices like the keyboard, mouse, or other devices. Next to the presentation layer is the application or business logic layer, which gives the functionality to the application program. The third layer provides the database service.

What is Business Logic ?

Business logic is the implementation of rules of the business in the software system. In a three-tier architecture, the business logic is a service. This service can be run on a separate server computer. This server computer is called the application server. The computer which runs the database is called 12

as the database server and many a times the same server hosts both the database and business logic layer.

In a three-tier client/server software application, the presentation layer does not have any information about the structure and working of the database. Instead, the presentation layer communicates with the application server using message protocol. The multi-tier design adapted in the book, separates the Interface (Presentation Layer) from the Business Logic and the Database Layer, so the system can be easily adapted to environments or scaled across multiple systems.

By choosing the Multi-tier design, we facilitate unlimited scalability of the application. An overview of the application architecture is given below.

Developing the presentation side of the database application can be grouped under threemain headings.

Menu

Transactions

Reports

Menu

In the windows environment, we use menus to enhance the user interface of an application. Menus offer a convenient and consistent way to organize related options into a group. In Visual Basic, we 13

can create two types of menus, the menus that appear on the menu bar and context menus, which appear when the right mouse button is clicked.

The menus that appear on the menu bar contain a list of options that a user can use for various requirements. For example, in a word processing application, the File menu has options, such as Open, Save and Close. Using Visual basic, we can also create similar menus for an application. Transactions

Many events occur in a company. These events may involve many implications like financial, stock and process implications. Whenever an event occurs a transaction is raised to record the details of the event. In a windows database application, all events are recorded using forms. Depending on the data to be recorded for the event, different user interface controls are used. For example, a textbox control is used to record text data of an event.

Let us see how one such transaction, like the cash voucher transaction is used to record all cash payments in a company.

Once you master the design and programming of this and other transactions listed in the application series books, you can easily extend the knowledge to develop a full fledged applications.

Cash Voucher Transaction

Briefly, a transaction is associated with an event in a firm, wherein, there is an exchange of goods or services for money. Commonly entered data in a cash voucher transaction / form are: Implementing transactions in .net

A transaction is a series of actions that must either succeed, or fail, as a whole. If one of the actions fail, then the entire transaction fails and all the changes made to the database so far, must be reversed (roll back). If all actions succeed, then the transaction is committed to the database. To save the data entered by the user in the cash voucher we will use two tables TranTable and AccountsTable. Both the tables are updated when we save the cash voucher, and then we can say that the transaction is completed. Only after the transaction is committed, other users will be able to view the effects. This process is very important to preserve the integrity of a database. The cash voucher form uses the services of the TranClass for purposes like retrieving and filling the ComboBox controls and saving the transaction.

In the application series books, you can learn how to create the form, how to select and place the controls and how to set the validations.

The following are the list of functions, procedures and event handlers which will enable us to program a complete industrial strength cash voucher transaction. These functions and procedures pertain to the cash voucher form. We write the following functions and procedures in the code behind window of the cash voucher form.

1. Private Sub VouRecForm_Load()

2. Private Function AppendMode()

3. Private Sub EnableMultiple()

4. Private Sub btnAdd_Click()

5. Private Sub btnAcept_Click()

6. Private Sub btnDelete_Click()

7. Private Sub btnCancel_Click()

8. Private Sub btnEdit_Click()

9. Private Sub CheckSaveButton()

10.Private Sub btnSave_Click()

11.Private Sub refreshvar()

12.Private Function Scatter()

13.Private Function ValidControls()

14.Private Sub txtDb_LostFocus(), Private Sub txtCr_LostFocus()

15.Private Sub CreditTotal(), Private Sub DebitTotal()

16.Private Sub txtdb_TextChanged(), Private Sub txtcr_TextChanged() 17.Private Sub MainAmount_LostFocus()

18.Private Sub MainAmount_TextChanged()

19.Private Sub VouRecForm_Closing()

Some of the functions and procedures are explained below.

Private Sub VoucRecForm_Load()

Purpose:

In this event, we initialize the transaction number, date, debit and credit text boxes, cash amount controls. We disable the selector box in the header in the data grid view control with the statement DataGridView1.AllowUserToAddRows = False. This ensures that user does not enter data directly into the datagrid. The debit, credit and accounts combo box controls are also disabled. The statement DataGridView1.DataSource = PrivateDataTable binds the DataGridView control to the DataTable. Private Sub VouRecForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

GetMainAccountDataLocal()

GetAccountDataLocal()

GetTableForGrid()

DataGridView1.AllowUserToAddRows = False

DataGridView1.DataSource = PrivateDataTable

AccountsCombo.Enabled = False

txtTranNo.Text = “”

txtDate.Text = “”

MainAmount.Text = 0

txtDb.Enabled = False

txtCr.Enabled = False

End Sub

GetMainAccountDataLocal()

This procedure instantiates the TranClass and calls the GetMainAccs() method to fill the MainCombo box control.

GetAccountDataLocal()

This procedure instantiates the TranClass and calls the GetMultipleAccs() method to fill the Accounts Combo box control.

GetTableForGrid()
This procedure calls the buildDataTable() procedure to build the datatable.

Private Sub GetAccountDataLocal()

If localAccountData Is Nothing Then

Dim localTransaction As New TranClass(tranCat)

localAccountData = localTransaction.GetMultipleAccs()

localTransaction = Nothing

AccountsCombo.DataSource = localAccountData.Tables.Item(0)

AccountsCombo.DisplayMember = “AccountName”

AccountsCombo.ValueMember = “AccountName”

End If

End Sub

Private Sub GetTableForGrid()

‘Calls the buildDataTable procedure

If localAccountTable Is Nothing Then

buildDataTable()

End If

End Sub

Private Sub GetMainAccountDataLocal()

If localMainAccountData Is Nothing Then

Dim localTransaction As New TranClass(tranCat)

localMainAccountData = localTransaction.GetMainAccs()

localTransaction = Nothing

MainCombo.DataSource = localMainAccountData.Tables.Item(0)

MainCombo.DisplayMember = “AccountName”

MainCombo.ValueMember = “AccountName”

End If

End Sub

Private Sub btnAdd_Click().

Purpose

This procedure is for accepting a new row of data. This new row of data is updated to the grid when the user clicks on the accept button.

Private Sub CheckSaveButton()

Purpose

This procedure enables the save button if the total of debit amounts is equal to the total of credit amounts. This procedure also performs the following tasks.

a.

Check whether transaction number text box is empty. If empty, set focus to the transaction number textbox control.

b.

Check whether transaction date text box is empty. If empty, set focus to the transaction date textbox control.

c.

Check whether account has been entered in the MainComboBox. If empty, set focus to the MainComboBox control.

d.

Check whether amount has been entered in the Amount textbox control. If empty, set focus to the control.

e.

Call function Scatter() to save the transaction.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim lret As Boolean

Dim dtot As Double

Dim ctot As Double

dtot = 0

ctot = 0

If (LTrim(RTrim(txtTranNo.Text)) = “”) Then

MsgBox(“Enter Transaction Number”)

txtTranNo.Focus()

Exit Sub

End If

If (LTrim(RTrim(txtDate.Text)) = “”) Then

MsgBox(“Enter Transaction Date”)

txtDate.Focus()

Exit Sub

End If

If (LTrim(RTrim(MainCombo.Text)) = “”) Then

MsgBox(“Enter Cash Account”)

MainCombo.Focus()

Exit Sub

End If

If MainAmount.Text <= 0 Then

MsgBox(“Enter Positive Amount”)

MainAmount.Focus()

Exit Sub

End If

If InStr(1, CASH_VOUCHER_CAT + CHEQ_VOUCHER_CAT, tranCat) > 0 Then ctot = MainAmount.Text + CreditTotal()

dtot = DebitTotal()

ElseIf InStr(1, CASH_RECEIPT_CAT + CHEQ_RECEIPT_CAT, tranCat) > 0 Then ctot = CreditTotal()

dtot = MainAmount.Text + DebitTotal()

End If

If (dtot = ctot) And dtot > 0 Then

Call Scatter()

Call refreshvar()

MsgBox(“Transaction Saved”)

PrivateDataTable.Clear()

btnSave.Enabled = False

Else

MsgBox(“Double Entry Mismatched”)

End If

End Sub

Private Function Scatter()

Purpose

This function is an important function. This function performs the following tasks. a.

The data which is entered in Tran No, Date, Main AccountsCombo, and Amount controls are assigned to TranClass property procedures.

b.

Calls TranClass-SaveTransaction() method to save the transaction. Private Sub MainAmount_LostFocus()
Purpose

The purpose of this procedure is to validate the numeric data. The MainAmount_LostFocus() event triggers if the focus is leaving the amount text box control.

Private Sub MainAmount_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles MainAmount.LostFocus

If Not IsNumeric(MainAmount.Text) And (MainAmount.Text <> “ “) Then MsgBox(“Not a Numeric Input! Try Again”)

MainAmount.Focus()

Exit Sub

ElseIf (MainAmount.Text < 0) Then

MsgBox(“Not a Numeric Input! Try Again”)

MainAmount.Focus()

Exit Sub

Else

Call CkeckSaveButton()

End If

End Sub

Private Sub MainAmount_TextChanged()

Private Sub MainAmount_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MainAmount.TextChanged

If Not IsNumeric(MainAmount.Text) And (MainAmount.Text <> “ “) Then MsgBox(“Not a Numeric Input! Try Again”)

MainAmount.Focus()

Exit Sub

End If

End Sub

Creating Reports

In an accounting application, data is entered when recording transactions. At the end of the day or at the end of the month, the management may need to view reports which reflect the working of the firm. There are various reports which are prepared in a firm. Let us see how to program the Register report.

The Business Objects Crystal Reports product is one of the world’s best reporting tools. A special edition of Crystal Reports has been embedded within the Visual Studio product. The datasource is the database in which data has been entered using the transaction forms. Cash voucher Register is a report which shows cash payment Transactions for a period. The format for this report is suitable for checking the cash voucher transactions. Steps to develop the report:

a.

Connect a Crystal report to an ADO.NET DataSet.

b.

Bind the report and Set the DataSource to the Populated DataSet. c.

Connect the report to the menu and view the Crystal Report.

Connect a Crystal report to an ADO.NET DataSet

To display cash voucher in the report, we have to retrieve the filtered data from the TranTable. We will connect the Crystal report to an ADO.NET DataSet through a DataSet schema. We need to 18

perform a few extra steps to generate a report from an ADO.NET DataSet, because the report is not connected directly to a database.

A ADO.NET DataSet schema provides a template of the data structure in XML. However, a report cannot retrieve data from the DataSet schema alone. The DataSet schema must first be instantiated as a strongly-typed DataSet instance. The DataSet instance must be filled with data through use of the DataAdapter class.

Step1:

We create a data connection and build a DataSet schema.

Step2:

Create a Report that Connects to the DataSet Schema.

Step1:

We create a data connection and build a DataSet schema.

We use a GUI approach to generate the DataSet schema. Visual Studio 2005 includes the DataSet Designer. It is a tool and is identical to the DataAdapter Configuration Wizard of Visual Studio.Net. In the Solution Explorer, right-click the project name, point to Add, and then click Add New Item. In the Add New Item dialog box, in the Templates list, select DataSet. In the Name field, enter

“TranDataSetSchema.xsd,” and then click Add. From the Server Explorer drag the TranTable onto the TranDataSetSchema.xsd window. From the Build menu, click Build Solution. A strongly-typed DataSet class is generated from the schema. From the File menu, click Save All. The DataSet schema that we have created for the TranTable is a data structure. At runtime, code is required to populate the DataSet structure with data from the database.

Step2:

Create a Report that Connects to the DataSet Schema

We will create this Crystal report that bases its data connectivity on the TranDataSetSchema schema. In Solution Explorer, right-click the project name, point to Add, and then click Add New Item. In the Add New Item dialog box, select Crystal Report. In the Name field, enter the name “Register.rpt” and click Add. Right click the Database fileds node and select Database Expert option. Expand the ADO.NET DataSets node and expand the TranDataSetSchema node. Expand the Tables node and select the TranTable node. Double-click the TranTable to move the table into the Selected Tables panel, and then click. Expand the TranTable in the Field Explorer window and drag the fields as shown below. The report is created with data connectivity to the TranDataSetSchema Binding the Report and Setting the DataSource to the Populated DataSet Create a form and name it as CashReg. Select CrystalReportViewer control from the ToolBox and place it on the form. We will now write code that binds the Crystal report to the DataSet. For the Crystal report binding code, we do the following steps in Form_Load method of CashReg windows form.

Instantiate the report.

Set its SetDataSource property to the populated DataSet property from the helper class. Bind the populated Crystal report to the CrystalReportViewer control. Write the following code in the CashReg Form.

Imports System.Data

Imports System.Data.SqlClient

Public Class CashReg

Dim mycashReg As New Register

Private Sub CashReg_Load(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Me.Load
Dim myConnection As New SqlConnection()

‘Create a connection string to hold the connection string to the FinAccounting Database

myConnection.ConnectionString = “server=SYS2;” +

“integrated

security=SSPI;” + “database=FinAccounting”

Dim MyCommand As New SqlCommand()

MyCommand.Connection = myConnection

MyCommand.CommandText = “select * from TranTable where TranCat=’a’”

MyCommand.CommandType = CommandType.Text

Dim MyDA As New SqlDataAdapter()

MyDA.SelectCommand = MyCommand

Dim myd As New TranDataSetSchema

MyDA.Fill(myd, “TranTable”)

‘Call the SetDataSource() method of the Register report instance and pass into it the DataSet instance.

myCashReg.SetDataSource(myd)

‘Bind the ReportSource property of the CrystalReportViewer control to the Register report instance.

CrystalReportViewer1.ReportSource = mycashReg

End Sub

End Class

Connect the report to the menu and view the Crystal Report. Private Sub cashRegisterToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cashRegisterToolStripMenuItem.Click Dim myCashForm As New CashReg

myCashForm.Show()

End Sub

 

OOPS

Object Oriented Programming is a design and programming methodology, using which we design and develop a software system which resembles a real life model. When we say we are developing an software application for inventory management using OOPs, what we mean is that we are using a real life model to design and develop the software application. A real life model is what we see and live among. The cars we see and the pens we use and tables we write on, are called objects in Object Oriented terminology. Every object in a real life situation or model can be said to belong to a Class. Cars belong to the Class automobile. The real life model can be extended. Suppose, we say that automobiles are classified into four wheelers and two wheelers, then car becomes the instance of the four wheeler sub class and a jeep another instance of the four wheeler sub class. So, the Object Oriented methodology is a classifying system in order to better understand a system. The benefit of OOPs is that we can understand and design a software application in a logical manner. In other words, in OOPs, we use the concept of Classes and Objects, and if required Sub Classes, to define a system. If we define the Class automobile as a class consisting of things which transport people, then the next step is to define the characteristics. The characteristics could be, the four wheels, and the seating, and the steering wheel. In OOPs terminology we call these as attributes or properties. Methods and attributes together are known as the interface of the class.

In a real life model we say the automobile class has the behavior of transporting people. In OOPs terminology, behaviour is referred to as service or method. In the domain (that is the Inventory domain) which we have considered, two of the classes and their objects are:

Clas

s Object

a.

ItemClass

Pens, Pencils

b.

TradeClass

Purchase Invoice, Sales Invoice etc.

The success of the Object Oriented approach lies in the fact that we can commence the design and analysis of the application by using real life situations and approach. Basic elements of an Object Oriented System

The four basic elements of an object oriented system are:

a.

Abstraction.

b.

Encapsulation.

c.

Inheritance.

d.

Polymorphism.

These are key requirements and are often used as evaluation criteria when defining a system. Abstraction

Abstraction is a technique that we all use to manage the complexity of the information. Using abstraction we can focus on the objects of an application and not on the implementation. This lets us think about what needs to be done and not how the computer will do it. 21

Encapsulation

Encapsulation is the ability to contain and hide the information about an object such as internal data structures and code. Encapsulation isolates the internal complexity of an object’s operation from the rest of the application. Polymorphism

Two or more classes having the same behavior or methods that are named the same and have the same basic purpose but different implementations, is polymorphism. For example, ItemClass has Save behavior or method and TradeClass also has Save behavior. But, the implementation of that behavior is completely different in each case. When the Save button is hit, the object knows the class it belongs to and automatically calls the appropriate Class save method.

OOPS Features in Visual Basic

There are many features in VB which support Object Oriented Programming. Some of them are detailed below:

a.

Defining a Class.

b.

Property Procedures.

c.

Polymorphic Methods.

d.

Generating Events.

e.

Viewing a Class.

f.

Creating Objects.

g.

Using Forms as Objects.

a.

Defining a Class

A Class is defined using a feature called Class Module. The Class Module can be added into the project using the Add Class Module option in the Project Menu. Using the Class Module, properties and property procedures can be defined. Behaviors or methods of the class can be implemented using sub and function procedures.

b.

Property Procedures

Property procedures provide the public interface to the private properties in a class. With property procedures we can get and set the value of object property. c.

Polymorphic Methods

Methods are the sub and function procedures in a class that provide the implementation of the object behaviors. Same names can be used for similar properties and methods in different classes which is polymorphism.

d.

Generating Events

In VB events can be defined in a Class. Events are the means to communicate between components.

e.

Viewing a Class
The object browser provides a list of all classes within the current project and the properties, methods and events for each class. You can use the Object Browser to quickly review the interface of a class. The Object Browser also provides a convenient online reference information.

f.

Creating Objects

Objects can be created from the classes. The reference to the created object is stored in an object variable. The object variable is then used to set or retrieve the object’s properties and invoke object’s methods.

g.

Using Forms as Objects

In VB, forms are treated as classes. Many of the features provided for classes are available in forms as well. You can add public properties and methods to a form class defined in a form module. A form object is created by VB when the form is loaded.

The above seven points explain the OOPs features of VB. Implementation of these features is explained in the coming topics.

SP vs Triggers

A stored procedure is a routine written in T-SQL by using the DML, which acts on a 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 stored procedures have been stored to the database, users and applications can 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 the data. Stored procedures which are nothing but data manipulation code, execute faster than passing SQL statements fromVB. A stored procedure can scan thousands 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 statements. After stored procedures are defined, they become part of the database and appear as database objects, like tables or views. This makes it easier to access the stored procedure statements and manipulate them. Once they are tested, stored procedures do not require compiling as is the case with VB code. We can set security on the stored procedure so that only callers with appropriate permissions can execute the logic. Using stored procedures we can encapsulate 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.

Creating and Executing Stored Procedures

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 stored 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.
Triggers are special types of stored procedures. Triggers are very important in SQL

programming. A trigger is a procedure that SQL Server invokes automatically when certain changes applied to data. These actions are inserting, deleting, and updating a row. We can think of triggers as VB event handlers for onUpdate, onInsert, and onDelete events. Creating a trigger is similar to creating a stored procedure.

 

 

 

 

 

Advertisements

9 thoughts on “Develop your database applications

  1. Hello, Neat post. There’s an issue with your site in web explorer, could test this? IE still is the marketplace chief and a large part of people will omit your magnificent writing because of this problem.

  2. Link exchange is nothing else but it is simply placing the other person’s
    web site link on your page at proper place and other person will also do similar in favor of you.

  3. Word Count Plus does just what you think it would plus a lot more.
    This is nice for people who might use a personal calendar, but a
    custom domain for their email. I don’t know if it’s something I’ll keep
    using, as my list of Google services is maybe only 5-6 long, but if the bookmarking is as simple as I think it
    is, I just might.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s