Imports System.Data.SqlServerCe
Imports System.Data.SqlServerCe.SqlCeException
Imports System.Xml
Imports System.Reflection
Imports System.io
Imports System.text

Public Class DatabaseConfig

#Region "Variables"
    Private Shared boolConfigLoaded = False
    Private Shared strDatabaseServer As String
    Private Shared strPublisherDB As String
    Private Shared strPublisher As String
    Private Shared strPublisherLogin As String
    Private Shared strPublisherPassword As String
    Private Shared strSubscriberConnectionString As String
    Private Shared strSubscriber As String
    Private Shared strSqlCeUrl As String
    Private Shared strLocalDBLocation As String
    Private Shared strLocalDBName As String
    Private Shared strIISLogin As String
    Private Shared strIISPassword As String
    Private Shared strNetworkTestName As String

#End Region
#Region "Properties"
    Private Property ConfigLoaded() As Boolean
        Get
            Return boolConfigLoaded
        End Get
        Set(ByVal Value As Boolean)
            boolConfigLoaded = Value
        End Set
    End Property
    Public Property DatabaseServer() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strDatabaseServer
        End Get
        Set(ByVal Value As String)
            strDatabaseServer = Value
        End Set
    End Property
    Public Property PublisherDB() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strPublisherDB
        End Get
        Set(ByVal Value As String)
            strPublisherDB = Value
        End Set
    End Property
    Public Property Publisher() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strPublisher
        End Get
        Set(ByVal Value As String)
            strPublisher = Value
        End Set
    End Property
    Public Property PublisherLogin() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strPublisherLogin
        End Get
        Set(ByVal Value As String)
            strPublisherLogin = Value
        End Set
    End Property
    Public Property PublisherPassword() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strPublisherPassword
        End Get
        Set(ByVal Value As String)
            strPublisherPassword = Value
        End Set
    End Property
    Public Property SubscriberConnectionString() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strSubscriberConnectionString
        End Get
        Set(ByVal Value As String)
            SubscriberConnectionString = Value
        End Set
    End Property
    Public Property Subscriber() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strSubscriber
        End Get
        Set(ByVal Value As String)
            strSubscriber = Value
        End Set
    End Property
    Public Property SqlCeUrl() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strSqlCeUrl
        End Get
        Set(ByVal Value As String)
            strSqlCeUrl = Value
        End Set
    End Property
    Public Property LocalDBLocation() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strLocalDBLocation
        End Get
        Set(ByVal Value As String)
            strLocalDBLocation = Value
        End Set
    End Property
    Public Property LocalDBName() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strLocalDBName
        End Get
        Set(ByVal Value As String)
            strLocalDBName = Value
        End Set
    End Property
    Public Property IISLogin() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strIISLogin
        End Get
        Set(ByVal Value As String)
            strIISLogin = Value
        End Set
    End Property
    Public Property IISPassword() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strIISPassword
        End Get
        Set(ByVal Value As String)
            strIISPassword = Value
        End Set
    End Property
    Public Property NetworkTestName() As String
        Get
            ' Check to make sure config has been loaded
            If Not ConfigLoaded Then
                LoadDBConfigSettings()
            End If
            Return strNetworkTestName
        End Get
        Set(ByVal Value As String)
            strNetworkTestName = Value
        End Set
    End Property
#End Region

    Private Function LoadDBConfigSettings()
        Dim strElementName As String
        Dim strAppWorkingDir As String = Path.GetDirectoryName([Assembly].GetExecutingAssembly.GetName.CodeBase)

        ' Get the configuration data from the config XML file
        Dim ConfigFile As New IO.FileStream(strAppWorkingDir & "\Config.xml", IO.FileMode.Open)
        Dim ConfigReader As New Xml.XmlTextReader(ConfigFile)
        ConfigReader.WhitespaceHandling = WhitespaceHandling.Significant
        While ConfigReader.Read
            If ConfigReader.NodeType = XmlNodeType.Element Then
                ' Store off name of element to use for TEXT element
                ' Don't worry about nesting since we know our config file is 
                ' only 1 element deep
                strElementName = ConfigReader.Name
            ElseIf ConfigReader.NodeType = XmlNodeType.Text Then
                ' Use the last element name read to determine what value we're looking at
                Select Case strElementName
                    Case "DatabaseServer"
                        strDatabaseServer = ConfigReader.Value
                    Case "DatabaseName"
                        strPublisherDB = ConfigReader.Value
                    Case "DatabaseSub"
                        strSubscriber = ConfigReader.Value
                    Case "DatabasePub"
                        strPublisher = ConfigReader.Value
                    Case "DatabaseLogin"
                        strPublisherLogin = ConfigReader.Value
                    Case "DatabasePassword"
                        strPublisherPassword = ConfigReader.Value
                    Case "SQLCEURL"
                        strSqlCeUrl = ConfigReader.Value
                    Case "LocalDBConnect"
                        strSubscriberConnectionString = ConfigReader.Value
                    Case "LocalDBLocation"
                        strLocalDBLocation = ConfigReader.Value
                    Case "LocalDBName"
                        strLocalDBName = ConfigReader.Value
                    Case "IISLogin"
                        strIISLogin = ConfigReader.Value
                    Case "IISPassword"
                        strIISPassword = ConfigReader.Value
                    Case "NetworkTestName"
                        strNetworkTestName = ConfigReader.Value
                End Select
            End If
        End While

        ' Cleanup - Close I/O
        ConfigReader.Close()
        ConfigFile.Close()

        ' Mark config indicator as loaded
        ConfigLoaded = True

    End Function
End Class

Public Class DBAccess
    Dim oDBConfig As New DatabaseConfig
    Dim oUtil As New Utility
    Dim Conn As New SqlCeConnection
    Public Function SyncHost() As SqlCeDataReader
        Dim strMessage As String
        Dim iExpCount As Integer
        Dim rda As SqlCeRemoteDataAccess = Nothing

        'Connect to SQL 2000
        Dim rdaOleDbConnectString As String = _
                "Provider=sqloledb; Data Source=" & oDBConfig.DatabaseServer & ";Initial" & _
                " Catalog=" & oDBConfig.PublisherDB & ";User Id=" & oDBConfig.PublisherLogin & ";Password=" & oDBConfig.PublisherPassword

        'create an RDA object to connect to the SQL Server CE database on the mobile device: 
        ' Initialize the RDA object.
        Try
            rda = New SqlCeRemoteDataAccess

            rda.InternetUrl = oDBConfig.SqlCeUrl
            rda.LocalConnectionString = _
                    "Provider=Microsoft.SQLSERVER." & _
                        "OLEDB.CE.2.0;Data Source=" & oDBConfig.LocalDBLocation & oDBConfig.LocalDBName

            rda.InternetLogin = oDBConfig.IISLogin
            rda.InternetPassword = oDBConfig.IISPassword

            'Pull from SQL 2000
            rda.Pull("MyFavourite", "Select * from MyFavourite", _
            rdaOleDbConnectString, _
            RdaTrackOption.TrackingOnWithIndexes)

        Catch err As SqlCeException
            MsgBox(oUtil.ComposeSqlErrorMessage(err))
            '//If any error occurs then clear the database
            If File.Exists(oDBConfig.LocalDBLocation & oDBConfig.LocalDBName) Then
                File.Delete(oDBConfig.LocalDBLocation & oDBConfig.LocalDBName)
            End If
        Finally
            rda.Dispose()
        End Try
    End Function
    Public Function UpdateHost()
        Dim strMessage As String
        Dim iExpCount As Integer
        Dim rda As SqlCeRemoteDataAccess = Nothing

        'Connect to SQL 2000
        Dim rdaOleDbConnectString As String = _
                "Provider=sqloledb; Data Source=" & oDBConfig.DatabaseServer & ";Initial" & _
                " Catalog=" & oDBConfig.PublisherDB & ";User Id=" & oDBConfig.PublisherLogin & ";Password=" & oDBConfig.PublisherPassword

        'create an RDA object to connect to the SQL Server CE database on the mobile device: 
        ' Initialize the RDA object.
        Try
            rda = New SqlCeRemoteDataAccess
            rda.InternetUrl = oDBConfig.SqlCeUrl
            rda.LocalConnectionString = _
                    "Provider=Microsoft.SQLSERVER." & _
                        "OLEDB.CE.2.0;Data Source=" & oDBConfig.LocalDBLocation & oDBConfig.LocalDBName

            rda.InternetLogin = oDBConfig.IISLogin
            rda.InternetPassword = oDBConfig.IISPassword

            'Push To SQL 2000
            rda.Push("MyFavourite", rdaOleDbConnectString, _
                 RdaBatchOption.BatchingOn)

            MsgBox("DB Updated sucessfully", MsgBoxStyle.Information)
        Catch err As SqlCeException
            MsgBox(oUtil.ComposeSqlErrorMessage(err))
        Finally
            rda.Dispose()
        End Try
    End Function
    Public Sub CreateDB()
        '// If database exists, delete it and create a new one
        If File.Exists(oDBConfig.LocalDBLocation & oDBConfig.LocalDBName) Then
            File.Delete(oDBConfig.LocalDBLocation & oDBConfig.LocalDBName)
        End If
        '//Create a new database
        Dim sqlEngine As New SqlCeEngine("Data Source=" & oDBConfig.LocalDBLocation & oDBConfig.LocalDBName)
        sqlEngine.CreateDatabase()
    End Sub
    Public Function OpenResultSet(ByVal sSql As String) As SqlCeDataReader
        '//To execute the the SQL statement and return the results in DataReader
        If sSql.Trim = "" Then
            MsgBox("Invalid Request", MsgBoxStyle.Information)
            Exit Function
        End If
        Try
            If Conn.State = ConnectionState.Closed Then
                Conn.ConnectionString = "Data Source=" & oDBConfig.LocalDBLocation & oDBConfig.LocalDBName
                Conn.Open()
            End If
            Dim Reader As SqlCeDataReader
            Dim cmd As New SqlCeCommand(sSql, Conn)
            Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Return Reader
            Conn.Close()
        Catch err As SqlCeException
            MsgBox(oUtil.ComposeSqlErrorMessage(err))
        Catch ComErr As Exception
            MsgBox(ComErr.ToString, MsgBoxStyle.Information)
        Finally
        End Try
    End Function
    Public Sub ExecuteSQL(ByVal sSql As String)
        '//Execute the query like Insert, Update and delete
        Try
            If Conn.State = ConnectionState.Closed Then
                Conn.ConnectionString = "Data Source=" & oDBConfig.LocalDBLocation & oDBConfig.LocalDBName
                Conn.Open()
            End If
            Dim Reader As SqlCeDataReader
            Dim cmd As New SqlCeCommand(sSql, Conn)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            Conn.Close()
        Catch err As SqlCeException
            MsgBox(oUtil.ComposeSqlErrorMessage(err))
        Catch ComErr As Exception
            MsgBox(ComErr.ToString, MsgBoxStyle.Information)
        Finally

        End Try
    End Sub
End Class