匿名通过本文主要向大家介绍了ORM制作，CURD等相关知识,希望本文的分享对您有所帮助
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.