namespace DynamicFramework
{
    public abstract class DataAccess : MarshalByRefObject
    {
        protected System.Data.Common.DbConnection connection;
        protected string cnnstr = "";
        protected DataAccess()
        {
        }

        public static string ConnPath = System.Windows.Forms.Application.StartupPath + "\LocalDB.mdb";
        public static DataAccess LocalDb
        {
            get
            {
                return new OleAccess("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConnPath);
                //return new SqlClientAccess("Server=localhost;Trusted_Connection=true;Database=RestaurantDB");
            }
        }

        public static DataAccess ServerDb
        {
            get
            {
                //return new OleAccess("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ConnPath);
                //if (Configs.LocalConfig.Instanct.IsLocalServer)
                //{

                //}
                //Trusted_Connection=true;
                //return new SqlClientAccess("Server=.;Database=RestaurantDB,uid = sa,pwd =");

                return new SqlClientAccess("Data Source=.;Initial Catalog=RestaurantDB;Persist Security Info=True;User ID=sa");
            }
        }

        private System.Data.Common.DbCommand GetCommand(string sql, Dictionary parameters)
        {
            System.Data.Common.DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (KeyValuePair item in parameters)
                {
                    System.Data.Common.DbParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = item.Key;
                    parameter.Value = item.Value;
                    cmd.Parameters.Add(parameter);
                }
            }
            return cmd;
        }

        #region DataAccess Command

        public int ExcuteCommand(string sql,Dictionary parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return GetCommand(sql, parameters).ExecuteNonQuery();
            }     
        }

        public object ExecuteScalar(string sql, Dictionary parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return GetCommand(sql, parameters).ExecuteScalar();
            }
        }

        public object ExecuteReader(string sql, Dictionary parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return GetCommand(sql, parameters).ExecuteReader();
            }
        }

        public System.Data.DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null);
        }

        public System.Data.DataTable ExecuteDataTable(string sql, Dictionary parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return DbHelper.ToTable(GetCommand(sql, parameters).ExecuteReader());
            }
        }

        public List ExcuteList(string sql, Dictionary parameters) 
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return DbHelper.ToList(GetCommand(sql, parameters).ExecuteReader());
            }
        }

        public T GetEntity(string sql, Dictionary parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return DbHelper.ToEntity(GetCommand(sql, parameters).ExecuteReader());
            }
        }

        public List ExcuteList()
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return DbHelper.ToList(GetCommand(string.Format("select * from {0}", typeof(T).Name), null).ExecuteReader());
            }
        }

        public System.Data.DataTable FillDataTable(string sql)
        {
            return FillDataTable(sql, null);
        }

        public System.Data.DataTable FillDataTable(string sql, Dictionary parameters)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            Fill(dt, GetCommand(sql, parameters));
            return dt;           
        }

        public int Fill(System.Data.DataTable dt, System.Data.Common.DbCommand cmd)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                System.Data.Common.DbDataAdapter adapter = CreateAdapter();
                adapter.SelectCommand = cmd;

                return adapter.Fill(dt);
            }
        }
        public int SaveDataTable(System.Data.DataTable dt)
        {
            return SaveDataTable(dt, dt.TableName);
        }
        public int SaveDataTable(System.Data.DataTable dt, string tableName)
        {
            return SaveTable(dt, "select * from " + tableName + " where 1 = 2");
        }

        public int SaveTable(System.Data.DataTable dt, string sql)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                System.Data.Common.DbDataAdapter adapter = CreateAdapter();
                adapter.SelectCommand = GetCommand(sql, null);
                System.Data.Common.DbCommandBuilder cmdBuild = CreateCommandBuilder();
                cmdBuild.DataAdapter = adapter;
                cmdBuild.QuotePrefix = "[";
                cmdBuild.QuoteSuffix = "]";
                return adapter.Update(dt);
            }
        }

        public int SaveDataSet(System.Data.DataSet ds)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                int updates = 0;
                foreach (System.Data.DataTable item in ds.Tables)
                {
                    updates += SaveDataTable(item);
                }
                return updates;
            }
        }

        #endregion

        internal virtual System.Data.Common.DbDataAdapter CreateAdapter()
        {
            throw new System.ApplicationException("DbDataAdapter Can Not Created!");
        }

        public virtual System.Data.Common.DbCommandBuilder CreateCommandBuilder()
        {
            throw new System.ApplicationException("DbCommandBuilder Can Not Created!");
        }

    }
}

namespace DynamicFramework
{
    public sealed class DbHelper
    {
        public static List ToList(System.Data.IDataReader reader) 
        {
            List list = new List();
            Csla.Data.SafeDataReader sr = new Csla.Data.SafeDataReader(reader);
            while (sr.Read())
            {
                T t = Activator.CreateInstance();
                Type entityType = t.GetType();
                for (int i = 0; i < sr.FieldCount; i++)
                {
                    string pName = reader.GetName(i);
                    System.Reflection.PropertyInfo p = entityType.GetProperty(pName);
                    if (p != null)
                    {
                        p.SetValue(t, GetValue(p,sr,i), null);
                    }
                }
                list.Add(t);
            }           
            return list;
        }

        private static object GetValue(System.Reflection.PropertyInfo p,Csla.Data.SafeDataReader sr,int index)
        {
            if (p.PropertyType == typeof(string))
            {
                return sr.GetString(index);
            }
            else if (p.PropertyType == typeof(int))
            {
                return sr.GetInt32(index);
            }
            else if (p.PropertyType == typeof(decimal))
            {
                return sr.GetDecimal(index);
            }
            else if (p.PropertyType == typeof(DateTime))
            {
                return sr.GetDateTime(index);
            }
            else if (p.PropertyType == typeof(bool))
            {
                return sr.GetBoolean(index);
            }
            else if (p.PropertyType == typeof(double))
            {
                return sr.GetDouble(index);
            }
            else
            {
                return sr.GetValue(index);
            }

        }

        public static T ToEntity(System.Data.IDataReader reader)
        {
            Csla.Data.SafeDataReader sr = new Csla.Data.SafeDataReader(reader);
            while (sr.Read())
            {
                T t = Activator.CreateInstance();
                Type entityType = t.GetType();
                for (int i = 0; i < sr.FieldCount; i++)
                {
                    string pName = reader.GetName(i);
                    System.Reflection.PropertyInfo p = entityType.GetProperty(pName);
                    if (p != null)
                    {
                        p.SetValue(t, GetValue(p, sr, i), null);
                    }
                }
                return t;
            }
            return default(T);
        }

        public static List TableToList(System.Data.DataTable dt) 
        {
            return ToList(dt.CreateDataReader());
        }

        public static System.Data.DataTable ListToTable(IList list)
        {
            if (list == null) return null;

            System.Data.DataTable dt = new System.Data.DataTable(typeof(T).Name);

            System.Reflection.PropertyInfo[] props = typeof(T).GetProperties();
            if (props.Length >= 0)
            {
                for (int column = 0; column < props.Length; column++)
                {
                    dt.Columns.Add(props[column].Name, props[column].PropertyType);
                }
            }
            foreach (T item in list)
            {
                System.Data.DataRow dr = dt.NewRow();
                foreach (System.Data.DataColumn column in dt.Columns)
                {
                    dr[column] = item.GetType().GetProperty(column.ColumnName).GetValue(item, null);
                }
                dt.Rows.Add(dr);
            }
            //dt.AcceptChanges();           
            return dt;
        }

        public static System.Data.DataTable ToTable(System.Data.IDataReader reader)
        {
            System.Data.DataTable dt = new System.Data.DataTable();          
            dt.Load(reader);
            return dt;
        }

        public static void SaveEntity(T obj)
        {
            string tb = obj.GetType().Name;
            string SQL = "insert into {0}({1})values({2})";
            string fles = "";
            string sparam = "";
            Dictionary dicParams = new Dictionary();
            foreach (System.Reflection.PropertyInfo var in obj.GetType().GetProperties())
            {
                fles += var.Name + ",";
                sparam += "@" + var.Name + ",";
                dicParams.Add("@" + var.Name,var.GetValue(obj, null));
            }
            SQL = string.Format(SQL, tb, fles.Remove(fles.Length - 1), sparam.Remove(sparam.Length - 1));
            DataAccess.ServerDb.ExecuteScalar(SQL, dicParams);           
        }

        public static void SaveLocalEntity(T obj)
        {
            string tb = obj.GetType().Name;
            string SQL = "insert into {0}({1})values({2})";
            string fles = "";
            string sparam = "";
            Dictionary dicParams = new Dictionary();
            foreach (System.Reflection.PropertyInfo var in obj.GetType().GetProperties())
            {
                fles += var.Name + ",";
                sparam += "@" + var.Name + ",";
                dicParams.Add("@" + var.Name, var.GetValue(obj, null));
            }
            SQL = string.Format(SQL, tb, fles.Remove(fles.Length - 1), sparam.Remove(sparam.Length - 1));
            DataAccess.LocalDb.ExecuteScalar(SQL, dicParams);
        }
    }

    #region DataAsss == OleDb - SqlClient - SQLite

    public class OleAccess : DataAccess
    {
        public OleAccess()
        {
            connection = new System.Data.OleDb.OleDbConnection();
        }

        public OleAccess(string connectionString)
        {
            connection = new System.Data.OleDb.OleDbConnection(connectionString);
            cnnstr = connectionString;
        }

        internal override System.Data.Common.DbDataAdapter CreateAdapter()
        {
            return new System.Data.OleDb.OleDbDataAdapter();
        }

        public override System.Data.Common.DbCommandBuilder CreateCommandBuilder()
        {
            return new System.Data.OleDb.OleDbCommandBuilder();
        }
    }

    public class SqlClientAccess : DataAccess
    {
        public SqlClientAccess()
        {
            connection = new System.Data.SqlClient.SqlConnection();
        }

        public SqlClientAccess(string connectionString)
        {
            connection = new System.Data.SqlClient.SqlConnection(connectionString);
            cnnstr = connectionString;
        }

        internal override System.Data.Common.DbDataAdapter CreateAdapter()
        {
            return new System.Data.SqlClient.SqlDataAdapter();
        }

        public override System.Data.Common.DbCommandBuilder CreateCommandBuilder()
        {
            return new System.Data.SqlClient.SqlCommandBuilder();
        }
    }

    public class SQLiteAccess : DataAccess
    {
        public SQLiteAccess()
        {
            connection = new System.Data.SQLite.SQLiteConnection();
        }

        public SQLiteAccess(string connectionString)
        {
            connection = new System.Data.SQLite.SQLiteConnection(connectionString);
            cnnstr = connectionString;
        }

        internal override System.Data.Common.DbDataAdapter CreateAdapter()
        {
            return new System.Data.SQLite.SQLiteDataAdapter();
        }

        public override System.Data.Common.DbCommandBuilder CreateCommandBuilder()
        {
            return new System.Data.SQLite.SQLiteCommandBuilder();
        }
    }

    #endregion
}