Using Methods with 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 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 most advanced topics in Visual Basic.NET 2005.  If you are very new to OOP in VB.NET, I request that 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 file. 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 article 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.

Developing methods which return Boolean values

In my previous article, I introduced classes, members, fields, methods and objects.  Now, in this article, we shall look a bit more in depth into classes and methods.

The following is a new method I added to the class "Emp:"

PublicFunction isEmployeeExist(ByVal empno As String) As Boolean
        Dim cn As New SqlConnection("Data Source=.sql2k5;initial
catalog=sample;user id=sa;password=eXpress2005")
        Dim cmd As New SqlCommand("select 'exists' from sample.dbo.emp
where empno='" & empno & "'", cn)
        Dim found As Boolean = False
        Try
            m_empno = empno
            cmd.Connection.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader
            If rd.Read() Then
                found = True
            End If
        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
        Return found
    End Function

The above method can be considered to be a "method returning a value." In this case, we are returning a Boolean value. To execute the above method, I modified the "btnSearch_click" as follows:

PrivateSub btnSearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSearch.Click
        Me.lblErrMsg.Text = ""
        Try
            Dim ep As New Emp
            If ep.isEmployeeExist(Me.txtEmpno.Text) Then
                ep.load(Me.txtEmpno.Text)
                Me.txtDeptno.Text = ep.m_deptno
                Me.txtEname.Text = ep.m_ename
                Me.txtSal.Text = ep.m_sal
            Else
                Throw New Exception("Employee not found")
            End If
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

Developing methods with parameters having default values

Sometimes, it may be necessary for one or more parameters of a method to have default values. If the calling program didn't pass any values to the parameters, the default values will be taken into consideration. If the user passes values to the parameters, the default values get replaced with the values sent by the calling program.

Let us modify the same method listed in the previous section:

PublicFunction isEmployeeExist(Optional ByVal empno As String = Nothing)
As Boolean
        Dim cn As New SqlConnection("Data Source=.sql2k5;initial
catalog=sample;user id=sa;password=eXpress2005")
        If Not empno Is Nothing Then
            m_empno = empno
        End If
        Dim cmd As New SqlCommand("select 'exists' from sample.dbo.emp
where empno='" & m_empno & "'", cn)
        Dim found As Boolean = False
        Try
            cmd.Connection.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader
            If rd.Read() Then
                found = True
            End If
        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
        Return found
    End Function

From the method above, you can understand that the parameter is mentioned as optional. Currently, I assigned the default value for the "empno" as "nothing." If the calling program doesn't pass any value to this method, it takes "m_empno" as the value to search. To execute the above method, you can work with the same code available in the previous section, or you can further modify it as follows:

PrivateSub btnSearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSearch.Click
        Me.lblErrMsg.Text = ""
        Try
            Dim ep As New Emp
            ep.m_empno = Me.txtEmpno.Text
            If ep.isEmployeeExist() Then
                ep.load(Me.txtEmpno.Text)
                Me.txtDeptno.Text = ep.m_deptno
                Me.txtEname.Text = ep.m_ename
                Me.txtSal.Text = ep.m_sal
            Else
                Throw New Exception("Employee not found")
            End If
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

Dealing with method overloading to attain more flexibility

Let us work with the method "add" now. It is defined as follows:

    Public Sub add()
        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 = "insert into sample.dbo.emp values ('" &
m_empno & "','" & m_ename & "," & m_sal & "," & m_deptno & ")"
                .Connection = cn
                .Connection.Open()
                .ExecuteNonQuery()
            End With
        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 Sub

The following is the code needed to execute the above method:

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click

        Me.lblErrMsg.Text = ""
        Try
            Dim ep As New Emp
            With ep
                .m_empno = Me.txtEmpno.Text
                .m_deptno = Me.txtDeptno.Text
                .m_ename = Me.txtEname.Text
                .m_sal = Me.txtSal.Text
                .add()
            End With
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

From the above code, you can observe that I am assigning all the values to the fields and then only calling the method "add." But, let us consider that I would like to have one more method with the name "add" (for flexibility) which accepts all the fields as parameters.  The following would be another method named "add" (apart from the old method "add").

PublicSub add(ByVal empno As String, ByVal ename As String, ByVal sal As
Double, ByVal deptno As Integer)
        m_empno = empno
        m_ename = ename
        m_sal = sal
        m_deptno = deptno
        add()
    End Sub

The following would be the code needed to execute the above method:

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

Now, you can observe that I defined two methods with the same name ("add"), but with a difference in parameters.  You can call the two methods individually at any time. Depending on the parameters, it would automatically identify the method to execute. This is called "method overloading."

Developing methods with parameters as objects

In the previous section, I explained two different methods with the same name, "add." Now, I shall further extend the same class with one more method (again with the same name) with a different type of parameter, just to extend its flexibility further. Let us go through the code now:

PublicSub add(ByVal objEmp As Emp)
        m_empno = objEmp.m_empno
        m_ename = objEmp.m_ename
        m_sal = objEmp.m_sal
        m_deptno = objEmp.m_deptno
        add()
    End Sub

The above method will be the third method with the name "add" (but with a difference in parameters), still strengthening the "method overloading" concept.  The above method accepts a different type of parameter. It is nothing but a parameter which accepts an object of the type "emp" class. 

To execute the above method, you need to create a separate object of type "emp," assign values to the fields and pass the same object as a parameter, while calling the method "add." The code is shown below:

PrivateSub 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
                .m_deptno = Me.txtDeptno.Text
                .m_empno = Me.txtEmpno.Text
                .m_ename = Me.txtEname.Text
                .m_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 methods which return objects

In the previous section, we developed methods accepting parameters as objects.  In this section, we shall examine the methods returning objects.  Let us look at the code first:

PublicFunction getEmployee(ByVal empno As String) As Emp
        Dim objEmp As New Emp
        Dim cn As New SqlConnection("Data Source=.sql2k5;initial
catalog=sample;user id=sa;password=eXpress2005")
        Dim cmd As New SqlCommand("select * from sample.dbo.emp where
empno='" & empno & "'", cn)
        Try
            objEmp.m_empno = empno
            cmd.Connection.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader
            If rd.Read() Then
                objEmp.m_ename = rd("ename")
                objEmp.m_sal = rd("sal")
                objEmp.m_deptno = rd("deptno")
            Else
                Throw New Exception("Employee not found")
            End If
        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

        Return objEmp
    End Function

Within the above code, you can observe the object named "objEmp." I created it just to place values in it and return the same. Through SqlDataReader, I retrieved all the values and assigned them to the fields of "objEmp."  Finally, I returned the same.

To execute the above code, I wrote the following code:

PrivateSub 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.m_deptno
            Me.txtEname.Text = objReturned.m_ename
            Me.txtSal.Text = objReturned.m_sal
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

The most important issue is the object named "objReturned."  It is not a complete object (as it is not declared with a "new" operator).  It is simply an "object Reference." That means it refers to some other object assigned to it (but it doesn't allocate any memory by itself). In the current case, "getEmployee" creates memory allocation and refers it with "objEmp." Later the same is assigned to "objReturned" (which "refers" to the memory allocated by some other object).

In this article, I simply wanted to explain the topics related to 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 issues.

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