Compact Framework Movie Collection Manager

• Download source - 16 KB

Introduction

This article attempts to to illustrate the steps required in creating a relatively simply "database" application using the compact framework, SQL Server CE and windows forms.

This application will introduce you to creating, populating and querying SQL Server CE databases from a Compact Framework application.

A friend of mine has a vast collection of movies, both on DVD and VHS, that he has painstakingly organized and numbered. When I starting looking for development ideas he brought up how useful it would be to have an application where he could keep that information, and be able to search for movies of a certain type.

This is my first submission, so please be gentle.

Background

My background primarily consists of MFC windows programming, but I've been involved with some ASP.NET programming recently. That spurred my interest in C# and Windows Forms and I've been a big fan of Pocket PC/Windows Mobile devices for quite a while. So I took this up as a challenge to use C# and the Compact Framework to create a handheld application. Originally I had expected to use access as the database since I'm familiar with that but found that it's not supported. I also investigated using XML for the storage but that proved too clunky.

Using the code

The code is broken down into two classes, one for each form within the application. The main form sports a tab control with two panels, along with a ListView control. The listview supports a "context menu" which brings up two menu choices. Once menu choice supports deleting the selected record, while the other brings up the record details form.

The collection tab shows a ratings breakdown of movies with the collection, along with a button to create new record. The other tab, Filter, supports filtering the collection by various properties

The details form provides controls to create and modify movie records. It simply adds or updates records by taking the values from the controls and applying them to the database.

Probably the most interesting aspect of the application is how it uses SQL Server CE.

Creating the Database

The code creates a new SQLServer CE 'file' called MovieManager.sdf

  private SqlCeEngine m_cEngine = null;
  private SqlCeConnection m_cConn = null;
  
  public MovieManagerForm()
  {
    //
    // Required for Windows Form Designer support
    //
    InitializeComponent();

    // Instantiate a SqlCeEngine object
    m_cEngine = new SqlCeEngine(
          "Data Source = \\My Documents\\MovieManager.sdf");
    
    // Check to see if the DB is already there, if not call a func to 
    // create it and it's tables
    bool bCreated = false;
    if ( !File.Exists("\\My Documents\\MovieManager.sdf") )
    {
      bCreated = true;
      m_cEngine.CreateDatabase();
    }
    
    m_cConn = new SqlCeConnection(
       "Data Source = \\My Documents\\MovieManager.sdf");
    m_cConn.Open();

    if ( bCreated )
      DoInitialDatabaseSetup();
  }

  private void DoInitialDatabaseSetup()
  {
    // Instantiate a SqlCeCommand object
    SqlCeCommand sqlCreateTable = m_cConn.CreateCommand();

    // Create the MOVIE_MNGR_TABLE
    sqlCreateTable.CommandText = 
      "CREATE TABLE MOVIE_MNGR_TABLE(MovieID int IDENTITY(0,1) " +
      "PRIMARY KEY, MovieNmbr ntext, MovieTitle ntext, " +
      "MovieRating int, MovieFormat int, MovieType int, " +
      "Animated bit, MovieNotes ntext)";
    sqlCreateTable.ExecuteNonQuery();
  }

Basically, the engine in initialized, if the SDF file doesn't already exist it is created, then a connection is established to the database, and, finally, if it was created then the table is built through an SQL command.

Querying the Database and loading the list

  private void LoadList(string szSQL)
  {
    // Clear out anything that might already be in the list
    CollectionList.Items.Clear();

    // save off the SQL Statement
    m_szCurrFilter = szSQL;

    // Throw Movies into the list
    SqlCeCommand sqlLoadEvents = m_cConn.CreateCommand();
    sqlLoadEvents.CommandText = szSQL;
    SqlCeDataReader rReader = sqlLoadEvents.ExecuteReader();
    // roll through the reader and put items into the list
    while ( rReader.Read() )
    {
      ListViewItem lvItem = new ListViewItem( 
         rReader.GetValue(rReader.GetOrdinal(
         "MovieNmbr")).ToString() ); //Nmbr
      // Note: Since a "tag" is not supported by the 
      // compact framework, we'll use the "ImageIndex"
      // property to hold the movie ID, this way we can
      // find it later when the user clicks
      // on a movie
      lvItem.ImageIndex = (int)rReader.GetValue(
        rReader.GetOrdinal("MovieID"));
      lvItem.SubItems.Add( rReader.IsDBNull(rReader.GetOrdinal(
          "MovieTitle")) ? "" : rReader.GetValue(
          rReader.GetOrdinal("MovieTitle")).ToString()); // Title
      lvItem.SubItems.Add(GetRatingFromNumber((int)rReader.GetValue(
          rReader.GetOrdinal("MovieRating")) )); // Rating
      CollectionList.Items.Add( lvItem );
    }
  }

This method queries the database and through a SqlCeReader object adds row to the ListView. I decided to pass in the SQL select statement, as I could then reuse this method to support the user doing a filter. This method saves the supplied filter into a member variable so that it can be reused later.

Modifying the Database

  // Adding a new Movie Record
  SqlCeCommand sqlCommand = m_cConn.CreateCommand();

  sqlCommand.CommandText = "INSERT INTO MOVIE_MNGR_TABLE (MovieNmbr,"+ 
         "MovieTitle, MovieRating, MovieFormat, MovieType, Animated, "+
         "MovieNotes) VALUES (?, ?, ?, ?, ?, ?, ?)";
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieNmbr",
         SqlDbType.NText)).Value = NumberText.Text;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieTitle", 
         SqlDbType.NText)).Value = TitleText.Text;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieRating", 
         SqlDbType.Int)).Value = RatingCombo.SelectedIndex+1;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieFormat", 
         SqlDbType.Int)).Value = FormatCombo.SelectedIndex+1;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieType", 
         SqlDbType.Int)).Value = TypeCombo.SelectedIndex+1;
  sqlCommand.Parameters.Add(new SqlCeParameter("Animated", 
         SqlDbType.Bit)).Value = CheckState.Checked == 
         AnimatedCheckBox.CheckState ? true : false;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieNotes", 
         SqlDbType.NText)).Value = NotesText.Text;

  sqlCommand.ExecuteNonQuery();

Adding records uses a traditional INSERT statement. I decided to make use of SqlCeParameter objects to protect against any data with apostrophes. Everything was pretty cut and dried, but exceptions will be thrown if anything in your SQL statement is incorrect or out of place. I decided to keep values like rating, and type as a number in the database rather than a string, and I decided to reserve zero for use in filtering.

  // Updating an existing Movie Record
  SqlCeCommand sqlCommand = m_cConn.CreateCommand();
  sqlCommand.CommandText = 
            "UPDATE MOVIE_MNGR_TABLE set MovieNmbr = ?, " +
            "MovieTitle = ?, MovieRating = ?, MovieFormat = ?,"+
            " MovieType = ?, Animated = ?, MovieNotes = ? WHERE "+
            "MovieID = " + m_nMovieID.ToString();;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieNmbr", 
   SqlDbType.NText)).Value = NumberText.Text;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieTitle", 
   SqlDbType.NText)).Value = TitleText.Text;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieRating", 
   SqlDbType.Int)).Value = RatingCombo.SelectedIndex+1;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieFormat", 
   SqlDbType.Int)).Value = FormatCombo.SelectedIndex+1;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieType", 
   SqlDbType.Int)).Value = TypeCombo.SelectedIndex+1;
  sqlCommand.Parameters.Add(new SqlCeParameter("Animated", 
   SqlDbType.Bit)).Value = CheckState.Checked == 
   AnimatedCheckBox.CheckState ? true : false;
  sqlCommand.Parameters.Add(new SqlCeParameter("MovieNotes", 
   SqlDbType.NText)).Value = NotesText.Text;
  sqlCommand.ExecuteNonQuery();

An update works almost identically to an insert.

Points of Interest

SQL Server CE

I did all the development on a machine without Activesync installed, using the emulator for initial testing, and then copying the executable onto an SD card and running on my Dell Axim. The emulator worked well as the Studio installed the necessary SQL CE components automatically, but was a little more difficult to get going on the Axim. Eventually I found 2 CAB files that represent SQL Server CE and ran them on the actual handheld. Your environment should have the CAB files you need. Eventually I found that I needed 'sqlce.dev.ppc3.arm.CAB' and 'sqlce.ppc3.arm.CAB'.

The SQLCE Query application proved useful to run ad-hoc queries against the data for debugging and testing, on both the emulator and handheld, but you have to remember to 'close' the database before starting the application.

Note: To use SQL Server CE add a 'using System.Data.SqlServerCe;' statement as well as a reference to System.Data.SqlServerCe.

Windows forms and the Compact Framework

While the Compact Framework makes working with Window Forms pretty painless, occasionally I ran into a catch with something not supported in the framework. For example, in my MFC programming I like to use the "Item Data" of a list control row to keep the unique ID for that row to make deletes and so forth easy. I found the 'Tag' property in the ListView control wasn't supported in the Framework, so as a work-around I used the ImageIndex property. If I want to add an imagelist in the future I'll have to find something else.

I like the way Window Forms makes it easy to change background color, fonts and other visual elements, unfortunately it's not supported for every control in the framework.

History

• 14 May 2004 - Initial Submission