DATABASE

ADO.NET Programming : Microsoft SQL Server CE (part 1) - SQL Server CE Files, SQL Server CE Syntax

2/19/2013 6:42:58 PM

A variety of databases are available for mobile devices, but the one we use throughout these article is SQL Server Windows CE Edition, a slimmed-down version of Microsoft’s desktop database, SQL Server (in the interest of brevity, we refer to the CE version as “SQL Server CE”). It is the mobile database of choice for .NET Compact Framework programming because it is fully supported in the .NET Compact Framework runtime environment and the .NET Compact Framework development environment.

Most applications written for SQL Server CE have two primary database tasks:

  1. Manipulating the data while disconnected

  2. Transferring the data to SQL Server when the device is connected to the desktop machine

The first database task involves using the generic ADO.NET classes, and the provider-specific classes, which we discuss shortly. For programmers who have worked with ADO.NET on desktop systems, all of the generic classes on the .NET Compact Framework implementation of ADO.NET are very familiar.

The second major database task, the transfer of data between SQL Server and SQL Server CE, involves not only the participating database engines but also Microsoft Internet Information Services (IIS). Two different mechanisms are available to help with this: Merge Replication and Remote Data Access (RDA). 

Accessing SQL Server CE Databases from Native Code

ADO.NET classes are available only in managed code. But you do not need managed code to access the data in a SQL Server CE database. Two separate native APIs are available for manipulating the data in a SQL Server CE database. One involves a COM object (ADOCE), and the other is an API (OLEDBCE). These components match up with their desktop counterparts, ADO and OLE DB.


1. SQL Server CE Files

Each SQL Server CE database is stored as a single file on your CE device. The recommended naming convention for the file extension is .sdf, and the name of the file is the name of the database. Thus, you open a connection to mydb.sdf, not to mydb. You may associate a password with the database and also encrypt the physical file using 128-bit RSA encryption. Because each database is one file, it is visible in the File Explorer. Figure 1 shows a database file selected in the File Explorer.

Figure 1. A Database File Viewed in the File Explorer


Because SQL Server CE databases are files in the Windows CE file system, they can be accessed using the File and Directory classes. For example, you can copy a database file, delete a database file, or test for the existence of a specifically named database file. Your ability to operate on database files is subject to the same restrictions as other files in the file system; for example, you can delete a database file only if the file is not currently in use.

SQL Server CE reclaims space within a database file only when you ask it to; it’s not a capability available through the data manipulation language, nor is it provided as a background process. To reclaim space within a SQL Server CE database, you must use the Compact method of the SqlCeEngine[3] class.

[3] Fully qualified name: System.Data.SqlServerCe.SqlCeEngine.

2. SQL Server CE Syntax

SQL Server CE programming is simpler than SQL Server programming because the SQL language supported by SQL Server CE is a subset of the SQL available with SQL Server. For example, because a SQL Server CE database is a single-user database whose security is controlled by a file password, there is no need for GRANT, DENY, and REVOKE statements.

Also missing are SQL Server’s Transact SQL extensions: no stored procedures, triggers, multistatement batches, or DECLARE, SET, IF, or WHILE statements. Even some standard SQL had to be left out; for example, views are not supported. Not all the SQL Server data types could be included, but most of the missing ones can be converted to ones that do exist. For example, Windows CE itself supports only Unicode, and so Unicode is the only character data type (nchar, nvarchar, ntext).

What SQL Server CE does provide is support for tables, indexes, defaults, and referential integrity. It also has the ability to add, modify, and delete rows in those tables using standard SQL Data Manipulation Language (DML). Therefore, your application can manipulate data in SQL Server CE by connecting to the database file and submitting INSERT, UPDATE, and DELETE statements to the database. You can write these DML statements yourself, or you can have ADO.NET write them for you as one step in the synchronization of data set updates to the database. Also, the SQL syntax uses single quotes to delineate literals, which is very convenient when you are quoting that SQL within your code—it eliminates the need to quote quotes.

Your SQL Server CE–based application may be more elementary than what you’ve come to expect from a typical SQL Server application, but it should provide enough functionality to be useful in mobile situations. The lack of the complete Transact SQL syntax is an inevitable limitation given the available memory of a typical Windows CE–powered device. Yet in spite of this size limitation, SQL Server CE provides a more than adequate set of SQL commands for just about any Windows CE–based application you are likely to need.

Tables 1 and 2 list what functionality in SQL Server is unsupported and supported, respectively, in SQL Server CE.

Table 1. Unsupported SQL Server Functionality in SQL Server CE
FunctionalityComments
DCL[a] GRANT, REVOKE, DENYThese are not needed in a single-user database
DDL[b] Views, triggers, stored procedures, user-defined functions, user-defined data typesMost of these are SQL Server Transact SQL extensions to the ANSI functionality. This is logic that could reside on the SQL Server. It must be coded into the application when using SQL Server CE
DML[c] IF-ELSE, WHILE, DECLARE, SETMost of these are SQL Server Transact SQL extensions to the ANSI functionality
INFORMATION_SCHEMA TABLESThis is replaced by MSysObjects and MSysConstrains tables

[a] Data Control Language.

[b] Data Definition Language.

[c] Data Manipulation Language.

Table 2. Supported SQL Server Functionality in SQL Server CE
FunctionalityComments
DDL Databases, tables, data types, indexes, constraintsOnly Unicode character types are supported
DML SELECT, INSERT, UPDATE, DELETE 
Functions Aggregate, Math, DateTime, String, System 
TransactionsThe transaction isolation level is always READ COMMITTED. The limit on nesting is 5. Exclusive lock granularity is table-level and is held for the duration of the transaction. Single-phase commit is the only commit type allowed

ANSI Standard SQL

The specification for the SQL syntax is a standard established by an American National Standards Institute (ANSI) Standards Committee. Both SQL Server and SQL Server CE are highly compliant with the ANSI 92 standard. SQL Server has some options for choosing between ANSI compliance and compliance with older versions of SQL Server. These options are not supported in SQL Server CE; ANSI compliance is the only choice in SQL Server CE.


SQL Server CE is a little fussier about syntax than SQL Server. Having a smaller footprint means having less code that can “deduce” what you meant. Thus, the following SQL, which is missing the comma between the last column definition and the constraint definition that is required by the ANSI 92 specification, executes as intended in SQL Server but produces a syntax error in SQL Server CE:

CREATE TABLE Products
          ( ProductID integer not null primary key
          , ProductName nchar(20) not null
          , CategoryID integer not null
            CONSTRAINT FKProductCategory foreign key (CategoryID)
                                references Categories(CategoryID)
           )

Constraints

There are two primary constraints for databases: primary key and foreign key. The primary key constraint specifies the column or columns of a table in which duplicate values are prohibited and a non-null value is required. The product ID in a product table and the order number in an order table are both examples of primary keys. Foreign keys are values that exist as primary keys elsewhere in the database. For instance, the customer number column in an order table is an example of a foreign key because its value must exist in the customer table also.


Column names can be aliased within a SELECT statement. These aliases propagate into the data objects and onto the bound controls by default. Thus, the following SELECT statement, used to fill a DataTable object bound to a DataGrid control, would provide the results shown in Figure 2:

SELECT P.ProductID as ID
     , P.ProductName as Name
     , C.CategoryName as Category
  FROM Products P
  JOIN Categories C on C.CategoryID = P.CategoryID

Figure 2. A DataGrid Control with Aliased Column Names


The primary reason to use column aliases is to provide column names for calculated columns and literals or to resolve ambiguous names; it is not the preferred way to specify a column title of a DataGrid column. Rather, using the DataGridColumnStyle object, is the preferred way to set DataGrid column names because it is independent of the underlying SELECT statement.

Other  
 
Top 10
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
OPEL MERIVA : Making a grand entrance
FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
BMW 650i COUPE : Sexy retooling of BMW's 6-series
BMW 120d; M135i - Finely tuned
PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
Java Tutorials : Nested For Loop (part 1)
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS