Step 4 - Implementing the Business Layer

This part assumes you have followed the instructions up to step 3 (creating the tables) and "Before step 4 - parameters". This part uses the information presented earlier and the objects created and code written, to implement the functionality described in the program design part.

What is the Business Layer?

The "business layer" is a set of Subs the user interface calls and are logically located in between the user-interface and the database. Since a complete explanation of the multitier (multilayer) architechture is out of the scope of this guide, here is a link to the relevant Wikipedia article. For our purpose it is enough to know that the "Layer" is actually a set of subs. We call these subs from the UI, and they call the database. This way we can isolate database from the UI, Thus

1. Creating a more readable code

2. Allowing later replacement of each side easily

3. Simplifying debugging by isolating database accessing from UI events.

Methods contained in the Business layer

The business logic layer is the part of the program with which the user interface connects. Subs implemented there are:

• getOrderData (ID) - retrieves all data of a single order with a given ID.

• addOrder(sum, tabNum, seatNum, isHamburger, isFrench, isCoke, isWater, isServed, timeTaken ) - adds an order, with the given parameters, to the order list.
• updateOrder(id, sum, tabNum, seatNum, isHamburger, isFrench, isCoke, isWater, isServed, timeTaken ) - Update an order with a given ID in the order list.
• deleteOrder(id) - Delete an order with a given ID from the list.
• getListOpenedOrdersToTable(isServed) - Return a list of all orders containing given value in the isServed field.
• getListOrdersToTable - Returns a list of all orders in the database.

Points of interest in the code

A few points below worth mentioning even if you are not about to read it all. If you are in a hurry, pay attention especially to the transactions part, and to the ExecuteTable sub, described below. Apart from these two new things, there are a few tips and a demonstration of using SQL main statements: SELECT, INSERT INTO, UPDATE, DELETE and WHERE.

Transactions

Before we go on to the implementation, we introduce an important  feature to be aware of. The Connection object demands that when executing any SQL command, there must be a "Transaction" opened for the command. If there is not one, the Connection object creates one. When executing several commands in a row, it is faster to create the transaction manually. Start with Connection.BeginTransaction and end your code-block with Connection.EndTransaction. For instruction reasons, we will follow this rule in each Sub here.

Code for the Business Layer

getOrderData(ID) - notes:

1. We start the transaction at line 3, and end it at line 26.

2. Adding parameters is done as described here.

3. In line 13 we assign the data to a global variable, defined in Sub_Globals, called order. This is a structure that holds the order data. It is used as a bridge between the Subs and the User Interface: later these values are copied to the form's controls.

4. Do not forget to close the reader.

1 Sub getOrderData(ID) 2 'Start transaction 3 Connection.BeginTransaction 4 'Set parameter values 5 Command.AddParameter("ID") 6 Command.SetParameter("ID", ID) 7 Command.CommandText="SELECT * FROM t_orders WHERE ID=@ID" 8 'Fetch data 9 Reader.Value = Command.ExecuteReader 10 11 'Assign data 12 If Reader.ReadNextRow = True Then 13 order.ID = ID 14 order.sum = Reader.GetValue(1) 15 order.tableNum = Reader.GetValue(2) 16 order.seatNum = Reader.GetValue(3) 17 order.hamburger = Reader.GetValue(4) 18 order.french = Reader.GetValue(5) 19 order.coke = Reader.GetValue(6) 20 order.water = Reader.GetValue(7) 21 order.isServed = Reader.GetValue(8) 22 order.timeTaken = Reader.GetValue(9) 23 End If 24 Reader.Close 25 ' End transaction 26 Connection.EndTransaction 27 End Sub

Sub addOrder(sum, tabNum, seatNum, isHamburger, isFrench, isCoke, isWater, isServed, timeTaken )
	' Start transaction
	Connection.BeginTransaction
	' Create parameter
	Command.AddParameter("sumMon")
	command.AddParameter("tabNum")
	command.AddParameter("seatNum")
	command.AddParameter("isHamburger")
	command.AddParameter("isFrench")
	command.AddParameter("isCoke")
	command.AddParameter("isWater")
	command.AddParameter("isServed")
	command.AddParameter("timeTaken")
	

	command.SetParameter("sumMon" ,sum)	     
	command.SetParameter("tabNum", tabNum)
	command.SetParameter("seatNum", seatNum)
	command.SetParameter("isHamburger", isHamburger)
	command.SetParameter("isFrench", isFrench)
	command.SetParameter("isCoke", iscoke)
	command.SetParameter("isWater", isWater)
	command.SetParameter("isServed", isServed)
	command.SetParameter("timeTaken", Time(timeTaken))	

	Command.CommandText = "INSERT INTO t_orders (ID,Sum,TableNum,SeatNum,Hamburger,French,Coke,Water,isServed,Time)" & _
	 " VALUES (null,@sumMon,@tabNum,@seatNum,@isHamburger,@isFrench,@isCoke,@isWater,@isServed,@timeTaken)"
						  
	Command.ExecuteNonQuery
	' End transaction
	Connection.EndTransaction
End Sub

Sub updateOrder(id, sum, tabNum, seatNum, isHamburger, isFrench, isCoke, isWater, isServed, timeTaken )
	' Start transaction
	Connection.BeginTransaction
	' Create parameters
	Command.AddParameter("id")
	Command.AddParameter("sum")
	Command.AddParameter("tabNum")
	Command.AddParameter("seatNum")
	Command.AddParameter("isHamburger")
	Command.AddParameter("isFrench")
	Command.AddParameter("isCoke")
	Command.AddParameter("isWater")
	Command.AddParameter("isServed")
	Command.AddParameter("timeTaken")	

	Command.SetParameter("id" ,id)	     
	Command.SetParameter("sum" ,sum)
	Command.SetParameter("tabNum", tabNum)
	Command.SetParameter("seatNum", seatNum)
	Command.SetParameter("isHamburger", isHamburger)
	Command.SetParameter("isFrench", isFrench)
	Command.SetParameter("isCoke", isCoke)
	Command.SetParameter("isWater", isWater)
	Command.SetParameter("isServed", isServed)
	Command.SetParameter("timeTaken", timeTaken)	

	Command.CommandText = "UPDATE t_orders SET Sum=@sum, TableNum=@tabNum, SeatNum=@seatNum, Hamburger=@isHamburger, French=@isFrench, " & _
						  "Coke=@isCoke, Water=@isWater, isServed=@isServed, Time=@timeTaken WHERE id=@id" 
	Command.ExecuteNonQuery
	' End transaction
	Connection.EndTransaction
End Sub

Sub deleteOrder(id)
	' Start transaction
	Connection.BeginTransaction
	' Create parameters
	Command.AddParameter("id")	
	Command.SetParameter("id" ,id)	     

	Command.CommandText = "DELETE FROM t_orders WHERE id=@id" 
	Command.ExecuteNonQuery	

	' End transaction
	Connection.EndTransaction
End Sub

Sub getListOpenedOrdersToTable(isServed)
	' Start transaction
	Connection.BeginTransaction
	' Create parameters
	Command.AddParameter("isServed")	
	Command.SetParameter("isServed" ,isServed)	   	

	Command.CommandText = "SELECT * FROM t_orders WHERE isServed=@isServed" 
	Command.ExecuteTable("tblOrdersList", 1000)	

	' End transaction
	Connection.EndTransaction
End Sub

Sub getListOrdersToTable()
	' Start transaction
	Connection.BeginTransaction

	Command.CommandText = "SELECT * FROM t_orders" 
	Command.ExecuteTable("tblOrdersList", 1000)

	' End transaction
	Connection.EndTransaction
End Sub