Installing the Connector
Visual Studio is not supplied with a default MySQL/MariaDB connector.Use the MySQL connector.
https://dev.mysql.com/doc/connector-net/en/connector-net-installation.html
Run the installer.
image
Select Typical installation
image
In Visual Studio, to add the connector go to the Project/Add Reference. Select Assemblies and search for MySQL. Remember you will need to do this for each project (even within a solution).
image
Tick MySQL.Data and click on OK.
Example program in VB.Net
The program uses a simple form.One vertical Split Container, with one button in one panel, and a multi-line, docked text box with vertical scroll bar in the other panel.
Code
Public Class Form1' *** Test application -
' *** to a remote MariDB database, insert a row and then display all rows
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim myDB As New MySql.Data.MySqlClient.MySqlConnection("Server=192.168.1.191;Database=datastorage;Uid=datawriter;Pwd=*******;")
Dim myCommand As New MySql.Data.MySqlClient.MySqlCommand()
Dim myReader As MySql.Data.MySqlClient.MySqlDataReader
Dim sSQL As String
addtext("Connecting")
myDB.Open()
addtext("Connected")
addtext("Inserting")
sSQL = "insert into datavalues(user,title,value) values(CURRENT_USER(),'VBtest',23);"
With myCommand
.Connection = myDB
.CommandText = sSQL
.ExecuteNonQuery()
End With
addtext("Inserted")
sSQL = "SELECT * FROM datavalues"
addtext("Selecting")
With myCommand
.CommandText = sSQL
myReader = .ExecuteReader()
End With
addtext("Selected")
addtext("Reading")
While myReader.Read()
Dim sLine As String = ""
For ix As Integer = 0 To myReader.FieldCount - 1
If sLine <> "" Then
sLine = sLine & ", " & myReader.GetString(ix)
Else
sLine = myReader.GetString(ix)
End If
Next
addtext(sLine)
End While
addtext("Reading")
addtext("Closing")
myReader.Close()
myDB.Close()
addtext("Closed")
End Sub
''' <summary>
''' Add string parameter to current content of text box
''' </summary>
''' <param name="aString">Text to be added to text box </param>
Private Sub addtext(aString As String)
TextBox1.Text = TextBox1.Text & aString & vbCrLf
End Sub
End Class
Output
Connecting
Connected
Inserting
Inserted
Selecting
Selected
Reading
1, root@localhost, test, 1, 27/02/2019 11:06:09
2, root@localhost, test, 2, 27/02/2019 11:06:16
3, root@localhost, test, 3, 27/02/2019 11:06:23
5, datawriter@192.168.1.%, test, 30, 28/02/2019 13:13:07
6, datawriter@192.168.1.%, test, 30, 28/02/2019 13:13:23
7, datawriter@192.168.1.%, test, 30, 30/07/2019 08:06:21
8, datawriter@192.168.1.%, VBtest, 23, 30/07/2019 10:49:39
9, datawriter@192.168.1.%, VBtest, 23, 30/07/2019 10:58:02
Reading
Closing
Closed
Example Code in C#
This is a console application.Code
using System;using MySql.Data.MySqlClient;
public class MySQLTest
{
public static void Main()
{
string connStr = "Server=192.168.1.191;Database=datastorage;Uid=datawriter;Pwd=********;";
MySqlConnection myDB = new MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting");
myDB.Open();
Console.WriteLine("Connected");
Console.WriteLine("Inserting");
string sql = "insert into datavalues(user,title,value) values(CURRENT_USER(),'C#test',54);";
MySqlCommand myCommand = new MySqlCommand(sql, myDB);
myCommand.ExecuteNonQuery();
Console.WriteLine("Inserted");
Console.WriteLine("Selecting");
sql = "SELECT * FROM datavalues";
myCommand.CommandText = sql;
MySqlDataReader myReader = myCommand.ExecuteReader();
Console.WriteLine("Selected");
Console.WriteLine("Reading");
while (myReader.Read())
{
string rowtext = "";
for (int ix = 0; ix < myReader.FieldCount; ix += 1)
{
if (rowtext == "")
{
rowtext = string.Concat("", myReader[ix]);
}
else
{
rowtext = string.Concat(rowtext,", ", myReader[ix]);
}
}
Console.WriteLine(rowtext);
}
myReader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
myDB.Close();
Console.WriteLine("Done.");
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
}
Output
Connecting
Connected
Inserting
Inserted
Selecting
Selected
Reading
1, root@localhost, test, 1, 27/02/2019 11:06:09
2, root@localhost, test, 2, 27/02/2019 11:06:16
3, root@localhost, test, 3, 27/02/2019 11:06:23
5, datawriter@192.168.1.%, test, 30, 28/02/2019 13:13:07
6, datawriter@192.168.1.%, test, 30, 28/02/2019 13:13:23
7, datawriter@192.168.1.%, test, 30, 30/07/2019 08:06:21
8, datawriter@192.168.1.%, VBtest, 23, 30/07/2019 10:49:39
9, datawriter@192.168.1.%, VBtest, 23, 30/07/2019 10:58:02
10, datawriter@192.168.1.%, test, 30, 30/07/2019 13:28:56
11, datawriter@192.168.1.%, C#test, 54, 30/07/2019 13:42:00
12, datawriter@192.168.1.%, test, 30, 30/07/2019 13:43:13
13, datawriter@192.168.1.%, C#test, 54, 30/07/2019 13:45:37
14, datawriter@192.168.1.%, C#test, 54, 30/07/2019 13:47:51
15, datawriter@192.168.1.%, C#test, 54, 30/07/2019 13:48:04
16, datawriter@192.168.1.%, C#test, 54, 30/07/2019 13:52:03
17, datawriter@192.168.1.%, C#test, 54, 30/07/2019 13:56:07
Done.
Press any key to exit.
