Database utility classes for Visual Basic.net

DatabaseReader.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

''' <summary>
''' A class that facilitates reading data from a database into obhjects of arbirtrary classes.
''' </summary>
Public Class DatabaseReader

	Public Property ConnectionString as String
	
	Private Function NewConnection() as SqlConnection
		Return New SqlConnection(ConnectionString)
    End Function 

	''' <summary>
	''' Read objects of a particular class from a database query.
	''' </summary>
	''' <typeparam name="T">Requested class.</typeparam>
	''' <param name="configurationHandle">Name of the connection string defined in Web.Config to use for this connection.</param>
	''' <param name="query">Query to run.</param>
	''' <param name="func">Function that reads data from a reader into the outbound object.</param>
	''' <returns>A collection of obejcts of the requested class.</returns>
	''' <remarks>If T contains a constructor with a IDataReader parameter, 'func' is simply 'return new T(reader)'.</remarks>

	Public Shared Function ReadObjects(Of T)(ByVal query As String, ByVal func As Func(Of IDataReader, T)) As IEnumerable(Of T)
		Dim outbound As New List(Of T)

		Using connection As SqlConnection = NewConnection()
			connection.Open()
			Using command As SqlCommand = New SqlCommand(query, connection)
				command.CommandType = CommandType.StoredProcedure
				command.CommandTimeout = 0
				Dim reader As SqlDataReader = command.ExecuteReader
				While reader.Read()
					outbound.Add(func.Invoke(reader))
				End While
			End Using
			connection.Close()
		End Using

		Return outbound
	End Function

	''' <summary>
	''' Read objects of a particular class from a stored procedure.
	''' </summary>
	''' <typeparam name="T">Requested class.</typeparam>
	''' <param name="configurationHandle">Name of the connection string defined in Web.Config to use for this connection.</param>
	''' <param name="procedureName">Name of the procedure to run.</param>
	''' <param name="parameters">Parameters to pass to the procedure.</param>
	''' <param name="func">Function that reads data from a reader into the outbound object.</param>
	''' <returns>A collection of obejcts of the requested class.</returns>
	''' <remarks>If T contains a constructor with a IDataReader parameter, 'func' is simply 'return new T(reader)'.</remarks>

	Public Shared Function ReadObjects(Of T)(ByVal procedureName As String, ByVal parameters As IDictionary(Of String, Object), ByVal func As Func(Of IDataReader, T)) As IEnumerable(Of T)
		Dim outbound As New List(Of T)

		Using connection As SqlConnection = NewConnection()
			connection.Open()
			Using command As SqlCommand = New SqlCommand(procedureName, connection)
				command.CommandType = CommandType.StoredProcedure
				command.CommandTimeout = 0
				For Each kvp As KeyValuePair(Of String, Object) In parameters
					command.Parameters.AddWithValue(kvp.Key, kvp.Value)
				Next
				command.ExecuteScalar()
			End Using
			connection.Close()
		End Using

		Return outbound
	End Function

	''' <summary>
	''' Execute a stored procedure.
	''' </summary>
	''' <param name="configurationHandle">Name of the connection string defined in Web.Config to use for this connection.</param>
	''' <param name="procedureName">Name of the procedure to run.</param>
	''' <param name="parameters">Parameters to pass to the procedure.</param>

	Public Shared Sub RunProcedure(procedureName As String, parameters As IDictionary(Of String, Object))

		Using connection As SqlConnection = NewConnection()
			connection.Open()
			Using command As SqlCommand = New SqlCommand(procedureName, connection)
				command.CommandType = CommandType.StoredProcedure
				command.CommandTimeout = 0
				For Each kvp As KeyValuePair(Of String, Object) In parameters
					command.Parameters.AddWithValue(kvp.Key, kvp.Value)
				Next
				command.ExecuteScalar()
			End Using
			connection.Close()
		End Using

	End Sub

End Class

ProcedureReader.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

''' <summary>
''' A class that facilitates reading data from a database into obhjects of arbirtrary classes.
''' </summary>
''' <remarks>
''' See class DatabaseReader, which can read procedures but also can read direct queries.
''' If policy requires communicating only by stored procedure, then this class would be more appropriate.
''' </remarks>
Public Class ProcedureReader(Of T)

    Public Property ProcedureName As String
    Public Property ReaderFunction As Func(Of IDataRecord, T)
    Public Property ConnectionString As String

    Dim _parameters As New List(Of SqlParameter)

    Public Sub AddParameter(parameter As SqlParameter)
        _parameters.Add(parameter)
    End Sub

    Public Function Execute() As T()
        Dim outbound As New List(Of T)

        Using connection As New SqlConnection(Me.ConnectionString)

            Using command As IDbCommand = New SqlCommand(Me.ProcedureName, connection)

                command.CommandType = CommandType.StoredProcedure
                command.CommandTimeout = 200

                For Each p In _parameters
                    command.Parameters.Add(p)
                Next

                Try
                    connection.Open()
                    Dim reader As IDataReader = command.ExecuteReader
                    While reader.Read()
                        outbound.Add(Me.ReaderFunction.Invoke(reader))
                    End While
                Catch ex As Exception
                    Throw
                Finally
                    connection.Close()
                End Try

            End Using ' command
        End Using ' connection

        If outbound IsNot Nothing Then
            Return outbound.ToArray()
        Else
            Return Nothing
        End If

    End Function

End Class