Layered approach in software development – A clean way to database connectivity

Introduction

It is a good practice to develop software in a well structured way; this is generally achieved by object oriented approach. Moreover, it is clean to separate programs into layers - the Presentation layer, the Business Logic layer and the Data Access layer. This helps a lot in change management, and helps others understand your code and intent very clearly.

I propose to let you understand my way of coding medium level applications, by means of walking through an example. Also, I will talk about some programming practices when dealing with databases.

The output of the application discussed below is a table that contains cumulative sums that result from rigorous churning of data.

The problem could be tackled in various ways:

1. Write complex queries in the database, and generate a final table in the database by means of views, without using the front end at all.

The problems that arise with this approach is the cumbersomeness in getting the SQL work. Changes are very difficult to make. If the data is very huge, the view generation might as well fail due to time out.

2. Another approach would be to take data to the front end, then make changes, and write as HTML output.

This results in dirty code; changes are very difficult to make.

3. The best is to use object oriented approach, where taking data from the database is very wise, the manipulation of data is done at the middle level by means of generating objects, and finally, to write the output into the front end.

This approach is the best because, the user can walk through the code, understand at each point what is going on, and debugging is very easy to do.

Our SQL database is read once, and a copy of the SQL database table is read into the .NET code as a .NET data table. This makes further querying very efficient and fast, because we don't go back and forth between the physical table in the database, but only to the virtual table in .NET.

Suppose, we are collecting data for the whole of USA. The data of a small town is made of various locations of the town. The data of a region might be a collection of data of a few towns. The data of a zone is made of data of many regions, the regions may combine to form many districts, and so on. Thus, some objects pertaining to a smaller entity join together to form a bigger picture. This is easily done by constructing an upward directed tree, that is the middle layer of the program.

The final table is made by walking through the tree, and it then needs to be displayed. The general approach is to add columns to the display table by means of adding columns. This is probably not a good approach. A better method would be to read from an XML schema file and get to know the columns to be displayed.

A look at the layered approach

We have different classes at different levels - the database access level, the middle level, and the presentation level. We make it work by using objects, which return outputs as required by the other layer.

A look at the data access layer

Imports System.Data.SqlClient

Namespace PowerPlanComplianceNew

'-----------------------------------------------------------------------
' Class Name: CDataBase
'        

'        
' Function:    This is the class that deals with the database. 
'              It runs a stored procedure and gets 
'              the table in the database and fills the datatable
'              named tPowerplanviewTable and it is used 
'              by the whole application.
'    
' Notes:
'-----------------------------------------------------------------------

Public Class CDataBase
    Public Shared SqlConnection As New System.Data.SqlClient.SqlConnection
    Public Shared tPowerplanViewTable As New DataTable

    Shared Sub New()

        Dim configurationAppSettings As System.Configuration.AppSettingsReader = _
            New System.Configuration.AppSettingsReader
        SqlConnection.ConnectionString = _
           CType(configurationAppSettings.GetValue(_
           "PowerplanCompliance.ConnectionString", _
           GetType(System.String)), String)
        FillTrendViewTable()
    End Sub
    '--------------------------------------------------------------------------------
    ' Name: FillTrendViewTable
    ' Description:   This calls a stored procedure in the database
    ' and fills the datatable. we use a data adapter to fill the table
    '         
    ' Parameters:  
    '        
    ' Returns:datatable
    '       
    '-------------------------------------------------------------------------------- 
    Private Shared Sub FillTrendViewTable()

        Dim myCommand As New SqlCommand("P_PowerplanComplianceView", SqlConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        Try

            Dim adTrendTable As New SqlDataAdapter(myCommand)
            adTrendTable.Fill(tPowerplanViewTable)

        Catch ex As Exception
            Throw ex
        End Try

    End Sub

End Class

End Namespace

This is all this class does. Use a configuration reader to read the configuration from the web.config file. (It is not a good idea to put the connection string hard coded inside the code.) Use the configurationAppSetting reader to do the job for you.

The data table that is filled by the data access object can now be used by objects in the middle level.

A look at the middle layer

We will take a look at the middle level code here that uses the data access layer described above.

Imports System.Data.SqlClient
Imports System.Collections


Namespace PowerPlanComplianceNew

'-----------------------------------------------------------------------
' Function:    this has two functions. the first is GetDistinctDesks,
'              that returns the desks.
'              the other function, GetListOfLocsForsDesk returns
'              the locations for the given desk.
'    
' Notes:
'-----------------------------------------------------------------------

Public Class CDistinctLocations
'-------------------------------------------------------------------
' Name: GetDistinctsDesks
' Description:  GetDistinctsDesks is called to return
'               the distinct desks available in the database.
' we find the duplicates and remove them. 
' Parameters:  
'        
' Returns:datatable
'       
'-------------------------------------------------------------------

    Public Shared Function GetDistinctsDesks() As ArrayList
    
        Dim asDesks As New ArrayList
        For Each row As DataRow In CDataBase.tPowerplanViewTable.Select()
            asDesks.Add(row.Item("Desk"))
        Next
        Dim returnArray As ArrayList = _
            MCommonFncs.DeleteDuplicates(asDesks)
        returnArray.Sort()
        Return returnArray
    
    End Function

We can also use it in other parts of the middle level code.

'------------------------------------------------------------------
' Name: GetValueofMakeMissForstring
' Description:   we get the makeormisscount from the database for the passed string
' Parameters:  stringpass
'        
' Returns:integer, the makeormisscount
  '       
'------------------------------------------------------------------ 

Private Function GetValueofMakeMissForstring(ByVal stringpass As String) As Integer

    Dim tablerows() As DataRow
    tablerows = CDataBase.tPowerplanViewTable.Select(stringpass)
    If tablerows.Length = 0 Then
        Return 0
    Else
        Return (tablerows(0).Item("MakeorMissCount"))
    End If
End Function

We must make the code in such a way that we don't write any code that does the same thing at more than one place.

We create a common function module that finds some value based on make and miss values given to it. It would be a bad idea to have a function that does this action at every point of the tree. All the objects can make direct reference to this module and get the proper output. Any change done at this function is reflected through out.

Function FinddPercentageForMakeMiss(ByVal make As Integer, ByVal miss As Integer) As String
    If make = 0 And miss = 0 Then
         Return "  "
    Else
        Return Format(Math.Round(make / (make + miss) * 100, 0), "0")
    End If

End Function

The middle layer is by itself made up of many sub layers, and we construct it in a layered way.

'--------------------------------------------------------------------------------
' Name: ConstructSingleRowObject
' Description:   
' Parameters:  for each distinct date, we construct
'     the elements that make the row. we return the created row
'        
' Returns:CSingleRow
'       
'-------------------------------------------------------------------------------- 

Private Function ConstructSingleRowObject(ByVal loc As String, ByVal sDesk As String, _
                           ByVal TypeofTrain As Integer) As CSingleRow

    Dim aElementArray As New ArrayList ' contains CElements

    For Each dat As Object In CDistinctDates.GetSortedDistinctDates ' we have the dates
        'CType(dat, Date)
        aElementArray.Add(ConstructElement(loc, dat, TypeofTrain))
    Next

    Dim objSingleRow As New CSingleRow(sDesk, loc, TypeofTrain, aElementArray)

    Return objSingleRow ' this contains array of CElements and sLocname

End Function

'--------------------------------------------------------------------------------
' Name: ConstructElement
' Description: we get the values of the elements
'              for the given location, typeof train and date.
' Parameters:  we pass location, date and type of train.
'              we construct elementstruc objs, that make the element
'        
' Returns:CsDeskWiseData
'       
'-------------------------------------------------------------------------------- 

Private Function ConstructElement(ByVal loc As String, _
        ByVal dat As Date, ByVal TypeofTrain As Integer) _
        As CElements

    Dim stringtopass = " Locname = " & "'" & loc & "'" & _
                       " AND ReportDate = " & "'" & dat & "'" _
                       & " AND PowerPlan =  " & "'" & _
                       GetTrainType(TypeofTrain) & "'"

    Dim stringArrival = stringtopass & " AND Event =" & "'" & "ARR" & "'"
    Dim stringDep = stringtopass & " AND Event =" & "'" & "DEP" & "'"
    Return (New CElements(ConstructElementStruc(stringArrival), _
                          ConstructElementStruc(stringDep)))

End Function

'--------------------------------------------------------------------------------
' Name: ConstructElementStruc
' Description:   We pass the string that will return the elementstruc,
'                that will be used to construct an element
' Parameters:    We pass location, date and type of train.
'                we construct elementstruc objs, that make the element
'        
' Returns:CsDeskWiseData
'       
'-------------------------------------------------------------------------------- 

Private Function ConstructElementStruc(ByVal stringpassed As String) As CElementStructure
    Dim elem As New CElementStructure
    Dim yes As Integer

    Dim stringYes = stringpassed & " AND MakeOrMiss = " & "'" & "Y" & "'"
    Dim stringNo = stringpassed & " AND MakeOrMiss = " & "'" & "N" & "'"


    Return (New CElementStructure(GetValueofMakeMissForstring(stringYes), _
                 GetValueofMakeMissForstring(stringNo)))

End Function

We see that at every point, a function calls other functions in a recursive way, so readability is increased. You lose your cognitive efficiency if you have more than, say, 2 or 3 For loops running at the same place of your code. The best thing is to call another function in case it can be done. It might be a good practice to limit the size of each function to, say, 10-15 lines. Any activity that can be done separately should be done outside the function, by means of calling it as a sub function.

A look at the front layer

The front end must have nothing but what is the input and what is the output. It must be as clean as what is presented here. It just has a call to an object of the middle layer. The middle layer returns a data table, which is connected to a datagrid. We do nothing else at this point.

Anyone who reads this program clearly understands what comes to the front end.

Dim mainDataTable As New DataTable
' mainDatatable is the datatable that is used to bind to the datagrids
Private Sub Page_Load(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Dim cdataset As New CConstructDataSet
    ' we generate the datatable and bind to the datagrid
    mainDataTable = cdataset.GenerateDataTable()
    DataGrid1.DataSource = mainDataTable
    DataGrid1.DataBind()
End Sub

Similarly, the button clicks don't have any middle level code associated with them at all.

They just call the appropriate middle level object.

Private Sub ExportToExcel_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles ExportToExcel.Click

    Session("DataTableForExcelExport") = mainDataTable 'take the datagrid data
    Page.RegisterStartupScript("ClientScript", "<script>  window.open" & _ 
       "('ExportToExcel.Aspx','cal','width=500,height=400,left=300," & _ 
       "top=20,menubar=yes,scrollbars=yes,resizable=yes') </script>")

End Sub

The following shows how well a code can be written, that can help another programmer understand your intent very clearly, by creating functions that in turn call sub functions, which in turn call their sub functions. This makes it very readable and easy to change as the input and output are very easy to identify.

Sub Main()
    OpenLogFile()
    MakePsngrActualSegmentTable() ' step 1
    MakePsngrScheduledSegmentTable() ' step2
    MakeFinalDisplayTblActualScheduled() ' step 3
    MakeExcelSheetfromFinalDisplay() 'step4
    UpDateFinalTableToHistory()

    CloseLogFile()
    Exit Sub
End Sub

Public Sub MakePsngrActualSegmentTable()
    psngrTrnActSeg.ReadV_PassengerFinalTable()
    mywriter.WriteLogWriter(" Read completed from V_PassengerFinalTable")
    psngrTrnActSeg.getCorrectedTable()
    mywriter.WriteLogWriter(" Completed getting the Corrected Table")

    psngrTrnActSeg.MakeFirstTableFrmCorrected()
    psngrTrnActSeg.MakeSecondTableFrmCorrected()

    psngrTrnActSeg.MakeSegmentTable()
    mywriter.WriteLogWriter(" Completed Updating the SegmentTable")
End Sub

The above example is part of a very complex piece of application, and is shown to give an idea about the complexity of the code, which was made easy to understand by means of the approaches discussed above. Another aspect of good coding style would be to create a function library of commonly used libraries.