Programming SQL Server 2005 Compact Edition with ADO - Page 2

Listing 7.3 Command Object C#

SqlCeCommand myCmd = myConnection.CreateCommand();

Listing 7.3 Command Object VB.NET

Dim myCmd As SqlCeCommand = myConn.CreateCommand()

Commonly used command object methods are listed in Table 7.3.

Command Object Properties

The data command commonly used properties are described in Table 7.4. The CommandText property contains the actual text of the SQL command to be executed. The Connection property contains a reference to a connection object. The Parameters property contains a collection of parameters required for the SQL command. The Command object properties will be validated for syntax errors. Final validation occurs when a command is executed by a data source.

Table 7.3 Commonly Used Command Object Methods

Table 7.4 Commonly Used Command Object Properties

---

NOTE - SQL Server Compact Edition Data Provider does not support batched queries.

---

ExecuteReader, ExecuteScalar, and ExecuteNonQuery are the most import methods to execute an SQL command against a connection object.

The following section discusses the SqlCeDataReader class. Using Data Reader the above methods can be executed.

Data Reader

SQL Server Compact Edition provides an SqlCeDataReader class to implement Data Reader. A Data Reader is like a forward-only cursor for reading a data source. To use Data Reader, you need to create an SqlCeDataReader object and execute an ExecuteReader method of the object. To retrieve the rows, use the read method in a loop. Each iteration in a loop will read a row.

Listing 7.4 opens an SQLCeConnection. It creates a command. A command type property is set to TableDirect, and the CommandText property is set to a table name. A TableDirect command is similar to a Select statement that returns all rows of a table.

When you define a CommandType property to TableDirect, you use a base table cursor. You do not use a base table cursor with a Select statement. Instead, you can use it with Data Reader and a result set by setting the CommandType property to TableDirect.

The code creates the Data Reader by the ExecuteReader method while the loop reads the rows and populates the listbox.

Listing 7.4 Data Reader C#

// Code 
SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Program Files\\DataReader\\Northwind.sdf");
myConnection.Open();
SqlCeCommand myCmd = myConnection.CreateCommand();
SqlCeDataReader myDataReader ;
myCmd.CommandText = "Products";
myCmd.CommandType = CommandType.TableDirect;
myDataReader = myCmd.ExecuteReader();
while (myDataReader.Read()){
listbox1.Items.Add(myDataReader.GetValue(0));
}
myDataReader.Close();
}

Listing 7.4 Data Reader VB.NET

Dim myConnection As SqlCeConnection
myConnection = New SqlCeConnection("Data Source=\Program Files\DataReader\Northwind.sdf;")
myConnection.Open()
Dim myCmd As SqlCeCommand = myConnection.CreateCommand()
myCmd.CommandText = "Products"
myCmd.CommandType = CommandType.TableDirect
Dim myDataReader As SqlCeDataReader = myCmd.ExecuteReader()
While myDataReader.Read()
   listbox1.Items.Add(myDataReader.GetValue(0))
End While
myDataReader.Close()
myConnection.Close()

The method exposed by Data Reader is shown in Table 7.5.

The Open method is used to open the DataReader. The DataReader is positioned at the beginning of a result set, before the first row. The Read method reads the row from the result. Any subsequent Read method will read the next row from the result set.

Table 7.5 Commonly Used Data Reader Object Methods

---

NOTE - The SQL Server Compact Edition Data Provider does not support the GetChar and Next ResultSet methods. These methods are supported by the SQL Server Data Provider.

---

Table 7.6 lists the SqlCeDataReader object properties.

Table 7.6 Commonly used Data Reader Object Properties

Data Adapter

Data Adapter sits between the Connection object and DataSet and provides a gateway between the SQL Server Compact Edition Connection object and the DataSet object. Data Adapter receives the data from the Connection object and passes it to DataSet. After updates are done, it passes the changes back from the DataSet to the connection that updates the data in the database. Instead of looping through a set of rows with Data Reader, Data Adapter fills the DataSet with data.

DataAdapter contains four command objects:

• SelectCommand

• UpdateCommand

• InsertCommand

• DeleteCommand

SelectCommand is used to fill a DataSet. The other three command objects are used to transmit changes back to the data source.

You can create the Data Adapter at design time and at runtime.

Listing 7.5 demonstrates the usage of DataAdapter. The listing creates a command. It creates DataAdapter and DataSet. The Fill method loads the data from a database into DataTables of a DataSet.