Adoce for Ado Programmers
Old wolf Press: Recently, I have developed a database of Pocket PC. This article gave me a lot of help, so I translated it and hope to give you some help.
Are you a ADO developer who need to put your knowledge and code from the PC to the PCKET PC? I will show you the similar and different from ADO and Adoce through some code instances.
Download the 619-cf.exe from the msdn code center.
What you need
· Microsoft® Embedded Visual Tools.
· Microsoft SQL Server 2000 Developer Edition.
· SQL Server 2000 Windows® CE Edition.
· Microsoft AdoCE (ActiveX® Data Objects CE) 3.1 Included with SQL Server 2000 Windows CE Edition.
· To try The Server-Side Examples, You NEED Microsoft Visual Studio® and ADO 2.6.
Gotchas
Because there is a memory vulnerability in the CreateObject declaration, you'd better create only an AdoCE object once in your program. In the instance code, I put the call of CreateObject in each event, but that is just to compare the ADO code.
Languages Supported
ENGLISH
Data Access
data access
Most enterprise applications are data-centric, which means you need to know how to store information. My hobbies are stored data, using real databases on the Pocket PC - SQL Server 2000 Windows CE Edition. Therefore, my first suggestion is to get a license for a SQL Server 2000 Developer Edition you need.
When you have installed SQL Server 2000 Windows CE Edition, you will get Adoce 3.1, the latest version. I will compare it with the latest version of ADO 2.6 on the PC.
Main Differences
Main difference
Ok, let us first look at the important difference. Below is an Ado function list not supported by ADOCE:
Command and Parameter Objects
Collection Objects
Property Object
· Recordset Persistence (RecordSet.save)
· Asynchronous queries
· Disconnected Recordsets
· Dynamic Creation of Records
· Multiple queries (RecordSet.NextRecordset)
For me, there is no Recordset Persistence means that I can't save the results into the EXTENSIBLE MARKUP LANGUAGE (XML) to call other programs on the Pocket PC. Maybe more importantly, I can't put XML data into my result. This causes me to translate or reverse the data transformation from the server component to the Pocket PC - I also need some transformation. My suggestion is to see what ODyssey Software provides. Their Cefusion and Viaxml products are all useful here. If you are looking for a longer (read Microsoft .NET) to convert data, you should look at Pocketsoap. Anyway, most important features are implemented in Adoce. Now let's take a look at some differences in the same data access environment.
Data Retrieval
data collection
Let us start with the most common ideas - when you need to get some information from a data store. On the PC, fill in the data acquisition code of the ListView control will write this:
DIM LITM As ListItem
DIM Laco As Connection
Dim lars as recordset
DIM LSSQL AS STRING
'Add Column Headers
Lvwarticles.columnheaders.Add,, "Description", 2500
Lvwarticles.columnheaders.add,, "Price", 600, LvwcolumnRight
Lvwarticles.columnheaders.Add,, "Stock", 600, LvwcolumnRight
'Create Objects
Set laco = creteObject ("adodb.connection")
Set lars = creteObject ("adoDb.recordset")
'Open Connection
Laco.open "provider = sqloledb; data source = servername;" & _
"Initial Catalog = DBNAME; Trusted_Connection = YES"
'Open Recordset
LSSQL = "SELECT * ARTIM ARTICLE"
Lars.Open Lssql, Laco, AdopenForwardonly, AdlockReadonly
'Clear List and Get Item Rows
Do While NOT LARS.EOF
Set Litm = lvwarticles.listitems.add (, LARS ("Description"). Value)
Litm.Subitems (1) = lars ("price"). Value
Litm.subitems (2) = LARS ("Stock"). Value
Lars.movenext
Loop
'Close RecordSet and Connection
Lars.close
Laco.close
You will do this on the Pocket PC:
DIM LITM As ListItem
DIM Laco As Connection
Dim lars as recordset
DIM LSSQL AS STRING
'Add Column Headers
Lvwarticles.columnheaders.Add,, "Description", 2500LVwarticles.columnheaders.Add,, "Price", 600, LvwcolumnRight
Lvwarticles.columnheaders.Add,, "Stock", 600, LvwcolumnRight
'Create Objects
Set laco = creteObject ("adoect.connection.3.1)
Set lars = creteObject ("adoe.recordset.3.1))
'Open Connection
Laco.open "provider = microsoft.sqlser.Oledb.ce.1.0;" & _
"Data Source = / dbname.sdf"
'Open Recordset
LSSQL = "SELECT * ARTIM ARTICLE"
Lars.Open Lssql, Laco, AdopenForwardonly, AdlockReadonly
'Clear List and Get Item Rows
Do While NOT LARS.EOF
Set Litm = lvwarticles.listitems.add (, LARS ("Description"). Value)
Litm.Subitems (1) = lars ("price"). Value
Litm.subitems (2) = LARS ("Stock"). Value
Lars.movenext
Loop
'Close RecordSet and Connection
Lars.close
Laco.close
As you can see, there are some smaller differences in both examples. More apparent is different from the connection string of the Open method of the Connection object. If you ignore the Provider parameter, AdoCe will assume that you want to access a Pocket Access database file. The SQL Server CE is always referenced as a file name. In this case, the file will be placed in the root of the device, but can also be placed in your application directory (for example, Data Source = / Program Files / AppName / AppName.sdf).
Everything is available. Conclusion (a) All data access code is almost no need to modify it and (b) Most of your PC program data access code gets data, you will recognize that you are porting your PC application to the Pocket PC without huge s hard work.
Updating data
update data
Now let's take a look at another hypothesis - when we need to add some information to data storage. I will use and data to get the same code to open and close the Connection and Recordset objects. You will write similar code on the PC:
'Open Recordset
LSSQL = "SELECT * ARTIM ARTICLE"
Lars.open Lssql, Laco, AdoPENDYNAMIC, ADLOCKOPTIMISTIC
'Add New Article
Lars.Addnew
Lars ("description"). Value = "TEST"
Lars ("price"). Value = 50
Lars ("stock"). Value = 100Lars.Update
And you will see this code on the Pocket PC:
'Open Recordset
LSTABLE = "article"
Lars.open Lstable, Laco, AdoPENDYNAMIC, ADLOCKOPTIMISTIC, _
AdcmdtableDirect
'Add New Article
Lars.Addnew
Lars ("description"). Value = "TEST"
Lars ("price"). Value = 50
Lars ("stock"). Value = 100
Lars.Update
As you can see, the difference is that you open the Recordset method. As an update, Recordset is opened as a table than a SQL declaration (SELECT) better. You may do very well on the PC (ADO), but this is probably that you have to change the code from the PC to the PCKET PC.
Let us look at an updated situation. This is the code on the PC:
'Open Recordset
LSSQL = "SELECT * from articles where description = 'test'"
Lars.open Lssql, Laco, AdoPENDYNAMIC, ADLOCKOPTIMISTIC
'Update Article
IF not lars.eof then
Lars ("price"). Value = 200
Lars.Update
END IF
This is Pocket PC:
'Open Recordset
LSTABLE = "article"
Lars.open Lstable, Laco, AdoPENDYNAMIC, ADLOCKOPTIMISTIC, _
AdcmdtableDirect
Lars.Find "Description = 'Test'"
'Update Article
IF not lars.eof then
Lars ("price"). Value = 200
Lars.Update
END IF
It is impossible to create an updated Recordset like a SQL declaration on the PC. You can use the Find method to get the location you want in the Recordset.
If you enhance some performance, you can use a "RAW" SQL Update Statement to do the same thing:
Laco.execute "Update Article Set Price = 200 Where Description = 'Test'"
In order to improve the idea, let's take a look at how to delete data. On the PC, the code is like this:
'Open Recordset
LSSQL = "SELECT * from articles where description = 'test'"
Lars.open Lssql, Laco, AdoPENDYNAMIC, ADLOCKOPTIMISTIC
'Delete Article
IF not lars.eof kilns.delete
The corresponding code on the Pocket PC:
'Open Recordsetlstable = "article"
Lars.open Lstable, Laco, AdoPENDYNAMIC, ADLOCKOPTIMISTIC, _
AdcmdtableDirect
Lars.Find "Description = 'Test'"
'Delete Article
IF not lars.eof kilns.delete
The code here and the updated code are very similar. Note that the result set of the Find method is a RECORDSET that only matches the search criteria. This means that you get the same content in the PC and Pocket PC. I said this, to prevent you from doing more things about RecordSet on the Pocket PC, because you open the same table.
In addition, you can use a SQL DELETE statement to do almost the same thing:
Laco.execute "delete article where description = 'test'"
I said "almost" because the last Execute will delete records that match the query criteria, but the above will only delete the records of the first appearance. In practical cases, search comparisons may be set to unique keys to generate the same results.
For a complete example, please see the code of the example.
Conclusion
End
You already know what most about the ADO on the PC can be used on your Pocket PC, even if some things have slightly different. Similarly, when writing the Pocket PC program, most of the data access code can be applied to your program through some modifications.
Why didn't you start to transplant the enterprise program on a PC to the Pocket PC? Start now, then show someone to others - this will be your next project.
Welcome to Aawolf's columns: http://www.9cbs.net/develop/author/netauthor/aawolf/
