SQL execution class
CURD operation class
Other soy sauce classes
The CURD operation class is responsible for converting the conditions provided by the user into SQL statements are provided to IHelper for execution and return the Model collection.
The CURD class requires an interface to abstract public methods. It is easy to modify and extend and provides a generic interface. For the sake of simplicity, the implementation of JOIN is not provided for the time being. You can use the database view instead
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);
}Since I prefer the JQ operation method, I want to bring this execution method to the background to operate the database. Without further ado, define 2 first. Model and instantiate an operation class
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="…"});Execution of expression
User a=db.Select(u=>new{u.ID}).Where(u=>u.ID==54).First();Execution of text splicing
User a=db.Select("*").Where("ID=54").First();Execution of dictionary splicing
User a=db.Select("*").Where(new Dictionary<string, object>(){Key="ID",Value=54}).First();Paging code
List<User> lt=db.Select("*").Where("ID>0").Orderby("ID Desc").Index(2).Size(20).ToList();Use of transactions
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();}Only when the Insert, Update, Delete, Count, and ToList methods are called, the text will be spliced and then IHelper will be called to execute the SQL statement. Clear( will be automatically called after the call is completed. ) to clean up the saved where, select and other information.
The following is the implementation of an operation class that I provide. You can also implement your own operation class.
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();
}
} The above is the content of a simple ORM production (CURD operation class). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
