Base class for data helper library.

BaseData.vb

Imports System.Data.SqlClient
Imports Cpp.DataLayer.SqlCommandHelper
Imports Cpp.DataLayer.SqlDataReaderHelper

Namespace Data

  Public MustInherit Class BaseData

    Protected _dataHandlers As Handlers.OrmHandlers
    Private _connectionString As String

    Sub New(connectionString As String)
      _connectionString = connectionString
      _dataHandlers = New Handlers.OrmHandlers
    End Sub

    Protected Function Builder(Of T)(myCmd As SqlCommand, orm As Handlers.ActionHandlers.Orm) As IList(Of T)

      Dim lm As New List(Of T)()
      Dim l As IList = DirectCast(lm, IList)

      Using dr As SqlDataReader = myCmd.ExecuteReader()

        If dr.HasRows Then
          While dr.Read()
            l.Add(orm.Invoke(dr))
          End While
        End If

      End Using

      Return l

    End Function

    Protected Function IntegerListBuilder(sql As String) As IList(Of Integer)

      Dim OrmHandler As New Handlers.ActionHandlers.Orm(AddressOf _dataHandlers.ToInteger)
      Return SqlAdHoc(Of Integer)(sql, OrmHandler)

    End Function

    Private Function BuilderSpecialCase(Of T)(myCmd As SqlCommand, orm As Handlers.ActionHandlers.Orm) As IList(Of T)

      Dim lm As New List(Of T)()
      Dim l As IList = DirectCast(lm, IList)

      Using dr As SqlDataReader = myCmd.ExecuteReader()

        If dr.HasRows Then
          While dr.Read()
            l.Add(orm.Invoke(dr))
          End While
        End If

      End Using

      Return l

    End Function

    Protected Function SqlAdHoc(Of T)(sql As String, orm As Handlers.ActionHandlers.Orm) As IList(Of T)

      Return BaseNoParameters(Of T)(sql, orm, False)

    End Function

    Protected Function SqlAdHoc(Of T)(sql As String, params As List(Of SqlParameter), orm As Handlers.ActionHandlers.Orm) As IList(Of T)

      Return BaseWithParameters(Of T)(sql, params, orm, False)

    End Function

    Protected Function StoredProcedure(Of T)(spName As String, orm As Handlers.ActionHandlers.Orm) As IList(Of T)

      Return BaseNoParameters(Of T)(spName, orm, True)

    End Function

    Protected Function StoredProcedure(Of T)(spName As String, params As List(Of SqlParameter), orm As Handlers.ActionHandlers.Orm) As IList(Of T)

      Return BaseWithParameters(Of T)(spName, params, orm, True)

    End Function

    Private Function BaseNoParameters(Of T)(sqlRequired As String, orm As Handlers.ActionHandlers.Orm, isStoredProcedure As Boolean) As IList(Of T)

      Using myConn As New SqlConnection(_connectionString)
        Using myCmd As New SqlCommand(sqlRequired, myConn)
          myCmd.CommandType = GetCommandType(isStoredProcedure)

          myConn.Open()
          Return Builder(Of T)(myCmd, orm)

        End Using
      End Using

    End Function

    Private Function BaseWithParameters(Of T)(sqlRequired As String, params As List(Of SqlParameter), orm As Handlers.ActionHandlers.Orm, isStoredProcedure As Boolean) As IList(Of T)

      Using myConn As New SqlConnection(_connectionString)
        Using myCmd As New SqlCommand(sqlRequired, myConn)
          myCmd.CommandType = GetCommandType(isStoredProcedure)

          If params IsNot Nothing AndAlso params.Count > 0 Then
            For Each parm In params
              myCmd.Parameters.Add(parm)
            Next
          End If

          myConn.Open()
          Return Builder(Of T)(myCmd, orm)

        End Using
      End Using

    End Function

    Private Function GetCommandType(isStoredProcedure As Boolean) As CommandType

      If isStoredProcedure Then
        Return CommandType.StoredProcedure
      Else
        Return CommandType.Text
      End If
    End Function

  End Class

End Namespace