Implementing OOP to Develop Database Oriented Applications using VB.NET 2005

---

A downloadable zip file is available for this article.

---

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:

< <?xml version="1.0" encoding="utf-8"?>

<configuration>

    <system.diagnostics>

      .

      .

      .

      .

    </system.diagnostics>

      <connectionStrings>

            <add name="SampleDB" connectionString="Data Source=.;initial catalog=sample;user id=sa"/>

      </connectionStrings>

</configuration>

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:

Developing a class which holds an entire row in a 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 As Integer

    Private _ename As String

    Private _sal As Double

    Private _deptno As Integer

    Public Property Empno() As Integer

        Get

            Return _empno

        End Get

        Set(ByVal value As Integer)

            If _empno < 0 Then

                Throw New Exception("Invalid empno")

            End If

            _empno = value

        End Set

    End Property

    Public Property Ename() As String

        Get

            Return _ename

        End Get

        Set(ByVal value As String)

            _ename = value.ToUpper

        End Set

    End Property

    Public Property Sal() As Double

        Get

            Return _sal

        End Get

        Set(ByVal value As Double)

            If _sal < 0 Then

                Throw New Exception("Invalid salary")

            End If

            _sal = value

        End Set

    End Property

    Public Property Deptno() As Integer

        Get

            Return _deptno

        End Get

        Set(ByVal value As Integer)

            If _deptno < 0 Then

                Throw New Exception("Invalid deptno")

            End If

            _deptno = value

        End Set

    End Property

EndClass

For convenience, I even added few constructors (as follows) to the above class:

PublicSub New()

    End Sub

    Public Sub New(ByVal empno As Integer, ByVal ename As String, ByVal sal As Double, ByVal deptno As Integer)

        _empno = empno

        _ename = ename

        _sal = sal

        _deptno = deptno

    End Sub

    Public Sub New(ByVal e As Employee)

        _empno = e.Empno

        _ename = e.Ename

        _sal = e.Sal

        _deptno = e.Deptno

    End Sub

Developing a class which holds a set of rows belonging to the same table

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

    Public Sub add(ByVal e As Employee)

        List.Add(e)

    End Sub

    Public Sub delete(ByVal e As Employee)

        List.Remove(e)

    End Sub

    Public Sub delete(ByVal index As Integer)

        If index < 0 Or index > List.Count - 1 Then

            Throw New Exception("Invalid index to remove")

        End If

        List.RemoveAt(index)

    End Sub

    Default Public Overridable ReadOnly Property Item(ByVal index As Integer) As Employee

        Get

            Return CType(Me.List(index), Employee)

        End Get

    End Property

    Public ReadOnly Property Search(ByVal Empno As Integer) As Employee

        Get

            For Each e As Employee In Me.List

                If e.Empno = Empno Then Return e

            Next

            Return Nothing

        End Get

    End Property

EndClass

Developing a factory class to update rows to the database

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:

PublicClass EmployeeFactory

    Inherits Employee

    Public Sub New()

    End Sub

    Public Sub New(ByVal e As Employee)

        MyBase.New(e)

    End Sub

    Public Sub add()

        DBHelper.SQLExecute("insert into emp(empno,ename,sal,deptno) values (" & Empno & ",'" & Ename & "'," & Sal & "," & Deptno & ")")

    End Sub

    Public Sub add(ByVal e As Employee)

        Empno = e.Empno

        Ename = e.Ename

        Sal = e.Sal

        Deptno = e.Deptno

        add()

    End Sub

    Public Sub update()

        DBHelper.SQLExecute("update emp set ename='" & Ename & "',sal=" & Sal & ",deptno=" & Deptno & " where empno=" & Empno)

    End Sub

    Public Sub update(ByVal e As Employee)

        Empno = e.Empno

        Ename = e.Ename

        Sal = e.Sal

        Deptno = e.Deptno

        update()

    End Sub

    Public Sub delete()

        DBHelper.SQLExecute("delete from emp where empno=" & Empno)

    End Sub

    Public Sub delete(ByVal empno As Integer)

        Me.Empno = empno

        delete()

    End Sub

    Public Function getEmployee(ByVal empno As Integer) As Employee

        Dim dr As DataRow = DBHelper.getRow("select * from emp where empno=" & empno)

        empno = dr("empno")

        Ename = dr("ename")

        Sal = dr("sal")

        Deptno = dr("deptno")

        Return CType(Me, Employee)

    End Function

    Public Shared Function getList() As Employees

        Dim dt As DataTable = DBHelper.getDataTable("select * from emp")

        Dim colEmp As New Employees

        For Each dr As DataRow In dt.Rows

            colEmp.add(New Employee(dr("empno"), dr("ename"), dr("sal"), dr("deptno")))

        Next

        Return colEmp

    End Function

EndClass

Developing a class to interact with databases

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:

ImportsSystem.Data.SqlClient

PublicClass DBHelper

    Private Shared Function getConnectionString() As String

        Return
System.Configuration.ConfigurationManager.ConnectionStrings
("SampleDB").ConnectionString

    End Function

    Public Shared Sub SQLExecute(ByVal strSQL As String)

        Dim Conn As New SqlConnection(getConnectionString)

        Dim cmd As New SqlCommand(strSQL, Conn)

        With cmd

            .Connection.Open()

            .ExecuteNonQuery()

            .Connection.Close()

            .Dispose()

        End With

    End Sub

    Public Shared Function getRow(ByVal strSELECT As String) As
DataRow

        Dim da As New SqlDataAdapter(strSELECT,
getConnectionString)

        Dim dt As New DataTable

        da.Fill(dt)

        da.Dispose()

        If dt.Rows.Count = 0 Then Return Nothing Else Return
dt.Rows(0) 'return only first row

    End Function

    Public Shared Function getDataTable(ByVal strSELECT As
String) As DataTable

        Dim Conn As New SqlConnection(getConnectionString)

        Dim da As New SqlDataAdapter(strSELECT, Conn)

        Dim dt As New DataTable

        da.Fill(dt)

        da.Dispose()

        Return dt

    End Function

    Public Shared Function getRowValue(ByVal strSELECT As String)
As String

        Dim Conn As New SqlConnection(getConnectionString)

        Dim cmd As New SqlCommand(strSELECT, Conn)

        Dim value As String = ""

        With cmd

            .Connection.Open()

            value = .ExecuteScalar() & "" 'concatenating an empty
string..to eliminate null or nothing

            .Connection.Close()

            .Dispose()

        End With

        Return value

    End Function

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.

Developing the application

Once you have developed all the core classes, the only part left is the presentation.  The presentation is as simple as the following:

PublicClass Form1

    Dim clnEmp As New Employees

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Me.DataGridView1.DataSource = EmployeeFactory.getList()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim ef As New EmployeeFactory

        ef.add(New Employee(2001, "aaa", 4500, 20))

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim ef As New EmployeeFactory

        ef.delete(2001)

    End Sub

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.

blog comments powered by Disqus