一个简单的ORM制作(CURD操作类)

1. SQL执行类
2. CURD操作类
3. 其他酱油类

此篇是为上篇文章填坑的，不知道上篇砸过来的砖头够不够，不够的话请大家继续砸。

CURD操作类负责将用户提供的条件转换为SQL语句,并提供给IHelper执行,返回Model集合.

CURD类需要一个接口抽象出公共方法.便于修改和扩展,提供泛型接口。为了简单起见暂时未提供JOIN的实现，可以以数据库视图替代

public interface IDbOper<T> : IDisposable where T : new()
    {
       object Insert(T m);//新增MODEL，返回ID，简单起见只做了INT自增
       int Update(string str);//批量更新
       int Update(T m);//Model更新
       int Delete();//删除
        ///拼接字符版，需要自己防止注入，特别是Orderby容易被忽视
       IDbOper<T> Select(string sl);//选择字段
       IDbOper<T> Where(string sl);
       IDbOper<T> Orderby(string orby);
        ///Expression版重载，转化为参数方式执行，以参数方式拼接无注入风险 

       IDbOper<T> Select(Expression<Func<T, object>> sl);
       IDbOper<T> Where(Expression<Func<T, bool>> sl);
       ///Dictionary版重载，需要牛顿ＪＳＯＮ帮忙转化，以参数方式拼接无注入风险,此方式用于“等于”等查询方式，不提供大于小于查询
       IDbOper<T> Orderby(Dictionary<string, string> dic);
       IDbOper<T> Where(Dictionary<string, object> dic);
        ///
       IDbOper<T> Index(int i);
       IDbOper<T> Size(int i);
       T First();//获取第一个model
       void BegTran();
       void RollBack();
       void Commit();

       M ToObj<M>(Func<IDataReader, M> func,string sql);
       List<T> ToList();
　　　　　//转化为其他类型，若开启了事务的话需要此转化
       IDbOper<M> ToOper<M>() where M : new();
       int Count();

       //直接执行SQL语句

       int DoCommand(string sql, bool issp);
    }

由于比较喜欢ＪＱ的操作方式，所以想将这种执行方式带到后台操作数据库，废话不说了先定义２个Ｍodel和实例化一个操作类

public class User
{
     [Key]
     public int ID{get;set;}
     public string UserName{get;set;}
     public string Password{get;set;}
}
public class NewUser
{
     [Key]
     public int ID{get;set;}
     public string UserName{get;set;}
     public string Password{get;set;}
}

var db=new DbOper<User>(new DbInfo(){DbType="…",DbConntion="…"}); 

表达式的执行

User a=db.Select(u=>new{u.ID}).Where(u=>u.ID==54).First();

文本拼接的执行

User a=db.Select("*").Where("ID=54").First();

字典拼接的执行

User a=db.Select("*").Where(new Dictionary<string, object>(){Key="ID",Value=54}).First();

分页代码

List<User> lt=db.Select("*").Where("ID>0").Orderby("ID Desc").Index(2).Size(20).ToList();

事务的运用

db.BegTran();
try{
    int b=db.Where("ID=54").Delete();//user表删除ID=54
    int c=db.ToOper<NewUser>().Insert(new NewUser(){UserName="…",Password="…"});//newuser表新增一条记录
    db.Commit();
}
catch{db.RollBack();}

只有当调用Insert，Update，Delete，Count，ToList方法才会开始拼接文本再调用IHelper执行SQL语句，调用完成后会自动调用Clear()来清理保存的where，select等信息。

以下是我提供一个操作类的实现，大家也可以实现自己的操作类。

internal class DbOper<T> :IDbPhysiceOper<T>, IDisposable where T : new()
    {
        internal IHelper db;
        internal StringBuilder where;
        internal StringBuilder select;
        internal StringBuilder orderby;
        internal List<IDataParameter> ps;
        internal StringBuilder sqlinfo;
        internal int index = 0;
        internal int size = OrmGlobal.PageSize;//提供一个默认分页大小
        private DbOper(IHelper h, StringBuilder w, StringBuilder s, StringBuilder or, List<IDataParameter> p,StringBuilder sql)
        {
            db = h;
            where = w;
            select = s;
            orderby = or;
            sqlinfo = sql;
            ps = p;
        }
        internal DbOper(DbInfo info)
        {            //db为上篇上定义的数据库操作类,分分种切换到其他数据库            if (info.DbType.Equals("mssql"))
            {
                db = new Helper.Mssql(info.DbConntion);
            }
            else if (info.DbType.Equals("msmars"))
            {
                db = new Helper.MsMars(info.DbConntion);
            }
            else if (info.DbType.Equals("mysql"))
            {
                db = new Helper.Mysql(info.DbConntion);
            }
            where = new StringBuilder();
            select = new StringBuilder();
            orderby = new StringBuilder();
            sqlinfo = new StringBuilder();
            ps = new List<IDataParameter>();
        }
        public object Insert(T m)
        {
            try
            {
                StringBuilder fields = new StringBuilder();
                StringBuilder values = new StringBuilder();
                List<IDataParameter> lt = new List<IDataParameter>();
                string tp = string.Empty; object o = null;
                foreach (var n in m.GetType().GetProperties())
                {
                    if (n.GetCustomAttributes(typeof(ExcludeColumn), false).Length > 0) { continue; }
                    if (n.GetCustomAttributes(typeof(Key), false).Length > 0) { continue; }
                    o = n.GetValue(m,null);//4.5o = n.GetValue(m);
                    if (o == null) { continue; }
                    fields.Append(n.Name + ",");
                    tp = db.ParStr(n.Name);
                    values.Append(tp + ",");
                    lt.Add(db.Cp(tp, o));
                }
                if (fields.Length > 0) { fields.Length--; }
                if (values.Length > 0) { values.Length--; }
                tp = "INSERT INTO " + typeof(T).Name + "(" + fields.ToString() + ")VALUES(" + values.ToString() + ") " + db.GetIdStr;
                if (OrmGlobal.isrecord) { Record(tp); }
                object a = db.ExectueScalar(tp, lt, false);
                Clear();
                return a;
            }
            catch
            {
                OrmGlobal.DoErr(sqlinfo.ToString()); throw;
            }
        }
        public int Update(string str)
        {
            try
            {
                string tp = "UPDATE " + typeof(T).Name + " SET " + str + (where.Length > 0 ? " WHERE " + where : string.Empty);
                if (OrmGlobal.isrecord) { Record(tp); }
                int i = db.ExecuteQuery(tp, ps, false);
                Clear();
                return i;
            }
            catch
            {
                OrmGlobal.DoErr(sqlinfo.ToString()); throw;
            }
        }
        public int Update(T m)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("UPDATE " + typeof(T).Name + " SET ");
                List<IDataParameter> lt = new List<IDataParameter>();
                object o = null;
                foreach (var n in m.GetType().GetProperties())
                {//需要定义一个特性Key,以便更新Model                    o = n.GetValue(m,null);//4.5o = n.GetValue(m);
                    if (o == null) { continue; }
                    if (n.GetCustomAttributes(typeof(Key), false).Length > 0)
                    {
                        where.Append((where.Length > 0 ? " AND " : string.Empty) + n.Name + "=" + db.ParStr(n.Name));
                        lt.Add(db.Cp(db.ParStr(n.Name), o));
                        continue;
                    }
                    sb.Append(n.Name + "=" + db.ParStr(n.Name) + ",");
                    lt.Add(db.Cp(db.ParStr(n.Name), o));
                }
                if (sb.Length > 0) { sb.Length--; }
                if (where.Length > 0) { sb.Append(" WHERE " + where); }
                var sql = sb.ToString();
                if (OrmGlobal.isrecord) { Record(sql); }
                int i = db.ExecuteQuery(sql, lt, false);
                Clear();
                return i;
            }
            catch
            {
                OrmGlobal.DoErr(sqlinfo.ToString()); throw;
            }
        }
        public int Delete()
        {
            try
            {
                string sql = "DELETE FROM " + typeof(T).Name + (where.Length > 0 ? " WHERE " + where : string.Empty);
                if (OrmGlobal.isrecord) { Record(sql); }
                int i = db.ExecuteQuery(sql, ps, false);
                Clear();
                return i;
            }
            catch
            {
                OrmGlobal.DoErr(sqlinfo.ToString()); throw;
            }
        }
        public IDbOper<T> Select(string sl)
        {
            if (string.IsNullOrEmpty(sl)) { return this; }
            select.Append((select.Length > 0 ? "," : string.Empty) + sl); return this;
        }
        public IDbOper<T> Select(Expression<Func<T, object>> sl)
        {
            string tp=null;
            using (var tp1 = new LinqVisitor())
            {
                tp=tp1.VisitNew(sl.Body as NewExpression);
            }
            return Select(tp);
        }
        public IDbOper<T> Where(Dictionary<string, object> dic)
        {
            if (dic == null || dic.Count == 0) { return this; }
            var sb = new StringBuilder(); string tp;
            foreach (var n in dic)
            {
                if (sb.Length > 0) { sb.Append(" AND "); }
                sb.Append(n.Key);
                if (n.Value is string)
                {
                    tp = n.Value.ToString();
                    if (tp.Substring(tp.Length - 1, 1) == "*")
                    {
                        sb.Append(" LIKE ");
                        tp = tp.Substring(0, tp.Length - 1) + "%";
                    }
                    else { sb.Append("="); }
                    ps.Add(db.Cp(db.ParStr(n.Key), tp));
                }
                else
                {
                    sb.Append("=");
                    ps.Add(db.Cp(db.ParStr(n.Key), n.Value));
                }
                sb.Append(db.ParStr(n.Key));
            }
            Where(sb.ToString());
            return this;
        }
        public IDbOper<T> Where(string sl)
        {
            if (string.IsNullOrEmpty(sl)) { return this; }
            where.Append((where.Length > 0 ? " AND " : string.Empty) + sl); return this;
        }
        public IDbOper<T> Where(Expression<Func<T, bool>> sl)
        {
            List<object> tp=null;             //需要解析表达式树            using (var tp1 = new LinqVisitor())
            {
                tp = tp1.Visit(sl) as List<object>;
                StringBuilder sb = new StringBuilder(); string s = string.Empty;
                for (int i = 0; i < tp.Count; i += 4)
                {
                    s = db.ParStr(tp[i].ToString());
                    sb.Append(tp[i].ToString() + tp[i + 1].ToString() + s);
                    if (i + 4 < tp.Count) { sb.Append(tp[i + 3]); }
                    ps.Add(db.Cp(s, tp[i + 2]));
                }
                Where(sb.ToString());
            }
            return this;
        }
        public IDbOper<T> Orderby(string orby)
        {
            if (string.IsNullOrEmpty(orby)) { return this; }
            orderby.Append((orderby.Length > 0 ? "," : string.Empty) + orby); return this;
        }
        public IDbOper<T> Orderby(Dictionary<string, string> dic)
        {
            if (dic.Count == 0) { return this; }
            StringBuilder sb = new StringBuilder();
            foreach (var n in dic.Keys)
            {
                if(string.Compare("DESC",dic[n],true)!=0 && string.Compare("ASC",dic[n],true)!=0){continue;}
                sb.Append(n + " " + dic[n] + ",");
            }
            if (sb.Length > 0) { sb.Length--; }
            Orderby(sb.ToString()); return this;
        }
        public IDbOper<T> Index(int i) { if (i > 0) { index = i; } return this; }
        public IDbOper<T> Size(int i) { if (i > 0) { size = i; } return this; }
        public void BegTran() { db.BegTran(); }
        public void RollBack() { db.RollBack(); }
        public void Commit() { db.Commit(); }
        public void Clear()
        {
            where.Length = 0; select.Length = 0; orderby.Length = 0; ps.Clear(); index = 0; size = OrmGlobal.size;
        }
        public M ToObj<M>(Func<IDataReader, M> func, string sql)
        {
            try
            {
                if (OrmGlobal.isrecord) { Record(sql); }
                var rd = db.ExectueReader(sql, ps, false);
                M t = func(rd);
                rd.Close(); Clear();
                return t;
            }
            catch
            {
                OrmGlobal.DoErr(sqlinfo.ToString()); throw;
            }
        }
        public List<T> ToList() 
        {
            string sql = GetSql();
            return ToObj<List<T>>(rd => ToList(rd),sql);
        }        //返回List<T>类型        public List<T> ToList(IDataReader rd)
        {
            var lt = new List<T>();
            var set = DelegateExpr.SetMethod(typeof(T));//ExpressTree实现属性绑定,以提高Model赋值性能，可以以反射代替
            while (rd.Read())
            {
                var m = new T();
                for (var i = 0; i < rd.FieldCount; i++)
                {
                    if (rd[i] == DBNull.Value || rd[i] == null) { continue; }
                    set(m, rd.GetName(i).ToLower(), rd[i]);
                }
                lt.Add(m);
            }
            return lt;
        }
        public string GetSql()
        {
            return db.CreateSql(select.ToString(), typeof(T).Name, where.ToString(), orderby.ToString(), size, index);
        }
        public IDbOper<M> ToOper<M>() where M:new()
        {
            Clear();
            return new DbOper<M>(db,where,select,orderby,ps,sqlinfo);
        }
        public int Count()
        {
            try
            {
                string sql = "SELECT COUNT(*) FROM " + typeof(T).Name + (where.Length > 0 ? " WHERE " + where : string.Empty);
                if (OrmGlobal.RecordLog) { Record(sql); }
                int i= (int)db.ExectueScalar(sql, ps, false);
                Clear();
                return i;
            }
            catch
            {
                OrmGlobal.DoErr(sqlinfo.ToString()); throw;
            }
        }
        public int DoCommand(string sql,bool issp)
        {
            int i=db.ExecuteQuery(sql,ps,issp);
            Clear();
            return i;
        }
        public void Dispose()
        {
            where = null; select = null; orderby = null; db.Dispose(); ps = null; sqlinfo = null; 
            GC.SuppressFinalize(this);
        }
        public T First()
        {
            var lt=Size(1).Index(1).ToList();
            if (lt.Count > 0) { return lt[0]; }
            return default(T);
        }
        ~DbOper()
        {
            Dispose();
        }
    }