- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 1: Introduction
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 2: Setup MySQL Server
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 3: Install Sample Database
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 4: Create & Grant MySQL User Account
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 5: Install MySQL Connector Net
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 6: Create Connection
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 7: Perform SQL Operations
- Accessing MySQL on VB.NET using MySQL Connector/Net, Part 8: Display Result on GUI
Perform SQL Operations
From the previous part, I have successfully connect to world database on MySQL Server from VB.NET. Next, I try to perform basic SQL operations (SELECT, INSERT, UPDATE and DELETE) on the world database.
Note: This post is continued from the previous part. If you start a new project, you’ll need to add MySql.Data reference. See the previous post for more detail.
Declare a Connection String
If you continue from the previous part, you simply move the connStr variable from TestConnection() method to global scope. Otherwise, declare a new global variable as connection string with the value below.
Note: For more detail about how to build a connection string, see the previous post.
1 2 3 4
Private connStr As String = "Database=world;" & _ "Data Source=192.168.125.21;" & _ "User Id=worldUser;Password=worldpassword;" & _ "Connection Timeout=20"
Retrieve data from database
To retrieve data from MySQL, I’ll use MySqlDataReader Class from MySql.Data library. First, I open the connection to the world database on MySQL Server. Then, executes the query command by using ExecuteReader method and assigns to MySqlDataReader object. After that, looping on MySqlDataReader object to get result. Let’s see the code below.
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
Public Sub retriveData() Try Dim query As String = "SELECT * FROM Country" Dim connection As New MySqlConnection(connStr) Dim cmd As New MySqlCommand(query, connection) connection.Open() Dim reader As MySqlDataReader reader = cmd.ExecuteReader() While reader.Read() Console.WriteLine((reader.GetString(0) & ", " & _ reader.GetString(1))) End While reader.Close() connection.Close() Catch ex As Exception Console.WriteLine(ex.Message) End Try End Sub
- Line 21: Create a query variable as string.
- Line 22: Create a MySQLConnection object with the defined connection string in global as parameter.
- Line 23: Create a MySQLCommand object with previous 2 variables as parameters.
- Line 25: Open a connection to MySQL Server using the defined connection string.
- Line 27-28: Call ExecuteReader() method and assign the result to MySqlDataReader
- Line 30-33: Looping on MySqlDataReader object to get results to the console.
- Line 35-36: Close the reader and connection. I recommend to close these objects after using everytime.
- Line 38: If there is any error in the method, send to console.
Update Record on Database
Coding on INSERT, UPDATE and DELETE SQL operations are identical except only sql command that is executed. When I perform these operations to database, there is no need to get records from the database. So I use ExecuteNonQuery() Method from MySqlCommand Class. For INSERT, UPDATE and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.
Note: You can use ExecuteNonQuery to perform any type of database operation, however any result sets returned will not be available.
This function accepts a parameter as sql command and send to execute on MySQL Server. So the function can be use for INSERT, UPDATE and DELETE operations also any operation that doesn’t need a return result sets. Also, it returns an integer value of affected rows.
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
Function updateRecord(ByVal query As String) As Integer Try Dim rowsEffected As Integer = 0 Dim connection As New MySqlConnection(connStr) Dim cmd As New MySqlCommand(query, connection) connection.Open() rowsEffected = cmd.ExecuteNonQuery() connection.Close() Return rowsEffected Catch ex As Exception Console.WriteLine(ex.Message) End Try End Function
- Line 50: The code is similar to retrieve data section only it call ExecuteNonQuery() method and the return value is affected rows.
To execute INSERT command, try the statement below. The sql command will insert a new record to Country table on world database. Then, output the affected rows to console.
Console.WriteLine(updateRecord("INSERT INTO Country (Code, Name) VALUES ('AAA','Test Name')"))
The UPDATE command belows change Name to ‘Test2’ on row which has code = ‘AAA’. And output the affected rows to console.
Console.WriteLine(updateRecord("UPDATE Country SET Name='Test2' WHERE Code ='AAA'"))
The DELETE command deletes a record which has code equals ‘AAA’. And output the affected rows to console.
Console.WriteLine(updateRecord("DELETE FROM Country WHERE Code ='AAA'"))
You can download a complete project file at here – SampleMySQL (zip format). The project was created on Microsoft Visual Studio 2005.
Now you have reach the end of the article. After 7 parts, you should be able to develop an simple application to access MySQL Server on your own. I think that the article is quite clear than other accessing database server articles that I wrote last year. If you have any question, feel free to leave a comment below.