Articles in categories

Articles

SQL CE database class

SQL CE database class. I use this class to perform database actions with SQL Server Compact Edition.

Microsoft SQL Server Compact (SQL CE) is a compact relational database produced by Microsoft for applications that run on mobile devices and desktops. It's a good alternative for his biger brother, SQL Server.

SQL Server Compact shares a common API with the other Microsoft SQL Server editions.It also includes ADO.NET providers for data access using ADO.NET APIs, and built-in synchronization capabilities, as well as support for LINQ and Entity Framework. 

Unlike other editions of Microsoft SQL Server, SQL CE runs in-process with the application which is hosting it. It has a disk footprint of less than 2 MB and a memory footprint of approximately 5 MB.

SQL CE databases reside in a single .sdf file, which can be up to 4 GB in size. The .sdf file can simply be copied to the destination system for deployment, or be deployed through ClickOnce. SQL CE runtime has support for DataDirectories. Applications using an SQL CE database need not specify the entire path to an .sdf file in the ADO.NET connection string, rather it can be specified as |DataDirectory|\<database_name>.sdf, defining the data directory (where the .sdf database file resides) being defined in the assembly manifest for the application.

SQL Server Management Studio 2005 can read and modify CE 3.0 and 3.1 database files (with the latest service pack), but SQL Server Management Studio 2008 (or later) is required to read version 3.5 files. Microsoft Visual Studio Express 2008 SP1 can create, modify and query CE 3.5 SP1 database files. Visual Studio 2010 SP1 can handle CE 4.0 database files.

More info about Sql CE: http://support.microsoft.com/kb/920700

And here is the class I use. You're free to use and/or modify it.

using System;
using System.Collections;
using System.Data;
using System.Data.SqlServerCe;

namespace Stieven.SqlCe
{
  /// <summary>
  /// Helper class for SqlCe database
  /// </summary>
  public static class SqlCeAcces
  {
    private static readonly SqlCeConnection LocalConnection = null;
    private const string DataSource =
      @"Data Source=[path to your .sdf file]";

    static SqlCeHelper()
    {
      // create new database connection
      LocalConnection = new SqlCeConnection(DataSource);
    }

    private static void OpenConnection()
    {
      if (LocalConnection != null)
        LocalConnection.Open();
      else
      {
        throw new NullReferenceException("The connection string is null!");
      }
    }

    private static void CloseConnection()
    {
      if (LocalConnection != null)
        LocalConnection.Close();
    }

    #region ExecuteDataReader
    public static SqlCeDataReader ExecuteDataReader(string query)
    {
      SqlCeDataReader localReader = null;
      SqlCeCommand localCommand = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        localReader = localCommand.ExecuteReader(CommandBehavior.CloseConnection);
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
      }
      return (localReader);
    }

    public static SqlCeDataReader ExecuteDataReader(string query, ArrayList parameters)
    {
      SqlCeDataReader localReader = null;
      SqlCeCommand localCommand = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        if (parameters != null)
        {
          foreach (SqlCeParameter localParam in parameters)
            localCommand.Parameters.Add(localParam);

          localReader = localCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
      }
      return (localReader);
    }
    #endregion

    #region DataSet
    public static DataSet ExecuteDataSet(string query)
    {
      DataSet localDataSet = null;
      SqlCeCommand localCommand = null;
      SqlCeDataAdapter localDataAdapter = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        localDataAdapter = new SqlCeDataAdapter();
        localDataAdapter.SelectCommand = localCommand;
        localDataSet = new DataSet();

        localDataAdapter.Fill(localDataSet, "DATA");
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
      }
      return (localDataSet);
    }

    public static DataSet ExecuteDataSet(string query, ArrayList parameters)
    {
      DataSet localDataSet = null;
      SqlCeCommand localCommand = null;
      SqlCeDataAdapter localDataAdapter = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        localDataAdapter = new SqlCeDataAdapter();
        localDataAdapter.SelectCommand = localCommand;
        localDataSet = new DataSet();

        if (parameters != null)
        {
          foreach (SqlCeParameter localParam in parameters)
            localCommand.Parameters.Add(localParam);

          localDataAdapter.Fill(localDataSet, "DATA");
        }
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        localCommand.Dispose();
      }
      return (localDataSet);
    }
    #endregion

    #region ExecuteScalar
    public static object ExecuteScalar(string query)
    {
      object localScalarObject = string.Empty;
      SqlCeCommand localCommand = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        localScalarObject = localCommand.ExecuteScalar();
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
        CloseConnection();
      }
      return localScalarObject;
    }

    public static object ExecuteScalar(string query, ArrayList parameters)
    {
      object localScalarObject = string.Empty;
      SqlCeCommand localCommand = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        if (parameters != null)
        {
          foreach (SqlCeParameter localParam in parameters)
            localCommand.Parameters.Add(localParam);

          localScalarObject = localCommand.ExecuteScalar();
        }
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
        CloseConnection();
      }
      return localScalarObject;
    }
    #endregion

    #region ExecuteNonQuery
    public static int ExecuteNonQuery(string query)
    {
      int rowsAffected = 0;
      SqlCeCommand localCommand = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        rowsAffected = localCommand.ExecuteNonQuery();
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
        CloseConnection();
      }

      return (rowsAffected);
    }

    public static int ExecuteNonQuery(string query, ArrayList parameters)
    {
      int rowsAffected = 0;
      SqlCeCommand localCommand = null;

      try
      {
        OpenConnection();

        localCommand = new SqlCeCommand(query, LocalConnection);
        localCommand.CommandType = CommandType.Text;

        if (parameters != null)
        {
          foreach (SqlCeParameter localParam in parameters)
            localCommand.Parameters.Add(localParam);
        }
        rowsAffected = localCommand.ExecuteNonQuery();
      }
      catch (SqlCeException sqlCeEx)
      {
        throw (sqlCeEx);
      }
      catch (Exception ex)
      {
        throw (ex);
      }
      finally
      {
        if (localCommand != null) localCommand.Dispose();
        CloseConnection();
      }
      return (rowsAffected);
    }
    #endregion
  }
}