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(时间 # -> 引号)