SqlCeUpdateableRecord

You will use SQLCeUpdateableRecord with SqlCeResultSet. The SqlCeUpdateableRecord object represents a row. The SqlCeResultSet consists of one or more SqlCeUpdateableRecord objects. Table 7.13 shows the methods exposed by SqlCeUpdatableRecord. Table 7.14 describes the properties exposed by SqlCeUpdatableRecord.

Table 7.13 Commonly Used SqlCeUpdatable Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified two object instances are equal.

GetFieldType

Returns the CLR data type for a given field.

GetOrdinal

Return the ordinal for a given field.

GetType

Gets the type of a current instance.

GetValue

Returns the value of a given record.

GetValues

Returns the value of all fields in a record.

IsDBNull

Returns TRUE if field is NULL.

IsSetAsDefault

Returns TRUE if a given field is marked to use the Default value.

ReferenceEquals

Returns TRUE if a specified instance is the same as a given instance.

ToString

Returns a string having the name of the object.

Table 7.14 Commonly Used SqlCeUpdatableRecord Public Properties

Property

Property Description

FieldCount

Number of fields in a record.

Updatable

Returns TRUE if field is updatable.

SqlCeEngine

The SqlCeEngine class represents the SQL Server Engine object. This class cannot be inherited. You can use this class to create an instance of an SQL Server Compact Edition database. You can also use this class to Compact, Repair or Shrink SQL Server Compact Edition Database programmatically. Table 7.15 and Table 7.16 provide details of SqlCeEngine method and properties.

Table 7.15 Commonly Used SqlCeEngine Object Public Methods

Method

Method Description

Compact

This method reclaims the wasted space created by fragmentation. You should have a temporary space available to compact the database. The Compact mechanism creates a new database file and copies the database content to a new file.

Understanding Parameterized Queries

SQL Server Compact Edition supports parameterized queries. Parameterized queries give better performance as the queries are compiled once. Each time you execute an SQL Statement through ADO.NET, SQL Server Compact Edition will create a query plan. If you are executing the same query continuously with varying parameter values, you should consider using SqlCeParameterCollection. Parameterized queries also help protect against Sql Injections. You will create an SqlCeParameterCollection and add various parameters using the SqlCeParameter class. Parameterized statements will compile the query once and execute the same compiled plan on each execution.

Note - Preparing a command has overhead. If a statement is executed only once, there is no need to prepare it.

ExecuteScalar, ExecuteReader, or ExecuteNonQuery commands can be prepared.

SqlCeParameterCollection

The SqlCeParameterCollection object contains all SqlCeParameters and their respective mappings for SqlCeCommand. The number of parameter placed in SqlCeCommand should match with the parameters in the parameters collection.

SqlCeParameter

Use the SqlCeParameter class to create an instance of parameters to be used in SQL Server Compact Edition queries. You can populate the SQLCeParameter object associated with the SqlCeCommand.

First define the parameters in an SQL query with a parameter name. Table 7.19 and Table 7.20 define the SqlCeParameter object methods and properties. Then use the Add method of the parameter property to add the value of the parameter. The Add method accepts the name of the parameter and the value of the parameter. Add values of all parameters one by one.

Table 7.19 Commonly Used SqlCeParameter Object Public Methods

Method

Method Description

Equals

Returns TRUE if the specified two object instances are equal.

GetType

Gives the type of a current instance.

ReferenceEquals

Returns TRUE if the specified instance is the same as the given instance.

ResetDbType

Resets the type of SqlCeParameter.

ToString

Returns a string having the name of the component.

Use the Prepare method of SqlCeCommand to prepare the execution plan. Finally, execute the query.

Table 7.20 Commonly Used SqlCeParameter Object Public Properties

Property

Property Description

DbType

Gets and sets the DbType of the parameter.

IsNullable

Gets or sets a value to specify whether a parameter can have NULL values.

ParameterName

Gets or sets the name of the SqlCeParameter.

Precision

Gets or sets the maximum number of digits to represent Value Property.

Scale

Gets or sets the number of decimal places.

Size

Gets or sets the maximum data length.

SourceColumn

Gets or sets the source column corresponding to DataSet.

SourceVersion

Gets or sets the version of row. The possible values are Current, Default, Original, and Proposed.

SqlDbType

Gets or sets the SqlDbType parameter.

Value

Gets or sets the parameter value.

Note - The SQLCeParameter class cannot be inherited.

SQL Server CE 2.0 does not support named parameters. Named parameter support is added to SQL Server Compact Edition 3.x.

The same command object needs to be used for running all queries. If not, Destroy the Object and Create New Object queries will be compiled again.

Listing 7.10 describes the usage of a parameterized query. The code opens a connection and creates a command object. The code sets the CommandText property with SQL statements. Instead of defining the actual values, you define the parameters in command text. Then the code adds values to the parameter collection using the Add method. In conclusion, you prepare the command and execute the query.