Pocket Personal Health Record

//**************************************************************************//
// Copyright (C) Abdul Rasheed. All rights Reserved.                        //
// rasheedat.blogspot.com                                                   //
//**************************************************************************//
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.IO;
using System.Reflection;
namespace PPHR.DataAccess
{
    /// <summary>
    /// This class helps to connect to SQL CE db to get data
    /// </summary>
    public class DBConnection
    {
        string connectionString = string.Empty;
        string appPath = string.Empty;
        private SqlCeConnection conn;
       
        public DBConnection()
        {
            appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
        }
       
        private void Connect()
        {
            connectionString = @"Data Source=" + appPath +"\\PPHRData.sdf";
            if (conn == null || conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
            {
                conn = new SqlCeConnection(connectionString);
                conn.Open();
            }
        }
        
        private void Disconnect()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Execute the SQL statement and return dataset
        /// </summary>
        /// <param name="sSql"></param>
        /// <returns></returns>
        public DataTable ExecuteQueryAndGetDataTable(string sSql)
        {
            DataTable dt=null;
            DataSet ds = new DataSet();
            try
            {
                Connect();
                SqlCeDataAdapter da = new SqlCeDataAdapter(sSql, conn);
                da.Fill(ds);
                if (ds.Tables.Count >= 0)
                {
                    dt = ds.Tables[0];
                }
                Disconnect();
            }
            catch(Exception ex)
            {
                throw new Exception("DBConnection", ex);
            }
            return dt;
        }

        /// <summary>
        /// Execute the sql statement and return the commited records count
        /// </summary>
        /// <param name="sSql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sSql)
        {
            int commitedRows = -1;
            try
            {
                Connect();
                SqlCeCommand cmd = new SqlCeCommand(sSql, conn);
                commitedRows = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("DBConnection", ex);
            }
            return commitedRows;
        }
    }

}