[VB.NET] โค้ดจับเวลาการแสดงผลข้อมูลการเชื่อมต่อ MySQL Server ด้วยวิธี Bound/UnBound Data

thongkorn · โพสต์ 2019-8-13 14:05:56

แอดมินได้เคยอธิบายรายละเอียดวิธีการที่เรียกว่า Bound Data และ UnBound Data มาหลายรอบแล้ว ตั้งแต่ยุคสมัยของการใช้ VB6 ก็ไม่ขอกล่าวถึงซ้ำอีกน่ะครับ จากภาพเป็นเวลาที่ได้จากการเชื่อมต่อไปยัง DB Server ฟรีจาก remotemysql.com เป็นตารางเดี่ยวๆโดยมีข้อมูลอยู่ 10,000 รายการ แต่ค่าที่ได้ของเวลาก็จะขึ้นอยู่กับองค์ประกอบภายนอกด้วย เช่น สเปคคอมพิวเตอร์ และความเร็วอินเทอร์เน็ต ...

ก่อนจะรันโปรแกรม ต้อง Add Reference MySQL เข้ามาก่อน ... (อยู่ในไฟล์ต้นฉบับ)

มาดูโค้ดในส่วนของการเชื่อมต่อ MySQL Server ...

Imports MySql.Data.MySqlClient
Imports Microsoft.Win32
Public Class frmConnectMySQL
Private strFileINI As String
Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
If Trim(txtServer.Text.Length) = 0 Then
MessageBox.Show("Enter your DNS or IP Address.", "Report status", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtServer.Focus()
Return
ElseIf Trim(txtDataBase.Text.Length) = 0 Then
MessageBox.Show("Enter your DataBase Name.", "Report status", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtDataBase.Focus()
Return
ElseIf Trim(txtUsername.Text.Length) = 0 Then
MessageBox.Show("Enter your Username.", "Report status", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtUsername.Focus()
Return
ElseIf Trim(txtPassword.Text.Length) = 0 Then
MessageBox.Show("Enter your Password.", "Report status", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtPassword.Focus()
Return
End If
'// ConectMySQL in the modDataBase.vb
'// SERVER, DB Name, not keep User ID & Password for MySQL Server in INI File.
Cursor = Cursors.WaitCursor
If ConnectMySQL(Trim(txtServer.Text), Trim(txtDataBase.Text), Trim(txtUsername.Text), Trim(txtPassword.Text)) Then
MsgBox("Connection to MySQL Server successful.")
'// Save them to config.ini
WriteIni(strFileINI, "Config", "SERVER", Trim(txtServer.Text))
WriteIni(strFileINI, "Config", "DB", Trim(txtDataBase.Text))
WriteIni(strFileINI, "Config", "Username", Trim(txtUsername.Text))
WriteIni(strFileINI, "Config", "Password", Trim(txtPassword.Text))
'//
Cursor = Cursors.Default
frmBoundUnBoundData.Show()
Me.Hide()
End If
End Sub
Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub
Private Sub frmConnectMySQL_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
Me.Dispose()
Application.Exit()
End Sub
Private Sub SetupDataBase()
strFileINI = MyPath(Application.StartupPath) & "Config.ini"
'// Check if there is a Config.ini file.
If My.Computer.FileSystem.FileExists(strFileINI) Then
txtServer.Text = ReadIni(strFileINI, "Config", "SERVER", "")
txtDataBase.Text = ReadIni(strFileINI, "Config", "DB", "")
txtUsername.Text = ReadIni(strFileINI, "Config", "Username", "")
txtPassword.Text = ReadIni(strFileINI, "Config", "Password", "")
'// In case of not being found, start the new value.
Else
txtServer.Text = "remotemysql.com"
txtDataBase.Text = ""
txtUsername.Text = ""
txtPassword.Text = ""
End If
End Sub
Private Sub frmConnectMySQL_Load(sender As Object, e As System.EventArgs) Handles Me.Load
Call SetupDataBase()
End Sub
End Class

คัดลอกไปที่คลิปบอร์ด

มาดูโค้ดในส่วนของ modDataBase.vb ...

Imports MySql.Data.MySqlClient
Module modDataBase
'// Declare variable one time but use many times.
Public Conn As MySqlConnection
Public Cmd As MySqlCommand
Public DS As DataSet
Public DR As MySqlDataReader
Public DA As MySqlDataAdapter
Public DT As DataTable
Public strSQL As String '// Major SQL
Public strStmt As String '// Minor SQL
' / --------------------------------------------------------------------------------
'// Connect to MySQL Server
Public Function ConnectMySQL(ByVal DNS As String, ByVal DB As String, ByVal UID As String, PWD As String) As Boolean
'// Use Dynamic DNS from No-IP.com
'// Server=localhost; User ID=YourUserID; Password=YourPassword; DataBase=YourDB
Dim strCon As String = _
" Server=" & DNS & "; " & _
" Database=" & DB & "; " & _
" User ID=" & UID & "; " & _
" Password=" & PWD & "; "
Conn = New MySqlConnection
Conn.ConnectionString = strCon
Try
Conn.Open()
Return True
Catch ex As Exception
MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
'Me.Close()
Return False
End Try
End Function
' / --------------------------------------------------------------------------------
' / Get my project path
' / AppPath = C:\My Project\bin\debug
' / Replace "\bin\debug" with ""
' / Return : C:\My Project\
Function MyPath(AppPath As String) As String
'/ MessageBox.Show(AppPath);
AppPath = AppPath.ToLower()
'/ Return Value
MyPath = AppPath.Replace("\bin\debug", "").Replace("\bin\release", "").Replace("\bin\x86\debug", "")
'// If not found folder then put the \ (BackSlash has ASCII Code = 92) at the end.
If Microsoft.VisualBasic.Right(MyPath, 1) <> Chr(92) Then MyPath = MyPath & Chr(92)
End Function
End Module

คัดลอกไปที่คลิปบอร์ด

มาดูโค้ดในส่วนของการทดสอบด้วยวิธี Bound และ UnBound Data ...

Imports MySql.Data.MySqlClient
Public Class frmBoundUnBoundData
'/ Start-Stop Timer
Private mTimeDouble As Double
Private sWatch As New Stopwatch()
'//
Private CheckMethod As Byte
' / --------------------------------------------------------------------------
'/ BOUND DATA
Private Sub btnBound_Click(sender As System.Object, e As System.EventArgs) Handles btnBound.Click
CheckMethod = 1
Label3.Text = "Bound Data"
sWatch.Reset()
sWatch.Start()
Cursor = Cursors.WaitCursor
GridView1.DataSource = Nothing
'// START
Try
'// Open connection
If Conn.State = ConnectionState.Closed Then Conn.Open()
DA = New MySqlDataAdapter("SELECT * FROM mytable ORDER BY pk", Conn)
DS = New DataSet
DA.Fill(DS, "MyTest")
Me.GridView1.DataSource = DS.Tables("MyTest").DefaultView
Call SetupDataGridView(GridView1)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
DS.Dispose()
DA.Dispose()
'//
Cursor = Cursors.Default
sWatch.Stop()
mTimeDouble = sWatch.ElapsedMilliseconds * 0.001
txtBoundTime.Text = mTimeDouble.ToString
txtRecordCount.Text = String.Format(GridView1.Rows.Count, "N2")
End Sub
' / --------------------------------------------------------------------------
'/ UNBOUND DATA
Private Sub btnUnBound_Click(sender As System.Object, e As System.EventArgs) Handles btnUnBound.Click
CheckMethod = 2
Label3.Text = "UnBound Data"
GridView1.DataSource = Nothing
Call SetupDataGridView(GridView1)
sWatch.Reset()
sWatch.Start()
Cursor = Cursors.WaitCursor
'// START
Dim tbl As New DataTable
Try
tbl = New DataTable
tbl.Columns.Add("PK", GetType(Long))
tbl.Columns.Add("ID", GetType(String))
tbl.Columns.Add("NumberField", GetType(Decimal))
tbl.Columns.Add("DoubleField", GetType(Double))
tbl.Columns.Add("MyDateField", GetType(Date))
'// Open connection
If Conn.State = ConnectionState.Closed Then Conn.Open()
Cmd = New MySqlCommand("SELECT * FROM mytable ORDER BY pk", Conn)
DR = Cmd.ExecuteReader
While DR.Read()
If DR.HasRows Then
tbl.Rows.Add(New Object() { _
Val(DR.Item("PK").ToString), _
DR.Item("ID").ToString, _
DR.Item("NumberField").ToString, _
DR.Item("DoubleField").ToString, _
DR.Item("MyDateField").ToString _
})
End If
End While
DR.Close()
Cmd.Dispose()
GridView1.DataSource = tbl
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
'//
Cursor = Cursors.Default
sWatch.Stop()
mTimeDouble = sWatch.ElapsedMilliseconds * 0.001
txtBoundTime.Text = mTimeDouble.ToString
txtRecordCount.Text = String.Format(GridView1.Rows.Count, "N2")
End Sub
Private Sub SetupDataGridView(ByRef DGV As DataGridView)
With DGV
.Columns.Clear()
.RowTemplate.Height = 22
.AllowUserToOrderColumns = True
.AllowUserToDeleteRows = False
.AllowUserToAddRows = False
.ReadOnly = True
.MultiSelect = False
.SelectionMode = DataGridViewSelectionMode.FullRowSelect
.Font = New Font("Tahoma", 8)
If CheckMethod = 1 Then
.AlternatingRowsDefaultCellStyle.BackColor = Color.Orange
.DefaultCellStyle.SelectionBackColor = Color.LightSkyBlue
Else
.AlternatingRowsDefaultCellStyle.BackColor = Color.LightYellow
.DefaultCellStyle.SelectionBackColor = Color.LightBlue
End If
'/ Auto size column width of each main by sorting the field.
.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
'.AutoResizeColumns()
'/
Dim PK As New DataGridViewTextBoxColumn
With PK
.DataPropertyName = "pk"
.Name = "pk"
.HeaderText = "PK"
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
.Columns.Add(PK)
'/
Dim ID As New DataGridViewTextBoxColumn
With ID
.DataPropertyName = "ID"
.Name = "ID"
.HeaderText = "ID"
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
.Columns.Add(ID)
'/
Dim NumberField As New DataGridViewTextBoxColumn
With NumberField
.DataPropertyName = "NumberField"
.Name = "NumberField1"
.HeaderText = "NumberField"
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
.Columns.Add(NumberField)
'/
Dim DoubleField As New DataGridViewTextBoxColumn
With DoubleField
.DataPropertyName = "DoubleField"
.Name = "DoubleField"
.HeaderText = "DoubleField"
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
.Columns.Add(DoubleField)
'/
Dim MyDateField As New DataGridViewTextBoxColumn
With MyDateField
.DataPropertyName = "MyDateField"
.Name = "MyDateField"
.HeaderText = "MyDateField"
.DefaultCellStyle.Format = "dd/MM/yyyy"
.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
End With
.Columns.Add(MyDateField)
End With
End Sub
Private Sub frmBoundUnBoundData_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
If Conn.State = ConnectionState.Open Then Conn.Close()
Me.Dispose()
Application.Exit()
End Sub
End Class

คัดลอกไปที่คลิปบอร์ด

มาดูโค้ดในส่วนของการจัดการ Initialized File ...

Imports Microsoft.Win32
Module modINI
' / --------------------------------------------------------------------------------
' / Initialized Management
Private Declare Unicode Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringW" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpString As String, _
ByVal lpFileName As String _
) As Int32
Private Declare Unicode Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringW" ( _
ByVal lpApplicationName As String, _
ByVal lpKeyName As String, _
ByVal lpDefault As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Int32, _
ByVal lpFileName As String _
) As Int32
' / --------------------------------------------------------------------------------
' / --------------------------------------------------------------------------------
Public Sub WriteIni(ByVal iniFileName As String, ByVal Section As String, ByVal ParamName As String, ByVal ParamVal As String)
Dim Result As Integer = WritePrivateProfileString(Section, ParamName, ParamVal, iniFileName)
End Sub
Public Function ReadIni(ByVal IniFileName As String, ByVal Section As String, ByVal ParamName As String, ByVal ParamDefault As String) As String
Dim ParamVal As String = Space$(1024)
Dim LenParamVal As Long = GetPrivateProfileString(Section, ParamName, ParamDefault, ParamVal, Len(ParamVal), IniFileName)
ReadIni = Left$(ParamVal, LenParamVal)
End Function
End Module

คัดลอกไปที่คลิปบอร์ด

ดาวน์โหลดโค้ดต้นฉบับแบบเต็ม VB.NET (2010) ได้ที่นี่ ...

		จำสถานะนี้ไว้ในครั้งหน้า	ลืมรหัสผ่าน
รหัสผ่าน			ลงทะเบียน

[VB.NET] โค้ดจับเวลาการแสดงผลข้อมูลการเชื่อมต่อ MySQL Server ด้วยวิธี Bound/UnBound Data

ขออภัย! โพสต์นี้มีไฟล์แนบหรือรูปภาพที่ไม่ได้รับอนุญาตให้คุณเข้าถึง