Dataaccess SqlHelper

   /// <summary>    /// 數據訪問基類    /// </summary>    public abstract class DataAccess : MarshalByRefObject    {        /// <summary>        /// LifetimeService        /// </summary>        /// <returns>object</returns>        public override object InitializeLifetimeService()        {            return null;        }        PRotected string cnnstr = "";        protected DataAccess()        {        }        /// <summary>        /// 獲取壓縮傳輸DataTable        /// </summary>        /// <param name="aSQL">SQL</param>        /// <param name="aParameters">Parameters</param>        /// <returns>byte[]</returns>        public byte[] GetTableData(string aSQL, Dictionary<string, object> aParameters)        {            return DataCompression.CompressionDataTable(GetDataTable(aSQL,aParameters));        }        /// <summary>        /// 保存壓縮數據表        /// </summary>        /// <param name="aDataTable">DataTable</param>        /// <param name="aSQL">Sql</param>        /// <returns>int</returns>        public int SaveDataTable(byte[] aDataTable, string aSQL)        {            return SaveTable(DataCompression.DecompressionxmlDataTable(aDataTable), aSQL);        }        /// <summary>        /// 創建System.Data.Common.DbCommand        /// </summary>        /// <param name="sql">sql</param>        /// <param name="parameters">Dictionary<string, object></param>        /// <returns>System.Data.Common.DbCommand</returns>        private System.Data.Common.DbCommand GetCommand(System.Data.Common.DbConnection connection, string aSQL, Dictionary<string, object> aParameters = null)        {            System.Data.Common.DbCommand cmd = connection.CreateCommand();            cmd.CommandText = application.SQLStatementOpr.PrepareQuery(aSQL);            if (aParameters != null)            {                foreach (KeyValuePair<string, object> item in aParameters)                {                    System.Data.Common.DbParameter parameter = cmd.CreateParameter();                    parameter.ParameterName = item.Key;                    parameter.Value = item.Value;                    cmd.Parameters.Add(parameter);                }            }            return cmd;        }        #region DataAccess Command        /// <summary>        /// 新型查詢        /// </summary>        /// <param name="aSQL">sql</param>        /// <param name="aParameters"> Dictionary參數</param>        /// <returns>影響行數</returns>        public int ExcuteCommand(string aSQL, Dictionary<string, object> aParameters)        {            try            {                using (System.Data.Common.DbConnection connection = CreateConnection())                {                    Authorize(aSQL);                    connection.Open();                    return GetCommand(connection, aSQL, aParameters).ExecuteNonQuery();                }            }            catch (Exception e)            {                throw new Exception("ExcuteCommand error of : " + e.Message + aSQL, e);            }        }        /// <summary>        /// 返回第一行第一列值or Null        /// </summary>        /// <param name="aSQL">sql</param>        /// <returns>object</returns>        public object ExecuteScalar(string aSQL)        {            return ExecuteScalar(aSQL, null);        }        /// <summary>        /// 返回第一行第一列值or Null        /// </summary>        /// <param name="aSQL">sql</param>        /// <param name="aParameters">Dictionary參數</param>        /// <returns>object</returns>        public object ExecuteScalar(string aSQL, Dictionary<string, object> aParameters)        {            try            {                using (System.Data.Common.DbConnection connection = CreateConnection())                {                    Authorize(aSQL);                    connection.Open();                    return GetCommand(connection, aSQL, aParameters).ExecuteScalar();                }            }            catch (Exception e)            {                throw new Exception("ExecuteScalar error of : " + e.Message + aSQL, e);            }        }        /// <summary>        /// 查詢返回DbDataReader        /// </summary>        /// <param name="aSQL">sql</param>        /// <param name="aParameters">Dictionary參數</param>        /// <returns>System.Data.Common.DbDataReader</returns>        public System.Data.Common.DbDataReader ExecuteReader(string aSQL, Dictionary<string, object> aParameters)        {            System.Data.Common.DbConnection connection = CreateConnection();            connection.Open();            return GetCommand(connection, aSQL, aParameters).ExecuteReader(System.Data.CommandBehavior.CloseConnection);        }        /// <summary>        /// DbDataAdapter Fill DataTable        /// </summary>        /// <param name="aDataTable">DataTable</param>        /// <param name="aSQL">sql</param>        /// <returns>影響行數</returns>        public int Fill(System.Data.DataTable aDataTable, string aSQL)        {            return Fill(aDataTable, aSQL, null);        }        /// <summary>        /// 使用TableName填充        /// </summary>        /// <param name="aDataTable">DataTable</param>        /// <returns>影響行數</returns>        public int Fill(System.Data.DataTable aDataTable)        {            return Fill(aDataTable, "select * from " + aDataTable.TableName);        }        /// <summary>        /// DbDataAdapter Fill DataTable        /// </summary>        /// <param name="aDataTable">DataTable</param>        /// <param name="aSQL">sql</param>        /// <param name="aParameters">Dictionary</param>        /// <returns>影響行數</returns>        public int Fill(System.Data.DataTable aDataTable, string aSQL, Dictionary<string, object> aParameters)        {            try            {                using (System.Data.Common.DbConnection connection = CreateConnection())                {                    Authorize(aSQL);                    connection.Open();                    System.Data.Common.DbDataAdapter adapter = CreateAdapter();                    adapter.SelectCommand = GetCommand(connection, aSQL, aParameters);                    //adapter.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;  //David 2014-5-1                    return adapter.Fill(aDataTable);                }            }            catch (Exception e)            {                throw new Exception("FillDataTable error of : " + e.Message + aSQL, e);            }        }                /// <summary>        /// DbDataAdapter Fill DataTable        /// </summary>        /// <param name="aSQL">sql</param>        /// <returns>DataSet</returns>        public System.Data.DataSet GetDataSet(string aSQL)        {            return GetDataSet(aSQL, null);        }        /// <summary>        /// DbDataAdapter Fill DataTable        /// </summary>        /// <param name="aSQL">sql</param>        /// <param name="aParameters">Dictionary</param>        /// <returns>DataSet</returns>        public System.Data.DataSet GetDataSet(string aSQL, Dictionary<string, object> aParameters)        {            System.Data.DataSet ds = new System.Data.DataSet();            FillDataSet(ds, aSQL, aParameters);            return ds;        }        /// <summary>        /// DbDataAdapter Fill DataTable        /// </summary>        /// <param name="aDataSet">DataSet</param>        /// <returns>影響行數</returns>        public int FillDataSet(System.Data.DataSet aDataSet)        {            int iReturn = 0;            for (int i = 0; i < aDataSet.Tables.Count; i++)