Mengekalkan ConnectionString pangkalan data SQLite aplikasi Console mudah alih

Maintaining SQLite ConnectionString in Console App

Intro

Apabila kita membina aplikasi Winform atau Console secara portable yang berhubung dengan pangkalan data SQLite secara relatif, masalah utama ialah untuk mengubah ConnectionString pada App.config supaya ia berubah mengikut lokasi aplikasi tersebut setiap kali aplikasi ini berubah lokasi.

Adalah mustahil unutk memasukkan relative path kedalam App.config terutama bagi pengguna Entity Framework.

Apa yang perlu dilakukan ialah kita perlu mengubah path pada data source di setiap ConnectionString secara dalam program

---

SQLiteDbPathFixer.vb

Imports System.Configuration
Imports System.Data.SQLite
Imports System.IO

Namespace MyCode

    ''' <summary>
    ''' SQLite database path maintainer.
    ''' This class will fix the path in connectionstring to be relative to the application path.
    ''' Support Normal and EF6 type ConnectionsString
    ''' </summary>
    Public Class SQLiteDbPathFixer

        ''' <summary>
        ''' Fix Db Path in ConnectionString
        ''' </summary>
        Public Shared Function FixDbPath() As Boolean
            Dim ReturnValue As Boolean = False
            Dim AppDataPath As String = System.AppDomain.CurrentDomain.BaseDirectory & "App_Data\"

            For Each ConStrSet As ConnectionStringSettings In ConfigurationManager.ConnectionStrings
                Dim ConnName As String = ConStrSet.Name

                'For Normal Connectionstring
                If ConStrSet.ProviderName = "System.Data.SQLite" Then
                    Dim csb As New SQLiteConnectionStringBuilder(ConStrSet.ConnectionString)

                    Dim DbName As String = Path.GetFileName(csb.DataSource)
                    csb.DataSource = AppDataPath & DbName

                    ReturnValue = SaveConnectionString(ConnName, csb.ConnectionString)
                End If

                'For EF6 ConnectionString
                If ConStrSet.ProviderName = "System.Data.EntityClient" Then

                    Dim csb As New System.Data.EntityClient.EntityConnectionStringBuilder(ConStrSet.ConnectionString)

                    If csb.Provider = "System.Data.SQLite.EF6" Then
                        Dim ProviderConnectionString2 As String = csb.ProviderConnectionString.Replace("data source=", "")
                        Dim DbName As String = Path.GetFileName(ProviderConnectionString2)
                        csb.ProviderConnectionString = "data source=" & AppDataPath & DbName

                        ReturnValue = SaveConnectionString(ConnName, csb.ConnectionString)
                    End If

                End If
            Next

            Return ReturnValue
        End Function

        Private Shared Function SaveConnectionString(ConnName As String, ConnectionString As String) As Boolean
            Dim ReturnValue As Boolean = False

            Dim Config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
            If Not ConfigurationManager.ConnectionStrings(ConnName).ConnectionString = ConnectionString Then
                Config.ConnectionStrings.ConnectionStrings(ConnName).ConnectionString = ConnectionString
                Config.AppSettings.SectionInformation.ForceSave = True
                Config.Save(ConfigurationSaveMode.Modified)

                ConfigurationManager.RefreshSection("connectionStrings")

                ReturnValue = True
            End If

            Return ReturnValue
        End Function

    End Class

End Namespace

Masukkan Class diatas kedalam applikasi Console atau Winform, kemudia cuma run fungsi FixDbPath() pada permulaan kod.

---