SQL执行类
CURD操作类
其他酱油类
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();
}
} 以上就是一个简单的ORM制作(CURD操作类)的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！
Pengenalan kepada rangka kerja yang digunakan oleh vscode
Apakah itu Bitcoin? Adakah ia satu penipuan?
Bagaimana untuk mengimport easygui dalam vscode
Apakah itu Bitcoin ETF?
Bagaimana untuk memadam emotikon WeChat
Bagaimana untuk menetapkan textarea baca sahaja
Perbezaan antara wlan dan wifi
Adakah sah untuk membeli dan menjual Bitcoin di Huobi.com?
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
