Using Methods with Object Oriented Database Development with VB.NET 2005
This is the second article in a series on developing object oriented database applications using Visual Basic.NET 2005. In this article, I shall deal with the concept of “methods” a bit more in depth, along with data access.
A downloadable file for this article is available here.
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 AsString) AsBoolean Dim cn AsNew SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa;password=eXpress2005") Dim cmd AsNew SqlCommand("select 'exists' from sample.dbo.emp where empno='" & empno & "'", cn) Dim found AsBoolean = False Try m_empno = empno cmd.Connection.Open() Dim rd As SqlDataReader = cmd.ExecuteReader If rd.Read() Then found = True EndIf Catch ex As Exception ThrowNew Exception(ex.Message) Finally If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() EndIf cmd.Dispose() cn.Dispose() EndTry Return found EndFunction
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 AsNew 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 ThrowNew Exception("Employee not found") EndIf Catch ex As Exception Me.lblErrMsg.Text = ex.Message EndTry EndSub
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(OptionalByVal empno AsString = Nothing) AsBoolean Dim cn AsNew SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa;password=eXpress2005") IfNot empno IsNothingThen m_empno = empno EndIf Dim cmd AsNew SqlCommand("select 'exists' from sample.dbo.emp where empno='" & m_empno & "'", cn) Dim found AsBoolean = False Try cmd.Connection.Open() Dim rd As SqlDataReader = cmd.ExecuteReader If rd.Read() Then found = True EndIf Catch ex As Exception ThrowNew Exception(ex.Message) Finally If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() EndIf cmd.Dispose() cn.Dispose() EndTry Return found EndFunction
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 AsNew 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 ThrowNew Exception("Employee not found") EndIf Catch ex As Exception Me.lblErrMsg.Text = ex.Message EndTry EndSub
Let us work with the method "add" now. It is defined as follows:
PublicSub add() Dim cn AsNew SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa;password=eXpress2005") Dim cmd AsNew SqlCommand Try With cmd .CommandText = "insert into sample.dbo.emp values ('" & m_empno & "','" & m_ename & "," & m_sal & "," & m_deptno & ")" .Connection = cn .Connection.Open() .ExecuteNonQuery() EndWith Catch ex As Exception ThrowNew Exception(ex.Message) Finally If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() EndIf cmd.Dispose() cn.Dispose() EndTry EndSub
The following is 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 AsNew Emp With ep .m_empno = Me.txtEmpno.Text .m_deptno = Me.txtDeptno.Text .m_ename = Me.txtEname.Text .m_sal = Me.txtSal.Text .add() EndWith Catch ex As Exception Me.lblErrMsg.Text = ex.Message EndTry EndSub
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 AsString, ByVal ename AsString, ByVal sal As Double, ByVal deptno AsInteger) m_empno = empno m_ename = ename m_sal = sal m_deptno = deptno add() EndSub
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 AsNew Emp ep.add(Me.txtEmpno.Text, Me.txtEname.Text, Me.txtSal.Text, Me.txtDeptno.Text) Catch ex As Exception Me.lblErrMsg.Text = ex.Message EndTry EndSub
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."
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:
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 AsNew Emp With objParam .m_deptno = Me.txtDeptno.Text .m_empno = Me.txtEmpno.Text .m_ename = Me.txtEname.Text .m_sal = Me.txtSal.Text EndWith Dim ep AsNew Emp ep.add(objParam) Catch ex As Exception Me.lblErrMsg.Text = ex.Message EndTry EndSub
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 AsString) As Emp Dim objEmp AsNew Emp Dim cn AsNew SqlConnection("Data Source=.sql2k5;initial catalog=sample;user id=sa;password=eXpress2005") Dim cmd AsNew 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 ThrowNew Exception("Employee not found") EndIf Catch ex As Exception ThrowNew Exception(ex.Message) Finally If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close() EndIf cmd.Dispose() cn.Dispose() EndTry Return objEmp EndFunction
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 AsNew 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 EndTry EndSub
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.