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