Linglom's blog

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.

You can see index of this series at Accessing MySQL on VB.NET using MySQL Connector/Net, Part I: Introduction

Section

1. Declare a Connection String
2. Retrieve Data from Database
3. Update Record on Database
4. Download Code
5. Summary

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.

Back to top

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.

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

Back to top

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.

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.

Back to top

Download Code

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

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.
Back to top