【SQL Server CLR】 TResult实战

 

TResult 数据库结果类型

构造方法：

declare @TResult TResult='';  --空对象

属性/方法	类型	说明/调用示例
CreateModel	function(name,TSqls)	declare @TSqls TSqls='select id,name from table where id=@id' set  @TSqls= @TSqls.AppendParams(1); @TResult.CreateModel('model',@TSqls)
CreateArray	function(name,TSqls)	@TResult.CreateModel('array','select top 10 id from table')
CreateList	function(name,TSqls)	@TResult.CreateList('list','select top 10 id,name from table')
CreateOperate	function(name,TSqls)	@TResult.CreateOperate('operate',0,'操作成功')
CreatePager	function(string name, TSqls data_sql, TSqls total_sql, int page_size)	@TResult.CreatePager('pager','select top 10 id from table','select count(1) from table',20);
CreateJson	function(name,TJson)	declare @TJson TJson='{"name":"tresult"}' @TResult.CreateModel('model',@TJson )
CreateResult	function(name,TResult)	declare @TResult2 TResult='' set @TResult2=@TResult2.CreateModel('array','select top 10 id from table') @TResult.CreateResult('result',@TResult2 )
SetValue	function(name,object)	同TJson SetValue一致
SetJson	function(name,TJson)	同TJson SetJson一致
Sqls	function	@TResult.Sqls()  获取TResult中所有脚本
ToFormatValue	function	@TResult.ToFormatValue() 预览TResult结构化格式
ToString()	function	@TResult.ToString()   --获取完整序列化数据

代码演示：

----------声明--------------------------
declare @TResultTResult TResult='';
----------设置值-----------------------
---创建一个员工实体类型
set @TResultTResult=@TResult.CreateModel('model','select id,name,sex from e_Employee where id=1');  
---创建一个id数组
set @TResultTResult=@TResult.CreateArray('ids','select top 10 id from e_Employee ');  
---创建一个实体数组
set @TResultTResult=@TResult.CreateList('list','select top 10 id,name,sex from e_Employee '); 
---创建一个操作消息体
set @TResultTResult=@TResult.CreateOperate('operate',0,'操作成功');
---创建一个操作消息体
set @TResultTResult=@TResult.CreatePager('pager','分页数据脚本','总数脚本','每条多少条');
--创建一个自定义的json数据
set @TResultTResult=@TResult.CreateJson('json',TJson类型);
--创建一个TResult类型
set @TResult=@TResult.CreateResult('result',TResult类型);
--------------应用于结果展示----
------------单个对象-----------------
declare @TResult TResult=''
declare @TSqls TSqls='';
set @TSqls=@TSqls.AppendSql('select id,name,sex from e_Employee where id=@id').AppendParams(1);
set @TResult=@TResult.CreateModel('model',@TSqls);
select dbo.TResultToJson(@TResult);
结果：{id:1,name:"ted",sex:"男"}
-------------多个对象--------
declare @TResult TResult=''
set @TResult=@TResult.CreateModel('model','select id,name,sex from e_Employee where id=1');
set @TResult=@TResult.CreateArray('ids','select top 10 id from e_Employee ');  
结果：{model:{id:1,name:"ted",sex:"男"},ids:[1,2,3,4,5,6,7,8]}
------------TResult嵌套-------
declare @TResult TResult=''
set @TResult=@TResult.CreateModel('model','select id,name,sex from e_Employee where id=1');
set @TResult=@TResult.CreateArray('ids','select top 10 id from e_Employee '); 

set @TResult=@TResult.CreateArray('ids','select top 10 id from e_Employee '); 

结果：{model:{id:1,name:"ted",sex:"男"},ids:[1,2,3,4,5,6,7,8]}