Implementing OOP to Develop Database Oriented Applications using VB.NET 2005 - Developing a class to interact with databases
(Page 5 of 6 )

You can observe that the class in the previous section works with another class called “DBHelper.”  This class is mainly responsible for interacting with the database independently of any type of object.  The following is the code for the class:

ImportsSystem.Data.SqlClient

PublicClass DBHelper

    Private Shared Function getConnectionString() As String

        Return
System.Configuration.ConfigurationManager.ConnectionStrings
("SampleDB").ConnectionString

    End Function

    Public Shared Sub SQLExecute(ByVal strSQL As String)

        Dim Conn As New SqlConnection(getConnectionString)

        Dim cmd As New SqlCommand(strSQL, Conn)

        With cmd

            .Connection.Open()

            .ExecuteNonQuery()

            .Connection.Close()

            .Dispose()

        End With

    End Sub

    Public Shared Function getRow(ByVal strSELECT As String) As
DataRow

        Dim da As New SqlDataAdapter(strSELECT,
getConnectionString)

        Dim dt As New DataTable

        da.Fill(dt)

        da.Dispose()

        If dt.Rows.Count = 0 Then Return Nothing Else Return
dt.Rows(0) 'return only first row

    End Function

    Public Shared Function getDataTable(ByVal strSELECT As
String) As DataTable

        Dim Conn As New SqlConnection(getConnectionString)

        Dim da As New SqlDataAdapter(strSELECT, Conn)

        Dim dt As New DataTable

        da.Fill(dt)

        da.Dispose()

        Return dt

    End Function

    Public Shared Function getRowValue(ByVal strSELECT As String)
As String

        Dim Conn As New SqlConnection(getConnectionString)

        Dim cmd As New SqlCommand(strSELECT, Conn)

        Dim value As String = ""

        With cmd

            .Connection.Open()

            value = .ExecuteScalar() & "" 'concatenating an empty
string..to eliminate null or nothing

            .Connection.Close()

            .Dispose()

        End With

        Return value

    End Function

EndClass

At this point, I didn’t include any mapping to stored procedures in the above class. It is a very simple data access class with very few routines. For a better data access class, you can try to use Microsoft Enterprise Library 2.0 with the above DBHelper class to make it more robust. If you are new to that, I suggest you check out my other articles on that topic at www.aspfree.com/cp/bio/Jagadish-Chatarji.

Next: Developing the application >>
 

More Visual Basic.NET Articles
More By Jagadish Chaterjee

Comments On: Implementing OOP to Develop Database Oriented Applications using VB.NET 2005

· This is a simple introduction to developer OOP based database applications using...    · Excellent article. Jag is a great teacher.keep up the good work   >>> Post your comment now!