Programming SQL Server 2005 Compact Edition with ADO - Page 5

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

Table 7.14 Commonly Used SqlCeUpdatableRecord Public Properties

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

Table 7.16 Commonly Used SqlCeEngine Object Public Properties

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

Table 7.18 Commonly Used SqlCeParameterCollection Object Public Properties

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

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

Table 7.20 Commonly Used SqlCeParameter Object Public Properties

---

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.