SQL Server CE Sample Code

In response to some recent Microsoft newsgroup questions about using SQL Server CE for PocketPC applications, I am posting this sample code. It is a database utilities class that demonstrates how to create a database, create a table, and read / query and insert records from the table. Please forgive the fact that I didn't take the time to pretty up the format. It is readable enough for a reasonablty experience PocketPC developer to follow.

The sample is written in C# using the .NET Compact Framework.

using System;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace database
{
 /// 

 /// Summary description for Database.
 /// 
 /// 
 public class DatabaseUtils
 {
  private String strFile = @"My Documents\myDB.sdf";
  private String strConn = 
     "DataSource="+@"My Documents\myDB.sdf";
  private SqlCeConnection connection;
  private SqlCeDataAdapter adapter;

  #region LoadTable Event Type Declarations
  public class LoadTableEventArgs
  {
   private System.Data.DataSet data_set;

   #region LoadTableEventArgs Class Properties
   public System.Data.DataSet DataSet
   {
    get { return (this.data_set); }
   }
   #endregion
   public LoadTableEventArgs()
   {
    this.data_set = new System.Data.DataSet();
   }
  }

  public delegate void LoadTableEventHandler(
     Object sender,
     LoadTableEventArgs args);
  public event LoadTableEventHandler LoadTableEvent;
  #endregion

  public DatabaseUtils()
  {
   //
   // TODO: Add constructor logic here
   //
   adapter = new SqlCeDataAdapter();

  }

  public void CreateDatabase()
  {
   if (System.IO.File.Exists(this.strFile))
   {
    DialogResult res;
    res = MessageBox.Show(
     "The database " + this.strFile +
                " already exists.\r\n" +
     "This operation will delete and
          recreate and empty database.\r\n" +
     "Do you want to proceed?",
     "Database Delete",
     MessageBoxButtons.YesNo,
     MessageBoxIcon.Question,
     MessageBoxDefaultButton.Button2
     );
    if (res == DialogResult.Yes)
    {
     System.IO.File.Delete(this.strFile);
    }
    else
    {
     return;
    }
   }

   SqlCeEngine dbEngine = new SqlCeEngine();
   dbEngine.LocalConnectionString = this.strConn;
   try
   {
    dbEngine.CreateDatabase();
    if (OpenConnection())
    {
     SqlCeCommand cmd = new SqlCeCommand();
     cmd.Connection = connection;
     cmd.CommandText =
      "create table Users " +
      "(user_id integer not null IDENTITY(0,1)
         constraint PKuser_id PRIMARY KEY " +
      ", first_name nvarchar(100) " +
      ", last_name nvarchar(100))";
     cmd.ExecuteNonQuery();
     CloseConnection();
    }
   }
   catch (System.Data.SqlServerCe.SqlCeException e)
   {
    System.Windows.Forms.MessageBox.Show(
     "Unable to create database"
     );
   }
  }

  public void InsertUser(String first_name,
    String last_name)
  {
   try
   {
    if (OpenConnection())
    {
     SqlCeCommand cmd = new SqlCeCommand();
     cmd.Connection = connection;
     cmd.CommandText =
     "insert into Users (first_name, last_name)+
     "values" +
     "('" + first_name + "', '" + last_name +"')";
     cmd.ExecuteNonQuery();
     CloseConnection();
    }
   }
   catch (SqlCeException e)
   {
    MessageBox.Show(
     "Unable to insert record into Users table." +
       Reason: " + e.Errors[0].Message
     );
   }
  }

  public bool LoadTable(String table)
  {
   bool bResult = true;
   try
   {
    if (OpenConnection())
    {
     if (this.LoadTableEvent != null)
     {
      LoadTableEventArgs args
       = new LoadTableEventArgs();
      SqlCeCommand cmd = new SqlCeCommand();
      cmd.Connection = connection;
      cmd.CommandText = "select * from " + table;
      adapter.SelectCommand = cmd;
      adapter.Fill(args.DataSet);
      this.LoadTableEvent(this, args);
     }
     CloseConnection();
    }
   }
   catch (SqlCeException e)
   {
    MessageBox.Show(
     "An error occurred in ViewTable."
          + e.Errors[0].Message
     );
    bResult = false;
   }
   return (bResult);
  }

  ///Private helper methods
  private bool OpenConnection()
  {
   bool bResult = true;
   try
   {
    connection = new SqlCeConnection();
    connection.ConnectionString = this.strConn;
    connection.Open();
   }
   catch (System.Data.SqlServerCe.SqlCeException e)
   {
    System.Windows.Forms.MessageBox.Show(
     "Error occurred in OpenConnection"
       + e.Errors[0].Message
     );
    bResult = false;
   }
   return (bResult);
  }

  private bool CloseConnection()
  {
   bool bResult = true;
   try
   {
    connection.Close();
   }
   catch (SqlCeException e)
   {
    System.Windows.Forms.MessageBox.Show(
     "Error occurred in CloseConnection"
       + e.Errors[0].Message
     );
    bResult = false;
   }
   return (bResult);
  }
 }
}