Using Constructors with Object Oriented Database Development with VB.NET 2005 - Developing a simple Data Helper

(Page 5 of 5 )

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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Using Constructors with Object Oriented Database Development with VB.NET 2005

· Hello guys. Now it is the time for constructors in OO DB development with vb.net...   >>> Post your comment now!