Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 21, 2007

Programming SQL Server 2005 Compact Edition with ADO - Page 2

By DatabaseJournal.com Staff

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.

 

Text—when passing a query.

 

TableDirect—returns 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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM