VB2005序列 資料庫處理指令

Transcription

1 6.1 VB200X 序列資料庫物件關聯圖 引入資料處理類別 建立連線 屬性說明 選項參數說明 相關語法 資料處理 執行 SQL 指令 讀取資料表紀錄 使用 ADODB 類別與方法讀取紀錄 表列紀錄 DataGridView 常用屬性 動態設定 DataGridView 表格及加入資料 取出 DataGridView 表格資料 排序 過濾紀錄 關聯查詢 逐筆處理資料 動態產生記憶體資料表 BLOB 檔案處理 匯出檔案 資料表定義匯出為 SQL 指令 資料表紀錄匯出為 SQL 指令 資料表紀錄匯出為 XML 格式 交易機制處理指令 交易機制處理指令一 交易機制處理指令二 交易機制處理指令三 資料表上鎖 批次方式執行轉檔交易 動態表單 前置作業 程式設計 應用範例 報表設計技巧 前置作業 37 1

2 6.8.2 程式設計 條碼製作技巧 加入條碼控制項 條碼列印 46 回目錄 2

3 本章介紹以 VB 程式語言來開發資料庫應用系統之技巧, 此外也將詳細解說各種資料處理物件之相關屬性和方法 6.1 VB200X 序列資料庫物件關聯圖 ADODB DataReader Windows 控制項 TextBox Database ODBC Driver SQL Command Connection Command DataAdapter DataSet DataTable DataView Windows 控制項 TextBox BindingSource BindingNavigator CommandBuilder DataGridView 圖 6.1 資料庫物件關聯圖上圖說明 MySQL 資料庫使用到的類別及相關物件之關係, 引用物件時, 須按照上圖連線之箭頭方向依序引用 類別 :System.Data.Odbc 物件 : OdbcConnection: 資料庫連接器, 用途為與伺服端資料庫之連線 OdbcCommand:SQL 指令處理器, 將主控端 SQL 指令送往伺服端處理 OdbcDataReader: 前導型資料讀取器, 逐筆讀取開啟之資料表紀錄 OdbcDataAdapter: 資料表橋接器, 透過開啟之連線, 建立資料表在伺服端與主控端之橋接機制, 並將取得的資料表加入記憶體資料庫 OdbcCommandBuilder: 資料表更新處理器, 建立橋接資料表資料更新機制, 諸如新增 修改及刪除等作業 ADODB: 使用 ADO 類別與方法處理紀錄集 通用型資料處理物件 : DataSet: 記憶體資料庫, 用於存放以橋接器連接之資料表 DataTable: 記憶體資料表, 將記憶體資料表轉成可前後移動紀錄指標之資料表, 此資料表可用於新增 修改及刪除等用途 DataView: 視界型資料表, 將 DataTable 轉成視界, 視界型資料表可排 3

4 序及設定過濾條件, 並與 DataGridView 控制項結合, 使得資料瀏 覽更加方便 BindingSource: 資料來源, 將記憶體資料庫轉成可與其它控制項結合之資料 來源 BindingNavigator: 紀錄導覽器, 建立來源資料之紀錄導覽按鈕 ODBC Driver: 作為應用系統在 Windows 平台與 MySQL 間之溝通驅動程式 6.2 引入資料處理類別 在表單程序區前端加入下述指令, 引入資料處理類別後, 才可以在表單程序 內叫用相關物件及方法 Imports System.Data.Odbc Imports System Imports Microsoft.VisualBasic 6.3 建立連線 可在模組區建立公用連線方法, 開啟表單時, 可叫用此方法來開啟連線 本書將其命名為 opencon, 程式內容如下 : Public Sub opencon(byval dbstr As String) Try Dim opvalue As Long Dim str As String opvalue = , 說明 : 通用型選項參數值 str = "DRIVER={MySQL ODBC " & DRV & " Driver};SERVER=" & HST & "; DATABASE=" & dbstr & ";UID=" & USR & ";PASSWORD=" & PSW & ";Option= " & opvalue mycon = New OdbcConnection(str) If Not mycon.state = ConnectionState.Open Then mycon.open() 說明 : 若須處理中文資料, 則須加入下列程式 If mycon.state = ConnectionState.Open Then str = "Set character_set_client = big5;" 說明 :MySQL 指令 mycmd = New OdbcCommand(str, mycon) mycmd.executenonquery() str = "Set character_set_results= big5;" 說明 :MySQL 指令 mycmd = New OdbcCommand(str, mycon) mycmd.executenonquery() str = "Set character_set_connection = big5;" 4

5 說明 :MySQL 指令 mycmd = New OdbcCommand(str, mycon) mycmd.executenonquery() Catch ex As Exception MsgBox(ex.Message) End Try End Sub 屬性說明粗斜體字部份必須由讀者自己輸入, 通常可透過登錄表單來給值, 相關變數用途說明如下 可在完成登錄資料輸入後, 叫用 opencon 來開啟連線 DRV: 驅動程式版本序號 HST: 主機 IP dbstr: 資料庫名稱 USR: 帳號 PSW: 密碼 opvalue: 資料處理方法設定值, 詳細內容請參閱下節說明 選項參數說明有關連線參數 Option 之各種設定值, 代表意義請參看表 6.1 之說明 MySQL 推薦之預設值, 請參照表 6.2 說明 其中數值 3, 對照表 6.1 內並沒有 3 這個參數選項, 其實它是 1+2 之組合值, 代表數值 1 和 2 之選項組合 表 6.1 Option 參數對照表 數值 說 明 1 欄寬不做最佳化處理 2 傳回匹配紀錄值 4 紀錄驅動程式動作在 c:\myodbc.log 檔 8 允許大型資料表 16 連線時不提示 32 允許動態游標 64 忽略 db_name.tbl_name.col_name 格式 128 使用驅動程式管理員游標 256 不允許結果存在近端 512 字元欄位完整呈現 1024 以 SQLDescribeCol() 型式傳回資料表名 5

6 2048 使用壓縮協定 4096 忽略函數括弧前後之空白字元 8192 NT 系統以 named pipes 連線 長整數型轉型為整數型 以帳號取代 Table_qualifier 和 Table_owner 從 my.cnf 讀取 [client] 和 [odbc] 群組設定值 安全檢查 ( 最好不設定, 除非發生問題 ) 不允許交易機制允許查詢記錄在 c:\myodbc.sql ( 只允許在 debug 模 式 ) 查詢結果不做快取 ( 只允許 Forward-only 游標 ) 強制使用 Forward-only 游標 表 6.2 MySQL 推薦參數 系統規劃 Option Access 3 Visual Basic 3 大型資料表包含大量紀錄 2049 追蹤紀錄 ( 除錯模式 ) 4 記錄查詢結果 ( 除錯模式 ) 追蹤紀錄及查詢結果 ( 除錯模式 ) 大型資料表無快取 相關語法 建立連線物件 :New OdbcConnection(str),str 為連線參數 開啟連線 : 連線物件.Open() 處理中文資料 (SQL 指令 ): 上傳資料 :Set character_set_client = big5; 回傳結果 :Set character_set_results= big5; 連線過程信息 :Set character_set_connection = big5; 6.4 資料處理 執行 SQL 指令使用 OdbcCommand 物件來執行 SQL 指令, 此物件可與伺服端紀錄集連線, 但是不取出結果 mycmd = New OdbcCommand(strSQL, mycon) 6

7 mycmd.executenonquery() 相關語法建立處理物件 :New OdbcCommand(strSQL, mycon) 引數一 :SQL 指令, 範例 : Select * From mytable; 引數二 : 連線物件執行 SQL 指令 :SQL 指令物件.ExecuteNonQuery() 讀取資料表紀錄 使用 OdbcDataReader 物件來讀取紀錄 建立 OdbcDataReader 物件 Dim tblreader As OdbcDataReader 建立 SQL 指令物件 strsql = "Select * From mytable;" mycmd = New OdbcCommand(strSQL, mycon) 建立紀錄讀取物件 tblreader = mycmd.executereader() 逐筆讀取紀錄 While tblreader.read For i = 0 To tblreader.fieldcount -1 vl = tblreader.item(i).tostring, 說明 : 取出第 i 欄資料 Nest i End While 關閉物件 tblreader.close() 說明 :OdbcDataReade.Read 方法只能往前讀取紀錄 取出之 vl 可轉存至其它控制項或與記憶體變數進行運算, 例如 : txtname.text = tblreader.item(1).tostring OdbcDataReader 可使用 HasRows 屬性來判斷取得的資料表有無紀錄, 若包含紀錄則傳回 True 使用 ADODB 類別與方法讀取紀錄 ADODB 類別與方法可使用程式碼來處理資料表之新增 修改 刪除及轉檔工作 下述程式示範連線 開啟紀錄集, 以及逐筆處理紀錄 宣告連線物件 7

8 Dim con As New ADODB.Connection 宣告紀錄集物件 Dim rs As New ADODB.Recordset 定義連線屬性 Dim MysqlSTR As String MysqlSTR = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=mysal; USER=UserAccount; PASSWORD=XXXX; OPTION=3" 設定連線屬性 con.connectionstring = MysqlSTR 開啟連線 con.open() 設定 SQL 指令 Dim strsql As String strsql = "Select * from Bkicbl" 開啟紀錄集 rs.open(strsql, con, 2, 4) 逐筆讀取全部紀錄 Do While Not rs.eof vl = rs(0).value, 說明 : 取出欄位資料 rs.movenext() Loop rs.close 說明 : ADODB 類別事前須先引入專案才可使用, 引入步驟如下 : 啟動表單在設計模式 下拉專案功能表, 然後點擊 加入參考 功能, 當開啟 加入參考 對話表單後, 點選 COM 及捲動清單至 MicroSoft Active Data Objects 2.8 Library, 按下 確定, 即可加入此類別 RecordSet 相關方法與屬性使用說明 : Open 方法傳入引數說明引數一 :SQL 指令, 引數二 : 開啟之 ODBC 連線, 引數三 : 紀錄集開啟模式, 代表值如下 :, 0: adopenforwardonly, 8

9 1: adopenkeyset, 2: adopendynamic, 3: adopenstatic 引數四 : 紀錄集鎖定模式, 代表值如下 :, 1: adlockreadonly, 2: adlockpessimistic, 3: adlockoptimistic, 4: adlockbatchoptimistic EOF: 判斷是否已至檔案末端, BOF: 判斷是否已至檔案頂端, Close: 關閉紀錄集, AddNew: 新增一筆空紀錄, Delete: 刪除紀錄, Update: 更新紀錄, 使用時機為修改紀錄或新增紀錄後 ;Delete 方法不須搭配 Update 方法 取值 : 可使用下列方法 : 傳入欄位註標 ( 從 0 開始起算 ):rs(i).value 傳入欄位名稱 : rs( 欄名 ).Value 給值 : 可使用下列方法 rs(i).value = 值 rs( 欄名 ).Value = 值 移動紀錄指標 : 首筆 :rs.movefirst 次筆 :rs.movenext 前筆 :rs.moveprevious 次筆 :rs.movelast 過濾紀錄 :rs.filter = 欄名 = 欄值 [And Or.] 釋放過濾條件 :rs.filter = 表列紀錄 下述範例是以視界形式顯示紀錄, 可搭配 DaGridView 來展示多筆紀錄 Dim ds As New DataSet Dim da As New OdbcDataAdapter(SQL, mycon) Dim daview As DataView Dim tbl As DataTable da.fill(ds, "qrytable") 9

10 說明 : 將連線資料表加入記憶體資料庫, 並將資廖表取明為 qrytable tbl = ds.tables("qrytable") 說明 : 將記憶體資料表轉為 DataTable 物件 daview = tbl.defaultview 說明 : 將 DataTable 物件轉為視界型資料表 DataGridView1.DataSource = daview1 說明 : 將視界型資料表連結至表格物件 語法說明 OdbcDataAdapter( 資料表橋接器 ) 引數說明 : 引數一 :SQL 指令 引數二 : 連線物件 OdbcDataAdapter 須使用 Fill 方法將所取得的伺服端資料表加入記憶體資料庫, 語 法如下 : da.fill(ds, "qrytable") 記憶體資料庫可使用下述指令轉成記憶體資料表 : tbl = ds.tables("qrytable") 記憶體資料表可使用下述指令取得相關資料 : 取得紀錄筆數 : rn = tbl.rows.count 取得欄位個數 : fln = tbl.columns.count 取得欄位名稱 : na = tbl.columns(i).columnname 取得第 i 筆第 j 欄值 :vl = tbl.rows(i).item(j) 若使用 ADODB 之 RecordSet 取得來源資料, 可使用下述指令加入記憶體資料庫 da.fill(ds, myrs, "qrytable"), 說明 :myrs 為 ADODB 之 RecordSet 取出資料錄 下述程式示範如何取出資料表紀錄 Dim drow As DataRow drow = tbl.rows(n) 說明 :n 表第幾筆紀錄, 從 0 起算 Me.BK_BLNO.Text = drow("bk_blno") 說明 : 取值並存入文字方塊,dRow 引數可使用註標變數或使用欄名 vl = drow.item(0).tostring() 說明 : 取出欄值並存入記憶體變數,0 表第一欄 取出視界資料表紀錄 視界資料表可使用下述指令取得相關資料 : 10

11 daview(n).item(j).tostring 說明 : 傳入欄位註標,n 為紀錄序,j 為欄序 daview(n).item( 欄名 ).ToString 說明 : 傳入欄位名稱 取得欄位資料型態 下述程式示範如何取出資料表欄位型態, 以 SQL 指令新增或修改資料時, 必 須依欄位型態來組合 SQL datype = tbl.rows(0).item(i).gettype.tostring 傳回結果 : System.Char/ System.String: 字元型 System.Boolean: 布林型 System.Byte/ System.SByte: 位元組型 System.DateTime: 日期時間 System.Decimal/ System.Double/ System.Single: 實數型 System.Int16/ System.Int32/ System.Int64: 整數型 System.Uint16/ System.Uint32/ System.Uint64: 無號整數型 組合 SQL 時, 日期及字元型欄位必須在取得的控制項資料前後加上引號, 製 作過濾條件時亦同 範例 :strfilter = "BK_BLNO = " & txtno.text & " " DataGridView 常用屬性 下述程式示範 DataGridView 控制項之用法 DataGridView1.DataSource = Nothing, 說明 : 釋放原資料來源 DataGridView1.Refresh() DataGridView1.DataSource = daview, 說明 : 設定連結資料來源 語法說明 ColumnIndex: 取得目前游標位置欄註標 curci = DataGridView1.CurrentCell.ColumnIndex CurrentRow.Index: 取得目前游標位置列註標 curri = DataGridView1.CurrentRow.Index Cells(ci).Value: 取得目前游標位置欄值 VL =DataGridView1.CurrentRow.Cells(ci).Value Name: 取得目前游標位置之欄名 NA = DataGridView1.Columns(ci).Name 11

12 Frozen: 凍結欄位 DataGridView1.Columns(1).Frozen = True DefaultCellStyle.Format: 格式化資料 DataGridView1.Columns(i).DefaultCellStyle.Format = "yyyy/m/d H:mm:ss" HeaderText: 設定欄位抬頭 DataGridView1.Columns(i).HeaderText = 欄位抬頭 ValueType.: 取得欄位型態 ftp = DataGridView1.Columns(i).ValueType.ToString DefaultCellStyle.Alignment: 設定欄位對齊方式 DataGridView1.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight 應用範例 DataGridView 控制項應用範例如下圖 圖 6.2 DataGridView 控制項應用範例 動態設定 DataGridView 表格及加入資料 下述程式示範以動態方式 ( 在執行階段 ) 將資料加入 DataGridView 控制項, 請預先在表單內加入 DataGridView 控制項並取名為 datagridview1 Private Sub CreateDataGridView() datagridview1.columncount = 4, 說明 : 設定欄數 datagridview1.columnheadersvisible = True, 說明 : 設為可見 設定欄位抬頭樣式. Dim columnheaderstyle As New DataGridViewCellStyle() columnheaderstyle.backcolor = Color.Beige columnheaderstyle.font = New Font("Verdana", 10, FontStyle.Bold) datagridview1.columnheadersdefaultcellstyle = columnheaderstyle 設定欄名. datagridview1.columns(0).name = "Recipe 12

13 datagridview1.columns(1).name = "Category" datagridview1.columns(2).name = "Main Ingredients" datagridview1.columns(3).name = "Rating" 加入紀錄. Dim row1() As String = {"Meatloaf", "Main Dish", "ground beef", "**"} Dim row2() As String = _ {"Key Lime Pie", "Dessert", "lime juice, evaporated milk", "****"} Dim row3() As String = {"Orange-Salsa Pork Chops", "Main Dish", _ "pork chops, salsa, orange juice", "****"} Dim row4() As String = {"Black Bean and Rice Salad", "Salad", _ "black beans, brown rice", "****"} Dim rows() As Object = {row1, row2, row3, row4 } Dim rowarray As String() For Each rowarray In rows datagridview1.rows.add(rowarray) Next rowarray End Sub 取出 DataGridView 表格資料以下程式為點擊 DataGridView1 控制項時, 取出當筆紀錄內含鍵值欄資料 Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Click Try Dim fi As Integer If DataGridView1.CurrentRow.Index >= rn Then Exit Sub 說明 :rn, 事先取得之紀錄總數 curci = DataGridView1.CurrentCell.ColumnIndex curri = Me.DataGridView1.CurrentRow.Index If curci = 0 Then For fi = 0 To UBound(tbl.Columns.Count-1) If tbl.columns(fi).name = keyfield1 Then 說明 : 取出第一鍵值 KVL1 = Me.DataGridView1.CurrentRow.Cells(fi).Value If tbl.columns(fi).name = keyfield2 Then 說明 : 取出第二鍵值 ( 若有兩個主鍵時 ) KVL2 = Me.DataGridView1.CurrentRow.Cells(fi).Value 13

14 Next Catch ex As Exception MsgBox(ex.Message) End Try End Sub 此範例程式可應用於全螢幕編修紀錄, 修改紀錄前, 先點擊 DataGridView 鍵值欄控制項來取得鍵值 修改紀錄後, 再搭配下列方法來組合 SQL 指令, 然後執行該指令以修改紀錄 strsql = "Update 資料表名 Set " For fi = 0 To UBound(tbl.Columns.Count-1) strsql= strsql & tbl.columns(fi).name & "= " & DataGridView1.CurrentRow.Cells(fi).Value & "," Next strsql = Mid(strSQL, 1, Len(strSQL)-1) & " Where 過濾條件 ; " mycmd = New OdbcCommand(strSQL, mycon) mycmd.executenonquery() 排序 可搭配視界資料表將紀錄排序 daview1.sort = " 欄名 ASC DESC [, 第二排序欄位 ] " 過濾紀錄 可搭配視界資料表來過濾紀錄 daview.rowfilter = " 欄名 = 欄值 [And Or.] " 關聯查詢 進階應用 - 以關聯方式查詢兩個資料表, 當主資料表紀錄變更時, 可同步顯示子資料表之關聯紀錄 處理方法如下 : 主表以逐筆方式顯示, 當紀錄指標移動時, 同步取出鍵值並置入表單對應之文字方塊內 設定文字方塊資料變更事件程序 (TextChanged 事件 ), 當資料變更時, 將子資料表之 DataView 重設為 daview.rowfilter = " 過濾字串 " 將連結子資料表之 DataGridView.DataSource 重新設為 daview 逐筆處理資料 14

15 搭配紀錄導覽器及控制項 下述程式示範以紀錄導覽器來移動紀錄指標, 須在表單內加入紀錄導覽器控 制項, 並取名為 BindingNavigator1 宣告物件 Dim bindsrc As BindingSource Dim bindadp As OdbcDataAdapter Dim bindtbl As DataTable Dim ds As DataSet Dim builder As OdbcCommandBuilder Dim daview1 As DataView Dim tbl As DataTable, mkey(0) as DataColumn ds = New DataSet("ds") 定義 SQL strsql = 以 Select 前導之 SQL 指令 mkey(0) = 主鍵 建立資料物件 bindadp = New OdbcDataAdapter(strSQL, mycon) 說明 : 建立資料庫橋接器 bindsrc = New BindingSource 說明 : 建立表單連結資料來源物件 bindadp.fill(ds, 給定資料表名 ) 說明 : 資料表填入資料庫橋接器 bindtbl = ds.tables( 給定資料表名 ) 說明 : 建立連結資料表物件 bindsrc.datasource = ds 說明 : 建立資料來源物件 bindsrc.datamember = 給定資料表名 說明 : 設定表單連結資料來源 bindtbl.primarykey = mkey 說明 : 設定連結資料表主鍵 BindingNavigator1.BindingSource = bindsrc 說明 : 設定紀錄導覽器資料來源 tbl = ds.tables( 給定資料表名 ) fln = tbl.columns.count 說明 : 取得連結資料表之欄位數 定義輸入法 Dim ime1, ime2, ime3 As ImeMode 15

16 ime1 = Windows.Forms.ImeMode.Alpha 說明 : 英數 ime2 = Windows.Forms.ImeMode.Off 說明 : 關閉輸入法 ime3 = Windows.Forms.ImeMode.On 說明 : 中文半形 設定控制項屬性並連結資料來源 控制項.ImeMode = ime1 說明 : 設定控制項輸入法 控制項.Name = 連結資料表欄名 說明 : 設定控制項名稱 控制項名.DataBindings.Add(New Binding("Text", bindsrc, 欄名, True) 說明 : 連結控制項來源資料, 若為日期型欄位, 引數 "Text" 須改為 "Value" 紀錄導覽器及控制項應用範例 應用範例如下圖 紀錄導覽器 文字方塊 DataGridView: 關聯查詢 圖 6.3 紀錄導覽器控制項 搭配 DataTable 下述程式示範以 DataTable 物件來增修紀錄 宣告及建立物件 ds = New DataSet("ds") strsql : Select 前導之 SQL 指令 bindadp = New OdbcDataAdapter(strSQL, mycon) 說明 : 建立資料庫連接器 bindadp.fill(ds, 給定資料表名 ) 說明 : 資料表填入資料庫連接器 bindtbl = ds.tables( 給定資料表名 ) 說明 : 建立連結資料表物件 16

17 rn = bindtbl.rows.count 說明 : 取得紀錄筆數 fn = bindtbl.columns.count 逐筆處理紀錄 For i =0 To rn -1 說明 : 逐筆 For j = 0 To fn -1 說明 : 逐欄 vl = bindtbl.rows(i).item(j).tostring 說明 : 取得欄位資料 Next j Next i 新增 修改及刪除紀錄 以 DataTable 物件來增修紀錄時, 須搭配 OdbcCommandBuilder 物件 builder1 = New OdbcCommandBuilder(da1) bindsrc.datasource = dst bindsrc.datamember = wktbl tbl = dst.tables(wktbl) daview = New DataView(tbl) 過濾紀錄 daview.rowfilter = "" 說明 : 釋放過濾條件 daview.rowfilter = keyvl 說明 : 過濾紀錄 刪除紀錄指令 daview.delete 說明 : 刪除紀錄前, 須使用 RowFilter 過濾指定鍵值之紀錄 新增紀錄指令 Dim row As DataRowView = daview.addnew 說明 : 新增紀錄 row.beginedit() 說明 : 開啟編輯功能 For j = 0 To fn -1 row(j) = 資料 Next j 17

18 row.endedit() 說明 : 結束編輯功能 修改紀錄指令 Dim row As DataRowView For Each row In daview row.beginedit() 說明 : 開啟編輯功能 vl = row(fi) 說明 : 取值,fi 表欄位序號 row(fi) = vl 說明 : 給值 row.endedit() 說明 : 結束編輯功能 Next 將修改紀錄存回資料庫 Me.Validate() bindsrc.endedit() bindadp.update(tbl) 設定控制項連結資料來源欄位 設定控制項連結資料來源欄位之語法如下 : 控制項.DataBindings.Add(New Binding("Text", bindsrc, 欄名, True)) 批次更新連結資料表紀錄 開啟表單時, 須先建立紀錄更新物件 OdbcCommandBuilder, 指令如下 : builder = New OdbcCommandBuilder(bindADP) 然後使用指令按鈕來執行下述指令, 也可以將下述指令放在 FormClosed 事件內 Me.Validate() bindsrc.endedit() bindadp.update(bindtbl) 動態產生記憶體資料表 下述程式示範以動態方式產生記憶體資料表 加入資料及顯示 Dim statbl As DataTable = New DataTable("StaTable") Dim daview As DataView 18

19 Dim column As DataColumn Dim row As DataRow 定義欄位屬性 column = New DataColumn() column.datatype = System.Type.GetType("System.String") column.columnname = "Item" 'System.Single 將欄位加入資料表 statbl.columns.add(column) column = New DataColumn() column.datatype = System.Type.GetType("System.Single") column.columnname = "Value" statbl.columns.add(column) 新增紀錄 row = statbl.newrow() row(0) = " 筆數 " row(1) = st.recno 紀錄加入資料表 statbl.rows.add(row) 定義第二筆紀錄 row = statbl.newrow() row(0) = " 合計 " row(1) = st.sum 紀錄加入資料表 statbl.rows.add(row) 連結資料表至 DataGridView daview = statbl.defaultview DataGridView1.DataSource = daview BLOB 檔案處理 上傳檔案 下述程式可將主控端二進制檔案上傳至伺服端, 必須在表單程序前引入 System.IO 類別, 以及在表單程序宣告下列物件變數 Dim bocon As New ADODB.Connection Dim bors As New ADODB.Recordset 19

20 建立檔案物件 Dim stream As FileStream 說明 : 宣告檔案物件 Dim reader As BinaryReader 說明 : 宣告位元資料讀取物件 stream = New FileStream(fna, FileMode.Open, FileAccess.Read) 說明 : fna 表 Client 端 BLOB 含路徑檔名 reader = New BinaryReader(stream) Dim photo() As Byte = reader.readbytes(stream.length) reader.close() stream.close() 開啟連線 opvalue = 3 str = "DRIVER={MySQL ODBC " & DRV & " Driver};SERVER=" & bohost & "; DATABASE=" & dbstr & ";UID=" & USR & ";PASSWORD=" & PSW & ";Option= " & opvalue bocon.connectionstring = str bocon.open() 開啟紀錄集 strsql= "Select * From TempTable" 說明 :TempTable, 內含 BLOB 欄位之範例資料表 bors.cursorlocation = ADODB.CursorLocationEnum.adUseClient bors.open(strsql, bocon, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) 將檔案物件存入伺服端資料表 bors.fields(0).value = cna bors.fields(1).appendchunk(photo) 說明 : 此欄型態必需是 BLOB bors.update() bors.close() 將資料表二進制欄資料轉存至伺服端指名檔案 cmd = "Select ObjectFile Into Dumpfile '" & cna & "' From TempTable" 說明 :cna 表 Server 端 BLOB 含路徑檔名, 路徑符號必須使用 / ObjectFile : BLOB 欄位 bocon.execute(cmd) 下載 BLOB 檔案 20

21 下述程式可將伺服端二進制檔案下載至主控端, 必須在表單程序前引入 System.IO 類別, 以及在表單程序宣告下列物件變數 Dim dncon As New ADODB.Connection Dim dnrs As New ADODB.Recordset 開啟連線 opvalue = 說明 : 下載時必須使用此值 str = "DRIVER={MySQL ODBC " & DRV & " Driver};SERVER=" & HST & "; DATABASE=" & dbstr & ";UID=" & US 遠項 R & ";PASSWORD=" & PSW & ";Option= " & opvalue dncon.connectionstring = str dncon.open() 載入二進制檔至暫存表 cmd = "Update TempTable Set ObjectName='" & fna & "', ObjectFile= Load_File('" & fna & "');" dncon.execute(cmd) 說明 :Load_File() 為 MySQL 函數, 可用於載入伺服端檔案至資料表指名欄位 開啟紀錄集 strsql= "Select * From TempTable Where ObjectName='" & fna & "'" dnrs.open(strsql, dncon, 2, 4) lngimagesize = dnrs("objectfile").actualsize If My.Computer.FileSystem.FileExists(fna) Then My.Computer.FileSystem.DeleteFile(fna) 設定資料處理區塊大小 Dim lngimagesize As Long = 0 Dim lngoffset As Long = 0 Dim bytchunk() As Byte Const conchunksize = 128 建立檔案物件 Dim stream As FileStream Dim writer As BinaryWriter stream = New FileStream(cna, FileMode.OpenOrCreate, FileAccess.Write) writer = New BinaryWriter(stream) 將連線紀錄集資料逐段轉入主控端檔案物件 Do While lngoffset < lngimagesize 21

22 bytchunk = dnrs("objectfile").getchunk(conchunksize) writer.write(bytchunk) writer.flush() lngoffset = lngoffset + conchunksize Loop 關閉物件 writer.close() stream.close() dnrs.close() 註 1: 大型 Text 檔案, 諸如 Word 檔案 PowerPoint 檔都可使用此方法來上傳和下載 註 2: 主控端連線環境變數 Max_allowed_Packet 須設為 , 可在 my.ini 檔指定下列敘述 : Max_allowed_Packet = 匯出檔案 資料表定義匯出為 SQL 指令下述程式用於匯出資料表定義為 SQL 指令宣告及建立物件 Dim dareader As OdbcDataReader Dim cmd As String, fstr() As String, str As String cmd = "Show Create Table 資料表名 ", 說明 :MySQL 指令 mycmd = New OdbcCommand(cmd, mycon) dareader = mycmd.executereader() 讀取 SQL 定義 Do While dareader.read() cmd = dareader.item(1).tostring Exit Do Loop dareader.close() 轉換換行符號 fstr = Split(cmd, vblf) For i = 0 To UBound(fstr) - 1 str = str & fstr(i) & vbcrlf Next 22

23 將讀取結果存檔 My.Computer.FileSystem.WriteAllText( 存檔檔名, str, False, System.Text.Encoding.Default) 資料表紀錄匯出為 SQL 指令 下述程式用於匯出資料表紀錄為 SQL 指令 宣告及建立物件 Dim cmd As String, fstr() As String Dim str As String = "", tstr As String, dstr As String Dim drow As DataRow, i As Integer Dim ds As New DataSet, datable As DataTable Dim vl Dim rn As Long, fln As Integer, key As String = "" Dim da As New OdbcDataAdapter(strSQL, mycon) da.fill(ds, 資料表名 ) datable = ds.tables( 資料表名 ) rn = datable.rows.count fln = datable.columns.count - 1 組合對應 SQL 指令 i = 0 str = "Use 資料庫名 ;" & vbcrlf 說明 : 加入切換 MySQL 資料庫指令 str = str & "DROP TABLE IF EXISTS " & wktbl & ";" & vbcrlf 說明 : 加入 MySQL 刪除資料表指令 str = str & fstr(ubound(fstr)) & ";" & vbcrlf & vbcrlf str = str & "LOCK TABLES " & wktbl & " WRITE;" & vbcrlf 說明 : 加入 MySQL 資料表上鎖指令 str = str & "INSERT INTO " & wktbl & " Values" & vbcrlf 說明 : 加入 MySQL 資料表新增紀錄指令,wkTBL 表資料表名 Dim ri As Long = 0, ftp As String = "" For Each drow In datable.rows dstr = "(" For i = 0 To fln ftp = 取出第 i 欄型態 以下程序用於轉換欄位資料格式 Select Case ftp Case "Numeric" 23

24 dstr = dstr & " " & drow.item(i).tostring Case "Boolean" dstr = dstr & " " & drow.item(i).tostring Case "Date", "DateTime", "Stamp" vl = drow.item(i).tostring If Not IsDBNull(vl) Then vl = Now vl = Replace(vl, " 上午 ", "") vl = Replace(vl, " 下午 ", "") vl = Replace(vl, " ","") dstr = dstr & "'" & vl & "'" Case "Binary", "BLOB" dstr = dstr & "Null" Case "String", "Enum" dstr = dstr & "'" & drow.item(i).tostring & "'" Case "Text" vl = drow.item(i).tostring vl = Replace(vl, vbcrlf, "") vl = Replace(vl, vblf, "") dstr = dstr & "'" & vl & "'" Case Else vl = drow.item(i).tostring dstr = dstr & vl End Select If i < fln Then dstr = dstr &"," Else dstr = dstr &")," Next ri += 1 24

25 以下程序將讀取紀錄加入在 SQL 指令集內 If ri < rn Then str = str & dstr & vbcrlf dstr = "(" Else dstr = Mid(dstr, 1, Len(dstr) - 1) & ";" str = str & dstr & vbcrlf dstr = "" Next str = str & "UNLOCK TABLES;" & vbcrlf 說明 : 加入 MySQL 解鎖指令 da.dispose() ds.clear() 將讀取結果存檔 My.Computer.FileSystem.WriteAllText( 存檔檔名, str, False, & _ System.Text.Encoding.Default) 資料表紀錄匯出為 XML 格式 下述程式用於匯出資料表紀錄為 XML 格式 宣告及建立物件 Dim ds As New DataSet, datable As DataTable da = New OdbcDataAdapter(strSQL, mycon) da.fill(ds, 資料表名 ) datable = ds.tables( 資料表名 L) Dim mode As XmlWriteMode = XmlWriteMode.WriteSchema ds = datable.dataset 匯出紀錄 If fld = False Then '// 不寫入欄位結構 ds.writexml( 存檔檔名 ) Else ds.writexml( 存檔檔名, mode) '// 寫入欄位結構 6.6 交易機制處理指令 交易機制處理指令一 25

26 下列範例會建立 OdbcConnection 和 OdbcTransaction, 它也示範如何使用 BeginTransaction Commit 和 Rollback 方法 Public Sub ExecuteTransaction(ByVal connectionstring As String) Using connection As New OdbcConnection(connectionString) 說明 :Using 宣告之物件, 當程序結束時物件即被釋放, 不須使用 Close 方法,connectionString: 連線參數 Dim command As New OdbcCommand() Dim transaction As OdbcTransaction 說明 : 設定連線參數和物件 command.connection = connection 說明 : 開啟連線並啟動交易機制. Try connection.open() 說明 : 啟動區域型交易機制. transaction = connection.begintransaction() 說明 : 指派交易機制. command.connection = connection command.transaction = transaction 說明 : 執行相關 SQL 指令 (Insert 型或 Update 型 ). command.commandtext = _ "Insert into 資料表 (.) VALUES ( ')" command.executenonquery() command.commandtext = _ "Insert into 資料表 (.) VALUES ( ')" command.executenonquery() transaction.commit() '// 確認交易 MsgBox(" 紀錄已回存至資料庫.") Catch ex As Exception Console.WriteLine(ex.Message) 說明 : 發生錯誤時倒捲交易資料 Try transaction.rollback() Catch End Try End Try End Using End Sub 26

27 6.6.2 交易機制處理指令二 Using updatetransaction As New Transactions.TransactionScope 執行 SQL 指令 1 執行 SQL 指令 2.. updatetransaction.complete(), 說明 : 確認交易 End Using 交易機制處理指令三 使用 MySQL 交易指令來處理交易, 下列指令為 MySQL 指令, 須搭配 OdbcCommand 物件方可執行 START TRANSACTION; FROM table1 WHERE type=1; UPDATE table2 SET summary=@a WHERE type=1; COMMIT; 說明 : 確認 資料表上鎖 執行交易機制前資料表必須先鎖表, 執行 Lock Tables 指令時, 同一執行緒被上鎖之資料表將自動被解鎖 鎖表指令單一資料表 : Lock Table TblName Read Write; 多個資料表 : Locak Tables TblName1 Read Write, TblName2 Read Write...; 若一道 SQL 須開啟某個資料表多次, 則需要替該表另取別名, 否則無法上鎖 範例 : Lock Tables bkiclog Write, bkiclog As tmpbkiclog Write; str = "LOCK TABLE " & wktbl2 & " Write, " & wktbl2 & " AS objtable Write, " & wktbl & " Write;" 說明 :objtable 為別名 mycmd = New OdbcCommand(str, srccon) mycmd.executenonquery() trnsql = "Insert Into " & wktbl2 & "(" & cfld & ") Select " & mfld & " From " & wktbl & " Where (" & mfld & ")" trnsql = trnsql & " Not In (Select " & cfld & " From " & wktbl2 & " As objtable)" 27

28 mycmd = New OdbcCommand(trnSQL, objcon) mycmd.executenonquery() 批次方式執行轉檔交易 執行轉檔交易時, 可使用批次方式以加快執行速度 Step 1 首先定義當鍵值不存在時新增鍵值之 SQL 指令 trnsql = "Insert Into " & wktbl2 & "(" & cfld & ") Select " & mfld & " From " & wktbl & " Where (" & mfld & ")" trnsql = trnsql & " Not In (Select " & cfld & " From " & wktbl2 & " As objtable)" mycmd = New OdbcCommand(trnSQL, objcon) mycmd.executenonquery() 說明 :cfld 表子鍵,mFLD 表主鍵 Step2 接著定義資料小計 SQL 指令 trnsql = "Select " & mfld For vi = skipn To endn mtp = wktype(vi - skipn) If mtp <> " 取代 " Then trnsql = trnsql & ", Sum(" & fld(vi) & ") As " & fld(vi) Else trnsql = trnsql & ", " & fld(vi) Next trnsql = trnsql & " From " & wktbl & " Group By " & mfld 說明 : 轉檔處理可區分為取代 累加和累減 Step3 建立暫存表 - 依小計 SQL 指令 trnsql = "Create Temporary Table updtable " & trnsql mycmd = New OdbcCommand(trnSQL, objcon) mycmd.executenonquery() Step4 定義交易 SQL 指令 trnsql = "Update " & wktbl2 & ", updtable Set " For vi = skipn To endn mtp = wktype(vi - skipn) If mtp = " 累加 " Then trnsql = trnsql & wktbl2 & "." & Trim(fld(vi)) & "=" & wktbl2 & "." & Trim(fld(vi)) & " + updtable." & Trim(tblStruct(vi).fldName) 28

29 ElseIf mtp = " 累減 " Then trnsql = trnsql & wktbl2 & "." & Trim(fld(vi)) & "=" & wktbl2 & "." & Trim(fld(vi)) & " - updtable." & Trim(tblStruct(vi).fldName) ElseIf mtp = " 取代 " Then trnsql = trnsql & wktbl2 & "." & Trim(fld(vi)) & "= updtable." & Trim(tblStruct(vi).fldName) If vi < endn Then trnsql = trnsql & ", " Next strjoin = wktbl2 & "." & Trim(CKfld1) & "=" & "updtable." & Trim(Kfld1) If Kfld2 <> "" Then strjoin = strjoin & " And " & wktbl2 & "." & Trim(CKfld2) & " = updtable." & Trim(Kfld2) trnsql = trnsql & " Where " & strjoin 說明 : 不能使用 Inner Join 關聯指令, 而必須改用 Where 形式來建立關聯 Step5 執行轉檔作業 mycmd = New OdbcCommand(trnSQL, objcon) mycmd.executenonquery() Step6 刪除暫存表 trnsql = "Drop Temporary Table updtable" mycmd = New OdbcCommand(trnSQL, objcon) mycmd.executenonquery() 6.7 動態表單本節示範動態表單之設計技巧 所謂動態表單, 是指表單介面在設計階段未將處理資料所需之控制項加入, 而在啟動表單時, 才依傳入之資料表或 SQL 指令集內含之欄位加入合適的控制項, 以及設定相關的屬性資料 事件程序和連結資料來源 使用動態表單之優點, 主要在於只要一個資料輸入介面即可滿足眾多的資料表資料輸入作業需求, 除了可縮減軟體所需空間, 也可降低系統維護之困難度 前置作業 29

30 新增刪除前筆首筆址器紀錄定次筆末筆鍵值EasydoMySQL 上層表單動態表單之產生乃依據傳入之來源資料表或 SQL 指令, 因此需要一個能設定來源資料之上層表單, 以及一個能開啟動態表單之指令按鈕 本書使用下列公用變數來傳遞相關來源資料 wktbl: 主資料表 wksql: 主 SQL( 與 wktbl 為二選一 ) wktbl2: 子資料表 ( 若需要以關聯方式輸入資料時 ) wksql2: 子 SQL( 與 wktbl2 為二選一 ) 動態表單為了示範資料表之新增 修改 刪除及查詢功能, 請在動態表單上加入相關的控制項, 如下列圖示 : 紀錄導覽器 (BindingNavigator) 下拉方塊首值紀錄導覽器 下拉方塊搜值紀錄導覽器尋 指令按鈕 - 主資料表 圖 6.4 紀錄導覽器用圖說明 圖 6.5 主表指令按鈕各按鈕之用途說明如下 : 更新 : 將新增或編修紀錄存入資料表取值 : 將表單上之資料存入記憶體清空 : 將輸入方塊資料清除填入 : 將記憶體資料置入對應輸入方塊預設值 : 將預設值置入對應輸入方塊查閱 : 查閱其它資料表紀錄傳送 : 將表單資料以郵件傳送 表格控制項 (DataGridView) 用途 : 處理子資料表 指令按鈕 - 子資料表 30

31 圖 6.6 子表指令按鈕 各按鈕之用途說明如下 : 新增 : 新增紀錄編輯 : 修改紀錄刪除 : 刪除紀錄顯示子表 : 開啟子表以單筆形式查閱紀錄 文字方塊 ( 取名為 KeyText) 用途 : 當紀錄改變時, 將主鍵值填入此控制項, 再透過 TextChanged 事件來過濾子表 程式設計 變數宣告 在表單程序區宣告下列公用變數 : ds:dataset bindsrc:bindingsource bindadp:odbcdataadapter bindtbl:datatable bindadp2:odbcdataadapter bindtbl2:datatable daview2:dataview builder:odbccommandbuilder builder2:odbccommandbuilder DataView lbl(): 標籤控制項物件, 用於提示欄位抬頭 obj(): 標籤控制項物件, 用於設定欄位資料控制項 vl(): 控制項資料暫存變數 fln: 主表欄數 fln2: 子表欄數 tblstruct(): 主表欄位結構 tblstruct2(): 子表欄位結構 keyfield1: 主表主鍵 keyfield2: 主表第二主鍵 ckeyfield1: 子表主鍵 ckeyfield2: 子表第二主鍵 Dim ime1, ime2, ime3 As ImeMode 31

32 ime1 = Windows.Forms.ImeMode.Alpha ' 英數 ime2 = Windows.Forms.ImeMode.Off ime3 = Windows.Forms.ImeMode.On ' 中文半形 加入控制項 在表單之 Load 事件程序內加入下列敘述 : 開啟資料來源 ds = New DataSet("ds") bindadp = New OdbcDataAdapter(strSQL, mycon) bindadp.fill(ds, wktbl) bindtbl = ds.tables(wktbl) bindsrc.datasource = ds bindsrc.datamember = wktbl bindtbl.primarykey = 主表主鍵 tbl = ds.tables(wktbl) BindingNavigator1.BindingSource = bindsrc 設定資料表欄位結構及介面資料在此段加入資料表欄位結構及介面資料設定程式碼 ReDim tblstruct(fln) If wksql = "" Then Call gettablestructure(wktbl, 1) 說明 : 請自行設計副程式 Else Call getsqlstructure(wksql, 1) 計算資料表欄數 fln = tbl.columns.count - 1 ReDim lbl(fln) 說明 : 重新宣告相關變數陣列大小 ReDim obj(fln) ReDim vl(fln) 填入主表鍵值 - 搜尋鍵值下拉方塊 If keyfield1 <> "" Then keyvalue = getkeyvalue(wktbl, keyfield1) kvl = Split(keyValue, ",") 32

33 keycbo.items.clear() 說明 : 搜尋鍵值下拉方塊 keycbo.items.add("") 說明 : 空字串可用於取消 Filter For i = 0 To UBound(kvl) keycbo.items.add(kvl(i)) Next 加入控制項 - 依主表欄數 stx1 = 1, 說明 : 標籤 X 座標 sty1 = 60, 說明 : 標籤 Y 座標 stx2 = 95, 說明 : 資料控制項 X 座標 sty2 = 60, 說明 : 資料控制項 Y 座標 For i = 0 To fln 加入欄位抬頭 (Label) 控制項 lbl(i).text = tblstruct(i).fldtitle lbl(i).font = 自訂字型 loc.x = stx1 loc.y = sty1 lbl(i).location = loc sz.height = 18 sz.width = 94 lbl(i).size = sz Me.Controls.Add(lbl(i)) 加入資料輸入控制項 obj(i) = New TextBox 或是 ComboBox loc.x = stx2 loc.y = sty2 obj(i).location = loc obj(i).font = 自訂字型 sz.height = font.getheight + 5 obj(i).size = sz obj(i).name = tbl.columns(i).columnname Me.Controls.Add(obj(i)) sty2 = sty2 + sety 說明 : 遞增 Y 座標 sty1 = sty2, 33

34 設定滑鼠事件 AddHandler CType(obj(i), TextBox).MouseDown, AddressOf On_MouseDown 說明 :On_MouseDown 事件程序必須自行定義, 它將用來回應用戶按下滑鼠 設定輸入法 時, 需要處理之作業 If tblstruct(i).fldimemode = "Aa" Or tblstruct(i).fldimemode = "" Then obj(i).imemode = ime1 ElseIf tblstruct(i).fldimemode = "Of" Then obj(i).imemode = ime2 ElseIf tblstruct(i).fldimemode = "On" Then obj(i).imemode = ime3 Next 設定 KeyText 連結資料來源 此控制項用於存取鍵值 KeyText.DataBindings.Clear() KeyText.DataBindings.Add(New Binding("Text", bindsrc, keyfield1, True)) 開啟子表資料來源 Dim ds2 As New DataSet If wktbl2 <> "" Or wksql2 <> "" Then If wksql2 = "" Then strsql2 = "Select * From " & wktbl2 & " Order By " & ckeyfield1 Else strsql2 = wksql2 bindadp2 = New OdbcDataAdapter(strSQL2, mycon) bindadp2.fill(ds2, wktbl2) bindtbl2 = ds2.tables(wktbl2) daview2 = New DataView(bindTBL2) fln2 = bindtbl2.columns.count 1 說明 : 以下程式用於設定子表關聯條件 If tblstruct2(0).fldtype = "Numeric" Then daview2.rowfilter = ckeyfield1 & "=" & KeyText.Text Else daview2.rowfilter = ckeyfield1 & "='" & KeyText.Text & "'" 34

35 DataGridView1.DataSource = daview2 設定資料表欄位結構及介面資料 在此段加入子表資料表欄位結構及介面資料設定程式碼 ReDim tblstruct2(fln2) If wksql = "" Then Call gettablestructure(wktbl2, 2), 說明 : 請自行設計副程式 Else Call getsqlstructure(wksql2, 2) 定義 On_MouseDown 事件程序 Private Sub On_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Dim na As String ' 取得傳入事件之控制項名稱 na = sender.name 在此處加入自訂敘述說明 : 加入自訂敘述, 可依 na 個別設定對應程序, 當處理人員在控制項上按下滑鼠時, 就會啟動此段程序 End Sub 定義 KeyCBO_TextChanged 事件程序 用途 : 下拉選取鍵值, 當鍵值改變時, 觸發主表過濾機制 Private Sub keycbo_selectedindexchanged(byval sender As System.Object, ByVal e As System.EventArgs) Handles keycbo.selectedindexchanged Try bindsrc.filter = Nothing If keycbo.text = "" Then Exit Sub 說明 : 以下程式用於設定主表過濾條件 If tblstruct(0).fldtype = "String" Or tblstruct(0).fldtype = "Stamp" Then bindsrc.filter = keyfield1 & "='" & keycbo.text & "'" Else If tblstruct(0).fldtype = "Date" Then bindsrc.filter = keyfield1 & "='" & keycbo.text & "'" Else bindsrc.filter = keyfield1 & "=" & keycbo.text 35

36 Catch ex As Exception MsgBox(ex.Message) End Try End Sub 定義 KeyText_TextChanged 事件程序 Private Sub KeyText_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles KeyText.TextChanged Dim i As Integer Dim vl 說明 : 以下程式用於設定子表關聯條件 If wktbl2 <> "" Or wksql2 <> "" Then If tblstruct2(0).fldtype = "Numeric" Then daview2.rowfilter = ckeyfield1 & "=" & KeyText.Text Else daview2.rowfilter = ckeyfield1 & "='" & KeyText.Text & "'" End Sub 定義主表資料更新事件程序 事件程序應包含下列敘述 ( 子表更新程序與主表類似 ): 擷取控制項資料並存放於資料表結構體變數內語法 : For i = 0 To UBound(tblStruct) vl = obj(i).text vl = 依欄位型態將 vl 轉型 tblstruct(i).fldvalue = vl Next 修改資料時 upok = UpdateNewRecord(1, kv1, kv2) If upok = True Then lblstatus.text = " 更新成功..." Else lblstatus.text = " 更新失敗..." 36

37 新增資料時 upok = InsertNewRecord(1, kv1, kv2) If upok = True Then lblstatus.text = " 新增成功..." BindingNavigatorAddNewItem.Visible = True Else lblstatus.text = " 新增失敗..." 定義主表資料刪除事件程序 事件程序應包含下列敘述 ( 子表刪除程序與主表類似 ): Dim srtkey As String srtkey = 組合過濾鍵值 If newdrow = False Then If DeleteKeyValue(wkTBL, srtkey) = True Then lblstatus.text = " 紀錄已刪除.." 應用範例 下圖示範以動態表單新增和查詢關聯資料表記錄 圖 6.7 動態表單範例 6.8 報表設計技巧資料輸出介面除了使用表單形式並提示在螢幕外, 也經常使用報表形式輸出至印表機 本節介紹以印表機列印報表之技巧 前置作業 37

38 在設計報表前, 須準備下列物件 設定來源資料及設定報表格式 功能表單 : 開啟一個一般形式之表單, 在表單上加入下拉功能表或指令按鈕, 可以用來執行版面設定 預覽列印及列印功能 此外, 在表單上加入下列控制項 : PageSetupDialog: 版面設定控制項, PrintPreviewDialog: 預覽列印控制項, PrintDialog: 列印控制項, PrintDocument: 文件製作控制項 來源資料 : 可以是資料表或 SQL 指令 報表格式 : 設定如下圖所示之報表格式, 下圖中之控制項須分別設定其座標位置 特別提醒讀者, 報表之座標是以 1/100 英吋為單位 報表表頭 列印日期 欄位抬頭逐筆分開列印之資料錄設定是否加印分隔線設定是否須按鍵值列印小計值... 按分頁方式分頁 頁碼 圖 6.8 報表格式說明事前須決定下列屬性 : 列印字型, 列與列之間之行距, 行距大小取決於字型點數, 列與列之間是否加印分隔線, 是否須按鍵值小計, 分頁方式 : 依鍵值分頁或依可列印行數自動分頁 程式設計 設定資料來源及報表格式 請在功能表表單 Load 事件程序內加入下列敘述 : 開啟資料來源 38

39 取出預先設定之報表格式 啟動版面設定對話方塊 請在對應按鈕或下拉功能表之 Click 事件內加入列程式 : PageSetupDialog1.Document = PrintDocument1 說明 : 將列印版面指向預設之 PrintDocument 物件 PageSetupDialog1.PageSettings = New System.Drawing.Printing.PageSettings PageSetupDialog1.PrinterSettings = New System.Drawing.Printing.PrinterSettings PageSetupDialog1.ShowNetwork = False PageSetupDialog1.PageSettings.Margins.Left = mrgleft 說明 : 設為預設之左邊 PageSetupDialog1.PageSettings.Margins.Right = mrgright 說明 : 設為預設之右邊界 PageSetupDialog1.PageSettings.Margins.Top = mrgtop 說明 : 設為預設之上邊界 PageSetupDialog1.PageSettings.Margins.Bottom = mrgbottom 說明 : 設為預設之下邊界 Dim result As DialogResult result = PageSetupDialog1.ShowDialog() 說明 : 開啟對話方塊 If result = Windows.Forms.DialogResult.OK Then mrgleft = PageSetupDialog1.PageSettings.Margins.Left mrgright = PageSetupDialog1.PageSettings.Margins.Right mrgtop = PageSetupDialog1.PageSettings.Margins.Top mrgbottom = PageSetupDialog1.PageSettings.Margins.Bottom PrintDocument1.DefaultPageSettings = PageSetupDialog1.PageSettings 啟動預覽列印對話方塊 請在對應按鈕或下拉功能表之 Click 事件內加入列程式 : PrintPreviewDialog1.Document = Me.PrintDocument1 說明 : 將列印版面指向預設之 PrintDocument 物件 PrintPreviewDialog1.ShowDialog() 啟動列印對話方塊 請在對應按鈕或下拉功能表之 Click 事件內加入列程式 : PrintDialog1.Document = Me.PrintDocument1 說明 : 將列印版面指向預設之 PrintDocument 物件 39

40 If PrintDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then PrintDocument1.Print() 設計 PrintDocument1_PrintPage 事件程序請在此事件內加入下列程式碼, 當設定 PrintPreviewDialog1.Document 或 PrintDialog1.Document 時, 此事件程序就會被啟動來製作報表文件 設定工作變數 Dim x As Single, y As Single, txtw As Single Dim txt Dim drow As DataRow Dim fnd As Integer, str As String, i As Integer Dim gi As Integer, grpstring As String Dim prnx As Single, prnu As Single, maxpos As Single = recwidth Dim pen As New Pen(Color.Black) 說明 : 建立畫筆 pghight = e.marginbounds.height 說明 :e 代表列印事件, 讀取列印版面高度 pgwidth = e.marginbounds.width 說明 : 取出列印版面寬度 Dim docfont As Font docfont = New Font(titleFontName, titlefontsize) 說明 : 設定字型 Dim docsize As New Size(e.MarginBounds.Width, e.marginbounds.height - docfont.getheight(e.graphics)) 說明 : 設定列印文件方塊大小 Dim linera As Single = 1 + spcline 說明 : 設定每列文件佔用高度,spcLine 表行距 Dim docformat As New StringFormat docformat.trimming = StringTrimming.Word 說明 : 設定文字輸出格式, 中文字不截斷 If recwidth < pgwidth Then prnu = recwidth / linelength Else prnu = pgwidth / linelength x = e.marginbounds.left 說明 : 設定起始左邊界 40

41 y = e.marginbounds.top 說明 : 設定起始出上邊界 列印報表抬頭 Dim rect As New Rectangle(x, y, pgwidth, docfont.getheight(e.graphics)) e.graphics.drawstring(rpttitle, docfont, Brushes.Black, rect, docformat) y = y + docfont.getheight(e.graphics) * linera 列印報表表頭 Call PrintHead(x, y, sender, e) 說明 : 請自行設計所需表頭 y = y + docfont.getheight(e.graphics) * linera 列印欄位抬頭 Call PrintColHead(x, y, sender, e) 說明 : 請自行設計所需欄位抬頭 y = y + docfont.getheight(e.graphics) * 2.2 列印報表本文 - 輸出所需紀錄 Do While True, 說明 : 依序輸出來源資料之每筆紀錄 列印紀錄指標所在紀錄 For i = 0 To fln txt = Trim(dRow.Item(i).ToString) txtw = StrWidth(txt, datafontname, datafontsize) + 5 說明 :StrWidth 為自訂函數, 用來量測文件寬度 If sumfield(i) = True Then, 說明 : 需要小計時 sumvalue(i) = sumvalue(i) + Val(txt) ttlvalue(i) = ttlvalue(i) + Val(txt) If sumreport = False Then 非摘要式報表 If tblstruct(i).fldtype = "Numeric" Then If i < fln Then prnx = x + (colloc(i + 1) - 2) * prnu - txtw Else prnx = x + linelength * prnu - txtw rect = New Rectangle(prnX, y, txtw, docfont.getheight(e.graphics)) e.graphics.drawstring(txt, docfont, Brushes.Black, rect, docformat) Else 41

42 If tblstruct(i).fldtype = "Date" Or tblstruct(i).fldtype = "Stamp" Then txt = Trim(dRow.Item(i).ToString) If dafmt = "Short" Then txt = Format(txt, "Short Date") ElseIf tblstruct(i).fldtype <> "Binary" And tblstruct(i).fldtype <> "BLOB" Then txt = Trim(dRow.Item(i).ToString) Else txt = "Byte_data" 說明 : 預設 BLOB 欄不印, 並以 Byte_data 提示 txtw = StrWidth(txt, datafontname, datafontsize) prnx = x + colloc(i) * prnu 說明 :colloc(i) 表預設欄位之 X 座標 rect = New Rectangle(prnX, y, txtw, docfont.getheight(e.graphics)) e.graphics.drawstring(txt, docfont, Brushes.Black, rect, docformat) Next 列印分隔線 If grdline = True Then, 說明 : 需要分隔線 y = y + docfont.getheight(e.graphics) * linera e.graphics.drawline(pen, gx(0), y, gx(fln + 1), y) Else y = y + docfont.getheight(e.graphics) * linera 移動紀錄指標加入判斷次筆紀錄鍵值是否改變或已至檔尾 CurrentREC = CurrentREC + 1 drow = rpttable.rows(currentrec) 列印小計 If sumfld = True Then, 說明 : 需要小計 txt = " 小計 " txtw = StrWidth(txt, datafontname, datafontsize) + 5 prnx = x + (colloc(1) - 2) * prnu - txtw rect = New Rectangle(prnX, y, txtw, docfont.getheight(e.graphics)) e.graphics.drawstring(txt, docfont, Brushes.Black, rect, docformat) y = y + docfont.getheight(e.graphics) * linera For i = 1 To fln 42

43 If sumfield(i) = True Then txt = Trim(CStr(sumValue(i))) txtw = StrWidth(txt, datafontname, datafontsize) + 5 If i < fln Then prnx = x + (colloc(i + 1) - 2) * prnu - txtw Else prnx = x + linelength * prnu - txtw rect = New Rectangle(prnX, y, txtw, docfont.getheight(e.graphics)) e.graphics.drawstring(txt, docfont, Brushes.Black, rect, docformat) Next 檔案尾端處理 Call PrintTail(x, y, sender, e) 說明 : 請自行加入報表表尾程式 e.hasmorepages = False 說明 : 已無資料 Exit Do 說明 : 跳出迴圈 文件區已滿且尚有資料錄 If y >= (e.marginbounds.bottom - docfont.getheight(e.graphics) * 1.5) Then Call PrintTail(x, y, sender, e) 說明 : 請自行加入報表表尾程式 curpage = curpage + 1 說明 : 頁碼加 1 e.hasmorepages = True 說明 : 仍有資料 Exit Do Loop 特殊指令說明 列印資料 :e.graphics.drawstring 畫線 :e.graphics.drawline 列印影像 :e.graphics.drawimage 文件區已無資料 :e.hasmorepages = False 43

44 文件區尚有資料 :e.hasmorepages = True 中文字不截斷 :docformat.trimming = StringTrimming.Word 6.9 條碼製作技巧條碼常搭配表單或報表一起使用, 可讓取得來源資料人員快速擷取內含鍵值, 從而取得原始內容 製作條碼時, 必須先引用條碼控制項, 引用方法如下 : 開啟工具箱, 在工具箱上按下滑鼠右鍵以開啟快顯功能表, 點取 選擇項目, 開啟 工具箱選擇項目 對話控制項, 點取 COM 元件, 捲動選項清單至 MicroSoft 條碼控制項 9.0, 如圖 6.9, 點取 確定, 將條碼控制項加入工具箱 圖 6.9 引入條碼控制項 加入條碼控制項條碼必須搭配表單方能正常顯示, 因此需要在表單內加入條碼控制項, 接下來可依所需條碼規格設定條碼屬性, 工作畫面如圖 6.10 圖 6.10 條碼屬性對話控制項 44

45 條碼屬性說明如下 : 樣式 -Style: 可選用下列樣式 : 0 - UPC-A 1 - UPC-B 2 - JAN JAN Case Code 5 - NW7 6 - Code Code U.S. Post net 9 - U.S. Postal FIM 10 - JP Post 副樣式 -SubStyle: 選擇樣式後, 若有副樣式屬性, 可依規格來設定, 選項內容如下 : 0-UPC-A 1-UPC-A 2-JAN-13 及 3-JAN-8 有下列副樣式 : 0- 標準 1-2 位補充碼 2-5 位補充碼 3-POS Case Code 4-Case Code 有下列副樣式 : 0- 標準版 1- 擴充版 2- 附加版 8-U.S. Post net 有下列副樣式 : 0- 傳遞點 1-ZIP Code Digit ZIP Code 9-U.S. Post FIM 有下列副樣式 : 0-FIM-A Symbol 1-FIM-B Symbol 2-FIM-C Symbol 線條粗細 -LineWeight: 可設定 1-5 之線條樣式 列印方向 -Direction: 可設為下列形式 : 0-0 度 1-90 度 度 45

46 3-270 度 標籤寬度 -Width: 以 Pixel 為單位, 可自行調整控制項 標籤高度 -Height: 以 Pixel 為單位, 可自行調整控制項 可以使用程式來設定條碼屬性, 語法如下 : barcd.width = W barcd.height = H barcd.style =N barcd.substyle =M barcd.lineweight = L barcd.direction = D barcd.value = 來源資料欄位其中 barcd 為自定條碼控制項名稱 條碼列印 因為條碼控制項無法直接列印在報表上, 若要在報表上列印條碼, 必須先將條碼顯示在表單上, 然後將表單條碼複製在記憶體, 最後再將記憶體影像貼在列印物件上 複製影像時, 必須在表單公用程序區引入 BitBlt 函數, 語法如下 : <DllImport("gdi32.dll", CharSet:=CharSet.Auto, SetLastError:=True, ExactSpelling:=True)> Public Shared Function BitBlt(ByVal hdc As IntPtr, ByVal x As Integer, ByVal y As Integer, ByVal nwidth As Integer, ByVal nheight As Integer, ByVal hsrcdc As IntPtr, ByVal xsrc As Integer, ByVal ysrc As Integer, ByVal dwrop As Integer) As Boolean End Function 複製條碼 處理程式如下 : Private Sub SaveBarBMP(ByVal i As Integer) Try Dim bargd As Graphics Dim mmogd As Graphics Dim dc1 As IntPtr, dc2 As IntPtr bargd = barcd.creategraphics() 說明 : 建立條碼圖像物件 barbmp = New Bitmap(baStru(i).Width, bastru(i).height, bargd) 說明 : 將條碼圖像物件轉成 BitMap 圖像 mmogd = Graphics.FromImage(barBMP) 46

47 說明 : 將 BitMap 圖像轉存記憶體 dc1 = bargd.gethdc() 說明 : 取得條碼圖像物件參考指標 dc2 = mmogd.gethdc() 說明 : 取得記憶體圖像物件參考指標 BitBlt(dc2, 0, 0, bastru(i).width, bastru(i).height, dc1, 0, 0, ) 說明 : 複製圖像 bargd.releasehdc(dc1) 說明 : 釋放記憶體 mmogd.releasehdc(dc2) 說明 : 釋放記憶體 Catch ex As Exception End Try End Sub 列印條碼在 PrintDocument.PrintPage 事件區列印條碼, 語法如下 : e.graphics.drawimage(barbmp, x, y) 47

48 課後習題 1. 圖示並說明 VB 處理資料庫使用到之物件 2. 說明使用 VB 資料庫物件之前置處理成序 3. 詳述連線指令之主要參數 4. 任舉五個常用連線 Option 參數及用途 5. 在應用程式欲鄭常處理中文字元, 請問應如何處理? 6. OdbcCommand 指令有那三種與法? 7. 如何使用 ADODB 物件開啟紀錄集? 8. ADODB 開啟之紀錄集分成那些類型? 9. 要以表格形式列示多筆紀錄須使用那個物件? 詳述其使用步驟 10. 如何將一個二進制檔載入 MySQL 資料表? 11. 何謂交易處理? 12. 設計報表須使用那些物件與事件? 13. 如何在表單上列示條碼? 14. 微軟之條碼控制項可列示那些條碼類型? 回目錄 48