Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

Imports Nozama.General

Namespace Nozama.DataAccess

    Public Class Books
        Implements IDisposable

        Private dsCommand As SqlDataAdapter

        Public Sub New()
            MyBase.New()

            dsCommand = New SqlDataAdapter()

            dsCommand.SelectCommand = New SqlCommand()
            dsCommand.SelectCommand.Connection = New SqlConnection(Settings.ConnectionString)

            dsCommand.TableMappings.Add("Table", BookData.BOOKS_TABLE)
        End Sub

        Public Sub Dispose() Implements IDisposable.Dispose
            Dispose(True)
            GC.SuppressFinalize(True)
        End Sub

        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not disposing Then
                Exit Sub
            End If

            If Not dsCommand Is Nothing Then
                If Not dsCommand.SelectCommand Is Nothing Then
                    If Not dsCommand.SelectCommand.Connection Is Nothing Then
                        dsCommand.SelectCommand.Connection.Dispose()
                    End If
                    dsCommand.SelectCommand.Dispose()
                End If
                dsCommand.Dispose()
                dsCommand = Nothing
            End If
        End Sub

        Public Function GetBooksByClassID(ByVal classID As Integer) As BookData
            Dim data As BookData
            data = New BookData()

            With dsCommand
                Try
                    With .SelectCommand
                        .CommandType = CommandType.Text
                        .CommandText = "SELECT * FROM Books b, BookClass bc, Classes c " _
                                     & " WHERE c.UID = " & classID _
                                     & " AND b.UID = bc.BookID" _
                                     & " AND c.UID = bc.ClassID" _
                                     & " ORDER BY b.UID"

                    End With
                    .Fill(data)
                Finally
                    If Not .SelectCommand Is Nothing Then
                        If Not .SelectCommand.Connection Is Nothing Then
                            .SelectCommand.Connection.Dispose()
                        End If
                        .SelectCommand.Dispose()
                    End If
                    .Dispose()
                End Try
            End With

            Return data

        End Function

        Public Function GetBookByID(ByVal bookID As Integer) As BookData
            Dim data As BookData
            data = New BookData()

            Dim authorTable As New DataTable()
            authorTable.Columns.Add("Name", GetType(System.String))

            With dsCommand
                Try
                    With .SelectCommand
                        .CommandType = CommandType.Text
                        .CommandText = "SELECT * FROM Books Where UID = " & bookID
                    End With
                    .Fill(data)
                    .SelectCommand.CommandText = "SELECT Name From Authors, BookAuthor Where AuthorID=Authors.UID And BookID = " & bookID
                    .Fill(authorTable)

                    With authorTable
                        Dim row As DataRow
                        Dim sAuthors As String = ""
                        For Each row In .Rows
                            sAuthors &= row.Item("Name")
                            sAuthors &= ","
                        Next
                        data.Tables(BookData.BOOKS_TABLE).Rows(0).Item(BookData.AUTHORS) = sAuthors
                    End With
                Finally
                    If Not .SelectCommand Is Nothing Then
                        If Not .SelectCommand.Connection Is Nothing Then
                            .SelectCommand.Connection.Dispose()
                        End If
                        .SelectCommand.Dispose()
                    End If
                    .Dispose()
                End Try
            End With

            Return data
        End Function

        Public Function GetBooksByAuthor(ByVal searchText As String) As BookData
            Dim data As BookData
            data = New BookData()

            With dsCommand
                Try
                    With .SelectCommand
                        .CommandType = CommandType.Text
                        .CommandText = "SELECT DISTINCT TOP 50 * FROM Books b, BookAuthor ba, Authors a " _
                                     & " WHERE CHARINDEX('" & searchText & "' , a.Name) > 0" _
                                     & " AND b.UID = ba.BookID" _
                                     & " AND a.UID = ba.AuthorID" _
                                     & " ORDER BY b.UID"

                    End With
                    .Fill(data)
                Finally
                    If Not .SelectCommand Is Nothing Then
                        If Not .SelectCommand.Connection Is Nothing Then
                            .SelectCommand.Connection.Dispose()
                        End If
                        .SelectCommand.Dispose()
                    End If
                    .Dispose()
                End Try
            End With

            Return data
        End Function

        Public Function GetBooksBySubject(ByVal searchText As String) As BookData
            Dim data As BookData
            data = New BookData()

            With dsCommand
                Try
                    With .SelectCommand
                        .CommandType = CommandType.Text
                        .CommandText = "SELECT DISTINCT TOP 50 * FROM Books Where CHARINDEX('" & searchText & "', Subject) > 0"
                    End With
                    .Fill(data)
                Finally
                    If Not .SelectCommand Is Nothing Then
                        If Not .SelectCommand.Connection Is Nothing Then
                            .SelectCommand.Connection.Dispose()
                        End If
                        .SelectCommand.Dispose()
                    End If
                    .Dispose()
                End Try
            End With

            Return data
        End Function

        Public Function GetBooksByName(ByVal searchText As String) As BookData
            Dim data As BookData
            data = New BookData()

            With dsCommand
                Try
                    With .SelectCommand
                        .CommandType = CommandType.Text
                        .CommandText = "SELECT * FROM Books Where CHARINDEX('" & searchText & "' , Name) > 0"
                    End With
                    .Fill(data)
                Finally
                    If Not .SelectCommand Is Nothing Then
                        If Not .SelectCommand.Connection Is Nothing Then
                            .SelectCommand.Connection.Dispose()
                        End If
                        .SelectCommand.Dispose()
                    End If
                    .Dispose()
                End Try
            End With

            Return data
        End Function

    End Class

End Namespace