AvailabilityDb.vb

'##############################################################################

' Author: Kevin Koch

' Description:  This class represents the data tier middle layer class for the

'               AVAILABILITY table. It contains all data access methods and static

'               constants representing the stored procedures, field names

'               and Sql parameters required by this entity

'##############################################################################

Public Class AvailabilityDb

    Private Const CLASS_NAME As String = "AvailabilityDb"

    '#########################################################################################################################

    ' DB Configuration Properties

    '#########################################################################################################################

    Friend Shared WSB2B_GET_AVAILABILITY As String = "WSB2B_GET_AVAILABILITY"

    Friend Shared PARAM_AVAILABILITY_ID_NAME As String = "@paramAvailabilityId"

    Friend Shared PARAM_AVAILABILITY_ID_TYPE As SqlDbType = SqlDbType.Int

    Friend Shared PARAM_AVAILABILITY_ID_SIZE As Integer = 4

    ' Table field names, used for dataset references

    Public Shared FIELD_AVAILABILITY_ID As String = "AVAILABILITY_ID"

    Public Shared FIELD_AVAILABILITY_NAME As String = "AVAILABILITY_NAME"

    Friend Shared AVAILABILITY_TABLE_NAME As String = "AVAILABILITY"

    '#########################################################################################################################

    ' Returns the entire availability table

    '#########################################################################################################################

    Public Function GetAllAvailability() As DataSet

        Const METHOD_NAME As String = "GetAllAvailability"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim ReturnValue As Int32

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_AVAILABILITY, Nothing, Me.AVAILABILITY_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

End Class

'##############################################################################

' Author: Kevin Koch

' Description:  This class represents the data tier middle layer class for the

'               BOOK table. It contains all data access methods and static

'               constants representing the stored procedures, field names

'               and Sql parameters required by this entity

'##############################################################################

Public Class BookDb

    Private Const CLASS_NAME As String = "BookDb"

    '#########################################################################################################################

    ' DB Configuration Properties

    '#########################################################################################################################

    Friend Shared WSB2B_GET_BOOKS As String = "WSB2B_GET_BOOKS"

    Friend Shared WSB2B_UPDATE_BOOK As String = "WSB2B_UPDATE_BOOK"

    Friend Shared WSB2B_GET_BOOKS_DYNAMIC As String = "WSB2B_GET_BOOKS_DYNAMIC"

    Friend Shared PARAM_BOOK_ID_NAME As String = "@paramBookId"

    Friend Shared PARAM_BOOK_ID_TYPE As SqlDbType = SqlDbType.Int

    Friend Shared PARAM_BOOK_ID_SIZE As Integer = 4

    Friend Shared PARAM_AUTHOR_NAME As String = "@paramAuthor"

    Friend Shared PARAM_AUTHOR_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_AUTHOR_SIZE As Integer = 50

    Friend Shared PARAM_BOOK_NAME_NAME As String = "@paramBookName"

    Friend Shared PARAM_BOOK_NAME_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_BOOK_NAME_SIZE As Integer = 100

    Friend Shared PARAM_PRICE_NAME As String = "@paramPrice"

    Friend Shared PARAM_PRICE_TYPE As SqlDbType = SqlDbType.SmallMoney

    Friend Shared PARAM_PRICE_SIZE As Integer = 4

    Friend Shared PARAM_WHERE_CLAUSE_NAME As String = "@paramWhereClause"

    Friend Shared PARAM_WHERE_CLAUSE_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_WHERE_CLAUSE_SIZE As Integer = 1000

    ' Table field names, used for dataset references

    Public Shared FIELD_BOOK_ID As String = "BOOK_ID"

    Public Shared FIELD_BOOK_NAME As String = "BOOK_NAME"

    Public Shared FIELD_AUTHOR As String = "AUTHOR"

    Public Shared FIELD_PRICE As String = "PRICE"

    Friend Shared BOOK_TABLE_NAME As String = "BOOK"

    '#########################################################################################################################

    ' Returns a set of books, filtered by the criteria parameters

    '#########################################################################################################################

    Public Function GetBooks(ByVal BookId As Int32, ByVal AuthorName As String, ByVal BookName As String, ByVal AvailabilityId As Int32) As DataSet

        Const METHOD_NAME As String = "GetBooks"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_BOOK_ID_NAME, Me.PARAM_BOOK_ID_TYPE, Me.PARAM_BOOK_ID_SIZE, BookId))

            params.Add(DbObj.MakeParam(Me.PARAM_AUTHOR_NAME, Me.PARAM_AUTHOR_TYPE, Me.PARAM_AUTHOR_SIZE, AuthorName))

            params.Add(DbObj.MakeParam(Me.PARAM_BOOK_NAME_NAME, Me.PARAM_BOOK_NAME_TYPE, Me.PARAM_BOOK_NAME_SIZE, BookName))

            params.Add(DbObj.MakeParam(AvailabilityDb.PARAM_AVAILABILITY_ID_NAME, AvailabilityDb.PARAM_AVAILABILITY_ID_TYPE, _

                            AvailabilityDb.PARAM_AVAILABILITY_ID_SIZE, AvailabilityId))

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_BOOKS, params, Me.BOOK_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

    '#########################################################################################################################

    ' Updates the BookId specified with the parameter values

    '#########################################################################################################################

    Public Sub UpdateBook(ByVal BookId As Int32, ByVal AuthorName As String, ByVal BookName As String, ByVal Price As Double, _

                            ByVal AvailabilityId As Int32)

        Const METHOD_NAME As String = "UpdateBook"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_BOOK_ID_NAME, Me.PARAM_BOOK_ID_TYPE, Me.PARAM_BOOK_ID_SIZE, BookId))

            params.Add(DbObj.MakeParam(Me.PARAM_AUTHOR_NAME, Me.PARAM_AUTHOR_TYPE, Me.PARAM_AUTHOR_SIZE, AuthorName))

            params.Add(DbObj.MakeParam(Me.PARAM_BOOK_NAME_NAME, Me.PARAM_BOOK_NAME_TYPE, Me.PARAM_BOOK_NAME_SIZE, BookName))

            params.Add(DbObj.MakeParam(Me.PARAM_PRICE_NAME, Me.PARAM_PRICE_TYPE, Me.PARAM_PRICE_SIZE, Price))

           params.Add(DbObj.MakeParam(AvailabilityDb.PARAM_AVAILABILITY_ID_NAME, AvailabilityDb.PARAM_AVAILABILITY_ID_TYPE, _

                            AvailabilityDb.PARAM_AVAILABILITY_ID_SIZE, AvailabilityId))

            DbObj.ExecuteProc(Me.WSB2B_UPDATE_BOOK, params)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Sub

    '#########################################################################################################################

    ' Returns a set of books, filtered by the ids in the arraylist

    '#########################################################################################################################

    Public Function GetBooksByIds(ByVal BookIds As ArrayList) As DataSet

        Const METHOD_NAME As String = "GetBooksByIds"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            Dim Predicate As New Text.StringBuilder()

            Predicate.Append(" WHERE ").Append(Me.FIELD_BOOK_ID).Append(" IN(").Append(Utils.ConvertCollectionToCSV(BookIds)).Append(")")

           params.Add(DbObj.MakeParam(Me.PARAM_WHERE_CLAUSE_NAME, Me.PARAM_WHERE_CLAUSE_TYPE, Me.PARAM_WHERE_CLAUSE_SIZE, Predicate.ToString()))

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_BOOKS_DYNAMIC, params, Me.BOOK_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

End Class

'##############################################################################

' Author: Kevin Koch

' Description:  This class represents the data tier middle layer class for the

'               CLIENT table. It contains all data access methods and static

'               constants representing the stored procedures, field names

'               and Sql parameters required by this entity

'##############################################################################

Public Class ClientDb

    Private Const CLASS_NAME As String = "ClientDb"

    '#########################################################################################################################

    ' DB Configuration Properties

    '#########################################################################################################################

    Friend Shared WSB2B_GET_CLIENTS As String = "WSB2B_GET_CLIENTS"

    Friend Shared PARAM_CLIENT_ID_NAME As String = "@paramClientId"

    Friend Shared PARAM_CLIENT_ID_TYPE As SqlDbType = SqlDbType.Int

    Friend Shared PARAM_CLIENT_ID_SIZE As Integer = 4

    Friend Shared PARAM_CLIENT_NAME_NAME As String = "@paramClientName"

    Friend Shared PARAM_CLIENT_NAME_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_CLIENT_NAME_SIZE As Integer = 50

    Friend Shared PARAM_WS_TOKEN_NAME As String = "@paramWsToken"

    Friend Shared PARAM_WS_TOKEN_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_WS_TOKEN_SIZE As Integer = 100

    ' Table field names, used for dataset references

    Public Shared FIELD_CLIENT_ID As String = "CLIENT_ID"

    Public Shared FIELD_CLIENT_NAME As String = "CLIENT_NAME"

    Public Shared FIELD_WS_TOKEN As String = "WS_TOKEN"

    Friend Shared CLIENT_TABLE_NAME As String = "CLIENT"

    ' Static variable for application object storage/retrieval

    Public Shared CLIENT_DS_REF As String = "CLIENT_DS"

    '#########################################################################################################################

   ' Returns the entire client table

    '#########################################################################################################################

    Public Function GetAllClients() As DataSet

        Const METHOD_NAME As String = "GetAllClients"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim ReturnValue As Int32

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_CLIENTS, Nothing, Me.CLIENT_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

End Class

'##############################################################################

' Author: Kevin Koch

' Description:  This class represents the data tier middle layer class for the

'               ORDER table. It contains all data access methods and static

'               constants representing the stored procedures, field names

'               and Sql parameters required by this entity

'##############################################################################

Public Class OrderDb

    Private Const CLASS_NAME As String = "OrderDb"

    '#########################################################################################################################

    ' DB Configuration Properties

    '#########################################################################################################################

    Friend Shared WSB2B_GET_ORDERS As String = "WSB2B_GET_ORDERS"

    Friend Shared WSB2B_CREATE_ORDER As String = "WSB2B_CREATE_ORDER"

    Friend Shared WSB2B_CREATE_ORDER_BOOK_REL As String = "WSB2B_CREATE_ORDER_BOOK_REL"

    Friend Shared WSB2B_CONFIRM_ORDER As String = "WSB2B_CONFIRM_ORDER"

    Friend Shared PARAM_ORDER_ID_NAME As String = "@paramOrderId"

    Friend Shared PARAM_ORDER_ID_TYPE As SqlDbType = SqlDbType.Int

    Friend Shared PARAM_ORDER_ID_SIZE As Integer = 4

    Friend Shared PARAM_ORDER_STATUS_NAME As String = "@paramOrderStatus"

    Friend Shared PARAM_ORDER_STATUS_TYPE As SqlDbType = SqlDbType.NVarChar

    Friend Shared PARAM_ORDER_STATUS_SIZE As Integer = 20

    ' Table field names, used for dataset references

    Public Shared FIELD_ORDER_ID As String = "ORDER_ID"

    Public Shared FIELD_ORDER_STATUS As String = "ORDER_STATUS"

    Public Shared FIELD_CREATION_DATE As String = "CREATION_DATE"

    Friend Shared ORDER_TABLE_NAME As String = "ORDER"

    '#########################################################################################################################

    ' Possible order statuses, defined as static constants

    '#########################################################################################################################

    Public Shared ORDER_STATUS_PENDING As String = "Pending"

    Public Shared ORDER_STATUS_COMPLETE As String = "Complete"

    '#########################################################################################################################

    ' Searches the orders table with the parameters specified

    '#########################################################################################################################

    Public Function GetOrders(ByVal OrderId As Int32, ByVal OrderStatus As String, ByVal ClientId As Int32) As DataSet

        Const METHOD_NAME As String = "GetOrders"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_ID_NAME, Me.PARAM_ORDER_ID_TYPE, Me.PARAM_ORDER_ID_SIZE, OrderId))

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_STATUS_NAME, Me.PARAM_ORDER_STATUS_TYPE, Me.PARAM_ORDER_STATUS_SIZE, OrderStatus))

            params.Add(DbObj.MakeParam(ClientDb.PARAM_CLIENT_ID_NAME, ClientDb.PARAM_CLIENT_ID_TYPE, ClientDb.PARAM_CLIENT_ID_SIZE, ClientId))

            Return DbObj.ExecuteDataset(Me.WSB2B_GET_ORDERS, params, Me.ORDER_TABLE_NAME)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Function

    '#########################################################################################################################

    ' Creates a new pending order

    '#########################################################################################################################

    Public Sub CreateOrder(ByVal OrderId As Int32, ByVal BookIds As ArrayList, ByVal ClientId As Int32)

        Const METHOD_NAME As String = "CreateOrder"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_ID_NAME, Me.PARAM_ORDER_ID_TYPE, Me.PARAM_ORDER_ID_SIZE, OrderId))

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_STATUS_NAME, Me.PARAM_ORDER_STATUS_TYPE, Me.PARAM_ORDER_STATUS_SIZE, Me.ORDER_STATUS_PENDING))

            params.Add(DbObj.MakeParam(ClientDb.PARAM_CLIENT_ID_NAME, ClientDb.PARAM_CLIENT_ID_TYPE, ClientDb.PARAM_CLIENT_ID_SIZE, ClientId))

            ' Create the main order entry

            DbObj.ExecuteProc(Me.WSB2B_CREATE_ORDER, params)

            ' Now create an association relationship between the order, and each book in the order

            Dim BookId As Int32

            For Each BookId In BookIds

                params = New Collection()

                params.Add(DbObj.MakeParam(Me.PARAM_ORDER_ID_NAME, Me.PARAM_ORDER_ID_TYPE, Me.PARAM_ORDER_ID_SIZE, OrderId))

                params.Add(DbObj.MakeParam(BookDb.PARAM_BOOK_ID_NAME, BookDb.PARAM_BOOK_ID_TYPE, BookDb.PARAM_BOOK_ID_SIZE, BookId))

                DbObj.ExecuteProc(Me.WSB2B_CREATE_ORDER_BOOK_REL, params)

            Next

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

        Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Sub

    '#########################################################################################################################

    ' Confirms a pending order by updating its status to Complete

    '#########################################################################################################################

    Public Sub ConfirmOrder(ByVal OrderId As Int32)

        Const METHOD_NAME As String = "ConfirmOrder"

        Dim DbObj As DbAccess = New DbAccess()

        Try

            Dim params As Collection = New Collection()

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_ID_NAME, Me.PARAM_ORDER_ID_TYPE, Me.PARAM_ORDER_ID_SIZE, OrderId))

            params.Add(DbObj.MakeParam(Me.PARAM_ORDER_STATUS_NAME, Me.PARAM_ORDER_STATUS_TYPE, Me.PARAM_ORDER_STATUS_SIZE, Me.ORDER_STATUS_COMPLETE))

            DbObj.ExecuteProc(Me.WSB2B_CONFIRM_ORDER, params)

        Catch ex As Exception

            Log.WriteLogEntry(ex, Me.CLASS_NAME, METHOD_NAME)

            Throw New DbTierException(ex.Message, ex)

       Finally

            DbObj.Close()

            DbObj = Nothing

        End Try

    End Sub

End Class