prague12

以下是一段mysql .net + Adapter 的使用
資料庫操作功能包含 select , update 的使用
主要是藉由給予一個主要查詢欄位 >> 取出全表內欄位內容 個別欄位內容處理 >> 針對主查詢欄位更新
只有更新原本的欄位內容 所以表內資料不會新增其他欄位內容 （沒有新記錄）


'--------------------------
Imports MySql.Data.MySqlClient
Imports System.Data

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

DB_list.writeDB("a111")


End Sub
End Class

Public Class DB_list

Public Shared rfid As String
Public Shared inside_state As String
Public Shared time1 As Date
Public Shared time2 As Date
Public Shared de_money As String
Public Shared now_money As String

Public Shared Sub writeDB(ByVal rfid_Query As String)

'------------建立連線物件--------------------
Dim pubsSQLConn As New MySqlConnection("server=127.0.0.1;user id=gps;pwd=gps;database=gpsdata;port=3306 ;CharSet=utf8;") 'mysqlport=3306
pubsSQLConn.Open() '使用連線
' MsgBox(pubsSQLConn.State.ToString) '提示連線狀態
'------------/建立連線物件/--------------------





Dim whereCondition = " WHERE parking_record.rfid = " + "'" + rfid_Query + "'"
'----------一些sql 的動作語法-----------查詢 ,寫入--------
' Dim myquery As String = " INSERT INTO aaa.bbb (ab, op) VALUES ('1235', 'qqq65858') "
' Dim myquery As String = " INSERT INTO aaa.bbb (ab) VALUES ('qqqkman') " '對bbb資料表裡的op欄位插入qqqkman值
' Dim myquery As String = "SELECT op FROM bbb LIMIT 0, 30 "
'Dim myquery As String = "select * from parking_record " ' 去查詢有一個資料表 bbb 裡所有資料
Dim myquery As String = "select * from parking_record" + whereCondition ' 去查詢有一個資料表 bbb 裡所有資料
' Dim myquery As String = "select op from bbb " ' 去查詢有一個 bbb資料表 裡 op 欄位內資料
'----------/一些sql 的動作語法-----------查詢 ,寫入/--------




'-----------建立橋接器 ---------------------
Dim adpt As New MySqlDataAdapter(myquery, pubsSQLConn)
Dim myData As New DataTable '建立本地記憶體資料表 內部容器裝載
' myData.Clear() '先清空
adpt.Fill(myData) '將遠端資料喬進本地 DATATABLE 也將本地資料喬進遠端
'----------/建立橋接器/-------------------



'------抓取第一個欄位 裡的內容--------------
Dim mm As DataRow
For Each mm In myData.Rows
DB_list.rfid = mm(0).ToString
DB_list.inside_state = mm(1).ToString
DB_list.time1 = mm(2).ToString
DB_list.time2 = mm(3).ToString
DB_list.de_money = mm(4).ToString
DB_list.now_money = mm(5).ToString


Form1.ComboBox1.Items.Add(DB_list.rfid)
Next
'------/抓取第一個欄位 裡的內容/--------------

'-----------外部網頁容器裝載------------------
' Form1.DataGridView1.DataSource = myData 'GRIDVIEW控制項顯示出來
'-----------/外部網頁容器裝載/------------------



'-----Step2----------------------------------------------------------------------
Select Case DB_list.inside_state.Trim
Case "no"
' MsgBox("no")
DB_list.inside_state = "yes"
DB_list.time1 = Date.Now
DB_list.time2 = Nothing
DB_list.de_money = Nothing

DB_list.RenewTable(myData)

Case "yes"
' MsgBox("yes")
DB_list.time2 = Date.Now

Dim Day1_n = DB_list.time1.Day
Dim Hour1_n = DB_list.time1.Hour
Dim Min1_n = DB_list.time1.Minute
Dim Sec1_n = DB_list.time1.Second

Dim Day2_n = DB_list.time2.Day
Dim Hour2_n = DB_list.time2.Hour
Dim Min2_n = DB_list.time2.Minute
Dim Sec2_n = DB_list.time2.Second

Dim pay As Integer

If Day1_n = Day2_n Then
' pay = (Hour2_n - Hour1_n) '小時計費


'-----------以秒計費---------------
If Min2_n = Min1_n Then
pay = Sec2_n - Sec1_n
ElseIf Min2_n > Min1_n Then
pay = (Min2_n - Min1_n) * 60 + (Sec2_n - Sec1_n)

End If
'------------以秒計費---------------------


End If

' MsgBox(pay_Hour.ToString)

DB_list.de_money = pay.ToString
DB_list.now_money = CStr(CInt(DB_list.now_money) - CInt(DB_list.de_money))
DB_list.inside_state = "no"
DB_list.RenewTable(myData)
Case Else
MsgBox("else ERROR")


End Select



'-----/Step2/-------------------------------------------------------------------------




End Sub

Public Shared Sub RenewTable(ByRef myData As DataTable)
Dim mm As DataRow
For Each mm In myData.Rows
mm(0) = DB_list.rfid
mm(1) = DB_list.inside_state
mm(2) = DB_list.time1
mm(3) = DB_list.time2
mm(4) = DB_list.de_money
mm(5) = DB_list.now_money
Next



Dim q_all = "UPDATE `gpsdata`.`parking_record` SET `inside` ='" + DB_list.inside_state + "',`time1` = '" + DB_list.time1 + "',`time2` = '" + DB_list.time2 + "',`del_money` = '" + DB_list.de_money + "',`now_money` = '" + DB_list.now_money + "' WHERE `parking_record`.`rfid` = 'a111' "

Dim pubsSQLConn As New MySqlConnection("server=127.0.0.1;user id=gps;pwd=gps;database=gpsdata;port=3306 ;CharSet=utf8;") 'mysqlport=3306
Dim adpt As New MySqlDataAdapter(q_all, pubsSQLConn)
adpt.Fill(myData) '將遠端資料喬進本地 DATATABLE 也將本地資料喬進遠端


'-----------外部網頁容器裝載------------------
Form1.DataGridView1.DataSource = myData 'GRIDVIEW控制項顯示出來
'-----------/外部網頁容器裝載/------------------

End Sub
Public Shared Sub Clear_Table(ByRef myData As DataTable)
Dim mm As DataRow
For Each mm In myData.Rows
mm(0) = Nothing
mm(1) = Nothing
mm(2) = Nothing
mm(3) = Nothing
mm(4) = Nothing
mm(5) = Nothing
Next

'-----------外部網頁容器裝載------------------
Form1.DataGridView1.DataSource = myData 'GRIDVIEW控制項顯示出來
'-----------/外部網頁容器裝載/------------------

End Sub



End Class