DAlessonSQLCE連線ppt課件

《DAlessonSQLCE連線ppt課件》由會員上傳分享，免費在線閱讀，更多相關內(nèi)容在行業(yè)資料-天天文庫。

SQLCE

1註冊RDAREGSVR32

2IIS設定新增虛擬目錄

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

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

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

6IIS設定設定完成

7連線SQLServer

8連線SQLServerInitDB

9圖片顯示顯示圖片PrivateSubForm_Activate()DimstrpathAsStringstrpath=App.PathIfstrpath="\"Thenstrpath=""EndIfPictureBox1.Picture=strpath&"\a1.bmp"EndSub

10宣告連線變數(shù)所有變數(shù)宣告在Module中ConstDBFileSpec="\MyDocuments\"'PDA上SQL資料庫位置所在ConstDBName=“employee.sdf”‘PDA上SQL資料庫名稱'連接server上的SQLServerConststrRemoteProvider="Provider=sqloledb;InitialCatalog=Northwind;"ConstSQLEProvider="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;datasource="'------------------------------------------------------------------------------------DimstrRemoteSQLServerNameAsString'SQL主機名稱DimstrRemoteSQLServerUIDAsString'SQL帳號DimstrRemoteSQLServerPwdAsString'SQL密碼DimstrInternetURLAsString'RDA.DLL所在位置DimstrRemoteConnectAsString‘連接SQLSERVER連線參數(shù)DimstrDBFileNameAsString‘pda上的資料庫DimcnADOAsADOCE.Connection‘操作pda上的sdf資料庫的connection

11設定連線參數(shù)值設定連接參數(shù)PrivateSubInitDB()'聯(lián)結PDA上的SQL資料庫，判斷是否要覆蓋或繼續(xù)使用strRemoteSQLServerName=txtRemoteHost.Text'SQLServerstrRemoteSQLServerUID=txtUserID.Text'SQLServer帳號strRemoteSQLServerPwd=txtUserPasswd.Text'SQLServer密碼strInternetURL=txtsaURL.Text'RDA.DLL所在位置'連接資料庫strRemoteConnect=strRemoteProvider&"DataSource="&strRemoteSQLServerName&";UID="&strRemoteSQLServerUID&";password="&strRemoteSQLServerPwdMsgBox“SQLServer資料庫初始成功，可以開始進行上傳或下載資料的動作！“download_upload.ShowEndSub

12連線SQLServerdownload_uploadCOMMAND1command2

13從SQLServer下載資料IDimceRDAAsSSCE.RemoteDataAccess‘全域變數(shù)PrivateSubCommand1_Click()'下載資料DimCreateDBFlagAsBooleanDimintTempAsInteger'--------------------------------------------------------------------------‘判斷是否要在PDA重新產(chǎn)生SQL檔案CreateDBFlag=True'--------------------------------------------------------------------------strDBFileName=DBFileSpec&DBName'PDA上資料庫所在位置與路徑IfDBExists(strDBFileName)=TrueThen'檢查PDA上的資料庫是否存在intTemp=MsgBox("要覆寫資料庫嗎？"&strDBFileName&"?",vbYesNoCancel,"資料庫已經(jīng)存在")IfintTemp=vbYesThen'確定覆寫FileSystem1.KillstrDBFileName'刪除目前資料ElseCreateDBFlag=FalseEndIfEndIf

14從SQLServer下載資料II‘接前頁'----------------------------------------------------------------------------IfCreateDBFlag=TrueThenCreateDB'在PDA上建立SQL資料庫download_from_sql'開始下載資料****************ElseExitSubEndIfEndSub

15CreateDB'在PDA上建立SQL資料庫PrivateSubCreateDB()DimcatAsADOXCE.CatalogSetcat=CreateObject("ADOXCE.Catalog.3.1")'在PDA上建立SQL資料庫cat.Create(SQLEProvider&strDBFileName)Setcat=NothingEndSub

16THANKYOUSUCCESS2022/10/2117可編輯

17download_from_sql-I'下載資料************************Subdownload_from_sql()DimsrAsStringShowHourGlass'顯示漏斗connOpen'開啟PDA上的SQL資料庫'----------------------------------------------------IfTableExists(cnADO,“employees")=TrueThen'cnADO代表開啟的SQL資料庫的employees資料表cnADO.Execute("droptableemployees")'刪除資料表employeesEndIf

18download_from_sql-II'----------------------------------------------------connClose'關閉SQL資料庫'------------------------------------------------------------'連結遠端SQLSERVERSetceRDA=CreateObject("SSCE.RemoteDataAccess.2.0")ceRDA.LocalConnectionString=SQLEProvider&strDBFileNameceRDA.InternetURL=strInternetURLceRDA.InternetLogin=strInternetLoginceRDA.InternetPassword=strInternetPwd'strRemoteConnect代表SQLServer資料庫‘Pull代表將資料由SQLServe取出放入PDA上的SQL資料庫中ceRDA.Pull“employees","SELECT*FROMemployees",strRemoteConnect,TRACKINGON

19download_from_sql-IIIIfceRDA.ErrorRecords.Count>0ThenShowErrorsceRDA.ErrorRecords'顯示錯誤訊息ElseMsgBox"資料下載成功!"EndIfSetceRDA=NothingShowArrow‘恢復游標EndSub

20connOpen'開啟PDA上的SQL資料庫GPS.SDF檔案FunctionconnOpen()AsBooleanconnOpen=FalseSetcnADO=CreateObject("ADOCE.Connection.3.1")cnADO.Open(SQLEProvider&strDBFileName)IfcnADO.Errors.Count=0ThenconnOpen=True'沒有錯誤代表開啟成功ElseMsgBox"PDA上的SQL資料庫開啟失敗"EndIfEndFunction

21connClose'關閉PDA上的SQL資料庫SubconnClose()OnErrorResumeNextcnADO.CloseSetcnADO=NothingOnErrorGoTo0EndSub

22TableExistsPrivateFunctionTableExists(paramcnADOAsADOCE.Connection,paramTableNameAsString)AsBooleanDimcatTableAsADOXCE.TableDimcatAsADOXCE.CatalogTableExists=FalseSetcat=CreateObject("ADOXCE.Catalog.3.1")cat.ActiveConnection=paramcnADOForEachcatTableIncat.TablesIfInStr(1,catTable.Name,paramTableName)<>0ThenTableExists=TrueNextSetcatTable=NothingSetcat=NothingEndFunction

23ShowHourGlass&ShowArrow'改變游標變成漏斗PublicSubShowHourGlass()Screen.MousePointer=11EndSub'回覆游標PublicSubShowArrow()Screen.MousePointer=1EndSub

24Summary透過RDA連接SQLServerCERDASetceRDA=CreateObject("SSCE.RemoteDataAccess.2.0")ceRDA.LocalConnectionString=SQLEProviderceRDA.InternetURL=RDA的URL取值/回傳值ceRDA.Pull"goods","SELECT*FROMgoods",strRemoteConnect,TRACKINGONceRDA.Push"goods",strRemoteConnect連接遠端SQLServer參數(shù)strRemoteProvider="Provider=sqloledb;InitialCatalog=wealth;"strRemoteConnect=strRemoteProvider&"DataSource="&strRemoteSQLServerName&";UID="&strRemoteSQLServerUID&";password="&strRemoteSQLServerPwd操作PDA本機SQLServerCEADOCESQLEProvider="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;datasource=a.sdf"SetcnADO=CreateObject("ADOCE.Connection.3.1")cnADO.Open(SQLEProvider)

25顯示資料DimcnADOAsADOCE.ConnectionDimrsAsADOCE.RecordsetconnOpen'開啟PDASQL資料庫ssql="SELECT*FROMemployees"Setrs=CreateObject("ADOCE.Recordset.3.1")rs.Openssql,cnADO,adOpenKeyset,adLockPessimisticCombo1.Clear'清空下拉選單WhileNotrs.EOF‘顯示資料Combo1.AddItemTrim(rs(“employeeid"))Label7.Caption=rs(“FirstName")Label8.Caption=rs(“LastName")Label9.Caption=rs("Tel")rs.MoveNextWendrs.CloseSetrs=NothingconnClose

26顯示資料Subgrid()Dimi,jAsIntegerGridCtrl1.Rows=totalcount+1GridCtrl1.Cols=2GridCtrl1.Row=0GridCtrl1.Col=0GridCtrl1.Text=“編號"GridCtrl1.Col=1GridCtrl1.Text="名稱"rs.movefirstFori=1TototalcountGridCtrl1.Row=iForj=0To1GridCtrl1.Col=jSelectCasejCase0:GridCtrl1.Text=rs("goods_code")Case1:GridCtrl1.Text=rs("goods_name")EndSelectNextrs.MoveNextNextEndSub

27更改資料Setrs1=CreateObject("ADOCE.Recordset.3.1")rs1.Open"updateemployeessetfirstname='"&Text1.Text&"'whereemployeeid=1",cnADO,adOpenDynamic,adLockOptimistic

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

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

30上傳資料'上傳資料到ServerPrivateSubCommand2_Click()ShowHourGlass'顯示漏斗SetceRDA=CreateObject("SSCE.RemoteDataAccess.2.0")ceRDA.LocalConnectionString=SQLEProvider&strDBFileNameceRDA.InternetURL=strInternetURL'將資料回存到SQLServer中ceRDA.Push“employees",strRemoteConnect'employees資料表回存IfceRDA.ErrorRecords.Count>0ThenShowErrorsceRDA.ErrorRecords'顯示錯誤訊息ElseMsgBox"資料上傳成功!"EndIfSetceRDA=NothingShowArrow'恢復游標EndSub

31THANKYOUSUCCESS2022/10/2132可編輯