Retrieving Data by Using SqlCeDataReaderData 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:
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
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 tableC# 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
Using Parameterized SQL CommandsThe 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 commandC# 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 parametersC# 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 |