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

sql执行类

CURD操作类

其他酱油类

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

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

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>&gt; sl);         IDbOper<t> Where(Expression<func>&gt; sl);         ///Dictionary版重载,需要牛顿JSON帮忙转化,以参数方式拼接无注入风险,此方式用于“等于”等查询方式,不提供大于小于查询         IDbOper<t> Orderby(Dictionary<string> dic);         IDbOper<t> Where(Dictionary<string> 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> func,string sql);         List<t> ToList();       //转化为其他类型,若开启了事务的话需要此转化         IDbOper<m> ToOper<m>() where M : new();         int Count();         //直接执行SQL语句         int DoCommand(string sql, bool issp);      }</m></m></t></idatareader></m></t></t></string></t></string></t></func></t></func></t></t></t></t></t>

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

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>

表达式的执行

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

文本拼接的执行

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

字典拼接的执行

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

分页代码

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

事务的运用

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();}</newuser>

只有当调用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 &gt; 0) { continue; }                      if (n.GetCustomAttributes(typeof(Key), false).Length &gt; 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 &gt; 0) { fields.Length--; }                  if (values.Length &gt; 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 &gt; 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 &gt; 0)                      {                          where.Append((where.Length &gt; 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 &gt; 0) { sb.Length--; }                  if (where.Length &gt; 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 &gt; 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 &gt; 0 ? "," : string.Empty) + sl); return this;          }          public IDbOper<t> Select(Expression<func>&gt; 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> dic)          {              if (dic == null || dic.Count == 0) { return this; }              var sb = new StringBuilder(); string tp;              foreach (var n in dic)              {                  if (sb.Length &gt; 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 &gt; 0 ? " AND " : string.Empty) + sl); return this;          }          public IDbOper<t> Where(Expression<func>&gt; 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  Orderby(string orby)          {              if (string.IsNullOrEmpty(orby)) { return this; }              orderby.Append((orderby.Length &gt; 0 ? "," : string.Empty) + orby); return this;          }          public IDbOper<t> Orderby(Dictionary<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 &amp;&amp; string.Compare("ASC",dic[n],true)!=0){continue;}                  sb.Append(n + " " + dic[n] + ",");              }              if (sb.Length &gt; 0) { sb.Length--; }              Orderby(sb.ToString()); return this;          }          public IDbOper<t> Index(int i) { if (i &gt; 0) { index = i; } return this; }          public IDbOper<t> Size(int i) { if (i &gt; 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> 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>&gt;(rd =&gt; 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  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 &gt; 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 &gt; 0) { return lt[0]; }              return default(T);          }          ~DbOper()          {              Dispose();          }      }</m></m></t></t></t></list></t></idatareader></m></t></t></string></t></object></object></func></t></t></string></t></func></t></t></idataparameter></idataparameter></idataparameter></idataparameter></idataparameter></idataparameter></idataparameter></t></t>

 以上就是一个简单的ORM制作(CURD操作类)的内容,更多相关内容请关注PHP中文网(www.php.cn)!

© 版权声明
THE END
喜欢就支持一下吧
点赞15 分享