海鸥航迹

学习之笔记，好文之收集。

2007年8月11日 #

SQL Server CE 开发前准备[转]

 
SQL Server CE 开发前准备
作者：ericzhen   来源：joycode.com   时间：2004年9月11日 4:56   阅读4023次
 

SQL Server CE 2.0的全名是SQL Server 2000 Windows CE Edition version 2.0。2002年秋天就有了，那时候也正好是.NET Compact Framework刚刚出来的时候。那时候就想研究研究，但因为实际需求太少、没有人问问题、没人有做项目，所以就一直扔着。

不过随着Pocket PC/Windows Mobile越来越被人熟悉，一些实际需求也就带动出来了。典型的需求是Field Service。现在很多Field Service仍然是依赖纸张，做不到全部电子化。

例子1：公司派人去仓库验货、点货，可以把清单同步到Pocket PC上，然后在仓库里面直接在PPC上面update，回到公司一同步就可以了。否则按照原先做法，需要先打印一张清单，回来以后再录入一遍。

例子2：联想派人上门来修电脑，以前总是带着一张打印出来的派工单，维修晚了以后维修的人填写维修记录，然后让客户签字。这样做，一则是纸质派工单的档案管理很麻烦，二则维修记录等停留在纸上，如果想用作知识管理、Business Intelligence等更多分析用途，还需要再找人输入。如果能把派工单同步到Pocket PC里面，在现场直接填写记录以及让客户签字，回来一下子同步到服务器上，就方便多了。

所以，前两天下定决心把SQL CE摸了一下，写了一些心得。如果没有摸过SQL CE但也想了解一下，看我这些心得应该可以解决大部分常见的对于SQL CE的问题。

1. SQL CE的架构

 

首先要回答的问题是“SQL CE是什么”。简单的说，SQL CE就是运行在Pocket PC以及其他Windows CE平台上的一个很简化的SQL Server 2000/7.0。之所以说是“简化”的，因为它支持的功能少、数据类型比较少、周边工具也少。除了运行在Device上的部分以外，SQL CE还带了一套开发包（就是Sqlce Data Provider），以及SQL CE Server Tool用来连接服务器上的SQL Server 2000/7.0：

开发时，可以用VS.NET或者eVB/eVC来开发。如果用eVB/eVC开发，就用ADOCE 3.1；如果用.NET开发，就用.NET Compact Framework以及SQL Server CE Data Provider，用法基本上和原先的ADO.NET没啥太大的区别：

运行时，SQL CE的感觉就好像是SQL Server 2000/7.0的一个副本，SQL CE应用程序本身在Pocket PC上会占用1-3MB空间，另外数据回存放在后缀名为.sdf的文件中（和SQL2K中的mdf文件同样的角色）。用户在Pocket PC上做的操作都先发生在SQL CE上，然后再时不时地和SQL Server 2000/7.0同步（Replication）。当然，也可以用RDA（Remote Data Access）的方式，直接让数据操作发生在SQL Server 2000/7.0上。

这里一个有意思的地方是无论用Replication还是RDA，移动设备访问SQL Server 2000/7.0时必须通过IIS进行，不知道这个设计是否考虑了Web Server会放在DMZ里面而SQL会放在Internal的原因：

2. SQL CE的安装

 

如果我没有记错的话，SQL Server CE 2.0是免费的，只要有SQL2K Standard/Enterprise的License，就可以下载安装使用。不过如果做商业开发，最好再核实一下。

有兴趣的可以在http://www.microsoft.com/sql/ce/downloads/ce20.asp下载SQL Server CE 2.0的安装文件，大概40多兆。下载好以后，安装之前还要准备一下这些：VS.NET 2003，SQL Server 2000 SP1 or Higher，IIS。安装的步骤是：

a) 在开发环境上安装SQL CE。

主要是安装Sqlce Data Provider的Assembly，以及后面两步安装需要的安装文件。安装的方法是直接运行下载下来的Setup.exe，一路Next就可以了。需要有VS.NET 2003。安装好以后效果是这样的：

b) 在IIS上安装SQL CE Server Tool，并配置连接SQL Server 2000数据库。

如果SQL Server 2000是SP1或者SP2，这一步可以用C:\Program Files\Microsoft SQL Server CE 2.0\Redist\Server下的文件安装。如果装了SP3，可以到http://www.microsoft.com/sql/ce/downloads/sp3.asp下载安装文件。

安装好了以后要配置IIS和SQL Server 2000之间的连接。配置成这样就可以了：

这一步，如果SQL Server上面没有创建过Publisher，还需要马上创建一个。Snapshot folder等目录的权限最好放宽些，免得将来出错。

c) 最后，在Pocket PC上安装。

其实，这步不需要自己来做。VS.NET 2003在Deploy项目的时候自动会安装的。当然，如果需要手工安装，可以用C:\Program Files\Microsoft Visual Studio .NET 2003\CompactFrameworkSDK\v1.0.5000\Windows CE\下的文件安装，其中包括了数据库引擎、Query Analyzer等。我的Pocket PC是iPAQ 1940，系统是中文的PPC 2003，我用的安装文件是那个目录下的wce400\armv4\子目录。其他平台不太清楚。

这些都做完了，SQL CE的安装也就OK了。

3. 移动IBuySpy的实例

 

SQL CE 2.0自带了一些很好的实例，例如IBuySpy Delivery Application。IBuySpy是一个.NET的电子商务网站的demo solution，而这个IBuySpy Delivery Application是在此之上做的一个供销售人员用的移动办公工具。销售可以拿着Pocket PC到处接订单，然后回到公司以后同步到IBuySpy的数据库中。很典型的Field Service。

IBuySpy的安装路径是C:\Program Files\Microsoft Visual Studio .NET 2003\CompactFrameworkSDK\v1.0.5000\Windows CE\Samples\VB\Pocket PC\IBuySpyDelivery。不难装，一路Next就可以了，有Wizard，安装程序会把服务器上的数据库也Setup好，还会把Client发布到Pocket PC上（发布Client需要VS.NET 2003）。

下面是一些Pocket PC上的Screenshot，没摸过的兄弟看了就有感性认识了：

a) SQL CE在Device上存在形式是一个.sdf文件

b) SQL CE也有Query Analyzer

c) 和SQL Server 2000同步

d) IBuySpy例子提供的一个签名控件真不错，会转换成Bit Array存储在数据库中

上面这些心得，也可以在http://eric.tanqi.com/SQLCE/SQLServerCE.zip下载到，这是一个PPT文件。

很快SQL Server Mobile Edition也要出来了，跟着VS.NET 2005和Yukon一起，应该算是SQL CE 2.0的下一个版本。粗粗看了一眼，好像提供了Synchronization的进度API，这样就可以显示一个同步的进度条了。

http://www.biplip.com/default.aspx?mid=348&ctl=View&ItemId=310

posted @ 2007-08-11 23:41 海天一鸥 阅读(479) | 评论 (0) | 编辑

SQLite3 COM vs. SQLite COM

SQLite3 COM SQLite3 COM vs. SQLite COM

 

SQLite3的改进：
http://www.newobjects.com/pages/ndl/SQLite3%5Ccomparison23.htm
 

The table lists the most significant features of SQLite3 COM and SQLite COM. It covers both the database engine capabilities, the specific additions and the programming interface. It describes the database features as they are available in the SQLite COM/SQlite3 COM implementations.

Feature SQLite COM SQLite3 COM Comments
Zero configuration Yes Yes Only the file name of the database file must be supplied. Any other parameters are optional and can be specified at run-time.
COM interface integrated integrated The database engine and the COM interface are in single DLL. Note that AXPack1 core is still needed because the output from queries is packed in AXPack1 objects. This may sound as added footprint, but in the reality the benefit of using common objects for database communication and other tasks enables the applications to perform very complex tasks with little code. 
In-memory database Yes Yes Opening with empty file name opens a full featured in-memory SQL database.. 
SQL support SQL 92 SQL 92 Not all the features are supported. See the SQL reference for the both components for details.
Sub-queries static full support The sub-queries are one of the most powerful mechanisms in SQL. Static means that the sub-query is executed once and used for each iteration of the outer query. The full support allows the sub-query to refer fields from the outer query. 
Query parameters No support, You need a StringUtilities object to overcome this.  Named and by position. In SQLite COM you can parameterize queries only by using the string formatting functionality supplied by the StringUtilities object.
In SQLite3 COM you have methods like VExecute, CExecute and AExecute. They allow you put parameters in the executed queries (for example using ? or $<name>) and pass values for them as trailing arguments (VExecute), as collection (CExecute) or as array (AExecute). You can pass any kind of value including binary (blob). 
Session parameters None Scalar and object parameters supported. The SQLite3 COM's Parameters collection enables the application to set named parameters (values or objects) that can be used in any query executed with this database object. This includes not only directly executed queries, but also views and triggers. The session parameters are accessible through the Parameter database function. Any method or property on object parameters can be called using the CallObject database function.
Parameterized views No Yes See session parameters.
OLE Date/Time support Yes Yes - Extended For Windows applications it is more convenient to use OLE date/time values which are actually regular double precision floating point numbers. This date/time format is better than most other date/time formats (including the date/time format inherited from the SQL standard). The OLE date/time is not only directly convertible from/to double values, but also precise enough for time stamping. This makes it the recommended date/time format for SQLite COM and SQLite3 COM. 
Internal database types numeric and text integer, text, real, blob In SQLite3 COM the internal database value types are enough to fit the needs of any application.
Column types smart smart SQLite (2 and 3) allow values of any of the supported types to be put in any column. Thus the declared column type is actually just a suggestion. It is up to the application to decide if it will stick to it or will allow mixed types to be put in the column. Smart type conversion is applied in the SQL expressions and the application can determine the actual type of the value of each particular field in the returned result. 
Internal COM to database interface textual direct In SQLite COM the data is automatically or explicitly converted to text before sending/receiving it to/from the database engine (internally).   
In SQLite3 COM the data is converted from/to the closest internal database value type which makes the interface 3 to 5 times faster than in SQLite COM.
Triggers Yes Yes  
Transactions Yes Yes The transactions cannot be nested.
Database storage Single file (or in-memory) SQLite2 UTF-8 format Single file (or in-memory) SQLite3 UTF-8 format. The file format is machine independent. For example the Windows CE versions of the SQLite COM/SQLite3 COM work with the same files as the desktop versions. The format is the standard SQLite format and is compatible also with non SQLite COM implementations based on SQLite (for example UNIX applications using SQLite in their own way) 
Database manager ALP based ALP based Database manager is available for the both components. It is written using ALP. It can be obtained separately or as part of other products that include it (if you have installed some of our products you may already have it - check before downloading separately). 
Availability Windows 95/98/ME
Windows NT4/2k/XP/2003/Vista and above
Windows CE 3.0/CE.NET and above (including Pocket PC and Smartphone)		 Windows 95/98/ME
Windows NT4/2k/XP/2003/Vista and above
Windows CE 3.0/CE.NET and above (including Pocket PC and Smartphone)		  
Database locking Yes
(Windows CE - No)		 Yes (page level)
(Windows CE - No)		 On desktops multiple applications can work with the same database file at the same time. On Windows CE devices only one application may have full access to the database while the others can only read from it at the same time.
Extending the database with custom functions No Yes See CallObject.

 

Note that this is not comparison of the SQLite database engines alone - SQLite COM and SQLite3 COM add certain features to the standard engine which are not part of the original SQLite database.

...
newObjects Copyright 2001-2006 newObjects [ ]

posted @ 2007-08-11 22:51 海天一鸥 阅读(448) | 评论 (0) | 编辑