An Introduction to 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 covers the basics of object-oriented programming 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.  You do not need to be familiar with OOP to understand this article.

The entire source code for this article is available at the link indicated above 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 database interactions in a separate class: source code

Once you open the downloadable solution, you will be able to find a file named “emp.vb” in the solution explorer.  Double click on it to open and you will find the following source code in it:

ImportsSystem.Data.SqlClient

PublicClass Emp

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

    Public Sub load(ByVal empno As String)
        m_empno = empno
        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)
        cmd.Connection.Open()
        Dim rd As SqlDataReader = cmd.ExecuteReader
        rd.Read()
        m_ename = rd("ename")
        m_sal = rd("sal")
        m_deptno = rd("deptno")
        cmd.Connection.Close()
        cmd.Dispose()
        cn.Dispose()
    End Sub
EndClass

To access the same class, I designed a small form with four text boxes (for empno, ename, sal and deptno) and a few buttons.  The code for the button “search” is given below:

PrivateSub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim ep As New Emp
        ep.load(Me.txtEmpno.Text)
        Me.txtDeptno.Text = ep.m_deptno
        Me.txtEname.Text = ep.m_ename
        Me.txtSal.Text = ep.m_sal
    End Sub

I shall explain the above code in the next section.

Explaining the class

From the above code, you must be able to understand that I added a class to my solution named “Emp.”  When we speak about a class, we need to know its “members” as well.  Within the above class, we have the following “class members:”

• m_empno
• m_ename
• m_sal
• m_deptno
• load

Of all of the above, the first four members (m_empno, m_ename, m_sal and m_deptno) are generally termed fields/attributes/member variables/class level variables.  Since we defined all members as “public,” all of them are accessible everywhere (including within the class).  I shall explain accessibility options later in my upcoming articles. 

There exists one more member, called “load.”  It is not a variable.  It is a sub-routine containing some logic.  Any sub-routine/function available in class is generally termed a “method.” To put it simply, the class “emp” contains five “members,” where four are the “fields” and one is the “method.”

There can be several types of “methods” in a class. Some methods may be defined as sub-routines and others as functions.  A sub-routine doesn’t return a value, whereas a function would return something. They are also described as  “methods not returning values” (sub-routines) and “methods returning values” (functions).  Some methods may also be defined with some “parameters,” to pass some values to them.  They are generally termed “methods with parameters.”

In our case “load” is a “method not returning a value” and also a “method accepting a parameter.”  It is mainly used to encapsulate the logic of retrieving the employee information from the database.

Explanation continued

The first and most important issue to remember is that a class is simply a template/definition.  A class is very much like a user-defined data type.  A data type cannot hold data by itself.  A variable (which allocates some memory) must have a definition based on a data type to hold some value. The value is stored in the memory allocated by the variable. 

In very much the same way, a class doesn’t contain any data on its own.  We need to create something like a variable to work with a class, or to access class members. Any variable which is created on the basis of the class is termed an “object” (or “instance”).  Every “object” you create based on a class must be instantiated (or allocated with memory) with the operator “new.” The process of instantiation is simply the calculation of memory to be occupied by “class members” and the allocation of the same. 

Let us consider the first statement in “btnSearch_click:”

        Dim ep As New Emp

The above statement creates and instantiates an “object” (or “instance”) named “ep” based on the class “Emp.” Once the object is created/instantiated, we can access all of its members programmatically. Proceeding further, we have the following:

        ep.load(Me.txtEmpno.Text)

The above statement executes the method “load” available in the object “ep.”  The method “load” accepts a parameter (therefore called a “method with parameters”) to retrieve the information of the “empno” sent to it.  Once the method is executed successfully, all the information of that “empno” is assigned to the “fields” of the class.  We access the fields of object “ep” using the following statements:

        Me.txtDeptno.Text = ep.m_deptno
        Me.txtEname.Text = ep.m_ename
        Me.txtSal.Text = ep.m_sal

Expanding the class to handle errors

Any application must include error handling. If you do not include error handling, the application may get terminated abnormally when a run-time error occurs.  Run-time errors are a bit hard to detect and identify; it requires some experience.  Some types of run-time errors include database connectivity errors, row/table not found errors, network errors, data type conversion errors, and so forth.

The above class (and application) currently does not include any errors. To handle the errors, we need to work with “exception handling.”  We trap run-time errors using exception handling and provide our own alternative to run our application smoothly. We do this by giving proper, understandable messages to the user,  without any abnormal termination.

The method “load” in the class “emp” can be rewritten as follows to include exception handling:

    Public Sub load(ByVal empno As String)
        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
            m_empno = empno
            cmd.Connection.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader
            If rd.Read() Then
                m_ename = rd("ename")
                m_sal = rd("sal")
                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

    End Sub

If any error occurs in the above method "load," it will be thrown back to the calling program (in this case, it is the application itself).  So, we need to handle the exceptions thrown to the main application 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.load(Me.txtEmpno.Text)
            Me.txtDeptno.Text = ep.m_deptno
            Me.txtEname.Text = ep.m_ename
            Me.txtSal.Text = ep.m_sal
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

Further expanding the class to handle additional rows

Until now, in the previous sections, we have only seen examples of fetching a single row. Now, let us expand further to handle “adding an employee” using the same class. To do this, we need to add another method in the class “emp,” named “add.” The following would be the code needed to add a new employee:

PublicSub 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

Make sure that the above method is added to the existing class “emp” and that it has no parameters as well. To access the above method, I wrote the following code for a new button:

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
            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

You can observe that I am simply creating a new object, assigning values to the fields and calling the methods.

In this article, I simply wanted to explain classes, fields, methods and objects.  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.