Connecting and working with MySQL in VB.NET

Background. DB selection


There was a need to make friends a program written in VB, with a database server. Of course, numerous fellow software developers can question my choice of database (and will be right), calling MSSQL the simplest and most obvious choice. But it is necessary to take into account several facts:
  • in addition to the main program (VB), the customer wished to be able to use the web interface (php)
  • MySQL is installed on the customer’s server


Configure MySQL Connection


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

Connection


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

To create a connection, you must create an object of type MySqlConnection and set it to ConnectionString (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 localhost, with the specified username and password.

Query execution


To execute queries, it is also necessary 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 particular connection to use to fulfill the request.
 cmd.Connection = conn

Connector / NET uses various functions to perform various database queries.
So, for example, to execute queries that return only the total number of rows involved, the ExecuteNonQuery function is used, and for those who return data (rows), a reader object is created.

As a result, at the output we have an application that, upon loading, executes 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

Also popular now: