Connecting and working with MySQL in VB.NET
- From the sandbox
- Tutorial
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