How to connect to MySQL database from vb.net using app.config

Question

I need help in how to convert my current connection and migrate in using app.config in my vb.net project so when I transfer or edit my database credentials I don't need to recompile new installer for my program. Thanks in advance mates!

Here is my current connection:

(code inside my mdlConnection.vb)

Module mdlConnection
    Public connString1 As String = "server=localhost;user id=root;password=;Database=sampleprog"
    Public conn As MySqlConnection = New MySqlConnection(connString1)
    Public sql As String = ""

    Public Function ExecuteQuery(ByVal query As String) As DataTable
        Dim sqlDT As New DataTable
        Try
            Dim sqlCon As New MySqlConnection(connString1)
            Dim sqlDA As New MySqlDataAdapter(query, sqlCon)
            Dim sqlCB As New MySqlCommandBuilder(sqlDA)
            sqlDA.Fill(sqlDT)
        Catch ex As Exception
        End Try
        Return sqlDT
    End Function
End Module

This is the config inside my app.config:

You can add a connection string on the Settings page of the project properties. That will be automatically added to the config file and you can access it in code via `My.Settings`. — jmcilhinney, Oct 13 '17 at 06:56
@jmcilhinney I opened this question and started typing an answer a then got distracted. I just noticed now that I have submitted it that you had a similar suggestion in your comment. Sorry about that. — Fabulous, Oct 13 '17 at 07:48
@Fabulous Sir I just followed your instructions and edited my codes and i encountered some errors btw thanks you — DDD, Oct 16 '17 at 07:05
@Fabulous there is no more errors I just forgot to add the system config reference it now fully functional thanks again and btw is there a way that I can easily encrypt the database password if i put one so no one can see it thru my app.config if ever someone tries to sneak in my project. — DDD, Oct 16 '17 at 08:28
Hi @DDD, for that I would recommend you [look at this thread](https://stackoverflow.com/questions/11637348/encrypt-connection-string-in-app-config) — Fabulous, Oct 16 '17 at 08:57

score 1 · Accepted Answer · answered Oct 16 '17 at 07:04

Please update your current app.config file as below :

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
        </startup>

        <connectionStrings>
            <add name="MySqlConnectionString" connectionString="SERVER=localhost;DATABASE=sampleprog;UID=root;PASSWORD=;" />
        </connectionStrings>

    </configuration>

And then modify your 'mdlConnection.vb' code as below :

    Module mdlConnection
        Public connString1 As String = ConfigurationManager.ConnectionStrings("MySqlConnectionString").ToString()
        Public conn As MySqlConnection = New MySqlConnection(connString1)
        Public sql As String = ""

        Public Function ExecuteQuery(ByVal query As String) As DataTable
            Dim sqlDT As New DataTable
            Try
                Dim sqlCon As New MySqlConnection(connString1)
                Dim sqlDA As New MySqlDataAdapter(query, sqlCon)
                Dim sqlCB As New MySqlCommandBuilder(sqlDA)
                sqlDA.Fill(sqlDT)
            Catch ex As Exception
            End Try
            Return sqlDT
        End Function
    End Module

Please don't forget the add the namespace 'Imports System.Configuration' in your 'mdlConnection.vb' and the reference 'System.Configuration' in your solution.

Yes sir thank you very much your answer is exactly what my project needed. Im just having some struggle in trying to encrypt my database password — DDD, Oct 17 '17 at 06:02
@DDD - You can find some useful information on [Encrypting and Decrypting your DB config section here](https://msdn.microsoft.com/en-us/library/zhhddkxy(v=vs.100).aspx) — Md. Suman Kabir, Oct 17 '17 at 06:20
Sir I have a follow up question for this. What will I change if I will use a local server stand alone pc in my LAN as my wampserver / mysqlserver Im using its ip 192.168.1.2. @M.d Suman Kabir — DDD, May 29 '18 at 05:56

score 0 · Answer 2 · answered Oct 13 '17 at 07:46

Double click on your project's "My Project" entry in solution explorer. Go to the settings tab from the window that opens. Change the type to Connection String as shown in the screenshot below

Alternatively, you can add the following to your app.config file below the <configuration> opening tag;

<configuration>
    <connectionStrings>
        <add name="<Desired name here>" connectionString="server=localhost;user id=root;password=;Database=sampleprog" />
    </connectionStrings>
    <startup>
    ...
</configuration>

You can then access the connection string as follows in your code.

Dim con = My.Settings.DbConnection ' provided you named your connection string DbConnection in the first step above.

score 0 · Answer 3 · edited Oct 16 '17 at 11:20

0

Here is my settings

enter image description here

here is my updated mdlConnection

enter image description here

here is my updated mdlConnection

Dim con = My.Settings.DbConnection
Public conn As MySqlConnection = New MySqlConnection(con)
Public sql As String = ""

Public Function ExecuteQuery(ByVal query As String) As DataTable
    Dim sqlDT As New DataTable
    Try
        Dim sqlCon As New MySqlConnection(con)
        Dim sqlDA As New MySqlDataAdapter(query, sqlCon)
        Dim sqlCB As New MySqlCommandBuilder(sqlDA)
        sqlDA.Fill(sqlDT)
    Catch ex As Exception
    End Try
    Return sqlDT
End Function

edited Oct 16 '17 at 11:20

nvoigt

75,013
26
93
142

answered Oct 16 '17 at 06:42

DDD

33
1
8

1

Please don't use images. If possible use plain text inside an answer. – xMRi Oct 16 '17 at 08:41
Sorry my bad. I will take it note the next time I post my questions thankyouuu! – DDD Oct 16 '17 at 09:01

How to connect to MySQL database from vb.net using app.config

3 Answers3