Audio Book Player

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Win32;
using System.Windows.Forms;
using System.Collections;
using System.Reflection;
using System.IO;
using System.Runtime.InteropServices;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;

public class cDatabasePersistantcy : cBasePersistancy
{
    private String pActiveBookName = "";
    private SqlCeCommand cmd = null;
    private String[] BOOK_ATTRIBUTE_NAME = { "BOOKMARKENTRY", 
        "BOOKMARKLOCATION", "VOLUME", "SHUFFLE" };
    private int[] BOOK_ATTRIBUTE_DEFAULTS = { 0, 0, 40, 0 };
    // ============================================================================
    private const Int64 MAX_STORAGE_CARD_CAPACITY = 20000000000;//20GB;
    // initial schema creation ====================================================
    private const String CREATE_BOOKS_TABLE = "CREATE TABLE BOOKS(" +
        "BOOKNAME nVarChar(100) NOT NULL CONSTRAINT PK PRIMARY KEY, " +
        "BOOKSHELF nVarChar(100) DEFAULT(''), " +
        "BOOKMARKENTRY int DEFAULT(0), " +
        "BOOKMARKLOCATION int DEFAULT(0), " +
        "VOLUME int DEFAULT(0), " +
        "SHUFFLE int DEFAULT(0))";
    private const String CREATE_BOOKSHELVES_TABLE = "CREATE TABLE BOOKSHELVES(" +
        "BOOKSHELF nVarChar(100) NOT NULL)";
    private const String CREATE_BOOKFILES_TABLE = "CREATE TABLE BOOKFILES(" +
        "BOOKNAME nVarChar(100) NOT NULL, " +
        "FILENAME nVarChar(255) DEFAULT(''), " +
        "FILEORDER int)";
    private const String CREATE_ATTRIBUTES_TABLE = "CREATE TABLE ATTRIBUTES(" +
        "IX int NOT NULL CONSTRAINT PK PRIMARY KEY, " +
        "ACTIVEBOOKNAME nVarChar(100) DEFAULT(''), " +
        "SKIN nVarChar(100) DEFAULT(''))";
    private const String INSERT_ATTRIBUTES_RECORD = "INSERT INTO ATTRIBUTES " +
        "(IX) VALUES (1)";
    // ============================================================================

    public cDatabasePersistantcy(String assembly)
    {
        SqlCeResultSet rs = null;
        // DB not supported on storage card in emulator mode.
        String basePath = (IsEmulator) ?
            Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase):
            GetLargestStorageCard();
        if (basePath.Length == 0)
            throw new Exception("No Storage card Found");
        String dbFileName = basePath +
            "\\" + assembly + ".sdf";
        string connString = "Data Source='" + dbFileName + "'";
        cmd = new SqlCeCommand();
        cmd.Connection = new SqlCeConnection(connString);
        try
        {
            if (File.Exists(dbFileName))
            {
                cmd.Connection.Open();
                // get active books
                const String SQL = "SELECT ACTIVEBOOKNAME FROM ATTRIBUTES WHERE IX=1";
                cmd.CommandText = SQL;
                rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (rs.ReadFirst())
                    pActiveBookName = rs.GetString(0);
            }
            else
            {
                SqlCeEngine db = new SqlCeEngine(connString);
                db.CreateDatabase();
                db.Dispose();
                cmd.Connection.Open();
                cmd.CommandText = CREATE_BOOKS_TABLE;
                cmd.ExecuteNonQuery();
                cmd.CommandText = CREATE_BOOKFILES_TABLE;
                cmd.ExecuteNonQuery();
                cmd.CommandText = CREATE_BOOKSHELVES_TABLE;
                cmd.ExecuteNonQuery();
                cmd.CommandText = CREATE_ATTRIBUTES_TABLE;
                cmd.ExecuteNonQuery();
                cmd.CommandText = INSERT_ATTRIBUTES_RECORD;
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
    }
    // destructor - close db connection
    ~cDatabasePersistantcy()
    {
        cmd.Connection.Close();
        cmd.Dispose();
    }
    // get driver manufacturer of USB -
    // this is the only way i found to distinguish between the emulator
    // and a real device
    private bool IsEmulator
    {
        get
        {
            // manufacturer registry entries
            const String MANUFACURER_KEY = "drivers\\usb\\functiondrivers";
            const String MANUFACURER_VALUE = "manufacturer";
            const String MANUFACURER_DATA_EMULATOR = "Generic Manufacturer";

            RegistryKey RK = null;
            RegistryKey SRK = null;
            String data = "";
            bool result = false;
            try
            {
                RK = Registry.LocalMachine.OpenSubKey(MANUFACURER_KEY, false);
                String[] keys = RK.GetSubKeyNames();
                if (keys.Length == 0) return false;
                SRK = RK.OpenSubKey(keys[0], false);
                data = (String)SRK.GetValue(MANUFACURER_VALUE);
                if (data.Contains(MANUFACURER_DATA_EMULATOR))
                    result = true;
            }
            catch
            {
            }
            finally
            {
                if (SRK != null) SRK.Close();
                if (RK != null) RK.Close();
            }
            return result;
        }
    }
    private String PrepareSQLCommand(String cmd, params String[] list)
    {
        String[] splitCmd = cmd.Split('?');
        if((splitCmd.Length - list.Length) != 1)
            throw new Exception("Unmatched params count");
        String result = "";
        for (int i = 0; i < list.Length; i++)
            result += splitCmd[i] + list[i].Replace("'", "''");
        return result + splitCmd[list.Length];
    }
    // get/set active book
    public override String ActiveBookName
    {
        get
        {
            return pActiveBookName;
        }
        set
        {
            try
            {
                const String SQL = "UPDATE ATTRIBUTES SET ACTIVEBOOKNAME='?' WHERE IX=1";
                // write value
                cmd.CommandText = PrepareSQLCommand(SQL, value);
                cmd.ExecuteNonQuery();
                pActiveBookName = value;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
    }
    // get/set skin
    public override String Skin
    {
        get
        {
            SqlCeResultSet rs = null;
            const String SQL = "SELECT SKIN FROM ATTRIBUTES WHERE IX=1";
            String sk = "";
            try
            {
                cmd.CommandText = SQL;
                rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (rs.ReadFirst())
                    sk = rs.GetString(0);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (rs != null) rs.Close();
            }
            return sk;
        }
        set
        {
            try
            {
                const String SQL = "UPDATE ATTRIBUTES SET SKIN='?' WHERE IX=1";
                cmd.CommandText = PrepareSQLCommand(SQL, value);
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
    }
    // get book poperty
    public override int GetBookProperty(eBookProperty prop, String book)
    {
        if ((book == "") && (ActiveBookName == "")) return 0;
        SqlCeResultSet rs = null;
        int value = 0;
        try
        {
            const String SQL = "SELECT ? FROM BOOKS WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL, BOOK_ATTRIBUTE_NAME[(int)prop],
                ((book == "") ? ActiveBookName : book));
            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            if (rs.ReadFirst())
                value = rs.GetInt32(0);
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
        return value;
    }
    // set a book property
    public override bool SetBookProperty(eBookProperty prop, int value, String book)
    {
        if ((book == "") && (ActiveBookName == "")) return false;
        try
        {
            const String SQL = "UPDATE BOOKS SET ?=? WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL, 
                BOOK_ATTRIBUTE_NAME[(int)prop], 
                value.ToString(), 
                ((book == "") ? ActiveBookName : book));
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // get all bookshelves
    public override String[] Books
    {
        get
        {
            SqlCeResultSet rs = null;
            try
            {
                const String SQL = "SELECT BOOKNAME FROM BOOKS " +
                    "ORDER BY BOOKNAME";
                cmd.CommandText = SQL;
                rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                ArrayList list = new ArrayList();
                while (rs.Read())
                    list.Add(rs.GetString(0));
                return (String[])list.ToArray(typeof(String));
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (rs != null) rs.Close();
            }
            return new String[0];
        }
    }
    // get all bookshelves
    public override String[] Bookshelves
    {
        get
        {
            SqlCeResultSet rs = null;
            try
            {
                const String SQL = "SELECT BOOKSHELF FROM BOOKSHELVES " +
                    "ORDER BY BOOKSHELF";
                cmd.CommandText = SQL;
                rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                ArrayList list = new ArrayList();
                while (rs.Read())
                    list.Add(rs.GetString(0));
                return (String[])list.ToArray(typeof(String));
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (rs != null) rs.Close();
            }
            return new String[0];
       }
    }
    // get all books on (in) a bookshelf
    public override String[] GetBookshelfBooks(String bookshelf)
    {
        SqlCeResultSet rs = null;
        try
        {
            const String SQL = "SELECT BOOKNAME FROM BOOKS WHERE BOOKSHELF='?' " +
                "ORDER BY BOOKNAME";
            cmd.CommandText = PrepareSQLCommand(SQL, bookshelf);
            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            ArrayList list = new ArrayList();
            while (rs.Read())
                if(rs.GetString(0) != "")
                    list.Add(rs.GetString(0));
            return (String[])list.ToArray(typeof(String));
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
        return new String[0];
    }
    // get a book's bookshelf
    public override String GetBooksBookshelf(String book)
    {
        SqlCeResultSet rs = null;
        String value = "";
        if ((book == "") && (ActiveBookName == "")) return "";
        try
        {
            const String SQL = "SELECT BOOKSHELF FROM BOOKS WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL, 
                ((book == "") ? ActiveBookName : book));
            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            if (rs.ReadFirst())
                value = rs.GetString(0);
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
        return value;
        
    }
    // create new bookshelf
    public override bool NewBookshelf(String bookshelf)
    {
        try
        {
            const String SQL = "INSERT INTO BOOKSHELVES " +
                "(BOOKSHELF) " +
                "VALUES ('?')";
            cmd.CommandText = PrepareSQLCommand(SQL, bookshelf);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // delete a bookshelf
    public override bool DeleteBookshelf(String bookshelf)
    {
        try
        {
            // move all books in the bookshelf to the root node
            const String SQL1 = "UPDATE BOOKS SET BOOKSHELF='' WHERE BOOKSHELF='?'";
            // delete it
            const String SQL2 = "DELETE FROM BOOKSHELVES WHERE BOOKSHELF='?'"; 
            cmd.CommandText = PrepareSQLCommand(SQL1, bookshelf);
            cmd.ExecuteNonQuery();
            cmd.CommandText = PrepareSQLCommand(SQL2, bookshelf);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // change a book's bookshelf
    public override bool SetBooksBookshelf(String book, String newBookshelf)
    {
        if ((book == "") && (ActiveBookName == "")) return false;
        try
        {
            const String SQL = "UPDATE BOOKS SET BOOKSHELF='?' WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL, newBookshelf, ((book == "") ? ActiveBookName : book));
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // create new book
    public override bool NewBook(String book, String bookshelf)
    {
        try
        {
            const String SQL = "INSERT INTO BOOKS " +
                "(BOOKNAME, BOOKSHELF, BOOKMARKENTRY, BOOKMARKLOCATION, VOLUME, SHUFFLE) " +
                "VALUES ('?', '?', ?, ?, ?, ?)";
            cmd.CommandText = PrepareSQLCommand(SQL,
                book, 
                bookshelf,
                BOOK_ATTRIBUTE_DEFAULTS[0].ToString(),
                BOOK_ATTRIBUTE_DEFAULTS[1].ToString(),
                BOOK_ATTRIBUTE_DEFAULTS[2].ToString(),
                BOOK_ATTRIBUTE_DEFAULTS[3].ToString());
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // get book file count 
    public override int GetBookFileCount(String book)
    {
        if ((book == "") && (ActiveBookName == "")) return 0;
        SqlCeResultSet rs = null;
        int count = 0;
        try
        {
            const String SQL = "SELECT COUNT (*) FROM BOOKFILES WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL,
                ((book == "") ? ActiveBookName : book));
            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            if (rs.ReadFirst())
                count = rs.GetInt32(0);
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
        return count;
    }
    // get book files 
    public override String[] GetBookFiles(String book)
    {
        if ((book == "") && (ActiveBookName == "")) return new String[0];
        SqlCeResultSet rs = null;
        try
        {
            const String SQL = "SELECT FILENAME, FILEORDER FROM BOOKFILES WHERE BOOKNAME='?' " +
                "ORDER BY FILEORDER";
            cmd.CommandText = PrepareSQLCommand(SQL, ((book == "") ? ActiveBookName : book));
            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
            ArrayList list = new ArrayList();
            while (rs.Read())
                list.Add(rs.GetString(0));
            return (String[])list.ToArray(typeof(String));
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
        return new String[0];
    }
    // set the book files - 
    // we always add the files to existing ones.
    public override bool SetBookFiles(String[] newFiles, String book)
    {
        if ((book == "") && (ActiveBookName == "")) return false;
        SqlCeResultSet rs = null;
        try
        {
            String[] oldFiles = GetBookFiles(((book == "") ? ActiveBookName : book));
            // delete existing
            DeleteBookFiles(book);
            // write old & new files.
            const String SQL = "SELECT BOOKNAME, FILENAME, FILEORDER FROM BOOKFILES WHERE BOOKNAME='?' ";
            cmd.CommandText = PrepareSQLCommand(SQL, ((book == "") ? ActiveBookName : book));
            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable |
                ResultSetOptions.Updatable);
            int n = 0;
            foreach (String file in newFiles)
            {
                n++;
                SqlCeUpdatableRecord rec = rs.CreateRecord();
                rec.SetString(0, book);
                rec.SetString(1, file);
                rec.SetInt32(2, n);
                rs.Insert(rec);
            }
            foreach (String file in oldFiles)
            {
                n++;
                SqlCeUpdatableRecord rec = rs.CreateRecord();
                rec.SetString(0, book);
                rec.SetString(1, file);
                rec.SetInt32(2, n);
                rs.Insert(rec);
            }
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        finally
        {
            if (rs != null) rs.Close();
        }
        return false;
    }
    // delete a file entry
    public override bool DeleteBookFile(int ix, String book)
    {
        if ((book == "") && (ActiveBookName == "")) return false;
        try
        {
            ArrayList files = new ArrayList();
            files.AddRange(GetBookFiles(book));
            if ((ix < 0) || (ix > (files.Count - 1))) return false;
            files.RemoveAt(ix);
            DeleteBookFiles(book);
            SetBookFiles((String[])files.ToArray(typeof(String)), book);
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // delete all book file entries
    public override bool DeleteBookFiles(String book)
    {
        if ((book == "") && (ActiveBookName == "")) return false;
        try
        {
            const String SQL = "DELETE FROM BOOKFILES WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL, ((book == "") ? ActiveBookName : book));
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // rename a bookshelf
    public override bool RenameBookShelf(String newName, String bookshelf)
    {
        try
        {
            const String SQL1 = "UPDATE BOOKSHELVES SET BOOKSHELF='?' WHERE BOOKSHELF='?'";
            cmd.CommandText = PrepareSQLCommand(SQL1, newName, bookshelf);
            cmd.ExecuteNonQuery();
            const String SQL2 = "UPDATE BOOKS SET BOOKSHELF='?' WHERE BOOKSHELF='?'";
            cmd.CommandText = PrepareSQLCommand(SQL2, newName, bookshelf);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // rename a book
    public override bool RenameBook(String newName, String book)
    {
        if (book == "") return false;
        try
        {
            String[] files = GetBookFiles(book);
            String bookshelf = GetBooksBookshelf(book);
            int[] attr = new int[4];
            for (int i = 0; i < attr.Length; i++)
                attr[i] = GetBookProperty((eBookProperty)i, book);
            DeleteBook(book);
            NewBook(newName, bookshelf);
            SetBookFiles(files, newName);
            for (int i = 0; i < attr.Length; i++)
                SetBookProperty((eBookProperty)i, attr[i], newName);
            if (pActiveBookName == book)
                ActiveBookName = newName;
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
    // delete a book.
    public override bool DeleteBook(String book)
    {
        if (book == "") return false;
        // if active book is deleted - no active book anymore
        if (ActiveBookName == book)
            ActiveBookName = "";
        try
        {
            const String SQL1 = "DELETE FROM BOOKFILES WHERE BOOKNAME='?'";
            const String SQL2 = "DELETE FROM BOOKS WHERE BOOKNAME='?'";
            cmd.CommandText = PrepareSQLCommand(SQL1, book);
            cmd.ExecuteNonQuery();
            cmd.CommandText = PrepareSQLCommand(SQL2, book);
            cmd.ExecuteNonQuery();
            return true;
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
        return false;
    }
}