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