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:
- 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.
- 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.
- 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
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 = _
SqlConnection.ConnectionString = _
Private Shared Sub FillTrendViewTable()
Dim myCommand As New SqlCommand("P_PowerplanComplianceView", SqlConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim adTrendTable As New SqlDataAdapter(myCommand)
Catch ex As Exception
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.
Public Class CDistinctLocations
Public Shared Function GetDistinctsDesks() As ArrayList
Dim asDesks As New ArrayList
For Each row As DataRow In CDataBase.tPowerplanViewTable.Select()
Dim returnArray As ArrayList = _
We can also use it in other parts of the middle level code.
Private Function GetValueofMakeMissForstring(ByVal stringpass As String) As Integer
Dim tablerows() As DataRow
tablerows = CDataBase.tPowerplanViewTable.Select(stringpass)
If tablerows.Length = 0 Then
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 " "
Return Format(Math.Round(make / (make + miss) * 100, 0), "0")
The middle layer is by itself made up of many sub layers, and we construct it in a layered way.
Private Function ConstructSingleRowObject(ByVal loc As String, ByVal sDesk As String, _
ByVal TypeofTrain As Integer) As CSingleRow
Dim aElementArray As New ArrayList
For Each dat As Object In CDistinctDates.GetSortedDistinctDates
aElementArray.Add(ConstructElement(loc, dat, TypeofTrain))
Dim objSingleRow As New CSingleRow(sDesk, loc, TypeofTrain, aElementArray)
Private Function ConstructElement(ByVal loc As String, _
ByVal dat As Date, ByVal TypeofTrain As Integer) _
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), _
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), _
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
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim cdataset As New CConstructDataSet
mainDataTable = cdataset.GenerateDataTable()
DataGrid1.DataSource = mainDataTable
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
Page.RegisterStartupScript("ClientScript", "<script> window.open" & _
"('ExportToExcel.Aspx','cal','width=500,height=400,left=300," & _
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.
Public Sub MakePsngrActualSegmentTable()
mywriter.WriteLogWriter(" Read completed from V_PassengerFinalTable")
mywriter.WriteLogWriter(" Completed getting the Corrected Table")
mywriter.WriteLogWriter(" Completed Updating the SegmentTable")
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.
last few years interested in sql server. picked up .net an year ago.
interested in c#, ADO.net, j# etc etc