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
Method |
Method Description |
Cancel
|
Cancels execution.
|
CreateParameter
|
Creates a new parameter.
|
ExecuteNonQuery
|
Executes the query and returns the number of rows affected.
|
ExecuteReader
|
Builds a Data Reader.
|
ExecuteScalar
|
Executes the query and returns the first column of the first row of the result set.
|
ExecuteXMLReader
|
Builds an XML Reader.
|
Prepare
|
Builds a compiled version of command on data source.
|
ResetCommandTimeout
|
Sets the timeout to its default value.
|
Table 7.4 Commonly Used Command Object Properties
Properties |
Property Description |
CommandText
|
Gets or sets the SQL command that executes on a database.
|
CommandTimeOut
|
Gets or sets the amount of time to wait while executing a command before returning an error.
|
CommandType
|
Gets or sets a value that determines how CommandType is interpreted.
|
|
Textwhen passing a query.
|
|
TableDirectreturns all rows and columns of a table. Command Text is the name of the table.
|
Connection
|
Gets or sets the SqlCeConnection.
|
IndexName
|
Specifies the index to be opened.
|
Parameters
|
Gets the SqlCeParameterCollection.
|
Site
|
Gets or sets the ISite of a component.
|
Transaction
|
Gets or sets the transaction that SqlCeCommand executes.
|
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
Method |
Method Description |
Close
|
Closes the Data Reader object.
|
Equals
|
Returns TRUE if two object instances are equal.
|
GetBoolean
|
Gets the value of a specified column as Boolean.
|
GetByte
|
Gets the value of a specified column as Byte.
|
GetBytes
|
Reads a stream of bytes from a column offset.
|
GetChars
|
Reads a stream of characters from a column offset.
|
GetData
|
Returns a DBDataReader object.
|
GetDataTypeName
|
Gets the name of a data source type
|
GetDateTime
|
Gets the specified column as a DateTime object.
|
GetDecimal
|
Gets the specified column as a Decimal object.
|
GetDouble
|
Gets the specified column as a Double object.
|
GetFieldType
|
Gets the type of the object.
|
GetFloat
|
Gets the specified column as a Float object.
|
GetGuid
|
Gets the specified column as a Globally Unique Identified (GUID).
|
GetInt16
|
Gets the specified column as a 16-bit integer.
|
GetInt32
|
Gets the specified column as a 32-bit integer.
|
GetInt64
|
Gets the specified column as a 64-bit integer.
|
GetName
|
Gets the name of a column.
|
GetOrdinal
|
Gets the ordinal of a column whose name is specified.
|
GetSchemaTable
|
Returns a Data Table specifying the structure of DataReader.
|
GetSqlBinary
|
Gets the value of a given column as SqlBinary.
|
GetSqlBoolean
|
Gets the value of a given column as SqlBoolean.
|
GetSqlByte
|
Gets the value of a given column as SqlByte.
|
GetSqlDateTime
|
Gets the value of a given column as SqlDateTime.
|
GetSqlDecimal
|
Gets the value of a given column as SqlDecimal.
|
GetSqlDouble
|
Gets the value of a given column as SqlDouble.
|
GetSqlGuid
|
Gets the value of a given column as SqlGuid.
|
GetSqlInt16
|
Gets the value of a given column as SqlInt16.
|
GetSqlInt32
|
Gets the value of a given column as SqlInt32.
|
GetSqlInt64
|
Gets the value of a given column as SqlInt64.
|
GetSqlMoney
|
Gets the value of a given column as SqlMoney.
|
GetSqlSingle
|
Gets the value of a given column as SqlSingle.
|
GetSqlString
|
Gets the value of a given column as SqlString.
|
GetType
|
Gets the type of a current instance.
|
GetValue
|
Gets the value of a column.
|
IsDBNull
|
Gets the value to indicate whether a column contains nonexistent value.
|
Read
|
Advances the SqlCeDataReader to the next row.
|
Seek
|
Puts the SqlCeDataReader to a given record whose index is specified.
|
ToString
|
Converts the object to String and returns the string.
|
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
Properties |
Property Description |
Depth
|
Indicates the depth of nesting for the current row.
|
FieldCount
|
Gets the number of columns in a current row.
|
HasRows
|
Gets a value to indicate whether Data Reader contains at least one row.
|
HiddenFieldCount
|
Gets the number of a hidden field.
|
IsClosed
|
Indicates whether Data Reader is closed.
|
Item
|
Gets the value of a column in its native format.
|
RecordsEffected
|
Gets number of rows affected by insert, delete, update operation.
|
VisibleFieldCount
|
Gets number of fields that are not hidden.
|
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.