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 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")); var data1 = sql.Select <DataColumn>(0, 100, "SELECT * FROM SYS_USER WHERE USER_ACCOUNT = @T1", "", ODAParameter.CreateParam("@T1", "User1")); 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 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 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 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 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 Select() { ///ODA语法就SQL语法的直接映射,并尽量简法 ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); object data = U.Where(U.ColUserAccount == "User1") .And(U.ColIsLocked == "N") .And(U.ColStatus == "O") .And(U.ColEmailAddr.IsNotNull) .Select(U.ColUserAccount, U.ColUserPassword.As("PWD"), U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); 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); }
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 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 string Import() { DateTime prepare = DateTime.Now; DataTable data = new DataTable(); data.Columns.Add(new DataColumn("ADDRESS")); data.Columns.Add(new DataColumn("CREATED_BY")); data.Columns.Add(new DataColumn("CREATED_DATE", typeof(DateTime))); data.Columns.Add(new DataColumn("EMAIL_ADDR")); data.Columns.Add(new DataColumn("LAST_UPDATED_BY")); data.Columns.Add(new DataColumn("LAST_UPDATED_DATE", typeof(DateTime))); data.Columns.Add(new DataColumn("FAIL_TIMES", typeof(decimal))); data.Columns.Add(new DataColumn("STATUS")); data.Columns.Add(new DataColumn("DUMMY")); data.Columns.Add(new DataColumn("USER_ACCOUNT")); data.Columns.Add(new DataColumn("USER_NAME")); data.Columns.Add(new DataColumn("USER_PASSWORD")); data.Columns.Add(new DataColumn("IS_LOCKED")); for (int i = 0; i < 10000; i++) { object[] dr = new object[] { "第二次批量导入", "User2" + i.ToString(), DateTime.Now, "*****@*****.**", "User1", DateTime.Now, 0, "O", "Dummy", "User3" + DateTime.Now.GetHashCode().ToString() + i.ToString(), "导入的用户" + i.ToString(), "123", "N" }; data.Rows.Add(dr); } ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); DateTime Begin = DateTime.Now; U.Import(data); DateTime end1 = DateTime.Now; return("导入完成,prepare " + prepare.ToString("yyyy-MM-dd HH:mm:ss.fffff") + " begin " + Begin.ToString("yyyy-MM-dd HH:mm:ss.fffff") + " end " + end1.ToString("yyyy-MM-dd HH:mm:ss.fffff")); }
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 object SelectDynamic() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); var data = U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColEmailAddr.IsNotNull) .SelectDynamic(U.ColUserAccount, U.ColUserName, U.ColPhoneNo, U.ColEmailAddr); string UserName = ""; if (data.Count > 0) { UserName = data[0].USER_NAME; ///与 ColUserName 的 ColumnName一致. } 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 Lambda() { ///Lambda语法支持最多九个表连接查询 int total = 0; var data = new ODAContext().GetJoinCmd <CmdSysUser>() .InnerJoin <CmdSysUserRole>((u, ur) => u.ColUserAccount == ur.ColUserAccount & ur.ColStatus == "O") .InnerJoin <CmdSysRole>((u, ur, r) => ur.ColRoleCode == r.ColRoleCode & r.ColStatus == "O") .InnerJoin <CmdSysRoleAuthorization>((u, ur, r, ra) => r.ColRoleCode == ra.ColRoleCode & ra.ColIsForbidden == "O" & ra.ColStatus == "O") .Where((u, ur, r, ra) => u.ColStatus == "O" & (r.ColRoleCode == "Administrator" | r.ColRoleCode == "Admin") & u.ColIsLocked == "N") .Groupby((u, ur, r, ra) => new IODAColumns[] { r.ColRoleCode, u.ColUserAccount }) .Having((u, ur, r, ra) => ra.ColResourceId.Count > 10) .OrderbyAsc((u, ur, r, ra) => new IODAColumns[] { ra.ColResourceId.Count }) .Select(0, 20, out total, (u, ur, r, ra) => new IODAColumns[] { r.ColRoleCode, u.ColUserAccount, ra.ColResourceId.Count.As("ResourceCount") }); return(data); }
public static object SQLDebug() { 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); ////ODAContext.LastODASQL;属性是ODA最近生成的SQL语句块,包含了ODA 控制SQL语句(并非真正发送给数据库的SQL)、参数、语句类型、操作对象等; var ODASQL = ODAContext.LastODASQL; ///ctx.LastSQL属性是最近发送给数据库的SQL;由于分页的方法是两条SQL,所以此处的SQL是最后读取数据库的SQL; string sql = ctx.LastSQL; ///ctx.SQLParams属性是最近发送给数据库的SQL的参数; object[] param = ctx.SQLParams; return(data); }
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 InsertModel() { ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); U.Insert(new SYS_USER() { ADDRESS = "自由国度", CREATED_BY = "InsertModel", CREATED_DATE = DateTime.Now, FAIL_TIMES = 0, STATUS = "O", USER_ACCOUNT = "NYear1", USER_NAME = "多年1", USER_PASSWORD = "******", IS_LOCKED = "N", }); }
public static object Express() { ////Express方法, 用户可在 SELECT 字段中注入自定义的一段SQL脚本。 ////因为ODA 的表达式,是应用者注入的一段SQL语句,所以SQL注入的风险及是否可以跨数据库,就用开发者掌握了。 ODAParameter p1 = new ODAParameter() { ColumnName = "Params1", DBDataType = ODAdbType.OVarchar, Direction = System.Data.ParameterDirection.Input, ParamsName = ODAParameter.ODAParamsMark + "Params1", ParamsValue = "我是第一个参数的值", Size = 2000 }; ODAParameter p2 = new ODAParameter() { ColumnName = "Params2", DBDataType = ODAdbType.OVarchar, Direction = System.Data.ParameterDirection.Input, ParamsName = ODAParameter.ODAParamsMark + "Params2", ParamsValue = "这是SQL语句注入", Size = 2000 }; ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Select(U.Function.Express("1+1").As("COMPUTED"), U.Function.Express(" null ").As("NULL_COLUMN"), U.Function.Express(" 'Function( + " + ODAParameter.ODAParamsMark + "Params1, " + ODAParameter.ODAParamsMark + "Params2)' ", p1, p2).As("SQL_Injection")); return(data); }
public static void UpdateModel() { ///使用实体 Update 数据时,对于属性值为 null 的字段不作更新。 ///这是由于在ORM组件的实际应用中,多数时候界面回传的是完整的实体对象, ///或者收接时使用完整的实体作为反序列化的容器,那些不需要更新的字段也在其中,而且为null. ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); U.Where(U.ColUserAccount == "User1", U.ColIsLocked == "N", U.ColStatus == "O", U.ColEmailAddr.IsNotNull) .Update(new SYS_USER() { ADDRESS = "自由国度", CREATED_BY = "InsertModel", CREATED_DATE = DateTime.Now, STATUS = "O", USER_ACCOUNT = "NYear1", USER_NAME = "多年1", USER_PASSWORD = "******", IS_LOCKED = "N", }); }
public static object CaseWhen() { ////SQL 语句: case when 条件 then 值 when 条件 then 值 else 默认值 end ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); Dictionary <ODAColumns, object> Addr = new Dictionary <ODAColumns, object>(); Addr.Add(U.ColAddress.IsNull, "无用户地址数据..."); Addr.Add(U.ColAddress.Like("%公安局%"), "被抓了?"); Dictionary <ODAColumns, object> phone = new Dictionary <ODAColumns, object>(); phone.Add(U.ColPhoneNo.IsNull, "这个家伙很懒什么都没有留下"); phone.Add(U.ColPhoneNo == "110", "小贼快跑"); phone.Add(U.ColAddress.NotLike("%公安局%"), "被抓了?"); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Select(U.Function.CaseWhen(Addr, U.ColAddress).As("ADDRESS"), U.Function.CaseWhen(phone, "110").As("PHONE_NO")); return(data); }
public static object Case() { ////SQL 语句: case 字段 when 对比值 then 值 when 对比值 then 值 else 默认值 end ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); Dictionary <object, object> Addr = new Dictionary <object, object>(); Addr.Add(U.Function.Express(" NULL "), "无用户地址数据..."); Addr.Add("天堂", "人生最终的去处"); Dictionary <object, object> phone = new Dictionary <object, object>(); phone.Add(U.Function.Express(" NULL "), "这个家伙很懒什么都没有留下"); phone.Add("110", "小贼快跑"); phone.Add(U.ColAddress, "资料有误,电话与地址相同"); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Select(U.Function.Case(U.ColAddress, Addr, U.ColAddress).As("ADDRESS"), U.Function.Case(U.ColPhoneNo, phone, U.ColPhoneNo).As("PHONE_NO")); return(data); }
public static object Transaction() { ODAContext ctx = new ODAContext(); var U1 = ctx.GetCmd <CmdSysUser>(); ctx.BeginTransaction(); try { 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" ); U1.Insert(U.ColStatus == "O", U1.ColCreatedBy == "User1", U1.ColLastUpdatedBy == "User1", U1.ColLastUpdatedDate == DateTime.Now, U1.ColCreatedDate == DateTime.Now, U1.ColUserAccount == "Nyear", U1.ColUserName == "多年", U1.ColUserPassword == "123", U1.ColFeMale == "M", U1.ColFailTimes == 0, U1.ColIsLocked == "N"); ctx.Commit(); } catch { ctx.RollBack(); } return(null); }
public static object Recursion() { ///如Oracle的StartWith ConnectBy语句一致。ODA处理:先以 where 条作查出需要递归筛先的数据,然后在内存中递归筛选 ///由于是在内存递归,所以递归使所用到的所有字段必须包含在 Seclect 字段里。 ///注:ODA性能比 oracle 数据库的 StartWith ConnectBy 差一个等级,但比 SQLServer 的 with as 好一个级等。 ///递归有深度限制,数据量多的时候性能下降很快,最好保被递归筛选的数在10W条以内 ODAContext ctx = new ODAContext(); ////由根向叶子递归 Prior 参数就是递归方向 CmdSysResource RS = ctx.GetCmd <CmdSysResource>(); var rlt = RS.Where(RS.ColStatus == "O", RS.ColResourceType == "MENU") .StartWithConnectBy(RS.ColResourceName.ColumnName + "='根菜单'", RS.ColParentId.ColumnName, RS.ColId.ColumnName, "MENU_PATH", "->", 10) .Select(RS.ColResourceName.As("MENU_PATH"), RS.ColId, RS.ColParentId, RS.ColResourceName, RS.ColResourceType, RS.ColResourceScope, RS.ColResourceLocation, RS.ColResourceIndex); ////由叶子向根递归,Prior 参数就是递归方向 CmdSysResource RS1 = ctx.GetCmd <CmdSysResource>(); var rlt1 = RS.Where(RS.ColStatus == "O", RS.ColResourceType == "MENU") .StartWithConnectBy(RS.ColResourceName.ColumnName + "='菜单1'", RS.ColId.ColumnName, RS.ColParentId.ColumnName, "MENU_PATH", "<-", 10) .Select(RS.ColResourceName.As("MENU_PATH"), RS.ColId, RS.ColParentId, RS.ColResourceName, RS.ColResourceType, RS.ColResourceScope, RS.ColResourceLocation, RS.ColResourceIndex); rlt1.Merge(rlt); return(rlt1); }
public static void ReadData() { ODAContext ctx = DBConfig.GetODAContext(); var t = ctx.GetCmd <CmdTest>(); var list = t.SelectM(); }
public static void GetById() { ODAContext ctx = DBConfig.GetODAContext(); var t = ctx.GetCmd <CmdTest>(); var list = t.Where(t.ColId == 1).SelectM(); }