sql執(zhí)行類
CURD操作類
其他醬油類
CURD操作類負(fù)責(zé)將用戶提供的條件轉(zhuǎn)換為SQL語(yǔ)句,并提供給IHelper執(zhí)行,返回Model集合.
CURD類需要一個(gè)接口抽象出公共方法.便于修改和擴(kuò)展,提供泛型接口。為了簡(jiǎn)單起見(jiàn)暫時(shí)未提供JOIN的實(shí)現(xiàn),可以以數(shù)據(jù)庫(kù)視圖替代
public?interface?IDbOper<t>?:?IDisposable?where?T?:?new() ????{ ???????object?Insert(T?m);//新增MODEL,返回ID,簡(jiǎn)單起見(jiàn)只做了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版重載,轉(zhuǎn)化為參數(shù)方式執(zhí)行,以參數(shù)方式拼接無(wú)注入風(fēng)險(xiǎn)? ???????IDbOper<t>?Select(Expression<func>>?sl); ???????IDbOper<t>?Where(Expression<func>>?sl); ???????///Dictionary版重載,需要牛頓JSON幫忙轉(zhuǎn)化,以參數(shù)方式拼接無(wú)注入風(fēng)險(xiǎn),此方式用于“等于”等查詢方式,不提供大于小于查詢 ???????IDbOper<t>?Orderby(Dictionary<string>?dic); ???????IDbOper<t>?Where(Dictionary<string>?dic); ????????/// ???????IDbOper<t>?Index(int?i); ???????IDbOper<t>?Size(int?i); ???????T?First();//獲取第一個(gè)model ???????void?BegTran(); ???????void?RollBack(); ???????void?Commit(); ???????M?ToObj<m>(Func<idatareader>?func,string?sql); ???????List<t>?ToList(); //轉(zhuǎn)化為其他類型,若開(kāi)啟了事務(wù)的話需要此轉(zhuǎn)化 ???????IDbOper<m>?ToOper<m>()?where?M?:?new(); ???????int?Count(); ???????//直接執(zhí)行SQL語(yǔ)句 ???????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的操作方式,所以想將這種執(zhí)行方式帶到后臺(tái)操作數(shù)據(jù)庫(kù),廢話不說(shuō)了先定義2個(gè)Model和實(shí)例化一個(gè)操作類
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>
表達(dá)式的執(zhí)行
User?a=db.Select(u=>new{u.ID}).Where(u=>u.ID==54).First();
文本拼接的執(zhí)行
User?a=db.Select("*").Where("ID=54").First();
字典拼接的執(zhí)行
User?a=db.Select("*").Where(new?Dictionary<string>(){Key="ID",Value=54}).First();</string>
分頁(yè)代碼
List<user>?lt=db.Select("*").Where("ID>0").Orderby("ID?Desc").Index(2).Size(20).ToList();</user>
事務(wù)的運(yùn)用
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>
只有當(dāng)調(diào)用Insert,Update,Delete,Count,ToList方法才會(huì)開(kāi)始拼接文本再調(diào)用IHelper執(zhí)行SQL語(yǔ)句,調(diào)用完成后會(huì)自動(dòng)調(diào)用Clear()來(lái)清理保存的where,select等信息。
以下是我提供一個(gè)操作類的實(shí)現(xiàn),大家也可以實(shí)現(xiàn)自己的操作類。
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;//提供一個(gè)默認(rèn)分頁(yè)大小 ????????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為上篇上定義的數(shù)據(jù)庫(kù)操作類,分分種切換到其他數(shù)據(jù)庫(kù)????????????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()) ????????????????{//需要定義一個(gè)特性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>>?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?>?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>>?sl) ????????{ ????????????List<object>?tp=null;?????????????//需要解析表達(dá)式樹(shù)????????????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?>?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?&&?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>?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>>(rd?=>?ToList(rd),sql); ????????}????????//返回List<t>類型????????public?List<t>?ToList(IDataReader?rd) ????????{ ????????????var?lt?=?new?List<t>(); ????????????var?set?=?DelegateExpr.SetMethod(typeof(T));//ExpressTree實(shí)現(xiàn)屬性綁定,以提高M(jìn)odel賦值性能,可以以反射代替 ????????????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?>?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(); ????????} ????}</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>
?以上就是一個(gè)簡(jiǎn)單的ORM制作(CURD操作類)的內(nèi)容,更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)(www.php.cn)!