自已做的框架RJ 第七篇----------------查询篇 下

查询的最后一篇
排序

 var z = new SelectSqlSection<RJ.DModel.Users>();            

z.OrderBy(a => a.D_Id);

SELECT * FROM [Users] ORDER BY [Users].[D_Id]

 

var z = new SelectSqlSection<RJ.DModel.Users>();          

 z.OrderBy(a => new { a.D_Id ,a.U_Id.Desc});

SELECT * FROM [Users] ORDER BY [Users].[D_Id],[Users].[U_Id] DESC 

分页

int size;          

 var z = new SelectSqlSection<RJ.DModel.Users>();          

 z.OrderBy(a =>a.U_Id);//用的是WITH分的页要有一个排序字段            

db.ToIList<RJ.DModel.Users>(z, 20, 1, out size);//20条数据一页第一页 共size第数据            

db.ToDataSet(z, 20, 1, out size);//20条数据一页第一页 共size第数据

Case END

var z = new SelectSqlSection<RJ.DModel.Users>();          

 z.Case()                

.When(a=>a.D_Id>5).Then(100)              

 .When(a => a.D_Id > 10).Then(500)                

.Else(0).End();

SELECT ( CASE  WHEN [Users].[D_Id] > @p25d7757fxx2hyg THEN @pgwosgx9uu9plmq WHEN [Users].
[D_Id] > @pgujltixpryqwks THEN @pf4yy1ofdk991pa ELSE @pbdf0jcy209129l END )  FROM [Users]

 

var z = new SelectSqlSection<RJ.DModel.Users>();          

 z.Case(a=>a.D_Id)              

 .When(5).Then(100)                

.When( 10).Then(500)                

.Else(0).End();

SELECT ( CASE [Users].[D_Id] WHEN @p002i5mxj5knb9v THEN @pt84v16okva01sn WHEN 
@p493kirevrli45o THEN @pltp7reymbvnowd ELSE @phfy2i9owgqa48h END )  FROM [Users]

子查询

var z3 = new SelectSqlSection<RJ.DModel.Users>("a");          

 z3.AddColumn(a => a.U_Id.As("a").Min());          

 z3.Where(a => a.D_Id.As("a") == a.D_Id); 

var z = new SelectSqlSection<RJ.DModel.Users>();            

z.Where(a => a.U_Id==z3);

SELECT * FROM [Users] WHERE [Users].[U_Id] = (SELECT MIN([a].[U_Id]) FROM [Users] [a] WHERE 
[a].[D_Id] = [Users].[D_Id])

 

var z2 = new SelectSqlSection<RJ.DModel.Department>();          

 z2.AddColumn(a => a.D_Id);          

 z2.Where(a => a.D_Id > 4); 

var z = new SelectSqlSection<RJ.DModel.Users>();            

z.Where(a => a.D_Id.In(z2));

SELECT * FROM [Users] WHERE [Users].[D_Id] IN (SELECT [Department].[D_Id] FROM [Department] 
WHERE [Department].[D_Id] > @pm6w9ju6os6dgby)

合并

 var z = new SelectSqlSection<RJ.DModel.Users>();          

 var UNION = z | z;          

 var UNION_ALL = z + z;          

 var Intersect = z & z;          

 var Except = z ^ z;

SELECT * FROM [Users] UNION SELECT * FROM [Users]

SELECT * FROM [Users] UNION ALL SELECT * FROM [Users]

SELECT * FROM [Users] Intersect SELECT * FROM [Users]

SELECT * FROM [Users] Except SELECT * FROM [Users]

打字也是很累的- -就先这些了吧

RJ框架更新中~~~~