SQL CE.

Presentation on theme: "SQL CE."— Presentation transcript:

1 SQL CE

2 註冊 RDA REGSVR32

3 IIS 設定 新增虛擬目錄

4 IIS 設定 輸入虛擬目錄名稱

5 IIS 設定 輸入虛擬目錄位置

6 IIS 設定 輸入虛擬目錄權限 (執行一定要勾)

7 IIS 設定 設定完成

8 連線SQL Server

9 連線SQL Server InitDB

10 圖片顯示 顯示圖片 Private Sub Form_Activate() Dim strpath As String
strpath = App.Path If strpath = "\" Then strpath = "" End If PictureBox1.Picture = strpath & "\a1.bmp" End Sub

11 宣告連線變數 所有變數宣告在Module中
Const DBFileSpec = "\My Documents\" ' PDA上SQL資料庫位置所在 Const DBName = “employee.sdf” ‘ PDA上SQL資料庫名稱 ' 連接server上的SQL Server Const strRemoteProvider = "Provider=sqloledb;Initial Catalog=Northwind;" Const SQLEProvider = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=" ' Dim strRemoteSQLServerName As String 'SQL主機名稱 Dim strRemoteSQLServerUID As String 'SQL帳號 Dim strRemoteSQLServerPwd As String 'SQL密碼 Dim strInternetURL As String 'RDA.DLL所在位置 Dim strRemoteConnect As String ‘連接SQL SERVER連線參數 Dim strDBFileName As String ‘pda上的資料庫 Dim cnADO As ADOCE.Connection ‘操作pda上的sdf資料庫的connection

12 設定連線參數值 設定連接參數 Private Sub InitDB() ' 聯結PDA上的SQL資料庫，判斷是否要覆蓋或繼續使用
strRemoteSQLServerName = txtRemoteHost.Text ' SQL Server strRemoteSQLServerUID = txtUserID.Text ' SQL Server帳號 strRemoteSQLServerPwd = txtUserPasswd.Text ' SQL Server密碼 strInternetURL = txtsaURL.Text 'RDA.DLL所在位置 ' 連接資料庫 strRemoteConnect = strRemoteProvider & "Data Source=" & strRemoteSQLServerName & ";UID=" & strRemoteSQLServerUID & ";password=" & strRemoteSQLServerPwd MsgBox “SQL Server資料庫初始成功，可以開始進行上傳或下載資料的動作！“ download_upload.Show End Sub

13 連線SQL Server download_upload COMMAND1 command2

14 從SQL Server下載資料 I Dim ceRDA As SSCE.RemoteDataAccess ‘全域變數
Private Sub Command1_Click() ' 下載資料 Dim CreateDBFlag As Boolean Dim intTemp As Integer ' ‘ 判斷是否要在PDA重新產生SQL檔案 CreateDBFlag = True strDBFileName = DBFileSpec & DBName ' PDA上資料庫所在位置與路徑 If DBExists(strDBFileName) = True Then '檢查PDA上的資料庫是否存在 intTemp = MsgBox("要覆寫資料庫嗎？ " & strDBFileName & " ?", vbYesNoCancel, "資料庫已經存在") If intTemp = vbYes Then '確定覆寫 FileSystem1.Kill strDBFileName ' 刪除目前資料 Else CreateDBFlag = False End If

15 從SQL Server下載資料 II ‘ 接前頁
' If CreateDBFlag = True Then CreateDB '在PDA上建立SQL資料庫 download_from_sql ' 開始下載資料**************** Else Exit Sub End If End Sub

16 CreateDB ' 在PDA上建立SQL資料庫 Private Sub CreateDB()
Dim cat As ADOXCE.Catalog Set cat = CreateObject("ADOXCE.Catalog.3.1") '在PDA上建立SQL資料庫 cat.Create (SQLEProvider & strDBFileName) Set cat = Nothing End Sub

17 download_from_sql - I ' 下載資料************************
Sub download_from_sql() Dim sr As String ShowHourGlass '顯示漏斗 connOpen '開啟PDA上的SQL資料庫 ' If TableExists(cnADO, “employees") = True Then 'cnADO代表開啟的SQL資料庫的employees資料表 cnADO.Execute ("drop table employees") '刪除資料表employees End If

18 download_from_sql - II
' connClose '關閉SQL資料庫 ' '連結遠端SQL SERVER Set ceRDA = CreateObject("SSCE.RemoteDataAccess.2.0") ceRDA.LocalConnectionString = SQLEProvider & strDBFileName ceRDA.InternetURL = strInternetURL ceRDA.InternetLogin = strInternetLogin ceRDA.InternetPassword = strInternetPwd ' strRemoteConnect代表SQL Server資料庫 ‘ Pull代表將資料由SQL Serve取出放入PDA上的SQL資料庫中 ceRDA.Pull “employees", "SELECT * FROM employees", strRemoteConnect, TRACKINGON

19 download_from_sql - III
If ceRDA.ErrorRecords.Count > 0 Then ShowErrors ceRDA.ErrorRecords '顯示錯誤訊息 Else MsgBox "資料下載成功!" End If Set ceRDA = Nothing ShowArrow ‘恢復游標 End Sub

20 connOpen ' 開啟PDA上的SQL資料庫 GPS.SDF檔案 Function connOpen() As Boolean
connOpen = False Set cnADO = CreateObject("ADOCE.Connection.3.1") cnADO.Open (SQLEProvider & strDBFileName) If cnADO.Errors.Count = 0 Then connOpen = True '沒有錯誤代表開啟成功 Else MsgBox "PDA上的SQL資料庫開啟失敗" End If End Function

21 connClose ' 關閉PDA上的SQL資料庫 Sub connClose() On Error Resume Next
cnADO.Close Set cnADO = Nothing On Error GoTo 0 End Sub

22 TableExists Private Function TableExists(paramcnADO As ADOCE.Connection, paramTableName As String) As Boolean Dim catTable As ADOXCE.Table Dim cat As ADOXCE.Catalog TableExists = False Set cat = CreateObject("ADOXCE.Catalog.3.1") cat.ActiveConnection = paramcnADO For Each catTable In cat.Tables If InStr(1, catTable.Name, paramTableName) <> 0 Then TableExists = True Next Set catTable = Nothing Set cat = Nothing End Function

23 ShowHourGlass & ShowArrow
' 改變游標變成漏斗 Public Sub ShowHourGlass() Screen.MousePointer = 11 End Sub ' 回覆游標 Public Sub ShowArrow() Screen.MousePointer = 1

24 Summary 透過RDA連接SQL Server CERDA
Set ceRDA = CreateObject("SSCE.RemoteDataAccess.2.0") ceRDA.LocalConnectionString = SQLEProvider ceRDA.InternetURL = RDA的URL 取值/回傳值 ceRDA.Pull "goods", "SELECT * FROM goods", strRemoteConnect, TRACKINGON ceRDA.Push "goods", strRemoteConnect 連接遠端SQL Server參數 strRemoteProvider ="Provider=sqloledb;Initial Catalog=wealth;" strRemoteConnect = strRemoteProvider & "Data Source=" & strRemoteSQLServerName & ";UID=" & strRemoteSQLServerUID & ";password=" & strRemoteSQLServerPwd 操作PDA本機SQL Server CE ADOCE SQLEProvider ="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=a.sdf" Set cnADO = CreateObject("ADOCE.Connection.3.1") cnADO.Open (SQLEProvider)

25 顯示資料 Dim cnADO As ADOCE.Connection Dim rs As ADOCE.Recordset
connOpen '開啟PDA SQL資料庫 ssql = "SELECT * FROM employees" Set rs = CreateObject("ADOCE.Recordset.3.1") rs.Open ssql, cnADO, adOpenKeyset, adLockPessimistic Combo1.Clear '清空下拉選單 While Not rs.EOF ‘顯示資料 Combo1.AddItem Trim(rs(“employeeid")) Label7.Caption = rs(“FirstName") Label8.Caption = rs(“LastName") Label9.Caption = rs("Tel") rs.MoveNext Wend rs.Close Set rs = Nothing connClose

26 顯示資料 Sub grid() Dim i, j As Integer GridCtrl1.Rows = totalcount + 1
GridCtrl1.Cols = 2 GridCtrl1.Row = 0 GridCtrl1.Col = 0 GridCtrl1.Text = “編號" GridCtrl1.Col = 1 GridCtrl1.Text = "名稱" rs.movefirst For i = 1 To totalcount GridCtrl1.Row = i For j = 0 To 1 GridCtrl1.Col = j Select Case j Case 0: GridCtrl1.Text = rs("goods_code") Case 1: GridCtrl1.Text = rs("goods_name") End Select Next rs.MoveNext End Sub

27 更改資料 Set rs1 = CreateObject("ADOCE.Recordset.3.1")
rs1.Open "update employees set firstname = '" & Text1.Text & "' where employeeid =1", cnADO, adOpenDynamic, adLockOptimistic

28 新增資料－資料庫欄位屬性修正

29 新增資料 Set rs1 = CreateObject("ADOCE.Recordset.3.1")
rs1.Open "insert into goods (goods_name) values ('tt')", cnADO, adOpenDynamic, adLockOptimistic

30 上傳資料 ' 上傳資料到Server Private Sub Command2_Click() ShowHourGlass '顯示漏斗
Set ceRDA = CreateObject("SSCE.RemoteDataAccess.2.0") ceRDA.LocalConnectionString = SQLEProvider & strDBFileName ceRDA.InternetURL = strInternetURL ' 將資料回存到SQL Server中 ceRDA.Push “employees", strRemoteConnect ' employees資料表回存 If ceRDA.ErrorRecords.Count > 0 Then ShowErrors ceRDA.ErrorRecords '顯示錯誤訊息 Else MsgBox "資料上傳成功!" End If Set ceRDA = Nothing ShowArrow '恢復游標 End Sub