Retrieving Data by Using SqlCeDataReader

Retrieving Data by Using `SqlCeDataReader`

Data can be retrieved from a SQL Server CE database by using the SqlCeDataReader class. The SqlCeDataReader class provides fast, forward-only access to rows of data.

The steps to retrieve data by using the SqlCeDataReader are similar to the steps required to run the INSERT SQL command:

We create an instance of the SqlCeConnection . The SqlCeDataReader will use this connection to retrieve the requested data rows.
A SqlCeCommand object should be created with the appropriate SELECT statement.
Set the command's command type, and call the SqlCeCommand.ExecuteReader method.

The ExecuteReader method executes the command text against the database by using the SqlCeConnection . A SqlCeDataReader that will provide access to the return data is then returned. The SqlCeConnection will be busy serving the SqlCeDataReader until the reader is closed.

The ExecuteReader method takes one parameter of the CommandBehavior type. The CommandBehavior type is an enumeration that the SqlCeCommand uses; it helps determine what results are returned and how the SqlCeConnection is left after the command executes. Table 7.8 lists all of the CommandBehavior member values and their descriptions.

Table 7.8. `CommandBehavior` Values

MEMBER NAME	DESCRIPTION
`CloseConnection`	The connection is closed after the data reader is closed.
`Default`	The query may return multiple result sets.
`KeyInfo`	The query returns column and primary key information. The query is executed without any locking on the selected rows.
`SchemaOnly`	The query returns column information only.
`SequentialAccess`	The query provides a way for the `DataReader` to handle rows containing columns of large binary values.
`SingleResult`	The query returns a single result set.
`SingleRow`	The query is expected to return a single row. It is possible to specify `SingleRow` when executing queries that return multiple result sets. In that case, multiple result sets are still returned, but each result set has a single row.

Once the SqlCeDataReader has been returned from the call to ExecuteReader , it is ready to retrieve data. The SqlCeDataReader.Read method advances the reader to the next record. Initially, the SqlCeDataReader is positioned directly before the first record. So, a call to Read is required before any data can be retrieved. The Read method will return true until the SqlCeDataReader reaches the end of result set. It then returns false.

Once you are positioned on a data row, you can use one of SqlCeDataReader 's Get XXX methods to access the columns within each data row. For instance, the GetInt32 method retrieves an Int32 value from a column in the SqlCeDataReader 's current row. The GetInt32 method takes a parameter of type int . This parameter represents the column's ordinal number. If the column's ordinal is unknown at design time, you can use the GetOrdinal method to look up the column's ordinal by column name. Listing 7.6 demonstrates how to retrieve all of the package information from the Package table.

Listing 7.6 Retrieving all of the package information from the `Package` table

 C# public static void GetAllPackageInfo() {   string pckgStr =     "Package Data\nID: {0}\nCode: {1}\nDestination: {2}";   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     string dmlPackageInfo = "SELECT * FROM Package";     SqlCeCommand cmdGetPackageInfo = null;     SqlCeDataReader drPackageInfo = null;     try {       cmdGetPackageInfo = new SqlCeCommand(dmlPackageInfo, conn);       cmdGetPackageInfo.CommandType = CommandType.Text;       drPackageInfo =         cmdGetPackageInfo.ExecuteReader(CommandBehavior.Default);       while(drPackageInfo.Read()) {         System.Windows.Forms.MessageBox.Show(           string.Format(pckgStr,           drPackageInfo.GetInt32(0),           drPackageInfo.GetString(1),           drPackageInfo.GetString(2)));       }     } catch(SqlCeException scee) {       for(int curExNdx = 0; curExNdx < scee.Errors.Count; ++curExNdx) {         System.Windows.Forms.MessageBox.Show(           "Error:"+ scee.Errors[curExNdx].ToString()+"\n");       }     } finally {       if( cmdGetPackageInfo != null )         cmdGetPackageInfo.Dispose();       if( drPackageInfo != null )         drPackageInfo.Close();     }   } } VB Sub InsertNewPackage(ByVal pckgCode As String, ByVal destID As String)   Dim connstr As String   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim dmlInsertPackage As String   dmlInsertPackage = _     "INSERT INTO Package(Code, DestinationID) " & _     "VALUES ('" + pckgCode + "', '" + destID + "')"   Dim cmdInsertPackage As SqlCeCommand   cmdInsertPackage = New SqlCeCommand(dmlInsertPackage, conn)   Try     cmdInsertPackage = New SqlCeCommand(dmlInsertPackage, conn)     cmdInsertPackage.CommandType = CommandType.Text     cmdInsertPackage.ExecuteNonQuery()   Catch scee As SqlCeException     Dim curNdx As Int32     For curNdx = 0 To scee.Errors.Count       MessageBox.Show("Error:" & scee.Errors(curNdx).ToString())     Next   Finally     If Not cmdInsertPackage Is Nothing Then       cmdInsertPackage.Dispose()     End If   End Try   conn.Close() End Sub

SHOP TALK: OPTIMIZING `SELECT` QUERIES

When executing SELECT queries against a SQL Server CE database, you can get the fastest performance by using a SqlCommand with the CommandType property set to CommandType.TableDirect . TableDirect commands do not need to be compiled by the SQL Server CE Engine. Instead, a pointer into the table is created and maintained while the SqlCeDataReader moves through the results. This results in fast reads from the SQL Server CE database.

When creating a TableDirect command, the SqlCeCommand 's CommantText property must be set to the name of the table to be accessed. All rows and columns of the named table will be returned when you call one of the Execute methods. The following code demonstrates how to create TableDirect SqlCeCommand linked to the Package table.

 C# string connstr = @"Data Source=\My Documents\PTSystem.sdf"; SqlCeCommand cmdGetPackageInfo = null; SqlCeDataReader drPackageInfo = null; using(SqlCeConnection conn = new SqlCeConnection(connstr)) {   conn.Open();   cmdGetPackageInfo = new SqlCeCommand("Package", conn);   cmdGetPackageInfo.CommandType = CommandType.TableDirect;   drPackageInfo = cmdGetPackageInfo.ExecuteReader();   // Use the reader to get the package data. } VB Dim connstr As String connstr = "Data Source=\My Documents\PTSystem.sdf" Dim conn As SqlCeConnection conn = New SqlCeConnection(connstr) conn.Open() Dim cmdGetPackageInfo As SqlCeCommand Dim drPackageInfo As SqlCeDataReader cmdGetPackageInfo = Nothing drPackageInfo = Nothing cmdGetPackageInfo = New SqlCeCommand("Package", conn) cmdGetPackageInfo.CommandType = CommandType.TableDirect drPackageInfo = _   cmdGetPackageInfo.ExecuteReader(CommandBehavior.Default) ' Use the reader to get the package data

TableDirect queries can also be used to access multiple tables. The CommandText property should contain a comma-delimited list of table names without spaces or padding. When accessing multiple tables, a join of the specified tables is returned.

Using Parameterized SQL Commands

The SELECT statement used in Listing 7.5 was extremely simple. In practice your SELECT statements will most likely contain WHERE clauses that will help target specific data rows. For example, we can use the WHERE clause to select a specific package in the Package table. An example of the SELECT query follows :

 SELECT * FROM Package WHERE ID = "0987654321"

This SELECT query retrieves the package whose ID column contains the value 0987654321. Because the ID column is the primary key, the record set will contain zero or one data row. You can specify the ID at runtime by using string.Format to parameterize the SELECT statement. A better way to accomplish command parameterization is to use the SqlCeParameter objects. First, the SELECT command changes to the following format:

 SELECT * FROM Package WHERE ID = ?

Now, create a SqlCeCommand object with the preceding command. The SqlCeCommand object provides a Parameters property that is a collection of all parameters to the command. Add a parameter to the list that corresponds to the ID and then call the SqlCeCommand.Prepare method. The Prepare method compiles the statement against the database. This allows the command to be executed faster if it is used more than once. Finally, execute the command against the database as before. Listing 7.7 exemplifies the steps described in this section.

Listing 7.7 Executing a parameterized SQL command

 C# public static void GetPackageInfo(int pckgID) {   string pckgStr =     "Package Data\nID: {0}\nCode: {1}\nDestination: {2}";   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     string dmlPackageInfo = "SELECT * FROM Package WHERE ID = ?";     SqlCeCommand cmdGetPackageInfo = null;     SqlCeDataReader drPackageInfo = null;     try {       cmdGetPackageInfo = new SqlCeCommand(dmlPackageInfo, conn);       cmdGetPackageInfo.CommandType = CommandType.Text;       cmdGetPackageInfo.Parameters.Add("ID", pckgID);       cmdGetPackageInfo.Prepare();       drPackageInfo =         cmdGetPackageInfo.ExecuteReader(CommandBehavior.SingleRow);       while(drPackageInfo.Read()) {         System.Windows.Forms.MessageBox.Show(           string.Format(pckgStr,           drPackageInfo.GetInt32(0),           drPackageInfo.GetString(1),           drPackageInfo.GetString(2)));       }     } catch(SqlCeException scee) {       for(int curExNdx = 0; curExNdx < scee.Errors.Count; ++curExNdx) {         System.Windows.Forms.MessageBox.Show(           "Error:"+ scee.Errors[curExNdx].ToString()+"\n");       }     } finally {       if( cmdGetPackageInfo != null )         cmdGetPackageInfo.Dispose();       if( drPackageInfo != null )         drPackageInfo.Close();     }   } } VB Sub GetPackageInfo(ByVal pckgID As Int32)   Dim connstr As String   Dim pckgStr As String   pckgStr = "Package Data\nID: {0}\nCode: {1}\nDestination: {2}"   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim dmlPackageInfo As String   Dim cmdGetPackageInfo As SqlCeCommand   Dim drPackageInfo As SqlCeDataReader   dmlPackageInfo = "SELECT * FROM Package WHERE ID = ?"   cmdGetPackageInfo = Nothing   drPackageInfo = Nothing   Try     cmdGetPackageInfo = New SqlCeCommand(dmlPackageInfo, conn)     cmdGetPackageInfo.CommandType = CommandType.Text     cmdGetPackageInfo.Parameters.Add("ID", pckgID)     cmdGetPackageInfo.Prepare()     drPackageInfo = _       cmdGetPackageInfo.ExecuteReader(CommandBehavior.SingleRow)     While (drPackageInfo.Read())       System.Windows.Forms.MessageBox.Show( _         String.Format(pckgStr, _         drPackageInfo.GetInt32(0), _         drPackageInfo.GetString(1), _         drPackageInfo.GetString(2)))     End While   Catch scee As SqlCeException     Dim curExNdx As Int32     For curExNdx = 0 To scee.Errors.Count       System.Windows.Forms.MessageBox.Show( _         "Error:" & scee.Errors(curExNdx).ToString())     Next   Finally     If Not cmdGetPackageInfo Is Nothing Then       cmdGetPackageInfo.Dispose()     End If     If Not drPackageInfo Is Nothing Then       drPackageInfo.Close()     End If   End Try End Sub

Parameterized queries can be used in almost all SQL statements, DDL and DML. It is also possible to use more than one parameter in the query. For example, the following query can be used to SELECT and package with a parameterized code or destination ID.

 SELECT * FROM Package WHERE Code = ? OR DestinationID = ?

When using the SELECT statement with multiple parameters, you must add the SqlCeParameters objects to the Parameters collection in the order that the question marks appear from left to right. The preceding example would require the Code parameter to be inserted into the Parameters collection before the DestinationID was inserted. After adding the parameters to the Parameters collection, they can no longer be referenced by name. So, you must ensure that they are inserted in the proper order.

Parameterized queries are a great way to increase the performance of a query that must be executed more then once. You must take care to change the value of the parameters after each call to prepare. Listing 7.8 demonstrates how this should be done.

Listing 7.8 Executing a SQL command with multiple parameters

 C# public static void GetPackageInfo(int[] pckgID) {   string pckgStr =     "Package Data\nID: {0}\nCode: {1}\nDestination: {2}";   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     string dmlPackageInfo = "SELECT * FROM Package WHERE ID = ?";     SqlCeCommand cmdGetPackageInfo = null;     SqlCeDataReader drPackageInfo = null;     try {       cmdGetPackageInfo = new SqlCeCommand(dmlPackageInfo, conn);       cmdGetPackageInfo.CommandType = CommandType.Text;       cmdGetPackageInfo.Parameters.Add("ID", SqlDbType.Int);       cmdGetPackageInfo.Prepare();       for(int pckgNdx = 0; pckgNdx < pckgID.Length; ++pckgNdx) {         cmdGetPackageInfo.Parameters[0].Value = pckgID[pckgNdx];         try {           drPackageInfo =             cmdGetPackageInfo.ExecuteReader(CommandBehavior.SingleRow);           while(drPackageInfo.Read()) {             System.Windows.Forms.MessageBox.Show(               string.Format(pckgStr,               drPackageInfo.GetInt32(0),               drPackageInfo.GetString(1),               drPackageInfo.GetString(2)));           }         } catch(SqlCeException scee) {           for(int curExNdx=0;curExNdx<scee.Errors.Count;++curExNdx) {             System.Windows.Forms.MessageBox.Show(               "Error:"+ scee.Errors[curExNdx].ToString()+"\n");           }         } finally {           if( drPackageInfo != null )             drPackageInfo.Close();         }       }     } finally {       if( cmdGetPackageInfo != null )         cmdGetPackageInfo.Dispose();     }   } } VB Sub GetPackageInfo(ByVal pckgID As Int32())   Dim connstr As String   Dim pckgStr As String   pckgStr = "Package Data\nID: {0}\nCode: {1}\nDestination: {2}"   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   Dim dmlPackageInfo As String   Dim cmdGetPackageInfo As SqlCeCommand   Dim drPackageInfo As SqlCeDataReader   dmlPackageInfo = "SELECT * FROM Package WHERE ID = ?"   cmdGetPackageInfo = Nothing   drPackageInfo = Nothing   Try     cmdGetPackageInfo = New SqlCeCommand(dmlPackageInfo, conn)     cmdGetPackageInfo.CommandType = CommandType.Text     cmdGetPackageInfo.Parameters.Add("ID", SqlDbType.Int)     cmdGetPackageInfo.Prepare()     Dim pckgNdx As Int32     For pckgNdx = 0 To pckgID.Length       cmdGetPackageInfo.Parameters(0).Value = pckgID(pckgNdx)       Try         drPackageInfo = _           cmdGetPackageInfo.ExecuteReader(CommandBehavior.SingleRow)         While (drPackageInfo.Read())           System.Windows.Forms.MessageBox.Show( _             String.Format(pckgStr, _             drPackageInfo.GetInt32(0), _             drPackageInfo.GetString(1), _             drPackageInfo.GetString(2)))           End While       Catch scee As SqlCeException         Dim curExNdx As Int32         For curExNdx = 0 To scee.Errors.Count           System.Windows.Forms.MessageBox.Show( _             "Error:" & scee.Errors(curExNdx).ToString())         Next       Finally         If Not drPackageInfo Is Nothing Then           drPackageInfo.Close()         End If       End Try     Next   Finally     If Not cmdGetPackageInfo Is Nothing Then       cmdGetPackageInfo.Dispose()     End If   End Try   Conn.Close() End Sub