none
How to dynamically change a connection string for a dataset class object RRS feed

  • Question

  • Hi again,

    I have a problem that I need your expertise. I am creating a visual basic application with an MS Access database as the main data connectivity. In this regard I have configured a typed dataset will all the objects (tables, queries, relations) of the mdb file. And in my code I can easily refer to tables and others items within my typed dataset. My mdb file is not in the project folder but located at a different location (example: D:\Projects\Management\NotaBene\Databases). In the application settings an a connection string is added as

    Provider=Microsoft.Jet.OLEDB.4.0;DataSource="D:\Projects\Management\NotaBene\Databases\Accounts.mdb"

    At design there is no problem to connect to the mdb file. How when cerate an installation package and installed on another computer the connection to the database failed until I manually creat the  same folder structure on the user computer and copy the mdb file in.

    My problem is this: How can I configure the connection string at the design time so that the user can dynamically change it during runtime through a dialog box to locate the mdb file?

    I wanted to provide a way to change the connection string in the application setting but it is application level setting and therefore readonly.

    Thank you for any help you can provide

    Regards

    Ekoue


    Saturday, January 20, 2018 1:47 PM

All replies

  • Create a configuration option and in it use a file open dialog (such as for opening other types of files) so the user can browse to the file. Add whatever validation you want to ensure it is a valid database. Save the path (and name) in a configuration setting. Since you are vague about the language and such I can only be correspondingly vague. Then every time the database is opened you simply put the path and filename into the connection string. In your program you wuold just need "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" for the connection string and then you append the path and such. I assume you must also add the double-quotes.


    Sam Hobbs
    SimpleSamples.Info

    Sunday, January 21, 2018 1:24 AM
  • Thank you for the suggestion. I have actually created the option for th user to select the mdb file through the fileopendialog. However the big issue resides in the fact that my dataset is a typed dataset generated using the dataset configuration assistant and I don’t know where the connection initiation tooks place. But now exploring the associated vb code generated along with the dataset, I located the section where the connection string is initialized for every single tables and queries.

    Below is the initiation of the connectionstring

    Private Sub InitConnection()

      Me._connection = New Global.System.Data.OleDb.OleDbConnection()

      Me._connection.ConnectionString = Global.NotaBene.My.MySettings.Default.ManagementConnectionString

    End sub

    I create a user level setting which is editable and can be edited through the file open dialog. Then I replace the ManagementConnectionString which is a readonly and hardcoded value with the user level setting UserConnectionString

    So the initiation code becomes:

    Private Sub InitConnection()

      Me._connection = New Global.System.Data.OleDb.OleDbConnection()

    '  Me._connection.ConnectionString = Global.NotaBene.My.MySettings.Default.ManagementConnectionString

      Me._connection.ConnectionString = Global.NotaBene.My.MySettings.Default.UserConnectionString

    End sub

    And this works perfectly!

    Sunday, January 21, 2018 9:20 AM
  • This is one of the most asked questions if it is about generic data from Microsoft. The problem is that with software like generic datasets, linq to Sql and Linq to entities the connectionstring is made private. It seems despite of this endless times asked end user problem impossible for Microsoft to make it public. 



    Success Cor

    Sunday, January 21, 2018 10:30 AM
  • I apologize for being hasty in my reply.

    When we create a database connection string, whether for typed databases or for other connections, the default is to save the connection string in the App.config file; VS does that for us. It is called App.config in our project but the name is different for execution; usually it is called "project.exe.config" where "project" is the application. See Connection Strings and Configuration Files for more. In VS open the "App.config" file to see what it looks like. Note that to modify it you can go to "Settings" in the project properties for an interactive editor. In the "App.config" file note that the connection string is named something such as "Project.My.MySettings.db1ConnectionString". 

    Connection strings are normally "Application" scope, not "User" scope. You will see that in the "Settings" in the project properties. The difference between the two is that "Application" scope settings are normally not changed by the user so changing them during execution is not as easy as it is for "User" scope settings. Also note that each setting has a "type" and for connection strings the type is "Connection string". The following can be used to change and persist the "Data Source" in a connection string configuration.

     
    ' Might need to add a reference and Imports for System.Configuration
    Dim config As Configuration =
        ConfigurationManager.OpenExeConfiguration(System.Reflection.Assembly.GetExecutingAssembly().Location)
    Dim section As ConnectionStringsSection = DirectCast(config.GetSection("connectionStrings"), ConnectionStringsSection)
    ' See the App.config file for the name to be used in the following
    Dim settings As ConnectionStringSettings = section.ConnectionStrings("Project.My.MySettings.db1ConnectionString")
    Dim builder As New System.Data.OleDb.OleDbConnectionStringBuilder(settings.ConnectionString)
    builder("Data Source") = "C:\User\Documents\db1.mdb"
    settings.ConnectionString = builder.ConnectionString
    config.Save()

    Using that the configuration is changed permanently and there is no need for other modifications except the obvious such as a Try/Catch for the query code in situations where the data source has not been set.



    Sam Hobbs
    SimpleSamples.Info


    Sunday, January 21, 2018 6:02 PM
  • Hi Ekoue,

    I will move your thread to VB forum, If you have problems about visual studio installation, please feel free to let me know.

    Best regards,

    Joyce 


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 5:29 AM
  • Thank you for the suggestion. I have actually created the option for th user to select the mdb file through the fileopendialog. However the big issue resides in the fact that my dataset is a typed dataset generated using the dataset configuration assistant and I don’t know where the connection initiation tooks place. But now exploring the associated vb code generated along with the dataset, I located the section where the connection string is initialized for every single tables and queries.

    !

    Hi Ekoue,

    If you want to change connection string from config, you can take a look the following code:

     Dim dt As New DataTable
        Private Sub FrmAppconfig_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'Dim str As String = System.Configuration.ConfigurationManager.ConnectionStrings("Dbase").ConnectionString
            'Dim conn As New SqlConnection(str)
            'conn.Open()
            'Dim sql As String = "select * from Test1"
            'Dim cmd As New SqlCommand(sql, conn)
            'Dim adapter As New SqlDataAdapter(cmd)
            'adapter.Fill(dt)
            'conn.Close()
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim config As System.Configuration.Configuration
            Dim fileMap As New ExeConfigurationFileMap()
    
            fileMap.ExeConfigFilename = "D:\CherrySample\Demo(vb.net)\Test2\App.config"
            config = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None)
    
            '   Sets values to config file.
            If config.HasFile() Then
    
                config.AppSettings.Settings.Item("ConnectionString").Value = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testdb;Integrated Security=True"
                config.Save(ConfigurationSaveMode.Modified)
                ConfigurationManager.RefreshSection("AppSettings")
    
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim strConnString As String = ""
            Dim config As System.Configuration.Configuration
            Dim fileMap As New ExeConfigurationFileMap()
    
            fileMap.ExeConfigFilename = "D:\CherrySample\Demo(vb.net)\Test2\App.config"
            config = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None)
    
            '   Sets values to config file.
            If config.HasFile() Then
                strConnString = config.AppSettings.Settings.Item("ConnectionString").Value
            End If
            Dim conn As New SqlConnection(strConnString)
            conn.Open()
            Dim sql As String = "select * from Test1"
            Dim cmd As New SqlCommand(sql, conn)
            Dim adapter As New SqlDataAdapter(cmd)
            adapter.Fill(dt)
            conn.Close()
        End Sub

    Here is the similar thread, please take a look:

    https://stackoverflow.com/questions/9156940/changing-connectionstring-area-of-app-config-in-vb-net

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, January 23, 2018 6:54 AM
    Moderator
  • Hello,

    Here is a conceptual example that you can twist around to work for you. In this case (keeping it simple) we create a setting under My.Settings in project properties called OverrideConnection with no value.

    In form load check to see if we are running outside Visual Studio using If Not Debugger.IsAttached, if this condition is true then (remember this is conceptual, you could offer a dialog to change this etc) then set the connection string.

    Here I simply set it to a known path while for you the path needs to be figured out as desired.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        If Not Debugger.IsAttached Then
            If String.IsNullOrWhiteSpace(My.Settings.OverrideConnection) Then
                AppDomain.CurrentDomain.SetData("DataDirectory", "C:\Data")
            End If
        End If
    
        'TODO: This line of code loads data into the 'CustomersMainDataSet.Customers' table. 
        'You can move, or remove it, as needed.
        Me.CustomersTableAdapter.Fill(Me.CustomersMainDataSet.Customers)
    
    End Sub
    Nothing is affected while working in Visual Studio, you will still be pointing at the copy of the database you currently have setup. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, January 23, 2018 11:02 AM
    Moderator