Jaga koneksi ke mysql dengan vb.net

Reminder pribadi aja, butuh snip kode buat maintain koneksi aplikasi vb.net ke mysql, muter muter akhirnya nemu solusi disini, tapi di modif dikit sesuai kebutuhan pribadi dan di rapihin karena sumbernya agak bikin bingung dan tidak menggunakan timer, jadi hanya sekali cek saja.

Intinya cukup simpel

• Buat function koneksi standar ke mysql (function Connect)
• Buat prosedur deteksi status koneksi dan rekoneksi bila terputus (sub StateChangedEvent)
• Di layar login akses aplikasi pasang di tombol koneksi/login, koneksi awal ke mysql dan aktivasi timer
• Pasang timer dengan interval tertentu (kalau saya tiap 3 detik) untuk cek koneksi, dan otomatis koneksi ulang bila mysql terputus, tapi bila mysql terus terputus, akan memunculkan layar error terus menerus, bisa tambah variabel counter lagi, supaya tidak terus menerus mencoba koneksi dan menghentikan timer.

1:    Private Function Connect() As Boolean  
2:      Dim ReturnValue As Boolean = False  
3:      Dim connStr As String  
4:      connStr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false; Allow Zero Datetime=true;default Command Timeout=60; ", _  
5:           Me.txtServer.Text, Me.txtUser.Text, Me.txtPassword.Text, Me.txtDatabase.Text)  
6:
7:      conn = New MySqlConnection(connStr)  
8:      
9:      Try  
10:        conn.Open()  
11:        ReturnValue = True  
12:      Catch myerror As MySqlException  
13:        MyErrorString = "Error Connecting to Database: " & myerror.Message  
14:    
15:      End Try  
16:      Return ReturnValue  
17:    End Function  
18:    
19:    Private Sub StateChangedEvent(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs)  
20:      If Not (e.CurrentState = ConnectionState.Open) Then  
21:        If Not Connect() Then  
22:          MessageBox.Show(MyErrorString)  
23:        End If  
24:      End If  
25:    End Sub  
26:    
27:    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick  
28:      If Not Connect() Then  
29:        MessageBox.Show(MyErrorString)  
30:      Else  
31:        AddHandler conn.StateChange, AddressOf StateChangedEvent  
32:      End If  
33:    End Sub  
34:    
35:    Private Sub LoginButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginButton.Click  
36:      If Not conn Is Nothing Then conn.Close()  
37:    
38:      If Not Connect() Then  
39:        MessageBox.Show(MyErrorString)  
40:      Else  
41:        AddHandler conn.StateChange, AddressOf StateChangedEvent  
42:      End If  
43:      Timer1.Enabled = True  
44:              
45:      'kode query database dsb...
46:    End Sub  

formatted by http://codeformatter.blogspot.com/