How to connect vb2010 to MySQL

Connecting VB10 to MySQL

ADO.NET

ADO.NET is a data access technology from Microsoft .Net Framework , which provides communication between relational and non-relational systems through a common set of components.

·         The two key components of ADO.NET are Data Providers and DataSet . The .Net Framework includes mainly have three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider, OLEDB Data Provider and ODBC Data Provider. SQL Server uses the SqlConnection object, OLEDB uses the OleDbConnection Object and ODBC uses OdbcConnection Object respectively.

The four Objects from the .Net Framework provide the functionality of Data Providers in the ADO.NET.

Ø  Connection Object

           The Connection Object provides physical connection to the Data Source.

Ø  Command Object

          The Command Object uses to perform SQL statement or stored procedure to be executed at the Data Source.

Ø  DataReader Object

           The DataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data.

Ø  DataAdapter Object

           Finally the DataAdapter Object , which populate a Dataset Object with results from a Data Source .

CREATING CONNECTION

1.     Install mysqlconnector/net,

2.     Add the MySQL library. Right-click on the project name -> Add Reference. On Add Reference, select MySQL.Data on .NET tab. By default, the reference library (MySQL.Data) won’t be copied to the output directory. That means when the application is deployed on other PC which doesn’t have the library installed, it’ll throw error. Therefore, it is a good idea to set the Copy Local property of the library file to True. On the Project menu, click Show All Files icon. Expand References -> Select MySQL.Data -> Change Copy Local property to True.

3.     Now it’s time to code the application. First, is to import a namespace. Open the Code View and add this line on the top.

  

Imports MySql.Data.MySqlClient

PERFORMING SQL OPERATIONS

Note: This example uses the following database information

Database Name         : dbName                    Mysql Username:      mysqlUserID

Table Name               : tblStudents              Mysql Password:       mysqlPassword

tblStudents structure

Consider the interface below:

The first thing to do is to declare a connection string

Private dbConString As String = "Database=dbName;" & _             "Data Source=localhost;" & _             "User Id=mysqlUserID;Password=mysqlPassword;" & _             "Connection Timeout=20"

Retrieving data from database

            To retrieve data from MySQL, use MySqlDataReader Class from MySql.Data library. First, is to 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. See the code below which makes used of a user-defined procedure/function .

Public Sub retrieveData()

        Try

            'Create a query variable as string.

            'Create a MySQLConnection object with the defined connection string in global as parameter.

            'Create a MySQLCommand object with previous 2 variables as parameters.

            Dim query As String = "SELECT * FROM tblStudents"

            Dim dbConnection As New MySqlConnection(dbConString)

            Dim dbCmd As New MySqlCommand(query, dbConnection)

            'Open a connection to MySQL Server using the defined connection string.

            dbConnection.Open()

            'Call ExecuteReader() method and assign the result to MySqlDataReader object.

            Dim dbReader As MySqlDataReader

            dbReader = dbCmd.ExecuteReader()

            'Looping on MySqlDataReader object to get results

            While dbReader.Read()

                lblResID.Text = dbReader.GetString("ID")

                lblResFirstName.Text = dbReader.GetString("firstname")

                lblResMiddleName.Text = dbReader.GetString("middlename")

                lblResLastName.Text = dbReader.GetString("lastname")

            End While

            'Close the reader and connection. It is recommended closing these objects after using every             'time.

            dbReader.Close()

            dbConnection.Close()

        Catch ex As Exception

            'If there is any error in the method, send to msgbox.

            MsgBox(ex.Message)

        End Try

    End Sub

UPDATE, DELETE AND INSERT RECORD ON DATABASE

                Coding on INSERT, UPDATE and DELETE SQL operations are identical except only for sql command that will be executed. In performing these operations to database, there is no need to get records from the database. We need to 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.

dbEdit Function (user-defined 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.

    'Declares a user-defined function for UPDATE,DELETE, and INSERT db operation

    'The function accepts a string sql query operation to be performed as its parameter

    'The code is similar to retrieveData() procedure section only it call ExecuteNonQuery() method and            

    'the return value is affected rows.

    Function dbEdit(ByVal query As String) As Integer

        Try

            Dim rowsAffected As Integer = 0

            Dim dbConnection As New MySqlConnection(dbConString)

            Dim dbCmd As New MySqlCommand(query, dbConnection)

         

            dbConnection.Open()

            rowsAffected = dbCmd.ExecuteNonQuery()

            dbConnection.Close()

            Return rowsAffected

        Catch ex As Exception

            MsgBox(ex.Message)

            Return 0

        End Try

    End Function

COMPLETE CODE LISTING

Imports MySql.Data.MySqlClient

Public Class frmMain

    Private dbConString As String = "Database=dbName;" & _

            "Data Source=localhost;" & _

            "User Id=mysqlUserID;Password=mysqlPassword;" & _

            "Connection Timeout=20"

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    'Declares a user-defined procedure for retrieving db records

    Public Sub retrieveData()

        Try

           'Create a query variable as string.

            'Create a MySQLConnection object with the defined connection string in global as parameter.

            'Create a MySQLCommand object with previous 2 variables as parameters.

            Dim query As String = "SELECT * FROM tblStudents"

            Dim dbConnection As New MySqlConnection(dbConString)

            Dim dbCmd As New MySqlCommand(query, dbConnection)

            'Open a connection to MySQL Server using the defined connection string.

            dbConnection.Open()

            'Call ExecuteReader() method and assign the result to MySqlDataReader object.

            Dim dbReader As MySqlDataReader

            dbReader = dbCmd.ExecuteReader()

            'Looping on MySqlDataReader object to get results

            While dbReader.Read()

                lblResID.Text = dbReader.GetString("ID")

                lblResFirstName.Text = dbReader.GetString("firstname")

                lblResMiddleName.Text = dbReader.GetString("middlename")

                lblResLastName.Text = dbReader.GetString("lastname")

            End While

            'Close the reader and connection. It is recommended closing these objects after using every time.

            dbReader.Close()

            dbConnection.Close()

        Catch ex As Exception

            'If there is any error in the method, send to msgbox.

            MsgBox(ex.Message)

        End Try

    End Sub

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    'Declares a user-defined function for UPDATE,DELETE, and INSERT db operation

    'The function accepts a string sql query operation to be performed as its parameter

    'The code is similar to retrieveData() procedure section only it call ExecuteNonQuery()

    Function dbEdit(ByVal query As String) As Integer

        Try

            Dim rowsAffected As Integer = 0

            Dim dbConnection As New MySqlConnection(dbConString)

            Dim dbCmd As New MySqlCommand(query, dbConnection)

            dbConnection.Open()

            rowsAffected = dbCmd.ExecuteNonQuery()

            dbConnection.Close()

            Return rowsAffected

        Catch ex As Exception

            MsgBox(ex.Message)

            Return 0

        End Try

    End Function

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click

        'Calls the retrieveData() to perform its function

        Call retrieveData()

    End Sub

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim query As String = "UPDATE tblstudents SET id='" & txtID.Text & "',firstname='" & txtFirstName.Text & _

            "',middlename='" & txtMiddleName.Text & "',lastname='" & txtLastName.Text & "' WHERE id='" & lblResID.Text & "'"

        'Calls the dbEdit function and passes the variable query

        dbEdit(query)

    End Sub

End Class