DATABASE

ADO.NET Programming : Microsoft SQL Server CE (part 3) - Retrieving and Displaying Data

2/19/2013 6:46:51 PM

6. Retrieving and Displaying Data

Now that we have populated our SQL Server CE database with some data, we’ll look at how to display that data to the user. We start with the two-tier approach, which uses the SqlCeDataReader class, and then move on to the three-tiered approach based on the DataSet class.

6.1. The SqlCeDataReader Class

The SqlCeDataReader class allows your program to access the rows returned by a SQL query. It provides read-only, forward-only access to one row at a time. Once positioned at a row, you use methods of the DataReader class to access the information located in the individual fields of that row. Many of these methods tend to be very data-specific, and those whose names begin with the word Get treat the fields as a zero-based array of data. Thus, if the underlying SELECT statement were SELECT ProductID, ProductName FROM Products, the call to retrieve the ProductID field (field 0) from the DataReader object’s current row would be one of the following lines of code:

intVar = drdrDataReader.GetInt32(0);

//  or the slightly less efficient

intVar = drdrDataReader.GetValue(0);

//  or

intVar = drdrDataReader.Item["ProductID"];

Similarly, the call to retrieve the ProductName field would be one of the following lines of code:

strVar = drdrDataReader.GetString(1);

//  or

strVar = drdrDataReader.GetValue(1);

//  or

strVar = drdrDataReader.Item["ProductName"];

You cannot create a DataReader object directly; you must use the SqlCeCommand object’s ExecuteReader method to create the DataReader object. When first created, the DataReader object is open but not yet positioned at a row. You have two ways to position it to a row.

The first is to use the DataReader object’s Read method to move sequentially through the rows. The second is to use the DataReader object’s Seek method to position to a specific row. The Seek method requires the following conditions.

  • The underlying database table is indexed.

  • Additional properties of the SqlCeCommand object are set before executing the ExecuteReader method.

  • The index name and key value(s) are specified in the call to the Seek method.

  • The call to the Seek method is followed by a call to the Read method.

The advantage of using the Seek method is performance. In fact, performance is the very reason why the .NET Compact Framework development team added the Seek method to the product beginning with Version 1.1. Using the Seek method is almost always faster than using the WHERE clause of a SELECT statement for locating specific rows. The disadvantage is that the Seek method provides access to only one table at a time, and knowledge of the index is required. The former means you cannot combine the Seek method with the joining of two or more tables; the latter goes against the very grain of relational database theory. Of course, many programmers have gone against the grain when it was the faster way to go.

To illustrate both the Read and Seek methods, the following sample application, shown in Figure 6 and located in the DataReader project on the Web site, loads a ComboBox control with product key values. When the user clicks on the ComboBox control, the fields of the selected product are displayed in the TextBox controls.

Figure 6. The DataReader Program


Listing 4 shows the code for this application.

Listing 4. Source Code for the DataReader Program
using System;
using System.IO;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;

namespace DataReader
{
   /// <summary>
   /// Summary description for FormMain
   /// </summary>
   public class FormMain : System.Windows.Forms.Form
   {
     internal System.Windows.Forms.Label lblCategoryName;
     internal System.Windows.Forms.Label lblProductName;
     internal System.Windows.Forms.Label lblProductID;
     internal System.Windows.Forms.TextBox textCategoryName;
     internal System.Windows.Forms.TextBox textProductName;
     internal System.Windows.Forms.TextBox textProductID;
     internal System.Windows.Forms.ComboBox comboKeys;

     public FormMain()
       {
         //
         // Required for Windows Form Designer support
         //
         InitializeComponent();
      }
      /// <summary>
      /// Clean up any resources being used.
      /// </summary>
      protected override void Dispose( bool disposing )
      {
         base.Dispose( disposing );
      }
      #region Windows Form Designer generated code


      /// <summary>
      /// The main entry point for the application
      /// </summary>

      static void Main()
      {
         Application.Run(new FormMain());
      }


      //  File path and name
      private string  strFile = @"My Documents\ourProduceCo.sdf";

      //  Connection string
      private string  strConn = "Data Source=" +
                                @"My Documents\ourProduceCo.sdf";

      //  Select product keys.
      private string strGetProductIDs =
         " SELECT ProductID " +      "   FROM Products ";

      //  Select one product, joined with its category.
      private string strGetOneProduct =
         " SELECT ProductID, ProductName, CategoryName " +
         "   FROM Products P " +
         "   JOIN Categories C on C.CategoryID = P.CategoryID " +
         "  WHERE P.ProductID = ";

      //  Used to bypass the SelectIndexChanged event
      //     during the loading of the ComboBox
      private bool boolLoading = true;


      private void FormMain_Load(object sender, EventArgs e)
      {
         //  Display a close box.
         this.MinimizeBox = false;

         //  Let the form present itself.
         Application.DoEvents();

         //  Ensure that the database exists.
         if (! File.Exists(strFile) )
         {
            MessageBox.Show(
               "Database not found.  Run the CreateDatabase " +
               "program for this chapter first.  Then run " +
               "this program.");
         }

         //  Load product keys into the ComboBox
         //     and select the first one.
         LoadProductIDs();
         comboKeys.SelectedIndex = 0;
      }

      private void comboKeys_SelectedIndexChanged(object sender,
                                                  EventArgs e)
      {
         //  A product key has been selected; retrieve
         //     and display the corresponding product.
         if (! boolLoading )
         {
            LoadOneProduct((int)comboKeys.SelectedItem);
         }
      }

      private void textProductName_Validated(object sender,
                                             EventArgs e)
      {
         //  Update this product row in the database.
         UpdateSelectedRow(int.Parse(textProductID.Text),
                           textProductName.Text);
      }

      private void LoadProductIDs()
      {
         //  Clear the ComboBox.
         comboKeys.Items.Clear();

         //  A connection, a command, and a reader
         SqlCeConnection connDB =
            new SqlCeConnection(strConn);
         SqlCeCommand cmndDB =
            new SqlCeCommand(strGetProductIDs, connDB);
         SqlCeDataReader drdrDB;

         //  Open the connection.
         connDB.Open();

         //  Submit the SQL statement and receive
         //     the SqlCeReader for the results set.
         drdrDB = cmndDB.ExecuteReader();

         //  Read each row.  Add the contents of its
         //     only column as an entry in the ComboBox.
         //     Close the reader when done.
         while ( drdrDB.Read() )
         {
            comboKeys.Items.Add(drdrDB["ProductID"]);
         }
         drdrDB.Close();

         //  Close the connection.
         connDB.Close();

         //  Start responding to ComboBox's
         //     SelectedIndexChanged events.
         this.boolLoading = false;
      }

      private void LoadOneProduct( int intProductID)
      {
         //  A connection, a command, and a reader
         SqlCeConnection  connDB = new SqlCeConnection(strConn);
         SqlCommand  cmndDB = new SqlCommand(strSQL,connDB);
         SqlCeDataReader drdrDB;

         //  Open the connection.
         connDB.Open();

         //  Set the Command object to retrieve
         //     rows from one table via one index.
         //  Then retrieve the reader.
         cmndDB.Connection = connDB;
         cmndDB.CommandType = CommandType.TableDirect;
         cmndDB.CommandText = "Products";
         cmndDB.IndexName = "PKProducts";
         drdrDB = cmndDB.ExecuteReader();

         //  Retrieve the first (only) row from the
         //     Products table that has the ProductID
         //     that was selected in the ComboBox.
         //  Load the fields into the form's controls.
         //  Close the reader.
         drdrDB.Seek(DbSeekOptions.FirstEqual, intProductID);
            if( drdrDB.Read() )
            {
               LoadControlsFromRow(drdrDB);
            }
         drdrDB.Close();

         //  Close the connection.
         connDB.Close();
      }


      private void LoadControlsFromRow(  SqlCeDataReader drdrDB)
      {
         //  Transfer the column titles and the field
         //     contents of the current row from the
         //     reader to the form's controls.
         lblProductID.Text = drdrDB.GetName(0);
         textProductID.Text = drdrDB.GetValue(0).ToString();
         lblProductName.Text = drdrDB.GetName(1);
         textProductName.Text = drdrDB.GetValue(1).ToString();
         lblCategoryName.Text = drdrDB.GetName(2);
         textCategoryName.Text = drdrDB.GetValue(2).ToString();
      }

      private void UpdateSelectedRow(int intProductID,
                                    string strProductName)
      {
         //  A connection and a command
         SqlCeConnection connDB = new SqlCeConnection(strConn);
         SqlCeCommand cmndDB = new SqlCeCommand();

         //  Open the connection.
         connDB.Open();

         //  Update the product name for the selected product.
         cmndDB.Connection = connDB;
         cmndDB.CommandText =
            " UPDATE Products " +
            " SET ProductName = " + "'" + strProductName + "'" +
            " WHERE ProductID = " + intProductID;
         cmndDB.ExecuteNonQuery();

         //  Close the connection.
         connDB.Close();
      }
   }
}

					  

The LoadProductIDs routine submits a SELECT statement and uses a DataReader object to process all the rows retrieved by that SELECT statement. Note that the properties of the SqlCeCommand object must be initialized before a DataReader object can be created. Because the reader is not positioned to the first row when created, the Read method must be called at the start of the iterative loop, not at the end of the loop. In general, this makes for cleaner code and is a definite improvement over past ADO classes.

The LoadOneProduct routine uses the Seek method followed by the Read method to retrieve a single row from the database. Note also that a call to the Read method must follow the call to the Seek method. Without this call to the Read method, no row would be in the reader. Although there would be no row in the reader if the Read method call were missing, no exception would be raised. From ADO.NET’s point of view, nothing has gone wrong; you simply have an empty reader.

As you can see from the output shown earlier in Figure 6.12, all the columns came from the Products table, a built-in limitation of the Seek method. Listing 5 shows an alternative version of the LoadOneProduct method. It uses a WHERE clause rather than a Seek method index to specify the desired product. As the output in Figure 7 shows, this alternative version of the LoadOneProduct method can gather fields from more than one table, resulting in the more informative CategoryName field associated with each product, rather than the less informative CategoryID field. However, as mentioned earlier, this version of the method executes more slowly.

Listing 5. An Alternative Version of the LoadOneProduct Method
private void LoadOneProduct( int intProductID)
{
   //  Append the desired ProductID to the SELECT statement.
    string  strSQL = strGetOneProduct + intProductID;

   //  A connection, a command, and a reader
   SqlCeConnection  connDB = new SqlCeConnection(strConn);
   SqlCommand  cmndDB = new SqlCommand(strSQL,connDB);
   SqlCeDataReader drdrDB;

   //  Open the connection.
   connDB.Open();

   //  Submit the SQL statement and receive
   //     the SqlCeReader for the one-row
   //     results set.
   drdrDB = cmndDB.ExecuteReader();

   //  Read the first (only) row.
   //     Display it.  Close the reader.
   if ( drdrDB.Read() )
   {
      LoadControlsFromRow(drdrDB);
   }
   drdrDB.Close();

   //  Close the connection.
   connDB.Close();
}

					  

Figure 7. The Output from a Join Using the Alternative LoadOneProduct Method


We mentioned earlier that the DataReader object returned from the ExecuteReader method is not yet positioned to the first row. But it is already open, and it is your responsibility to close it. Unlike the desktop DataReader objects, which live in the multiuser environment, the SqlCeDataReader object was written for the mobile-device world. This means you can have multiple readers open on the same connection at one time. This is a somewhat necessary capability, given that you are allowed only one open SqlCeConnection object per database at a time.

The current rows of a DataReader object and a DataRow object are not the same thing. A DataRow is an object unto itself, while the current row of a DataReader is not. As we saw, we access the contents of the current row of a DataReader object by using methods and properties of the DataReader itself.

Other  
 
Top 10
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
3 Tips for Maintaining Your Cell Phone Battery (part 1) - Charge Smart
OPEL MERIVA : Making a grand entrance
FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
BMW 650i COUPE : Sexy retooling of BMW's 6-series
BMW 120d; M135i - Finely tuned
PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
Java Tutorials : Nested For Loop (part 1)
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
VIDEO TUTORIAL
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS