Namespace Acgg
    Public Class dbClass

        Public Shared strConn As String '= "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Database Password=;Data Source=C:\Inetpub\wwwroot\AcggDoc\AcggDoc.mdb;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"
        Dim connStr As String = ConfigurationSettings.AppSettings("ConnectionString")
        Private con As New OleDb.OleDbConnection(connStr)

        Public Sub OpenDB()
            Try
                con = New OleDb.OleDbConnection(connStr)
                con.Open()
            Catch Exc As Exception
                '    'Response.Write("Error: " & Exc.Message)
            Finally
            End Try
        End Sub

        Public Sub CloseDB()
            con.Close()
            con = Nothing
        End Sub

        Public Function retReader(ByVal strSql As String) As OleDb.OleDbDataReader
            'Dim con As New OleDb.OleDbConnection
            Dim objCmd As OleDb.OleDbCommand
            Dim objDR As OleDb.OleDbDataReader
            Try
                'con = New OleDb.OleDbConnection(connStr)
                'con.Open()

                'If con.State = ConnectionState.Closed Then con.Close()
                'Dim strSQL As String = "SELECT A.ID,A.Desc,A.Location,A.Date,'Web' as Type ,B.Name as PubName FROM Doc A ,Publisher B WHERE A.PubBy=B.ID "

                objCmd = New OleDb.OleDbCommand(strSql, con)
                objDR = objCmd.ExecuteReader
                retReader = objDR
                Return objDR
            Catch Exc As Exception
                '    'Response.Write("Error: " & Exc.Message)
            Finally
                objCmd.Dispose()
                objCmd = Nothing
                'objDR = Nothing
                'con = Nothing
            End Try
            'con.Close()
        End Function


        Public Function retDataSet(ByVal strSql As String) As DataSet
            'Dim con As New OleDb.OleDbConnection
            Dim objCmd As OleDb.OleDbCommand
            'Dim objDR As OleDb.OleDbDataReader
            Dim objDA As OleDb.OleDbDataAdapter
            Dim objDS As New DataSet

            Try

                'con = New OleDb.OleDbConnection(connStr)
                'con.Open()

                'If con.State = ConnectionState.Closed Then con.Close()
                'Dim strSQL As String = "SELECT A.ID,A.Desc,A.Location,A.Date,'Web' as Type ,B.Name as PubName FROM Doc A ,Publisher B WHERE A.PubBy=B.ID "

                objCmd = New OleDb.OleDbCommand(strSql, con)
                objDA = New OleDb.OleDbDataAdapter(objCmd)
                objDA.Fill(objDS)
                'objDR = objCmd.ExecuteReader
                'retReader = objDR
                'Return objDR
                Return objDS
            Catch Exc As Exception
                '    'Response.Write("Error: " & Exc.Message)
            Finally
                objCmd.Dispose()
                objCmd = Nothing
                'objDR = Nothing
                'con = Nothing
            End Try
            'con.Close()
        End Function

        Public Function boolManipulateData(ByVal strSql As String) As Boolean
            'Dim con As New OleDb.OleDbConnection
            Dim objCmd As OleDb.OleDbCommand

            Try
                'con = New OleDb.OleDbConnection(connStr)
                'con.Open()

                objCmd = New OleDb.OleDbCommand(strSql, con)
                'Dim x As Integer = objCmd.ExecuteNonQuery()
                objCmd.ExecuteScalar()
                Return True
            Catch Exc As Exception
                'Response.Write("Error: " & Exc.Message)
            Finally
                objCmd.Dispose()
                objCmd = Nothing
                'con.Close()
                'con = Nothing
            End Try
        End Function

        Public Shared Function AuthenticateUser(ByVal strUserID As String, ByVal strPwd As String) As Integer
            Dim con As New OleDb.OleDbConnection
            Dim objCmd As New OleDb.OleDbCommand
            Dim connStr As String = ConfigurationSettings.AppSettings("ConnectionString")

            Try
                con = New OleDb.OleDbConnection(connStr)
                con.Open()

                Dim strSQL As String = "SELECT * FROM Users WHERE UserID='" & strUserID & "' AND Pwd='" & strPwd & "'"
                objCmd = New OleDb.OleDbCommand(strSQL, con)
                Dim objDR As OleDb.OleDbDataReader
                objDR = objCmd.ExecuteReader()
                If objDR.HasRows Then
                    objDR.Read()
                    Return objDR("UserCat")
                Else
                    Return 0
                End If
            Catch Exc As Exception
                'Response.Write("Error: " & Exc.Message)
            Finally
                objCmd.Dispose()
                'con.Close()
                'con.Dispose()
            End Try

        End Function

        Public Function CalFile(ByVal strSql As String) As String
            'Dim con As New OleDb.OleDbConnection
            'Dim objCmd As OleDb.OleDbCommand
            'Dim objDR As OleDb.OleDbDataReader
            'Dim retFileNo(100) As String

            'Try
            '    con = New OleDb.OleDbConnection(connStr)
            '    con.Open()

            '    'Dim strSQL As String = "SELECT A.ID,A.Desc,A.Location,A.Date,'Web' as Type ,B.Name as PubName FROM Doc A ,Publisher B WHERE A.PubBy=B.ID "
            '    objCmd = New OleDb.OleDbCommand(strSql, con)
            '    objDR = objCmd.ExecuteReader
            '    If objDR.HasRows Then
            '        Dim i As Integer
            '        Do While objDR.Read()
            '            i = i + 1
            '            retFileNo(i) = objDR("FileNo")
            '        Loop
            '        Return retFileNo
            '    Else
            '        Return ""
            '    End If

            'Catch Exc As Exception
            '    'Response.Write("Error: " & Exc.Message)

            'Finally
            '    objCmd.Dispose()
            '    objCmd = Nothing
            '    objDR = Nothing
            '    con.Close()
            '    con = Nothing
            'End Try
        End Function

    End Class
End Namespace