public static object Where() { ///Join、Where、Having 查询参数:条件之间可用运算符 “|”(Or方法)或“&”(And方法)表明条件与条件之间的关系; ///如同时列出多个条件,则说明SQL语句要同时满足所有条件(即“And”关系); ///Where和Having方法可以多次调用,每调一次SQL语句累加一个条件(And、Or、Groupby、OrderbyAsc、OrderbyDesc方法类同); ///与 Where 方法同等级的 And 方法是等效的 ///也就是说,数据筛选条件可以根据业务情况动态增加; /// IS NULL/ IS NOT NULL 条件可由字段直接带出,如:ColEmailAddr.IsNotNull ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var UR = ctx.GetCmd <CmdSysUserRole>(); var data = U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O") .Where(U.ColStatus == "O", U.ColEmailAddr.IsNotNull.Or(U.ColEmailAddr == "*****@*****.**"), U.ColIsLocked == "N") .Where(UR.ColRoleCode.In("Administrator", "Admin", "PowerUser", "User", "Guest")) .Groupby(UR.ColRoleCode) .Having(U.ColUserAccount.Count > 2) .OrderbyAsc(U.ColUserAccount.Count) .Select(U.ColUserAccount.Count.As("USER_COUNT"), UR.ColRoleCode); //以下写法是等效的 U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O"); U.Where(U.ColStatus == "O"); U.Where(U.ColEmailAddr.IsNotNull.Or(U.ColEmailAddr == "*****@*****.**")); U.And(U.ColIsLocked == "N"); U.Where(UR.ColRoleCode.In("Administrator", "Admin", "PowerUser", "User", "Guest")); U.Groupby(UR.ColRoleCode); U.Having(U.ColUserAccount.Count > 2); U.OrderbyAsc(U.ColUserAccount.Count); data = U.Select(U.ColUserAccount.Count.As("USER_COUNT"), UR.ColRoleCode); return(data); }
public static object SubQuery() { ////嵌套子查询需要把一个查询子句转换成视图(ToView方法),转换成视图之后可以把它视作普通的Cmd使用。 ///视图里ViewColumns是视图字段的集合。 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var R = ctx.GetCmd <CmdSysRole>(); var UR = ctx.GetCmd <CmdSysUserRole>(); var UA = ctx.GetCmd <CmdSysUserAuthorization>(); var RA = ctx.GetCmd <CmdSysRoleAuthorization>(); var Admin = U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O") .InnerJoin(R, UR.ColRoleCode == R.ColRoleCode, R.ColStatus == "O") .Where(U.ColStatus == "O") .ToView(U.ColUserAccount.As("SYS_USER"), U.ColUserName, R.ColRoleCode.As("SYS_ROLE"), R.ColRoleName); ////子查询 var data = Admin.InnerJoin(UA, UA.ColUserAccount == Admin.ViewColumns[1], UA.ColIsForbidden == "N") .InnerJoin(RA, RA.ColRoleCode == Admin.ViewColumns[2], RA.ColIsForbidden == "N") .Where(Admin.ViewColumns[1] == "张三", Admin.ViewColumns[2] == "Administrator") .Select(); return(data); }
public static object GroupByHaving() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var UR = ctx.GetCmd <CmdSysUserRole>(); var data = U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O") .Where(U.ColStatus == "O", UR.ColRoleCode.In("Administrator", "Admin", "PowerUser", "User", "Guest")) .Groupby(UR.ColRoleCode) .Having(U.ColUserAccount.Count > 2) .OrderbyAsc(UR.ColRoleCode, U.ColUserAccount.Count) .Select(U.ColUserAccount.Count.As("USER_COUNT"), UR.ColRoleCode); return(data); }
public static object Exists() { ODAContext ctx = new ODAContext(); var RA = ctx.GetCmd <CmdSysRoleAuthorization>(); //Exists 子查询的条件 var RS = ctx.GetCmd <CmdSysResource>(); RA.Where(RA.ColIsForbidden == "N", RA.ColStatus == "O", RA.ColResourceId == RS.ColId); var data = RS.Where(RS.ColStatus == "O", RS.Function.Exists(RA, RA.AllColumn)) .SelectM(); return(data); }
public static object Join() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var R = ctx.GetCmd <CmdSysRole>(); var UR = ctx.GetCmd <CmdSysUserRole>(); ///LeftJoin\InnerJoin\RightJoin 可以无限连接,但实际上每种数据库都有对SQL语句长度作限制。 var data = U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O") .LeftJoin(R, UR.ColRoleCode == R.ColRoleCode, R.ColStatus == "O") .Where(U.ColStatus == "O", R.ColRoleCode == "Administrator") .Select <UserDefineModel>(U.ColUserAccount.As("UserAccount"), U.ColUserName.As("UserName"), R.ColRoleCode.As("Role"), R.ColRoleName.As("RoleName")); return(data); }
public static object In() { ODAContext ctx = new ODAContext(); var RA = ctx.GetCmd <CmdSysRoleAuthorization>(); var RS = ctx.GetCmd <CmdSysResource>(); ///IN 数组 RA.Where(RA.ColIsForbidden == "N", RA.ColStatus == "O", RA.ColRoleCode.In("Administrator", "Admin", "PowerUser")); ///IN 子查询 var data = RS.Where(RS.ColStatus == "O", RS.ColId.In(RA, RA.ColResourceId)) .SelectM(); return(data); }
public static void Sql() { ODAContext ctx = DBConfig.GetODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var UR = ctx.GetCmd <CmdSysUserRole>(); var data = U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O") .Where(U.ColStatus == "O", U.ColEmailAddr.IsNotNull.Or(U.ColEmailAddr == "*****@*****.**"), U.ColIsLocked == "N", UR.ColRoleCode.In("Administrator", "Admin", "PowerUser", "User", "Guest")) .Groupby(UR.ColRoleCode) .Having(U.ColUserAccount.Count > 2) .OrderbyAsc(U.ColUserAccount.Count) .Select(U.ColUserAccount.Count.As("USER_COUNT"), UR.ColRoleCode); }
public static void Paging() { ODAContext ctx = DBConfig.GetODAContext(); int total = 0; var t = ctx.GetCmd <CmdTest>(); var list = t.SelectM(20 * 50, 50, out total); }
public static object List() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var R = ctx.GetCmd <CmdSysRole>(); var UR = ctx.GetCmd <CmdSysUserRole>(); var data = U.ListCmd(UR, R) .Where(U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O", UR.ColRoleCode == R.ColRoleCode, R.ColStatus == "O", U.ColStatus == "O", R.ColRoleCode == "Administrator") .Select <UserDefineModel>(U.ColUserAccount.As("UserAccount"), U.ColUserName.As("UserName"), U.ColEmailAddr.As("Email"), R.ColRoleCode.As("Role"), R.ColRoleName.As("RoleName")); return(data); }
public static void Delete() { ////Delete的where条件可参考 SELECT 语句 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .Delete(); }
public static void Insert() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); U.Insert(U.ColStatus == "O", U.ColCreatedBy == "User1", U.ColLastUpdatedBy == "User1", U.ColLastUpdatedDate == DateTime.Now, U.ColCreatedDate == DateTime.Now, U.ColUserAccount == "Nyear", U.ColUserName == "多年", U.ColUserPassword == "123", U.ColFeMale == "M", U.ColFailTimes == 0, U.ColIsLocked == "N"); }
public static object Union() { ///被Union或UnionAll的是视图。要求视图与查询的字段的数据库类型及顺序及数据一致(数据库本身的要求,非ODA要求)。 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var UR = ctx.GetCmd <CmdSysUserRole>(); var RA = ctx.GetCmd <CmdSysRoleAuthorization>(); var RS = ctx.GetCmd <CmdSysResource>(); var U1 = ctx.GetCmd <CmdSysUser>(); var UA = ctx.GetCmd <CmdSysUserAuthorization>(); var RS1 = ctx.GetCmd <CmdSysResource>(); U.InnerJoin(UR, U.ColUserAccount == UR.ColUserAccount, UR.ColStatus == "O") .InnerJoin(RA, RA.ColRoleCode == UR.ColRoleCode, RA.ColStatus == "O") .InnerJoin(RS, RS.ColId == RA.ColResourceId, RS.ColStatus == "O") .Where(U.ColUserAccount == "User1"); U1.InnerJoin(UA, U1.ColUserAccount == UA.ColUserAccount, UA.ColStatus == "O") .InnerJoin(RS1, RS1.ColId == UA.ColResourceId, RS1.ColStatus == "O") .Where(U1.ColUserAccount == "User1"); var data = U.Union(U1.ToView(U1.ColUserAccount, U1.ColUserName, UA.ColIsForbidden, RS1.ColId, RS1.ColResourceType, RS1.ColResourceScope, RS1.ColResourceLocation )) .Select(U.ColUserAccount, U.ColUserName, RA.ColIsForbidden, RS.ColId, RS.ColResourceType, RS.ColResourceScope, RS.ColResourceLocation ); return(data); }
public static object UserSQL() { ///如果SQL语可以重复使用,或者为有程序更规范,推荐派生 ODACmd 类 重写SQL生成方法 ODAContext ctx = new ODAContext(); var sql = ctx.GetCmd <SQLCmd>(); var data = sql.Select("SELECT * FROM SYS_USER WHERE USER_ACCOUNT = @T1", ODAParameter.CreateParam("@T1", "User1")); return(data); }
public static object RecommendProcedure() { /// 推荐派生 ODACmd 类 重写SQL生成方法 ODAContext ctx = new ODAContext(); var sql = ctx.GetCmd <SQLCmd>(); var data = sql.Procedure(""); return(data); }
public static object Distinct() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .Distinct.Select(U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); return(data); }
public static void UpdateCompute() { ////支持的运算符号:+ 、 - 、*、/、% ///目前对一个字段更新时,只支持一个运算符号; ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .Update(U.ColFailTimes == U.ColFailTimes + 1, U.ColUserName == U.ColUserAccount + U.ColEmailAddr); }
public static object SelectM() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); List <SYS_USER> data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColStatus == "O", U.ColEmailAddr.IsNotNull) .SelectM(U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); return(data); }
public static object Procedure() { ///如果SQL语可以重复使用,或者为有程序更规范,推荐派生 ODACmd 类 重写SQL生成方法 ODAContext ctx = new ODAContext(); var sql = ctx.GetCmd <SQLCmd>(); var data = sql.Procedure(""); return(data); }
public static object NullDefault() { ///NullDefault 是对CaseWhen方法的再次封装,以方便应用 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Select(U.Function.NullDefault(U.ColAddress, "无用户地址数据...").As("ADDRESS"), U.Function.NullDefault(U.ColPhoneNo, 110).As("PHONE_NO")); return(data); }
public static object VisualColumn() { ///VisualColumn 方法是对 Express 方法的再次封装,为应用提供方便,免出数据转换麻烦、避免SQL注入风险、保证数据库通用。 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Select(U.Function.VisualColumn("HELLO , I am NYear software").As("STRING_COLUMN"), U.Function.VisualColumn(DateTime.Now).As("APPLICATION_DATETIME"), U.Function.VisualColumn(0).As("DIGIT_COLUMN")); return(data); }
public static object SelectPaging() { ODAContext ctx = new ODAContext(); int total = 0; var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .SelectM(0, 20, out total, U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); return(data); }
public static object UserDefined() { ///CreateFunc 方法,用可在 SELECT 字段中加入自定义的数据库函数,但不同的数据库对调用自定义函数的方法差异太大,ODA无法将其统一。 ///所以若要ODA Function.CreateFunc方法也要以跨数据库,则需要在创建数据库时,特殊处理数据库的schema,user,dbowner,database等对象的名称。 ODAContext ctx = new ODAContext(); var RS = ctx.GetCmd <CmdSysResource>(); object data = RS.Where(RS.ColStatus == "O", RS.ColResourceType == "MENU") .Select(RS.AllColumn, RS.Function.CreateFunc("dbo.GET_RESOURCE_PATH", RS.ColId).As("RESOURCE_PATH")); return(data); }
public static object Decode() { ///ODA Decode方法 模拟Oracle内置Decode函数,对Case方法的再次封装,以方便应用 ODAContext ctx = new ODAContext(); var RS = ctx.GetCmd <CmdSysResource>(); object data = RS.Where(RS.ColStatus == "O", RS.ColResourceType == "MENU") .Select(RS.Function.Decode(RS.ColResourceType, "未知类型", "WEB", "网页资源", "WFP_PAGE", "WPF页面资源", "WPF_WIN", "WPF程序窗口", "WIN_FORM", "FORM窗口").As("RESOURCE_TYPE") , RS.AllColumn); return(data); }
public static object SelectFirst() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .SelectDynamicFirst(U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); string UserName = data.USER_NAME; ///属性 USER_NAME 与 ColUserName 的ColumnName一致,如果没有数据则返回null return(data); }
public static void Update() { ///Update 的 where 条件可参考 SELECT 语句 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColStatus == "O", U.ColEmailAddr.IsNotNull) .Update( U.ColUserName == "新的名字", U.ColIsLocked == "Y" ); }
public static object Function() { ///ODA提供数据库常用的通用系统函数:MAX, MIN, COUNT, SUM, AVG, LENGTH, LTRIM, RTRIM, TRIM, ASCII, UPPER, LOWER ///这些函数由字段直接带出,如:ColUserAccount.Count ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Groupby(U.ColUserAccount) .Select(U.Function.Count.As("CountAll"), U.ColUserAccount.Count.As("CountOne"), U.ColUserAccount.Upper.As("UPPER_ACC"), U.ColUserAccount.Trim.Ltrim.As("TRIM_ACC")); return(data); }
public static object OrderBy() { ///OrderbyAsc 或OrderbyDesc 对数据按顺序或倒序排列,先给出的排序条件优先排。 ///OrderbyAsc 或OrderbyDesc 参数可以是多个字段。 ODAContext ctx = new ODAContext(); var RS = ctx.GetCmd <CmdSysResource>(); var datra = RS.Where(RS.ColResourceType == "WEB", RS.ColStatus == "O") .OrderbyDesc(RS.ColResourceIndex) .SelectM(); return(datra); }
public static object Hook() { ///开发者可以通过ODA钩子自定义SQL路由,在SQL执行前对SQL进行修改; ODAContext.CurrentExecutingODASql += ODASqlExecutingEvent; ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .Select(U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); ODAContext.CurrentExecutingODASql -= ODASqlExecutingEvent; return(data); }
public static object Monitor() { ///开发者可能通过此钩子,可以监控所有发送给数据库SQL语句及其参数。 ODAContext.CurrentExecutingSql += SqlExecutingEvent; ODAContext ctx = new ODAContext(); int total = 0; var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .Select(0, 20, out total, U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); ODAContext.CurrentExecutingSql -= SqlExecutingEvent; return(data); }
public static object SelectDefine() { ////返回的实体类型可以是任意自定义类型,并不一定是对应数据库的实体 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); List <SYS_USER> data = U.Where(U.ColUserAccount == "User1") .And(U.ColIsLocked == "N") .And(U.ColStatus == "O") .And(U.ColEmailAddr.IsNotNull) .Select <SYS_USER>(U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); return(data); }