OLE DB Commands (SQL Server Compact)

  • Article

In OLE DB, a command object is used to execute provider-specific text commands. These are similar to SQL statements.

Using Commands

The basic steps for using commands in OLE DB are:

  1. A command object is created from an existing session object using IDBCreateCommand::CreateCommand.

  2. The command text for the query is specified using ICommandText.

    The command syntax supported by the OLE DB Provider for Microsoft SQL Server Compact 4.0 is specified as DBGUID_SQL. DBGUID_SQL syntax is primarily SQL-92 syntax with ODBC escape sequences.

  3. The command is executed using ICommand::Execute.

SQL Server Compact 4.0 supports a subset of the SQL Server Transact-SQL query grammar. Queries that can typically be run on SQL Server Compact 4.0 can also be run on SQL Server. However, many of the features of Transact-SQL are absent from SQL Server Compact 4.0 and only a single SQL statement can be executed in a command.

SQL Server Compact 4.0 supports parameterized queries in which parameters are delimited with the question mark (?) character. It also supports named parameterized queries.

For more information about the query grammar supported by SQL Server Compact 4.0, see SQL Reference (SQL Server Compact).

Preparing Commands

To execute a query, the Database Engine must first parse, compile, and optimize the SQL statement. Frequently, this work can be completed one time if the command is to be executed multiple times, potentially saving time. If clients expect to run a query more than one time, we recommend that the command be prepared one time. Then, call Execute multiple times. This should maximize performance by avoiding query recompilation. Commands can be prepared before they are executed by calling ICommandPrepare::Prepare. This is equivalent to compiling the command.

Examples

For an example of creating and executing commands using the OLE DB provider for SQL Server Compact 4.0, see the example in OLE DB Parameters (SQL Server Compact).