Imports System
Imports System.Data.SqlClient

Namespace BulletinX.Data
    Public Class SqlData
        Dim sdc As New SqlDataControls

#Region "Categories"
        Public Sub ListCategory(ByVal CategoryID As Integer)
            Using cmd As SqlCommand = sdc.CreateCommand()
                cmd.Parameters.AddWithValue("@CategoryID", CategoryID)
                Try
                    If CategoryID = Nothing Or CategoryID.ToString.Trim() = "" Then
                        cmd.CommandText = "SELECT CategoryID, Name, SortOrder ORDER BY SortOrder"
                    Else
                        cmd.CommandText = "SELECT CategoryID, Name, SortOrder CategoryID=@CategoryID"
                    End If
                Catch ex As SqlException
                    Throw ex
                Finally
                    cmd.Connection.Close()
                End Try
            End Using
        End Sub
        ''' <summary>
        ''' Save the new category
        ''' </summary>
        ''' <param name="CategoryID">The value of the category.</param>
        ''' <param name="Name">The name of the category.</param>
        ''' <param name="SortOrder">The value of which the category will be shown.</param>
        ''' <remarks>
        ''' This creates the new category. If the category already exists then it will be updated.
        ''' If it doesn't already exists then it will be created.
        ''' </remarks>
        Public Sub SaveCategory(ByVal CategoryID As Integer, ByVal Name As String, ByVal SortOrder As Integer)
            Using cmd As SqlCommand = sdc.CreateCommand()
                cmd.Parameters.AddWithValue("@CategoryID", CategoryID)
                cmd.Parameters.AddWithValue("@Name", Name)
                cmd.Parameters.AddWithValue("@SortOrder", SortOrder)
                Try
                    If CategoryID > 0 Then
                        cmd.CommandText = "UPDATE bx_Category SET Name=@Name, SortOrder=@SortOrder WHERE CategoryID=@CategoryID"
                    Else
                        cmd.CommandText = "INSERT INTO bx_Category(Name, SortOrder) Values(@Name, @SortOrder)"
                    End If
                    cmd.ExecuteNonQuery()
                Catch ex As SqlException
                    Throw ex
                Finally
                    cmd.Connection.Close()
                End Try
            End Using
        End Sub
        ''' <summary>
        ''' Deleted the category.
        ''' </summary>
        ''' <param name="CategoryID">The value of the Category to be deleted</param>
        ''' <remarks>
        ''' This deleted the existing category.
        ''' </remarks>
        Public Sub DeleteCategory(ByVal CategoryID As Integer)
            Using cmd As SqlCommand = sdc.CreateCommand()
                cmd.Parameters.AddWithValue("CategoryID", CategoryID)
                Try
                    cmd.CommandText = "DELETE FROM bx_Category WHERE CategoryID=@CategoryID"
                Catch ex As SqlException
                    Throw ex
                Finally
                    cmd.Connection.Close()
                End Try
            End Using
        End Sub
#End Region
    End Class
End Namespace