
<p>
This is about developing Visual Basic classes for a web application. The classes access an SQL Server database via Stored Procedures.</p>
<p>
<br>
</p>
<h2>
Tests.vb</h2>
<pre class="ql-syntax" spellcheck="false">
Private Sub ResourceTypeTests()
    ResourceTypeAddTest()
    ResourceTypeGetTest()
End Sub

Private Sub ResourceTypeAddTest()
    Dim fake As New Fake

    Console.WriteLine("Adding resource type")
    Console.WriteLine()

    Dim objResourceType As New ResourceType With {
        .ResourceTypeID = 0,
        .ResourceTypeName = fake.Noun
    }
    OutputResourceType(objResourceType)
    Dim ResourceTypeID As Integer = objResourceType.Add()
    Console.WriteLine("Added Resource Type ID " &amp; ResourceTypeID)
    Console.WriteLine()

    Console.WriteLine("Modifying resource type with ID " &amp; ResourceTypeID)
    Console.WriteLine()

    fake = New Fake
    With objResourceType
        .ResourceTypeID = ResourceTypeID
        .ResourceTypeName = fake.Noun
    End With
    Console.WriteLine("Modification")
    OutputResourceType(objResourceType)
    Dim newResourceTypeID As Integer = objResourceType.Add()
    Console.WriteLine("Modified ResourceTypeID " &amp; newResourceTypeID)
    Console.WriteLine()
End Sub

Private Sub ResourceTypeGetTest()
    Console.WriteLine("Fetching resource types")
    Console.WriteLine()
    Dim objResourceType As New ResourceType()
    Dim ResourceTypeList As List(Of ResourceType) = objResourceType.GetResourceTypes()
    For Each ResourceType As ResourceType In ResourceTypeList
        OutputResourceType(ResourceType)
    Next
End Sub

Private Sub OutputResourceType(ResourceType As ResourceType)
    Console.WriteLine("Resource Type ID " &amp; ResourceType.ResourceTypeID)
    Console.WriteLine("Resource Type Name " &amp; ResourceType.ResourceTypeName)
    Console.WriteLine()
End Sub
</pre>
<h2>
ResourceType.vb</h2>
<pre class="ql-syntax" spellcheck="false">
Public Class ResourceType
    Private m_ResourceTypeID As Integer
    Private m_ResourceTypeName As String

    Private ReadOnly dataFields As New List(Of String) From {
        "ResourceTypeID", "ResourceTypeName"
    }

    Private ReadOnly objFields = dataFields

    Dim objGlobals As New Globals
    Dim _ConnectionString As String = objGlobals.getConnectionString()

    Property ResourceTypeID As Integer
        Get
            ResourceTypeID = m_ResourceTypeID
        End Get
        Set(value As Int32)
            m_ResourceTypeID = value
        End Set
    End Property

    Property ResourceTypeName As String
        Get
            ResourceTypeName = m_ResourceTypeName
        End Get
        Set(value As String)
            m_ResourceTypeName = value
        End Set
    End Property

    Public Function Add() As Int32
        Dim ReturnValue As Int32
        Try
            ReturnValue = StoredProcOutInt(
                _ConnectionString, "dbo.ResourceType_Add", Me,
                dataFields, objFields, "NewResourceTypeID"
            )
        Catch ex As Exception
            ErrorRecorder("Resource.Add", ex.Message, ex.Data.ToString)
            ReturnValue = 0
        End Try
        Return ReturnValue
    End Function

    Public Function GetResourceTypes() As List(Of ResourceType)
        Dim ObjResourceTypes As New List(Of ResourceType)
        Try
            StoredProc(Of ResourceType)(
            _ConnectionString, "dbo.ResourceType_Get",
            dataFields, objFields, ObjResourceTypes
        )
        Catch ex As Exception
            ErrorRecorder("ResourceType.GetResourceTypes", ex.Message, ex.Data.ToString)
        End Try
        Return ObjResourceTypes
    End Function
End Class
</pre>
<h2>
ResourceType_Add.sql</h2>
<pre class="ql-syntax" spellcheck="false">
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[ResourceType_Add]
    @ResourceTypeID INT,
    @ResourceTypeName NVARCHAR(100),    
    @NewResourceTypeID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF @ResourceTypeID = 0
        BEGIN
            INSERT INTO dbo.ResourceType
                    ( ResourceType_Name                    
                    )
            VALUES  ( @ResourceTypeName
                    )
            SET @NewResourceTypeID = SCOPE_IDENTITY()
        END
    ELSE
        BEGIN
            UPDATE dbo.ResourceType
            SET ResourceType_Name = @ResourceTypeName                
            WHERE ResourceTypeID = @ResourceTypeID
            SET @NewResourceTypeID = @ResourceTypeID
        END    
END
GO
</pre>
<h2>
ResourceType_Get.sql</h2>
<pre class="ql-syntax" spellcheck="false">
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE [dbo].[ResourceType_Get]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ResourceTypeID AS ResourceTypeID,
        ResourceType_Name AS ResourceTypeName
    FROM dbo.ResourceType
END
GO
</pre>
<p>
<br>
</p>


