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);
}
