Properties and Object Oriented Database Development with VB.NET 2005

For this article, I assume that you know enough about the basics of working with VB.NET controls, ADO.NET, and so forth of using Visual Basic.NET 2005.  Even though this article only gives you the basics of OOP along with database development, I shall extend it in the form of a series to cover the most advanced topics in Visual Basic.NET 2005.  If you are very new to OOP in VB.NET, I suggest you go through my first article in this series.

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 with Microsoft SQL Server 2005 Developer Edition on Microsoft Windows Server 2003 Enterprise Edition.  Even though I believe that the source code available with this contribution can work with Microsoft Visual Studio.NET 2003/2002, I didn’t really test it in any other environment.  I request that you post in the discussion area if you have any problems with execution.

To make this article simple, I created a sample database named “sample,” with a table “emp” containing the columns empno (string), ename (string), sal (double) and deptno (integer) and a few rows.

Defining your own properties within a class: the basics

In my first article, I covered the concept of fields (or member variables). Fields are mainly used to contain data. Sometimes, the data in fields may be sensitive to the logic, where they must not be altered by the calling program. Further, to assign data to the fields, we may need to enforce some validation. 

To put it simply, we need to protect the integrity of the data available in the fields along with proper accessibility. To solve all those needs, VB includes a separate construct called “Property.”  Let me rewrite a single field with proper accessibility and validation as follows:

Public Class Emp
...
    Private m_sal As Double

    Public Property sal() As Double
        Get
            Return m_sal
        End Get
        Set(ByVal value As Double)
            If value < 0 Then
                Throw New Exception("Salary cannot be negative")
            End If
            m_sal = value
        End Set
    End Property
...
End Class

The first and the most important statement is the following:

    Private m_sal As Double

You can observe that I replaced the previous “public” with “private.” This causes the field “m_sal” to be “private” to the class; it is accessible only within the class and not outside the class.  By making it private, we protect it totally by hiding it from any other source, apart from the class where it has been defined.

If the fields are made “private,” we need to provide some type of bridge to access them in a secured manner (which may also include some validation). That bridge will be nothing but the “Property” construct.  A “Property” construct bridges the gap between the private fields and the calling program. The calling program accesses the contents of the private field with the help of the “Property.”  The “Property” makes sure that it is secured with valid data.

The “Property” construct mainly contains two parts, “get” and “set.” The part “get” is executed when we try to retrieve from the “Property.”  The part “set” is executed when we try to assign to the “Property.”  A simple example of the get/set access of property is as follows:

Me.txtEmpno.Text = "1001"
Dim empno As String = Me.txtEmpno.Text

Within the above statement, I am assigning some value to the property “text,” which causes the part “set” to invoke (by passing “1001” as parameter to the part “set”).  The second statement simply retrieves some value from the property “text,” which causes the part “get” to invoke.

The class “Emp” rewritten with properties

Since I explained the “properties” in the previous section, we shall rewrite the class “emp” by adding properties to the fields as follows:

Public Class Emp

    Private m_empno As String
    Private m_ename As String
    Private m_sal As Double
    Private m_deptno As Integer

    Public Property sal() As Double
        Get
            Return m_sal
        End Get
        Set(ByVal value As Double)
            If value < 0 Then
                Throw New Exception("Salary cannot be negative")
            End If
            m_sal = value
        End Set
    End Property

    Public Property empno() As String
        Get
            Return m_empno
        End Get
        Set(ByVal value As String)
            m_empno = value
        End Set
    End Property

    Public Property ename() As String
        Get
            Return m_ename
        End Get
        Set(ByVal value As String)
            m_ename = value
        End Set
    End Property

    Public Property deptno() As Integer
        Get
            Return m_deptno
        End Get
        Set(ByVal value As Integer)
            m_deptno = value
        End Set
    End Property
...
End Class

In the current case, I included validation for the property “sal” only.  Based on the requirements, you may need to add a few more validations to a few more properties accordingly. 

Accessing the object properties from the form

Modifying the class as in the previous section would cause errors to be thrown in your form code.  You need to modify the form code as follows to work with properties:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Me.lblErrMsg.Text = ""
        Try
            Dim ep As New Emp
            Dim objReturned As Emp
            objReturned = ep.getEmployee(Me.txtEmpno.Text)
            Me.txtDeptno.Text = objReturned.deptno
            Me.txtEname.Text = objReturned.ename
            Me.txtSal.Text = objReturned.sal
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Me.lblErrMsg.Text = ""
        Try
            Dim objParam As New Emp
            With objParam
                .deptno = Me.txtDeptno.Text
                .empno = Me.txtEmpno.Text
                .ename = Me.txtEname.Text
                .sal = Me.txtSal.Text
            End With
            Dim ep As New Emp
            ep.add(objParam)
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

Developing read-only/write-only properties

There will be times when you need only read-only/write-only properties.  The best example would be something like the following statement:

a = Me.ListBox1.Items.Count

If I try to write the same statement shown above as something like the following, it would give an error:

Me.ListBox1.Items.Count = a

That means we can only read from the property “Count,” but not assign any value to it, which is what is called a “read-only” property. 

Let us now try to determine a read-only property called “EmployeeCount,” which returns the number of employees existing in the table.  The code would be something like the following:

Public ReadOnly Property EmployeeCount() As Integer
        Get
            Dim cn As New SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa;password=eXpress2005")
            Dim cmd As New SqlCommand("select count(*) from sample.dbo.emp", cn)
            Try
                Dim n As Integer
                cmd.Connection.Open()
                n = cmd.ExecuteScalar
                Return n
            Catch ex As Exception
                Throw New Exception(ex.Message)
            Finally
                If cmd.Connection.State = ConnectionState.Open Then
                    cmd.Connection.Close()
                End If
                cmd.Dispose()
                cn.Dispose()
            End Try
        End Get
    End Property

You can observe that the above property doesn’t have any “set” part and the property declaration is given with “readonly.” To use this property, I added a simple label called “lblNoOfEmployees” to the form and added the following code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim ep As New Emp
            Me.lblNoOfEmployees.Text = "No. of employees: " & ep.EmployeeCount
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

Very similar to the above read-only property, we also have a write-only property.  The write-only property is used only to assign a value, but not for retrieving a value.  In the case of the write-only property, we will have only a “set” part without a “get” part and the declaration is given with “writeonly.” 

Using read-only properties to deal with error messages

I already covered read-only properties in the previous section. There exists one more situation where you can use read-only properties flexibly. In my first article, I explained exception handling. Now, I can use exception handling and provide the error messages using a read-only property!

Let us go through the following code first:

Public Class Emp
      ...
    Private m_errMsg As String
      ...
    Public Sub delete(ByVal empno As String)
        m_errMsg = ""
        Dim cn As New SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa;password=eXpress2005")
        Dim cmd As New SqlCommand
        Try
            With cmd
                .CommandText = "delete from sample.dbo.emp where empno='" & empno & "'"
                .Connection = cn
                .Connection.Open()
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            m_errMsg = ex.Message
        Finally
            If cmd.Connection.State = ConnectionState.Open Then
                cmd.Connection.Close()
            End If
            cmd.Dispose()
            cn.Dispose()
        End Try
    End Sub

    Public ReadOnly Property ErrorMessages() As String
        Get
            Return m_errMsg
        End Get
    End Property
End Class

You can observe that I included a separate field to hold error messages. When any exception/error is generated within the method “delete,” the error message is stored in “m_errMsg.” You can check/retrieve the error messages using a read-only property called “ErrorMessages.” The above is simply an example to let you know about other possibilities. You should double check as to whether you really need this type of logic to be implemented in your application.

In this article, I simply wanted to explain the topics of OOPS along with data access.  The sample codes given in this article are neither the best in performance nor the best in programming methodologies.  My upcoming articles will deal with these aspects.

Any feedback, suggestions, bugs, errors, improvements etc., are highly appreciated at jag_chat@yahoo.com.