“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
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 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 three main headings. Menu
Transactions
Reports
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 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.
TransactionsMany 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()
Private Sub VoucRecForm_Load()
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
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
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"
Private Sub btnAdd_Click().
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()
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.
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
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
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.
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(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
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.
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 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 SchemaWe 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 DataSetCreate 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.
Imports System.Data
Public Class CashReg
Dim mycashReg As New Register
Private Sub CashReg_Load(ByVal sender As Object, ByVal e As
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”)
CrystalReportViewer1.ReportSource = mycashReg
End Sub
End Class
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