MBL 324 SQL Server Everywhere Windows Mobile SQL

MBL 324 使用SQL Server Everywhere 开发Windows Mobile最佳移动数据应用

课程内容概述什么是SQL Server Everywhere SQL Everywhere 与 SQL Express SQL Everywhere Click. Once功能 Windows Mobile 的本地数据存储 Sql. Ce. Result. Set Base Table Cursor

移动设备存储的关键 Size Performance Security Data integrity Ease of development Ease of data transfer Platform support Wireless connectivity

SQL Server Everywhere

什么是SQL Server Everywhere 嵌入到Mobile和桌面应用程序中的轻量级、关系型数据库 SSEv是SQL Mobile运行于Win 32平台的实现 SSEv 定位于桌面级数据库，不支持基于Web的应用程序 Click. Once、Data Directory支持运行于 Windows XP, Windows 2003, Vista B 2 Windows Mobile Visual Studio 2005、SQL Server 200 具支持安装文件 MSI Click. Once Book Online

Visual Studio 2005 创建 SQL Server Everywhere 桌面应用

SQL Everywhere 附加功能 Runtime增强功能支持ADO. NET部署到GAC |Data. Directory| 宏动态控制数据库文件的路径. Northwind. sdf 成为 |Data. Directory|Northwind. sdf VS Designer 增强功能支持Click. Once bootstrapper 支持私有部署

部署方式 Single Central Install (MSI) 使用 Microsoft Update 更新需要管理权限 Click. Once Bootstrapper 私有部署 (ISV Option) 通过应用程序将dll进行私有部署不需要管理员权限不支持 Microsoft Update 将SQL Everywhere作为应用程序的一部分

Deployment Options Click. Once with SQL Everywhere Bootstrapper Click. Once with Private DLL Deployment

SQL Ev私有部署显式安装的SQL Ev 优先级高于私有部署版本 SQL Ev运行于GAC中，需要设置管理权限改变 System. Data. Sql. Server. dll 引用为 Copy Local 拷贝 sqlce*. dll’s %Program. Files%Microsoft SQL Server Everywherev 3. 1 不支持 Microsoft Update 只能为宿主应用程序使用

SQL Express SQL Server Everywhere Windows CE Device Pocket PC Smartphone SQL Mobile Win 32 Single User Scenarios Server (1000’s of users) Workgroup (Dozens of users) Desktop (Single User) Laptop Tablet PC SQL Multi User Server Local Database Options

. NET Compact Framework 2. 0的高效率数据访问

Sql. Ce. Result. Set Class

创建Sql. Ce. Result. Set 使用Sql. Ce. Command. Execute. Result. Set方法 Typed Result. Set 使用Visual Studio 2005 Data Source wizard 将. xsd file Custom Tool 属性改为 MSResult. Set. Generator 为了增加适应性，通过partial classe扩展产生typed Sql. Ce. Result. Set 代码在Data Source页中使用数据绑定控件 Typed Result. Set可以通过new操作符来创建

Typed Sql. Ce. Result. Sets 支持每个数据列的强命名属性方法 Add record Delete record Bind to Binding. Source Open Move. To. Row Is column null Set column null 优势 – Intellisense 和编译期检查不影响执行效率支持窗体编辑器中的数据绑定控件

Result. Set. Options 使用Sql. Ce. Command. Execute. Result. Set创建对象无须设置Result. Set. Options参数（对象按照默认行为执行） Insensitive -不会发现其他数据连接对数据源的改变 Sensitive-察觉来自其他数据连接的改变 Base table cursors的属性始终为sensitive Table. Direct command type => base table cursor Scrollable -你可以向前或向后读取数据，使用内部的Bookmark. Array Updatable -允许更新 None –只读，单向浏览数据

Base Table Cursor 可以设置index，用于查询指定值、排序、设置范围获取一个数据行中的所有项，不能根据Select语句获取数据子集从数据表中读取所有列的速度最快 Example: // Create and execute Sql. Ce. Command cmd = new Sql. Ce. Command(“Authors", cnn); cmd. Command. Type = Command. Type. Table. Direct; Sql. Ce. Data. Reader dr = cmd. Execute. Reader(); // Retrieve Results while(dr. Read()) { Message. Box. Show("Name = " + dr["au_lname"]); } dr. Close(); dr. Dispose(); // Create Result Set Sql. Ce. Result. Set rs = cmd. Execute. Result. Set(Result. Set. Options. Scrollable | Result. Set. Options. Updatable);

Seek/Set. Range: 基本用法用于打开表的index 选择值的范围的最快方法 Example: cmd. Command. Type = Command. Type. Table. Direct; cmd. Command. Text = "Orders"; //Assume: Index contains one column [datetime] cmd. Index. Name = "Some. Index"; object[] start = new object[1]; object[] end = new object[1]; start[0] = new Sql. Date. Time(2001, 1, 1); end[0] = new Sql. Date. Time(2002, 2, 3; cmd. Set. Range(Db. Range. Options. Match, start, end); Sql. Ce. Data. Reader dr = cmd. Execute. Reader(); dr. Seek(Db. Seek. Options. First. Equal, new Sql. Date. Time(2001, 3, 4)); while(dr. Read()) { } // Read data in the usual way

Data. Set, Data. Reader和 Sql. Ce. Result. Set 真的最快吗?

Performance Results on Pocket PC Load 10000 Read 1000 SQL Syntax SQL statement Table. Direct 47500 Data Reader Update 4000 1900 List<T> 30 6 SQL statement Table. Direct Result Set SQL statement Table. Direct Read Store Data. Set 1366 1140 700 List<T> 20000 350 7* 730 1900 6000

Performance Results on Tablet PC Load 100000 Read 10000 SQL Syntax SQL statement Table. Direct 18000 Data Reader Update 40000 Data. Set 328 270 1100 List<T> 0 0 SQL statement Table. Direct Result Set SQL statement Table. Direct Read Store 219 List<T> 12000 50 0* 219 1100 2300

SQL Server Everywhere网站 http: //www. microsoft. com/sql/everywhere MSDN Blogs http: //blogs. msdn. com/Steve. Lasker/ http: //blogs. msdn. com/SQLServer. Everywhere/ SQL Server Everywhere CTP简介 http: //support. microsoft. com/? kbid=920700 User Group http: //forums. microsoft. com/MSDN/Show. Forum. aspx? Forum. ID=152&Site. ID=1 http: //blog. csdn. net/aawolf/