Connecting and working with MySQL in VB.NET

Prehistory DB selection

There was a need to make friends a program written in VB with a database server. Of course, numerous associates - software developers may question my choice of database (and they will be right), calling MSSQL the simplest and most obvious choice. But it is necessary to take into account several facts:

• besides the main program (VB), the customer wanted to be able to use the web interface (php)
• MySQL is installed on the customer's server

Configuring MySQL Connection

Since VB does not have a standard MySQL connection mechanism, it was decided to use Connector / NET rather than ODBC .
First you need to connect the connector library.
By default, there is My Project in the Solution Explorer. We actually choose it, then go to References.
It should look something like this , but without MySql.Data, respectively. I have already connected this library.
Click add (add), select the Browser tab and look for the library. It is located in the following path: path_where_your_state_mysql_connector / Assemblies / version .net / MySql.Data.dll
After adding the library to the project, be sure to change the copy locally parameter (in the Properties Window, Properties, with the list item highlighted) to true, otherwise, when running the software on a computer without MySQL Connector / NET, the application will crash with the dll error.
After all this, you can safely begin to write the program.

Connection

To begin with, import the types with the Imports MySql.Data.MySqlClient line. It must be placed in the definition area before creating any classes / objects. This is necessary in order to save us from having to write the full path to the types each time when defining variables.

Imports MySql.Data.MySqlClient 

To create a connection, you need to create an object of type MySqlConnection and set it with a ConnectionString (the connection string describes the necessary parameters for connecting to the server).

 'User id   ,   . Dim conn As New MySqlConnection("Server=127.0.0.1;User id=test_user;password=test_pwd;database=test_db") 

Thus, through conn, the program will connect to the specified database on the localhost, with the specified login and password.

Query execution

To execute queries, you also need to create an object of type MySqlCommand.

  Dim cmd As New MySqlCommand 

The query text in this state of affairs is set by changing the CommandText property of the cmd object:

  cmd.CommandText = " " 

In addition, it is important not to forget to specify which specific connection to use to fulfill the request.

  cmd.Connection = conn 

Connector / NET uses various functions to perform various database queries.
For example, to execute queries that return only the total number of rows involved, the ExecuteNonQuery function is used, and for returning data (rows), a reader object is created.
')
As a result, at the exit, we have an application that, when loaded, performs a query to the database.

 Imports MySql.Data.MySqlClient Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim conn As New MySqlConnection("Server=127.0.0.1;User id=test_user;password=test_pwd;database=test_db") Dim cmd As New MySqlCommand Try conn.Open() cmd.Connection = conn '    insert, update   ExecuteNonQuery,      cmd.CommandText = "INSERT INTO `test_table` (`id`, `test_info`) VALUES (NULL, 'some text info for current id');" Try cmd.ExecuteNonQuery() Catch ex As Exception ' ,        -   End Try '     (  select)  reader. cmd.CommandText = "SELECT * FROM `test_table`" Dim reader As MySqlDataReader reader = cmd.ExecuteReader() While reader.Read() '              MsgBox(reader.GetValue(0)) End While Catch ex As Exception '        End Try End Sub End Class