现有三张数据表如下:
ApplyProcessModule(模块表)
ApplyProcessTemplate(模板表)
ApplyProcessStepConfigInfo(流程步骤表)
表关系如下:
流程步骤表的流程模板Id=模板表模板id
流程步骤表的模块id=模块表模块id
流程步骤表的下一步骤ID=模块表模块id
数据库代码如下:
select *from(
select row_number() over (order by A.id asc) as num,
a.id,
b.Name as a,
d.Name as c,
a.NextStepMethod,
h.Name as b,
a.NextStepConfig,
a.StepType,
a.StepConfigDescription,
a.StepConfigModuleJson,
a.StepConfigSort,
a.NoticeMessage,
a.Addtime,
a.ModifyTime,
a.CreateBy,
a.ModifyBy
From ApplyProcessStepConfigInfo a WITH(NOLOCK)
left join ApplyProcessTemplate b ON a.TemplateId=b.Id
left join ApplyProcessModule d ON a.ModuleId=d.Id
left join ApplyProcessModule h ON a.NextStepId=h.Id
) a
where num>0 and num<=20 order by num
MVC中查询代码块
public DataSet GetAll(int? page=null,int? row=null,string sort=null,string order=null)
{
//连接SQL
var sql_joinlist=new List<SQLJoin>()
{
new SQLJoin("b",@"left join ApplyProcessTemplate b ON a.TemplateId=b.Id "),
new SQLJoin("d",@"left join ApplyProcessModule d ON a.ModuleId=b.Id "),
new SQLJoin("h",@"left join ApplyProcessModule h ON a.NextStepId=b.Id "),
}
//条件过滤SQL
var sb_sql_r1=new SQLBuilder(@"
a.id,
b.Name as a,
d.Name as c,
a.NextStepMethod,
h.Name as b,
a.NextStepConfig,
a.StepType,
a.StepConfigDescription,
a.StepConfigModuleJson,
a.StepConfigSort,
a.NoticeMessage,
a.Addtime,
a.ModifyTime,
a.CreateBy,
a.ModifyBy
From ApplyProcessStepConfigInfo a WITH(NOLOCK)",sql_footer:@"",sql_joinlist:sql_joinlist,keys:"b,d,h");
//字段查询SQL
var sb_sql_r2=new SQLBuilder(@"
select
a.Id,
b.Name as a,
d.Name as c,
a.NextStepMethod,
h.Name as b,
a.NextStepConfig,
a.StepType,
a.StepConfigDescription,
a.StepConfigModuleJson,
a.StepConfigSort,
a.NoticeMessage,
a.Addtime,
a.ModifyTime,
a.CreateBy,
a.ModifyBy
From ApplyProcessStepConfigInfo a WITH(NOLOCK)",sql_footer:@"",sql_joinlist:sql_joinlist,keys:"b,d,h");
//输出SQL
if(String.ISNullOrEmpty(sort))
{
sort="Id";
}
if(String.ISNullOrEmpty(order))
{
order="acs";//升序 desc降序
}
StringBuilder sb_sql_r3=new StringBuilder(string.Fromat(@"select * from (select row_number() over (order by A.{0} {1} as num,{2}) a",sort,order,sb_sql_r1.ToString()));
using (var dbContext=new DBContext())
{
if(!page.HasValue)
{
page=1;
sb_sql_r3= new SQLBuilder(@"
select
a.Id,
b.Name as a,
d.Name as c,
a.NextStepMethod,
h.Name as b,
a.NextStepConfig,
a.StepType,
a.StepConfigDescription,
a.StepConfigModuleJson,
a.StepConfigSort,
a.NoticeMessage,
a.Addtime,
a.ModifyTime,
a.CreateBy,
a.ModifyBy
from ({0}) as result",sb_sql_r3.ToString()));
}
else
{
sb_sql_r3.Insert(0,string.Fromat(@"select count(*) from({0}) r2 ; ",sb_sql_r2));
}
if(!rows.HasValue)
{
rows=int.MaxValue;
}
sb_sql_r3.AppendFromat(" where num>{0} and num<={1} order by num",(page-1)*rows,page*rows)
return dbContext.SqlDataSet(sb_sql_r3.ToString());
}
SQLJoin.cs
public string Key;
public string Value;
public bool IsAdd;