using System;

using System.IO;

using System.Text;

using System.Data;

using System.Data.Common;

using System.Data.SqlServerCe;

using System.Collections;

namespace MediaService.Pocket {

  public class Db {

    private const String DB_NAME = "MediaService.sdf";

      private static Db instance = null;

      public Db() { }

      public static Db NewInstance() {

        lock(typeof(Db)) {

          if (instance == null) {

            instance = new Db();

          }

          return instance;

        }

      } /* NewInstance */

      private void CheckDB() {

        if (!File.Exists(DB_NAME)) {

          SqlCeConnection conn = null;

          SqlCeTransaction trans = null;

          SqlCeEngine engine = new SqlCeEngine("Data Source = " + DB_NAME);

          engine.CreateDatabase();

          try {

            conn = new SqlCeConnection("Data Source = " + DB_NAME);

            conn.Open();

            SqlCeTransaction trans = conn.BeginTransaction();

            SqlCeCommand availableTable = conn.CreateCommand();

            availableTable.Transaction = trans;

            availableTable.CommandText = "CREATE TABLE Available(songId int,

                         songTitle nvarchar(200), songArtist nvarchar(200))";

            availableTable.ExecuteNonQuery();

            trans.Commit();

          } catch {

            trans.Rollback();

          } finally {

            if (conn != null && conn.State == ConnectionState.Open) {

              conn.Close();

            }

          }

        }

      } /* CheckDb */

private void OnGetSongs(IAsyncResult songsResult) {

  this.availableSongsCache = this.GetService().EndGetSongs(songsResult);

  Db.NewInstance().StoreSongs(this.availableSongsCache);

public void StoreSongs(Song[] songs) {

  this.CheckDB();

  SqlCeConnection conn = null;

  SqlCeTransaction trans = null;

  try {

    conn = new SqlCeConnection("Data Source = " + DB_NAME);

    conn.Open();

    trans = conn.BeginTransaction();

    SqlCeCommand deleteSong = conn.CreateCommand();

    deleteSong.Transaction = trans;

    String deleteSql = "DELETE FROM Available";

    deleteSong.CommandText = deleteSql;

    deleteSong.ExecuteNonQuery();

    SqlCeCommand insertSong = conn.CreateCommand();

    String insertSql = "INSERT INTO Available(songId, songTitle, songArtist)

                                              VALUES (?, ?, ?)";

    insertSong.Transaction = trans;

    insertSong.CommandText = insertSql;

    foreach (Song song in songs) {

      insertSong.Parameters.Clear();

      insertSong.Parameters.Add("@songId", song.ID);

      insertSong.Parameters.Add("@songTitle", song.Title);

      insertSong.Parameters.Add("@songArtist", song.Artist);

      insertSong.ExecuteNonQuery();

    }

    trans.Commit();

  } catch (SqlCeException ex) {

    trans.Rollback();

    System.Windows.Forms.MessageBox.Show(FormatErrorMessage(ex));

  } finally {

    if (conn != null && conn.State == ConnectionState.Open) {

      conn.Close();

    }

  }

} /* StoreSongs */