Recently I was working with an MS Visual Studio (Visual Basic App) and a Web Server database (MySQL/MariaDB). My idea was to distribute the Windows app to all client machines, to be able to see/add/edit data in that one database they all are using. While I was searching on how to connect the windows app to MySQL, I saw that many people struggling with the same thing. Now it’s time to collect all I found on the internet and to write this tutorial. This way, I show you all steps necessary to make that connection.
Now I’ll show you how to do that. Learn how to connect Windows Application with a Web Server MySQL Database.
For this tutorial, I’m going to install and set up a new VPS, with CentOS 7 and CWP. There is no need for a domain name, and all we want to set up is Database enabled for outside connections.
From the client side, we need the Server IP, a Database and user with all the credentials to make connection possible.
First thing first, you need to have MS Visual Studio installed, all together with MySql connection driver packages. On the other end, you’ll need a Web server with a Database enabled (with a user) for remote connections.
Install MySQL connection driver
MS Visual Studio doesn’t come with MySQL support by default. However, you can install the components you’ll need to make this happen.
Visit this LINK and install the component for your system. There you can find and download MySQL drivers needed for remote Database connection. Once you’re finished installing the MySQL driver, you can call the component within your Visual Basic code. Keep reading and see how that works.
Create a Database and make configuration
To be able to connect first you’ll need a database and remote connection enabled. Before you’ll be able to connect the Windows app to MySQL, you have to do some configurations
Note: Keep in mind that Server security can deny connections from outside IPs. For this tutorial, I’m going to allow outside connections, without explaining any Firewall or other security software features available.
I’m going to create a Database, a user with full permissions, and a table with some dummy data. Windows application should be able to connect, read, edit and add new data to that table. For this example, I’m going to show how to populate DataGridView with a records from remote Database.
The picture above shows my Database server and some extra details about it. I have created a Database with a remote connection enabled. It is called testuser_db with a new user testuser_remote.
MySQL Database is ready for incoming connections
Now I have a database ready for an incoming connection. It is time to create a table and import some dummy data. Let’s give a table name test_table.
OK, Database is ready, all together with a table and dummy data.
Note: Default user testuser_db is the root for that Database and it can’t be configured for remote connection via CWP. This is a security feature I suppose and it is OK. Instead, I’ve created a new user testuser_remote with remote connection privileges (%). I’ll make it clear in the next paragraph.
So, now we have a Database testuser_db, table test_table, and user testuser_remote. It is time to work with Microsoft Visual Studio from now on. Before continuing I want to share these two SQL query commands.
If you’re lazy or just don’t know how to create table, here comes the SQL query for my Database table:
CREATE TABLE `test_table` ( `ID` int(3) NOT NULL, `name` varchar(255) NOT NULL, `surname` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
And for dummy data SQL query looks like:
INSERT INTO `test_table` (`ID`, `name`, `surname`, `email`) VALUES (1, 'David', 'Mc Melly', 'email@example.com'), (2, 'Bono', 'Vox', 'firstname.lastname@example.org'), (3, 'Michael', 'Cash', 'email@example.com'), (4, 'Yohn', 'Bush', 'firstname.lastname@example.org'), (5, 'Jenny', 'Crash', 'email@example.com');
MS Visual Studio Win Form app and MySQL connection
Like I wrote before, to make this connection you’ll need a MySQL connection driver. I have provided a link source to download packages and install them on your system. Once packages are installed, all things work as they should. Using a Visual Studio and specific code you can call for a reference and make a connection to a MySQL or MariaDB Database.
Note: MySQL and MariaDB are practically the same Database engines. Drivers from this topic are originally designed for MySQL, however, they will work for MariaDB as well.
First add a reference to the MySQL connector for .NET
This could be tricky, and it all depends on the system you’re using. If you’re on x64 or x32 the file path can vary. It is important to find an appropriate reference file and to import that file into your Visual Studio Project.
If you have started a new VS project:
- Look in the Solution Explorer, and navigate under the project name, locate References, and right-click on it. Select Add Reference.
- In the Add Reference dialog, switch to the Browse tab and browse to the folder containing the downloaded connector.
- Navigate to the bin folder, and select the MySql.Data.dll file. Click OK.
- From now on you can use the code and call for
Once you have prepared everything, it is time to write some code. I’ll provide the code necessary for the connection, and connection string.
Connection string: MS Visual Studio connecting to MySQL
Connecting your Windows application to a remote Database isn’t something much more different than connecting an app to a local MS SQL. The main difference lays in your Web Server configuration actually. If you have done everything to allow remote connections to a database, there shouldn’t be any problems.
The connection string from
App.config looks like this:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> </configSections> <connectionStrings> <add name="App.My.MySettings.MyConnectionString" connectionString="server=266.266.266.266;user id=testuser_remote;database=testuser_db;password=somepassword;persistsecurityinfo=True;charset=utf8mb4" providerName="MySql.Data.MySqlClient" /> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" /> </startup> </configuration>
Note: I have wrote non-existent IPs on purpose, just to keep everyone away from potentially someone’s real IP.
For this tutorial, I’ll make a Windows form and connection to a MySQL database. Also, I’ll use a DataGridView to load and show data. The Connection string can be declared inside
App.config file or directly inside the actual Win Form code.
Start New Project in MS Visual Studio
I have made a Form1 and a DataGridView1. To be able to follow and connect the Windows app to MySQL here comes the code.
Code to load data from remote Database into Windows application looks like this:
Imports MySql.Data.MySqlClient Public Class Form1 Dim mysqlconn As MySqlConnection Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load mysqlconn = New MySqlConnection mysqlconn.ConnectionString = "server=266.266.266.266;user id=testuser_remote;database=testuser_db;password=somepassword;persistsecurityinfo=True;charset=utf8mb4" mysqlconn.Open() Dim sql As String = "SELECT * FROM test_table" Dim cmd As MySqlCommand = New MySqlCommand(sql, mysqlconn) Dim dr As MySqlDataReader = cmd.ExecuteReader Dim dt As New DataTable dt.Load(dr) dr.Close() DataGridView1.DataSource = dt mysqlconn.Close() End Sub End Class
I won’t go any further with this by writing a code to Add, Edit, and Delete data. If you know the basics of this topic, I think you’ll manage to write that code by yourself.
Read my other tutorials if you’re interested in cool knowledge.