Accessing MySQL on VB.NET using MySQL Connector/Net, Part 7: Perform SQL Operations

This entry is part 7 of 8 in the series Accessing MySQL on VB.NET

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"

The code will look similar as the figure below.
MySQL Connection String

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

Code Explanation:

  • 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
    object.
  • 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.

The code will look similar as the figure below.
Code for Retrieve Data from MySQL

The result of the query shows the first and second columns in the output window.
SELECT Result

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.

Update Function

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

Code Explanation:

  • Line 50: The code is similar to retrieve data section only it call ExecuteNonQuery() method and the return value is affected rows.

The function code will look similar as the figure below.
Code for Update Record

Example INSERT

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.

14
Console.WriteLine(updateRecord("INSERT INTO Country (Code, Name) VALUES ('AAA','Test Name')"))

Example UPDATE

The UPDATE command belows change Name to ‘Test2’ on row which has code = ‘AAA’. And output the affected rows to console.

15
Console.WriteLine(updateRecord("UPDATE Country SET Name='Test2' WHERE Code ='AAA'"))

Example DELETE

The DELETE command deletes a record which has code equals ‘AAA’. And output the affected rows to console.

16
Console.WriteLine(updateRecord("DELETE FROM Country WHERE Code ='AAA'"))

When I have run the application with the 3 statements above, the output window shows the row affected of each statement as the figure below.
Row Affected

Download Code

You can download a complete project file at here – SampleMySQL (zip format). The project was created on Microsoft Visual Studio 2005.

Summary

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.

Series Navigation<< Accessing MySQL on VB.NET using MySQL Connector/Net, Part 6: Create ConnectionAccessing MySQL on VB.NET using MySQL Connector/Net, Part 8: Display Result on GUI >>

36 Comments

  1. deepti bhatt March 25, 2009
  2. linglom March 28, 2009
  3. Sameer April 20, 2009
  4. carlos April 21, 2009
  5. liku April 24, 2009
  6. mr ed June 12, 2009
  7. Peter August 27, 2009
  8. Derek March 20, 2010
  9. Ebony April 8, 2010
  10. Daemon May 19, 2010
  11. linglom May 20, 2010
  12. Cris October 4, 2010
  13. Carlos October 4, 2010
  14. CLUS October 18, 2010
  15. her15bert October 22, 2010
  16. Peter December 26, 2010
  17. John December 28, 2010
  18. John December 28, 2010
  19. John December 28, 2010
  20. John December 28, 2010
  21. John December 28, 2010
  22. John December 28, 2010
  23. Peter December 30, 2010
  24. John January 3, 2011
  25. divya March 1, 2011
  26. Mohit Soni March 2, 2011
  27. Pablomex May 30, 2011
  28. Nevin September 18, 2011
  29. linglom September 19, 2011
  30. shomi January 23, 2012
  31. tom March 7, 2012
  32. tom March 7, 2012
  33. seng September 6, 2012
  34. kevin November 1, 2012
  35. Jean November 24, 2012
  36. TOM June 9, 2013

Leave a Reply

%d bloggers like this: