Oogynize/DataAccessLayer/DB.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;
using System.IO;
using System.Resources;
using System.Reflection;

namespace Foo.DataAccessLayer
{
    public class DB
    {
        /// <summary>
        /// Private Properties
        /// </summary>
        private static string _dbPath = "C:\\_ROOT_\\TestANizer\\DataAccessLayer";
        private static string _dbName = "OggyNize.sdf";
        private static string _dbPassword = "1kso123!~~!@";
        private static string _connString { get { return String.Format("Data Source={0}\\{1}; encryption mode=platform default; Password=\"{2}\"; Encrypt = TRUE;", Path.GetFullPath(_dbPath), _dbName, _dbPassword); } }

        /// <summary>
        /// Public Properties
        /// </summary>
        public static bool DoesDBExist { get { return (File.Exists(_dbPath + "\\" + _dbName)); } }

        /// <summary>
        /// Use this to create the Default Database
        /// </summary>
        public static void CreateDefaultDB()
        {
            CreateDB(_dbPath, _dbName, _dbPassword);
        }

        /// <summary>
        /// Use this to delete the Default Database
        /// </summary>
        public static void DeleteDefaultDB()
        {
            if (!String.IsNullOrEmpty(_dbPath) &&
                !String.IsNullOrEmpty(_dbName) &&
                 Directory.Exists(_dbPath) &&
                 File.Exists(_dbPath + "\\" + _dbName))
            {
                File.Delete(_dbPath + "\\" + _dbName);
            }
        }

        /// <summary>
        /// Use this function to create a New Database, if it doesn't already exist
        /// </summary>
        /// <param name="dbPath">Path to the Database</param>
        /// <param name="dbName">Name of the Database</param>
        /// <param name="dbPassword">Password to use on the Database</param>
        internal static void CreateDB(string dbPath, string dbName, string dbPassword)
        {
            try
            {
                if (dbPath.Length > 3 && dbPath[dbPath.Length - 1] == '\\')
                    dbPath = dbPath.Remove(dbPath.Length - 1);

                if (!String.IsNullOrEmpty(dbName) && !dbName.ToLower().Contains(".sdf"))
                    dbName = dbName + ".sdf";

                if (!String.IsNullOrEmpty(dbPath) && 
                    !String.IsNullOrEmpty(dbName) && 
                     Directory.Exists(dbPath) &&
                    !File.Exists(dbPath + "\\" + dbName))
                {
                    _dbPath = dbPath;
                    _dbName = dbName;
                    _dbPassword = dbPassword;
                    using (SqlCeEngine engine = new SqlCeEngine(_connString))
                    {
                        engine.CreateDatabase();
                    }

                    // This will now Construct all the necessary Tables
                    // we need ~very nice and automatic
                    CreateInitialDBTables();
                }
            }
            catch (Exception) { /* ignore */ }
        }

        /// <summary>
        /// Responsible for creating all the initial Tables for the database
        /// </summary>
        private static void CreateInitialDBTables()
        {
            foreach (string TableResourceName in ResourceHelper.GetTableResourceNames())
            {
                string strSQL = ResourceHelper.GetTextSqlFromResourceFile(TableResourceName);
                string[] strCommands = strSQL.Split(';');

                foreach (string strCommand in strCommands)
                {
                    // Is there a Label
                    if (strCommand.Contains(':'))
                    {
                        string[] strLabelAndCommand = strCommand.Split(':');
                        string strLabel = strLabelAndCommand[0];
                        string strCommand2 = strLabelAndCommand[1];

                        // Save the Label in the Version Map
                        string strTableName = ResourceHelper.GetTableNameFromTableResourceName(TableResourceName);
                        ResourceHelper.s_VersioningTablesMap[strTableName] = strLabel;

                        // Run the Command
                        RunSQLCommandTextExecuteNonQuery(strCommand2.Trim());
                    }
                    else
                    {
                        RunSQLCommandTextExecuteNonQuery(strCommand.Trim());
                    }                    
                }
            }
            
            // Now that all Tables are created, we can now save the VersionMap into the Database
            dVersioningTables versionTable = new dVersioningTables();
            foreach (string key in ResourceHelper.s_VersioningTablesMap.Keys)
                versionTable.AddUpdateVersionInformationForSpecifiedTable(key, ResourceHelper.s_VersioningTablesMap[key]);
        }

        /// <summary>
        /// Use this to Run SQL with the ExecuteNonQuery() Command Object
        /// </summary>
        /// <param name="SqlCommandText">SQL Text/Commands to execute</param>
        /// <returns>the result of the ExecuteNonQuery() operation</returns>
        internal static int RunSQLCommandTextExecuteNonQuery(string SqlCommandText)
        {
            int nResult = -1;
            try
            {                
                using (SqlCeConnection conn = new SqlCeConnection(_connString))
                {
                    conn.Open();
                    using (SqlCeCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = SqlCommandText;
                        nResult = cmd.ExecuteNonQuery();                        
                    }
                }
            }
            catch (Exception) { /* ignore */ }
            return nResult;
        }

        /// <summary>
        /// Use this to Run SQL with the ExecuteNonQuery() Command Object
        /// </summary>
        /// <param name="SqlCommandText">SQL Text/Commands to execute</param>
        /// <param name="parameters">Allows the use of sql parameters</param>
        /// <returns>the result of the ExecuteNonQuery() operation</returns>
        internal static int RunSQLCommandTextExecuteNonQuery(string SqlCommandText, SqlCeParameter[] parameters)
        {
            int nResult = -1;
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(_connString))
                {
                    conn.Open();
                    using (SqlCeCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = SqlCommandText;
                        foreach (SqlCeParameter parameter in parameters)
                            cmd.Parameters.Add(parameter);
                        nResult = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception) { /* ignore */ }
            return nResult;
        }

        /// <summary>
        /// Use this to Run SQL with the ExecuteScalar() Command Object
        /// </summary>
        /// <param name="SqlCommandText">SQL Text/Commands to execute</param>
        /// <returns>the result of the ExecuteScalar() operation</returns>
        internal static object RunSQLCommandTextExecuteScalar(string SqlCommandText)
        {
            object oResult = null;
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(_connString))
                {
                    conn.Open();
                    using (SqlCeCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = SqlCommandText;
                        oResult = cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception) { /* ignore */ }

            // Try Parsing the oResult, if an error occurs return null
            try
            {
                if (String.IsNullOrEmpty(oResult.ToString()))
                    oResult = null;
            }
            catch (Exception) { oResult = null; }
            return oResult;
        }

        /// <summary>
        /// Use this to Run SQL with the ExecuteScalar() Command Object
        /// </summary>
        /// <param name="SqlCommandText">SQL Text/Commands to execute</param>
        /// <param name="parameters">Allows the use of sql parameters</param>
        /// <returns>the result of the ExecuteScalar() operation</returns>
        internal static object RunSQLCommandTextExecuteScalar(string SqlCommandText, SqlCeParameter[] parameters)
        {
            object oResult = null;
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(_connString))
                {
                    conn.Open();
                    using (SqlCeCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = SqlCommandText;
                        foreach (SqlCeParameter parameter in parameters)
                            cmd.Parameters.Add(parameter);
                        oResult = cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception) { /* ignore */ }

            // Try Parsing the oResult, if an error occurs return null
            try
            {
                if (String.IsNullOrEmpty(oResult.ToString()))
                    oResult = null;
            }
            catch (Exception) { oResult = null; }
            return oResult;
        }

        /// <summary>
        /// Use this to Run SQL with the DataAdapter Fill()
        /// </summary>
        /// <param name="SqlText">SQL Text to execute</param>
        /// <returns>the result of the DataAdapter Fill() operation</returns>
        internal static DataSet RunSQLTextFillDataSet(string SqlText)
        {
            DataSet dataset = new DataSet();
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(_connString))
                {
                    conn.Open();
                    using (SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(SqlText, conn))
                    {
                        dataAdapter.Fill(dataset);      
                    }                    
                }
            }
            catch (Exception) { /* ignore */ }
            return dataset;
        }

        /// <summary>
        /// Use this to Run SQL with the DataAdapter Fill()
        /// </summary>
        /// <param name="SqlText">SQL Text to execute</param>
        /// <param name="parameters">Allows the use of sql parameters</param>
        /// <returns>the result of the DataAdapter Fill() operation</returns>
        internal static DataSet RunSQLTextFillDataSet(string SqlText, SqlCeParameter[] parameters)
        {
            DataSet dataset = new DataSet();
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(_connString))
                {
                    conn.Open();
                    using (SqlCeCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = SqlText;
                        foreach (SqlCeParameter parameter in parameters)
                            cmd.Parameters.Add(parameter);

                        using (SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd))
                        {
                            dataAdapter.Fill(dataset);
                        }
                    }                    
                }
            }
            catch (Exception) { /* ignore */ }
            return dataset;
        }        
    }
}