Implementing OOP to Develop Database Oriented Applications using VB.NET 2005
This article explains how to implement object oriented programming to develop database oriented applications using VB.NET 2005. I took the simplest approach to develop this application.
I am dividing this tutorial into two parts. Part one, which is this article, contains the full source code for developing the application. Part two will contain the full explanation for the source code.
I have already contributed several articles on developing Data Access Layers, Object Oriented Database Development, implementing COM+, working with Enterprise Application Blocks, Windows Services, and so forth on this web site. If you are new to any of those, I suggest you go through my articles at http://www.aspfree.com/cp/bio/Jagadish-Chatarji.
In this article, I thought I would concentrate on OOP rather than data access. Since I have already contributed several articles covering best practices with data access, I don’t want to cover all of them again here. The source code that I developed for this tutorial is purposely made simple. Please note that there are better, even excellent, ways to handle databases from within your application.
Setting up the environment for this contribution
Before we begin, create a new Windows forms application, add an “app.config” file and modify the same in such a way that it looks something like the following:
Since I wanted to demonstrate stored procedures which do not handle existing data in AdventureWorks, I created a table “emp” in a database called “sample,” with the following columns and data types:
Empno (int)
Ename (nvarchar(50))
Sal (float)
Deptno (int)
I pushed the following sample rows into that table:
Every row in a table (especially for lookup tables) must be available in the form of an object, according to the OOP concept. To hold these types of objects, we need to have a collection class to store all those objects.
Before creating the collection class, let us go through the main class which can hold a row of data. The following is the class which holds employee-related information.
PublicClass Employee
Private _empno AsInteger
Private _ename AsString
Private _sal AsDouble
Private _deptno AsInteger
PublicProperty Empno() AsInteger
Get
Return _empno
EndGet
Set(ByVal value AsInteger)
If _empno < 0 Then
ThrowNew Exception("Invalid empno")
EndIf
_empno = value
EndSet
EndProperty
PublicProperty Ename() AsString
Get
Return _ename
EndGet
Set(ByVal value AsString)
_ename = value.ToUpper
EndSet
EndProperty
PublicProperty Sal() AsDouble
Get
Return _sal
EndGet
Set(ByVal value AsDouble)
If _sal < 0 Then
ThrowNew Exception("Invalid salary")
EndIf
_sal = value
EndSet
EndProperty
PublicProperty Deptno() AsInteger
Get
Return _deptno
EndGet
Set(ByVal value AsInteger)
If _deptno < 0 Then
ThrowNew Exception("Invalid deptno")
EndIf
_deptno = value
EndSet
EndProperty
EndClass
For convenience, I even added few constructors (as follows) to the above class:
In the previous section, we saw a class which can store a whole row of information. If we have multiple rows to handle, we need to store them in the form of a collection. The following is the class that can hold multiple objects belonging to the same type:
ImportsSystem.Collections
PublicClass Employees
Inherits CollectionBase
PublicSub add(ByVal e As Employee)
List.Add(e)
EndSub
PublicSub delete(ByVal e As Employee)
List.Remove(e)
EndSub
PublicSub delete(ByVal index AsInteger)
If index < 0 Or index > List.Count - 1 Then
ThrowNew Exception("Invalid index to remove")
EndIf
List.RemoveAt(index)
EndSub
DefaultPublicOverridableReadOnlyProperty Item(ByVal index AsInteger) As Employee
Get
ReturnCType(Me.List(index), Employee)
EndGet
EndProperty
PublicReadOnlyProperty Search(ByVal Empno AsInteger) As Employee
Once we are provided with every object, we need to update them in the database whenever necessary. The following is the class which tries to save the object in the database as needed:
You can observe that the class in the previous section works with another class called “DBHelper.” This class is mainly responsible for interacting with the database independently of any type of object. The following is the code for the class:
value = .ExecuteScalar() & ""'concatenating an empty string..to eliminate null or nothing
.Connection.Close()
.Dispose()
EndWith
Return value
EndFunction
EndClass
At this point, I didn’t include any mapping to stored procedures in the above class. It is a very simple data access class with very few routines. For a better data access class, you can try to use Microsoft Enterprise Library 2.0 with the above DBHelper class to make it more robust. If you are new to that, I suggest you check out my other articles on that topic at www.aspfree.com/cp/bio/Jagadish-Chatarji.
PrivateSub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ef AsNew EmployeeFactory
ef.add(New Employee(2001, "aaa", 4500, 20))
EndSub
PrivateSub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim ef AsNew EmployeeFactory
ef.delete(2001)
EndSub
EndClass
The explanation for all of the code will be part of an upcoming article. Please check back frequently or sign up for a newsletter.
The entire source code for this article is available in the form of a downloadable zip. The solution was developed using Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition together with Microsoft SQL Server 2005 Developer Edition. I didn’t really test the solution with any other/previous editions. But I believe that the above code must execute even in .NET Framework 1.1. If you have any problems with executing the solution, please post in the discussion area.
Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.