DataAccess.cs

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<string, object> parameters)
        {
            System.Data.Common.DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (KeyValuePair<string, object> 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<string,object> parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return GetCommand(sql, parameters).ExecuteNonQuery();
            }     
        }

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

        public object ExecuteReader(string sql, Dictionary<string, object> 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<string, object> parameters)
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return DbHelper.ToTable(GetCommand(sql, parameters).ExecuteReader());
            }
        }

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

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

        public List<T> ExcuteList<T>()
        {
            using (connection)
            {
                connection.ConnectionString = cnnstr;
                connection.Open();
                return DbHelper.ToList<T>(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<string, object> 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!\");
        }

    }
}