Listing 7.8 SqlCeResultSet C#
//Fetch Data using Result Set
SqlCeCommand myCmd = myConn.CreateCommand();
myCmd.CommandText = "SELECT BookID, Title FROM Books";
myResultSet = myCmd.ExecuteResultSet(ResultSetOptions.Updatable |
ResultSetOptions.Scrollable);
while (myResultSet.Read())
{
listBox1.Items.Add(myResultSet.GetInt32(0).ToString() +
" " + myResultSet.GetString(1));
}
// Insert new record
SqlCeUpdatableRecord myRec = myResultSet.CreateRecord();
myRec.SetString(1, txtTitle.Text);
myResultSet.Insert(myRec);
//Update Record
myResultSet.SetString(1, txtTitle.Text.ToString());
myResultSet.Update();
txtTitle.Text = "";
//Delete Record
myResultSet.Delete();
Listing 7.8 SqlCeResultSet VB.NET
' INSERT RECORD
Dim myRec As SqlCeUpdatableRecord = myResultSet.CreateRecord
myRec.SetString(1, txtTitle.Text)
myResultSet.Insert(myRec)
txtTitle.Text = ""
' UPDATE RECORD
myResultSet.SetString(1, txtTitle.Text.ToString())
myResultSet.Update()
txtTitle.Text = ""
' DELETE RECORD
myResultSet.Delete()
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.
|
CreateDatabase
|
This method creates an empty SQL Server Compact Edition database file.
|
Equals
|
Returns TRUE if the specified object instances are equal.
|
Repair
|
This method repairs a corrupted SQL Server Compact Edition database file.
|
Shrink
|
Similar to compact, Shrink also reclaims the wasted space and moves empty pages toward the end of a file and truncates the file.
|
ToString
|
Returns a string having the name of the component.
|
Verify
|
This method validates the SQL Server Compact Edition database. The Verify method recalculates the checksum for all pages in the database and compares the checksum with the actual value to validate it.
|
Table 7.16 Commonly Used SqlCeEngine Object Public Properties
Method |
Method Description |
LocalConnectionString |
Gets or sets a connection String to the SQL Server Compact Edition database. |
Listing 7.9 SqlCeEngine C#
if (File.Exists("MyDatabase.sdf"))
File.Delete("MyDatabase.sdf");
string myConnectionStr = "Data Source = MyDatabase.sdf;";
SqlCeEngine engine = new SqlCeEngine(myConnectionStr);
engine.CreateDatabase();
Listing 7.9 SqlCeEngine VB.NET
If File.Exists("MyDatabase.sdf") Then
File.Delete("MyDatabase.sdf")
End If
Dim myConnectionStr As String = "Data Source = MyDatabase.sdf;"
Dim myEngine As SqlCeEngine
myEngine = New SqlServerCe.SqlCeEngine()
myEngine.LocalConnectionString = myConnectionStr
myEngine.CreateDatabase()
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.
Table 7.17 Commonly Used SqlCeParameterCollection Object Public Methods
Method |
Method Description |
Add
|
Adds an SqlCeParameter object to anSqlCeCommand object.
|
AddRange
|
Adds an array of SqlCeParameter objects to anSqlCeCommand object.
|
AddWithValue
|
Adds a new SqlCeParameter object to an SqlCeCommand object. The method also sets the value of a new SqlCeParameter object.
|
Clear
|
Removes all SqlCeParameters from SqlCeParameterCollection.
|
Contains
|
Returns TRUE if the SqlCeParameter exists in an SqlCeParameterCollection.
|
CopyTo
|
Copies SqlCeParameter objects from SqlCeParameterCollection to an array.
|
Equals
|
Returns TRUE if specified two object instances are equal.
|
GetType
|
Gets the type of an object instance.
|
IndexOf
|
Gets the index location of a specified SqlCeParameter in an SqlCeParameter collection.
|
Insert
|
Adds the SqlCeParameter in an SqlCeParameterCollection at a specified location.
|
ReferenceEquals
|
Returns TRUE if the specific object instances are the same.
|
Remove
|
Removes the given SqlCeParameter from SqlCeParameterCollection.
|
RemoveAt
|
Removes the SqlCeParameter from a specified position in SqlCeParameterCollection.
|
ToString
|
Returns a string having the name of the object.
|
Table 7.18 Commonly Used SqlCeParameterCollection Object Public Properties
Property |
Property Description |
Count
|
Returns the count of SqlCeParameter in the SqlCeParameterCollection.
|
Item
|
Gets or sets the SqlCeParameter with a given value.
|
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.