Using Constructors with Object Oriented Database Development with VB.NET 2005

---

A downloadable file for this article is available here.

---

For this article, I assume that you understand enough of 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 together 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 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.  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 in 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 constructors in a class

I already introduced fields/methods/properties in my previous articles. Now, we are about to deal with a new concept called "constructors." A constructor is a special type of method that has the name "new." A constructor is executed automatically when an object of the respective class is created.

Let us go through the following code:

Public Class Emp

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

    Public Sub New()
        m_empno = ""
        m_ename = ""
        m_sal = 0
        m_deptno = 0
        m_errMsg = ""
    End Sub
    ...
End Class

The above class contains a method named "New," which is called a constructor. It is automatically executed for every object created from that class. A constructor generally contains all the statements necessary for initialization.  In this scenario, it may not be essential. 

Some developers use constructors to read connection strings from XML files.  Some use them to open files or streams.  It all depends on the needs of the application.

Defining constructors with parameters in a class

Just like methods in a class, a constructor can also have parameters. This provides a great advantage sometimes when initializing objects with some values. Let us modify the constructor defined in the previous section 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
    Private m_errMsg As String

Public Sub New(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
        m_errMsg = ""
    End Sub
    ...
End Class

The above class is defined with a constructor with parameters. In the above case, we cannot simply create an object with the "new" keyword. The declaration of the object should look something like the following:

            Dim ep As New Emp("1001", "jag", 1000, 10)

The above statement creates and instantiates an object by calling the constructor with parameters. We can rewrite our code for "btnAdd_Click" as follows:

    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(Me.txtEmpno.Text, Me.txtEname.Text,
Me.txtSal.Text, Me.txtDeptno.Text)
            ep.add()
        Catch ex As Exception
            Me.lblErrMsg.Text = ex.Message
        End Try
    End Sub

Constructor overloading within a class

I already defined method overloading in my previous articles. If you are new to the concept, I suggest you go through my previous articles. Now, we shall examine constructor overloading within a class:

Public Class Emp

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

    Public Sub New()
        m_empno = ""
        m_ename = ""
        m_sal = 0
        m_deptno = 0
        m_errMsg = ""
    End Sub

Public Sub New(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
        m_errMsg = ""
    End Sub

    ...
End Class

From the above, we understand that there exist two constructors, constructors without any parameter (or default constructor) and constructors with parameters.  We can also include constructors accepting objects as parameters as follows:

Public Sub New(ByVal objEmp As Emp)
        m_empno = objEmp.empno
        m_ename = objEmp.ename
        m_sal = objEmp.sal
        m_deptno = objEmp.deptno
        m_errMsg = ""
    End Sub

Adding one more method to retrieve a connection string

You must have observed that I hard coded connection strings everywhere when working with database connections. It is always a bad practice to hard code connection strings within an application. I suggest you place the connection string in an XML based "config" file and read it when necessary.  This concept is a bit beyond the scope of this article. 

You can temporarily add a private method, "getConnectionString," to the same class which returns the database connection string. You can use this method everywhere whenever you open the database connections. The following is a simple template for the same:

    Private Function getConnectionString() As String
        Return "Data Source=.sql2k5;initial catalog=sample;user
id=sa;password=eXpress2005"
    End Function

If you are trying to read a "config" file for the connection string, try to include the logic for the same in the above method. Later you can use the above method for creating database connections as follows:

  Dim cn As New SqlConnection(getConnectionString())

You can define your own class to store all your settings in the form of a "config" file. The next section will give you the complete code for working with a "config" file. The source code mainly deals with an XML based "config" file.  It automatically creates a new "config" file, if it does not exist.

The code mainly uses the concept of serialization to store and load the XML data available within the "config" file.

The complete source code for working with the config file

The following is the source code used to work with the "config" file very effectively within a VB.NET application.

Imports System
Imports System.Xml.Serialization
Imports System.IO

Public Class ApplicationSettings
    Private m_DBConnectionString As String = "type ConnectionString to
connect to database here"

    Public Property DBConnectionString() As String
        Get
            Return m_DBConnectionString
        End Get
        Set(ByVal Value As String)
            m_DBConnectionString = Value
        End Set
    End Property

    Public Sub SaveAppSettings()
        Dim myWriter As StreamWriter = Nothing
        Dim mySerializer As XmlSerializer = Nothing
        Try
            ' Create an XmlSerializer for the 
            ' ApplicationSettings type.
            mySerializer = New XmlSerializer( _
              GetType(ApplicationSettings))
            'myWriter = New StreamWriter(Application.LocalUserAppDataPath
+ "Application.config", False)
            myWriter = New StreamWriter(APPLICATION_SETTINGS_FILEPATH +
"Application.config", False)
            ' Serialize this instance of the ApplicationSettings 
            ' class to the config file. 
            mySerializer.Serialize(myWriter, Me)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            ' If the FileStream is open, close it.
            If Not (myWriter Is Nothing) Then
                myWriter.Close()
            End If
        End Try
    End Sub
    Public Function LoadAppSettings() As Boolean
        Dim mySerializer As XmlSerializer = Nothing
        Dim myFileStream As FileStream = Nothing
        Dim fileExists As Boolean = False

        Try
            ' Create an XmlSerializer for the ApplicationSettings type.
            mySerializer = New XmlSerializer(GetType(ApplicationSettings))
            'Dim fi As New FileInfo(Application.LocalUserAppDataPath +
"Application.config")
            Dim fi As New FileInfo(APPLICATION_SETTINGS_FILEPATH +
"Application.config")
            ' If the config file exists, open it.
            If fi.Exists Then
                myFileStream = fi.OpenRead()
                ' Create a new instance of the ApplicationSettings by
                ' deserializing the config file. 
                Dim myAppSettings As ApplicationSettings = CType( _
                  mySerializer.Deserialize(myFileStream), _
                    ApplicationSettings)
                ' Assign the property values to this instance of 
                ' the ApplicationSettings class. 
                Me.DBConnectionString = myAppSettings.DBConnectionString
                fileExists = True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            ' If the FileStream is open, close it.
            If Not (myFileStream Is Nothing) Then
                myFileStream.Close()
            End If
        End Try

        Return fileExists
    End Function

End Class

Developing a simple Data Helper

Retrieving a single row

In general, when we develop any application, we frequently work with getting single or multiple rows, execute SQL statements, and so forth. It is always better to have at least a simple Data Helper to improve productivity.

A sample Data Helper method to retrieve a single row would be as follows:

ImportsSystem.Data.SqlClient
PublicClass DataHelper
    Public Shared Function getDataRow(ByVal sqlSELECT As String) As
System.Data.DataRow
        Dim ConnAs SqlConnection

        Dim da As SqlDataAdapter
        Try
            Conn= New SqlConnection(DB_CONNECTIONSTRING)

            Dim dt As New DataTable
            da = New SqlDataAdapter(sqlSELECT, Conn)
            da.Fill(dt)
            da.Dispose()
            If dt.Rows.Count = 0 Then
                Return Nothing
            Else
                Dim dr As DataRow = dt.Rows(0) 'return only first row
                Return dr
            End If
        Catch ex As Exception
            
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message)
        End Try
    End Function

EndClass

Retrieving multiple rows

In the previous section, I provided a method for retrieving a single row only. In this section, I shall provide another method which retrieves multiple rows.

    Public Shared Function getDataTable(ByVal sqlSELECT As String) As
System.Data.DataTable
        Dim Conn As SqlConnection
        Dim da As SqlDataAdapter
        Try
            Conn= New SqlConnection(DB_CONNECTIONSTRING)
            Dim dt As New DataTable

            da = New SqlDataAdapter(sqlSELECT, Conn)
            da.Fill(dt)
            da.Dispose()
            Return dt

        Catch ex As Exception
            Try
                da.Dispose()
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message)
        End Try
    End Function

To retrieve only a single value, you can use something like the following:

    Public Shared Function getRowValue(ByVal sqlSELECT As String) As
String
        Dim Conn As SqlConnection
        Dim cmd As SqlCommand
        Dim value As String = ""
        Try
            Conn= New SqlConnection(DB_CONNECTIONSTRING)

            cmd = New SqlCommand(sqlSELECT, Conn)
            With cmd
                .Connection.Open()
                value = .ExecuteScalar() & "" 'concatenating an empty
string..to eliminate null or nothing
                .Connection.Close()
                .Dispose()
            End With
            Return value
        Catch ex As Exception
            Try
                If cmd.Connection.State = ConnectionState.Open Then
                    cmd.Connection.Close()
                    cmd.Dispose()
                End If
            Catch e As Exception
                'do nothing...if still error persists
            End Try
            Throw New Exception(ex.Message)
        End Try
    End Function

In this article, I simply wanted to explain some 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.

blog comments powered by Disqus