Sql反射增刪查改

using System;using System.Collections.Generic;using System.Text;using System.Reflection;using System.Data;using System.Data.OleDb;namespace DAL{    public class SqlBuilder<T> where T : class    {        #region prop        private List<PropertyInfo> props = new List<PropertyInfo>();        private PropertyInfo pid;        private string _Id;        public string Id        {            get { return _Id; }            set { _Id = value; }        }        private string _TableName;        public string TableName        {            get { return _TableName; }            set { _TableName = value; }        }        public IEnumerable<string> GetNamesExceptId()        {            foreach (PropertyInfo p in props)                yield return p.Name;        }        public SqlBuilder(string id, string tableName)        {            this._Id = id;            this._TableName = tableName;            foreach (PropertyInfo p in typeof(T).GetProperties())            {                if (!p.Name.Equals(id, StringComparison.OrdinalIgnoreCase))                    this.props.Add(p);                else                    this.pid = p;            }        }        #endregion        #region insert        private const string INSERT_SQL = "insert into [{0}] ({1}) values ({2})";        public string BuildParInsert()        {            string f1 = GetMergeFields(false);            string f2 = GetMergeFields(true);            return string.Format(INSERT_SQL, TableName, f1, f2);        }        public OleDbParameter[] GetInsertParameter(T obj)        {            List<OleDbParameter> list = new List<OleDbParameter>();            foreach (PropertyInfo p in props)            {                list.Add(new OleDbParameter("@" + p.Name, p.GetValue(obj, null)));            }            return list.ToArray();        }        public string BuildInsert(T obj)        {            List<string> list = new List<string>();            foreach (PropertyInfo p in props)            {                if (p != null)                    list.Add(GetSqlField(obj, p));            }            string f1 = GetMergeFields(false);            string f2 = string.Join(",", list.ToArray());            return string.Format(INSERT_SQL, TableName, f1, f2);        }        #endregion        #region update        private const string UPDATE_SQL = "update [{0}] set {1} where {2}";        public string BuildUpdate(T obj)        {            List<string> list = new List<string>();            foreach (PropertyInfo p in props)            {                string key = p.Name;                string value = GetSqlField(obj, p);                list.Add(string.Format("[{0}] = {1}", key, value));            }            string f1 = string.Join(",", list.ToArray());            string f2 = string.Format("[{0}] = {1}", Id, GetSqlField(obj, pid));            return string.Format(UPDATE_SQL, TableName, f1, f2);        }        #endregion        #region utils        /// <summary>        /// 擷取insert用參數        /// </summary>        private string GetMergeFields(bool isPar)        {            List<string> list = new List<string>();            foreach (string key in this.GetNamesExceptId())            {                if (isPar)                    list.Add("@" + key);                else                    list.Add("[" + key + "]");            }            return string.Join(",", list.ToArray());        }        /// <summary>        /// 擷取update(Parameters)用參數        /// </summary>        private string GetFieldsPair()        {            List<string> list = new List<string>();            foreach (string key in this.GetNamesExceptId())            {                list.Add(string.Format("{0} = @{0}", key));            }            return string.Join(",", list.ToArray());        }        private static string GetSqlField(T obj, PropertyInfo prop)        {            if (prop.PropertyType.Equals(typeof(string)))                return "'" + prop.GetValue(obj, null) as string + "'";            if (prop.PropertyType.Equals(typeof(DateTime)))                return "#" + prop.GetValue(obj, null).ToString() + "#";            return prop.GetValue(obj, null).ToString();        }        /// <summary>        /// 支援string datetime double int        /// </summary>        private static void SetSqlField(ref T obj, PropertyInfo prop, object value)        {            if (value == null || value == DBNull.Value) return;            if (prop.PropertyType.Equals(typeof(string)))            {                string s = (string)value;                prop.SetValue(obj, s, null);            }            else if (prop.PropertyType.Equals(typeof(DateTime)))            {                DateTime time = (DateTime)value;                prop.SetValue(obj, time, null);            }            else if (prop.PropertyType.Equals(typeof(int)))            {                int i = (int)value;                prop.SetValue(obj, i, null);            }            else if (prop.PropertyType.Equals(typeof(double)))            {                //double i = (double)value;                prop.SetValue(obj, (double)value, null);            }        }        #endregion        #region parser        public static T Parser(IDataReader reader)        {            if (!reader.Read()) return null;            T obj = Activator.CreateInstance<T>();            foreach (PropertyInfo p in typeof(T).GetProperties())            {                int i = ReaderExists(reader, p.Name);                if (i != -1) SetSqlField(ref obj, p, reader[i]);            }            return obj;        }        public static T Parser(DataRow row)        {            if (row == null) return null;            T obj = Activator.CreateInstance<T>();            foreach (PropertyInfo p in typeof(T).GetProperties())            {                int i = TableExists(row.Table, p.Name);                if (i != -1) SetSqlField(ref obj, p, row[i]);            }            return obj;        }        public static List<T> Parser(DataTable dt)        {            List<T> list = new List<T>();            foreach (DataRow row in dt.Rows)            {                T obj = Parser(row);                if (obj != null) list.Add(obj);            }            return list;        }        /// <summary>        /// 判斷 DataTable 裡面是否包含指定的列        /// </summary>        public static int TableExists(DataTable dt, string columnName)        {            for (int i = 0; i < dt.Columns.Count; i++)            {                string name = dt.Columns[i].ColumnName;                if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase)) return i;            }            return -1;        }        /// <summary>        /// 判斷 DataReader 裡面是否包含指定的列        /// </summary>        public static int ReaderExists(IDataReader dr, string columnName)        {            int count = dr.FieldCount;            for (int i = 0; i < count; i++)            {                string name = dr.GetName(i);                if (string.Equals(name, columnName, StringComparison.OrdinalIgnoreCase)) return i;            }            return -1;        }        #endregion    }}

using System;using System.Collections.Generic;using System.Data;using System.Text;namespace DAL{    public abstract class TopService<T> where T : class    {        #region abstract        protected abstract SqlBuilder<T> GetBuilder();        protected abstract string GetTableName();        protected abstract string GetIdName();        public string TableName        {            get { return GetTableName(); }        }        public string IdName        {            get { return GetIdName(); }        }        public SqlBuilder<T> Builder        {            get { return GetBuilder(); }        }        #endregion        #region sql語句增刪查改        /// <summary>        /// 插入一條資料        /// </summary>        public virtual int Insert(T obj)        {            string sql = GetBuilder().BuildInsert(obj);            return DBHelper.ExecuteCommand(sql);        }        /// <summary>        /// 更新一條資料        /// </summary>        public virtual int Update(T obj)        {            string sql = GetBuilder().BuildUpdate(obj);            return DBHelper.ExecuteCommand(sql);        }        /// <summary>        /// 刪除一條資料        /// </summary>        public int Delete(int id)        {            string sql = string.Format("delete from [{0}] where [{1}] = {2}", this.TableName, this.IdName, id.ToString());            return DBHelper.ExecuteCommand(sql);        }        /// <summary>        /// 根據Id擷取對象        /// </summary>        public T GetById(int id)        {            string sql = string.Format("select * from [{0}] where [{1}] = {2}", this.TableName, this.IdName, id);            IDataReader reader = DBHelper.GetReader(sql);            return SqlBuilder<T>.Parser(reader);        }        /// <summary>        /// 擷取所有對象的DataTable        /// </summary>        public DataTable SelectAll()        {            string sql = string.Format("select * from [{0}]", this.TableName);            return GetTableBySql(sql);        }        /// <summary>        /// 根據SQL語句返回解析後的對象列表        /// </summary>        public List<T> GetObjBySql(string sql)        {            DataTable dt = GetTableBySql(sql);            return SqlBuilder<T>.Parser(dt);        }        #endregion        #region 其它功能        /// <summary>        /// 擷取前N項        /// </summary>        public DataTable GetTop(int n, bool isdesc)        {            string sql = string.Format("select top {2} * from [{0}] order by [{1}]", this.TableName, this.IdName, n);            if (isdesc) sql += " desc";            return GetTableBySql(sql);        }        /// <summary>        /// 根據ID排序擷取前N個對象        /// </summary>        public List<T> GetTopObjOrderById(int n, bool isdesc)        {            string sql = string.Format("select top {2} * from [{0}] order by [{1}]", this.TableName, this.IdName, n);            if (isdesc) sql += " desc";            return GetObjBySql(sql);        }        public DataTable GetTableBySql(string sql)        {            try            {                return DBHelper.GetDataSet(sql);            }            catch (Exception e)            {                Console.WriteLine(e.Message);                throw;            }        }        #endregion    }}

效率可能不如預存程序 不過極大減少了代碼量

適用於Access MSSQL(時間 # -> 引號)