HPR4405: What did I do at work today?

This show has been flagged as Explicit by the host.

This is about developing Visual Basic classes for a web application. The classes access an SQL Server database via Stored Procedures.

Tests.vb

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 " & ResourceTypeID)
    Console.WriteLine()

    Console.WriteLine("Modifying resource type with ID " & 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 " & 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 " & ResourceType.ResourceTypeID)
    Console.WriteLine("Resource Type Name " & ResourceType.ResourceTypeName)
    Console.WriteLine()
End Sub

ResourceType.vb

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

ResourceType_Add.sql

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

ResourceType_Get.sql

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

Provide feedback on this episode.

HPR Comments

Dave Morriss says: Thanks for bringing us along...

Mastodon Comments

More Information...