Base Repository Template

Repo.vb

Imports System.Data.SqlClient
Imports Cpp.Utility.Dal
Imports !n!

Namespace Data

    Public Class !p!Repository

        Private Const DefaultStatus = "active"

        Private Const _TableName = "!table!"

        Public Function GetAll!p!() As IEnumerable(Of Models.!s!)

            Return DryGet!p!($"SELECT * FROM {_tableName};", New List(Of SqlParameter))

        End Function

        Public Function Get!s!ById(id As Integer) As Models.!s!

            Dim S As New Cpp.Utility.Dal.SelectBlueprint(_tableName, "*", "pallet_id")

            S.Parameters.AddInteger(S.WhereFields(0), id)

            Return DryGet!p!(S.SqlStatement, S.Parameters).FirstOrDefault

        End Function

        Public Function DryGet!p!(sql As String, params As IEnumerable(Of SqlParameter)) As IEnumerable(Of Models.!s!)

            Dim L As New List(Of Models.!s!)

            Using myConn = New SqlConnection(GetConnectionString())
                Using myCmd = New SqlCommand(sql, myConn)

                    For Each p In params
                        myCmd.Parameters.Add(p)
                    Next

                    myConn.Open()
                    Using dr As SqlDataReader = myCmd.ExecuteReader()

                        Try

                            If dr.HasRows Then
                                While dr.Read
                                    L.Add(Orm(dr))
                                End While

                            End If

                        Catch ex As SqlException
                            Throw New ApplicationException(ex.Message, ex)
                        Catch ex As Exception
                            Throw ex.InnerException
                        End Try

                    End Using

                End Using
            End Using

            Return L.AsEnumerable

        End Function

        Public Sub Update!s!(!s! As Models.!s!)

            Dim U As New Cpp.Utility.Dal.UpdateBlueprint(_tableName, "title,descr", "pallet_id")

            U.Parameters.AddNVarchar50(U.Update.ParameterNames(0), pallet.Title)
            U.Parameters.AddNVarchar50(U.Update.ParameterNames(1), pallet.Description)

            U.Parameters.AddInteger(U.Update.WhereParametersNames(0), pallet.Id)

            SqlExecutor.ExecDevelopment(U.Update.SqlStatement, U.Parameters)

        End Sub

        Public Sub Add!s!(!s! As Models.!s!)

            Dim I As New Cpp.Utility.Dal.InsertBlueprint(_tableName, "title,descr,pallet_status")

            I.SqlParameters.AddNVarchar50(I.Fields(0), pallet.Title)
            I.SqlParameters.AddNVarchar50(I.Fields(1), pallet.Description)
            I.SqlParameters.AddNVarchar50(I.Fields(2), DefaultStatus)

            SqlExecutor.ExecDevelopment(I.SqlStatement, I.SqlParameters)

        End Sub

        <DebuggerStepThrough>
        Private Function Orm(dr As IDataReader) As Models.!s!

            Dim l As New Models.!s!
            l.Title = dr.ToStringVal("title")
            l.Id = dr.ToInteger("pallet_id")
            l.Status = dr.ToStringVal("pallet_status")
            l.Description = dr.ToStringVal("descr")

            Return l

        End Function

        Public Sub Delete!s!(id As Integer)

            Dim D As New Cpp.Utility.Dal.DeleteBlueprint(_tableName, "pallet_id")

            D.Parameters.AddInteger(D.WhereFields(0), id)

            SqlExecutor.ExecDevelopment(D.SqlStatement, D.Parameters)

        End Sub

        Private Shared Function GetConnectionString()

            Return Data.Config.Development

        End Function

    End Class
End Namespace