VB.Net - VB Net dengan menggunakan Database MySql

VB.Net - VB Net dengan menggunakan Database MySql

Step 1 : Download MySql Connector

ADO.NET Driver for MySQL (Connector/NET)
Link untuk download https://www.mysql.com/products/connector/ kemudian install

Step 2 : Add Reference Mysql Connector Pada VB.Net

 



Step 3 : Create New Class

Create New Class Contoh : clsConnMySql.VB
Copy Paste Code dibawah ini.

Imports MySql.Data
Imports MySql.Data.MySqlClient
'Add referense MySql Connector

Public Class clsConnMySql
    Public connString As String = "Server=127.0.0.1;Database=mysahamtest;Uid=root;Pwd=;Allow User Variables=True"
    Public conn As MySqlConnection
    Public SUCCESS As String = "SUCCESS"
    Public SERROR As String = "ERROR"

    Public Function CheckDBConnection() As Boolean
        conn = New MySqlConnection
        Try
            conn.ConnectionString = connString
            conn.Open()
        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function

    Public Function executeSQL_dt(ByVal sSql As String, ByRef sResult As String) As Data.DataTable
        Dim sReturn As String = ""
        'Dim sr As SqlDataReader = Nothing
        Dim dt As DataTable = New DataTable
        Dim da As New MySqlDataAdapter
        conn = New MySqlConnection
        Try
            conn.ConnectionString = connString
            conn.Open()
            Dim sComm As MySqlCommand = New MySqlCommand
            sComm.CommandText = sSql
            sComm.Connection = conn
            da.SelectCommand = sComm
            da.Fill(dt)
            conn.Close()
            sResult = SUCCESS
        Catch ex As Exception
            sResult = SERROR & ": " & ex.Message
            If (conn.State = Data.ConnectionState.Open) Then
                conn.Close()
            End If
            'MsgBox(sResult)
        End Try
        conn = Nothing
        Return dt
    End Function

    Public Function executeSQL_ds(ByVal sSql As String, ByRef sResult As String) As Data.DataSet
        Dim sReturn As String = ""
        'Dim sr As SqlDataReader = Nothing
        'Dim dt As DataTable = New DataTable
        Dim dt As DataSet = New DataSet
        Dim da As New MySqlDataAdapter
        conn = New MySqlConnection
        Try
            conn.ConnectionString = connString
            conn.Open()
            Dim sComm As MySqlCommand = New MySqlCommand
            sComm.CommandText = sSql
            sComm.Connection = conn
            da.SelectCommand = sComm
            da.Fill(dt)
            conn.Close()
            sResult = SUCCESS
        Catch ex As Exception
            sResult = SERROR & ": " & ex.Message
            If (conn.State = Data.ConnectionState.Open) Then
                conn.Close()
            End If
            'MsgBox(sResult)
        End Try
        conn = Nothing
        Return dt
    End Function
End Class

Step 4 : Cara menggunakan dengan DataTable

    Public Sub Possize_buy_stocks(ByVal qsymbol, ByVal qlotbuy, ByVal qbuyprice, ByVal qstoploss, ByVal qlaststopls)
        Dim Query As String = ""
        Dim QueryResult As String = ""
        Dim dsData As DataSet
        Dim dtData As DataTable
        Dim mydb As New clsConnMySql

        Dim rfound As Integer = 0
        Try
            Query = "select count(qsymbol) as rfound from tb_holding where qsymbol = '" & qsymbol & "' and qbuydate = '" & Now.ToString("yyyy-MM-dd") & "' and id_user = '" & zUserName & "' "
            dtData = mydb.executeSQL_dt(Query, QueryResult)
            For Each row As DataRow In dtData.Rows
                rfound = row.Item("rfound")
            Next
            If rfound = 0 Then
                Query = "insert into tb_holding ( qsymbol, qlotbuy, qbuyprice, qstoploss, qlaststopls, qbuydate, id_user ) values (" & _
                "'" & qsymbol & "'," & qlotbuy & "," & qbuyprice & "," & qstoploss & "," & qlaststopls & ",'" & Now.ToString("yyyy-MM-dd") & "','" & zUserName & "' ) "
                dsData = mydb.executeSQL_ds(Query, QueryResult)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub 

Step 5 : Cara Menggunakan dengan Dataset

Public Sub Possize_delete_data(ByRef datagridview1 As DataGridView)
        Dim Query As String = ""
        Dim QueryResult As String = ""
        Dim dsData As DataSet
        'Dim dtData As DataTable
        Dim mydb As New clsConnMySql

        Try
            For Each row As DataGridViewRow In datagridview1.SelectedRows
                Query = "delete from tb_possize where qidtr = " & row.Cells(0).Value & " and id_user = '" & zUserName & "' "
                dsData = mydb.executeSQL_ds(Query, QueryResult)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub