首页 > 数据库技术 > 详细

在SQL 中生成JSON数据

时间:2014-06-14 18:34:56      阅读:460      评论:0      收藏:0      [点我收藏+]

这段时间接手一个数据操作记录的功能,刚拿到手上的时候打算用EF做,后来经过仔细考虑最后还是觉定放弃,最后思考再三决定:

1、以模块为单位分表、列固定(其实可以所有的操作记录都放到同一个表,但是考虑到数据量大的时候查询性能的问题还是分表吧)列:主键ID、引用记录主键ID、操作时间、操作类型、详细信息(里面存储的就是序列化后的值)

2、在客服端解析保存的序列化的值

但是用xml还是用json呢,这有是一个问题,显然用xml在存储过程正很容易就能生成了:SELECT * FROM TABLE FOR XML AUTO 就ok了,

但是用xml的话,在客服端解析就麻烦些了,所以弃用了xml保存,而使用了最爱的json,现在的问题就是写一个通用的存储过程来生成json了,经过我1天多的拼写终于出炉了(其实难度不大,就是拼字符串容易出错)

/*=================获取JSON格式数据
 @table_name varchar(100):表名
 @Condition NVARCHAR(max):条件(and 1=1)
 @json varchar(max) OUTPUT:生成的JSON数据
    @limit int =NULL :取前多少条,为null 取所有
 @ConvertColumns NVARCHAR(max)=null:要转换的列,建议填写非html的列,html列目前会有问题
==================================*/
CREATE PROC GetJSON(
 @table_name varchar(100),
 @Condition NVARCHAR(max)=‘‘,
 @json varchar(max) OUTPUT,
    @limit int =NULL,
 @ConvertColumns NVARCHAR(max)=null
)
AS
declare @query varchar(max),
  @table_schema varchar(max) = null
if(charindex(‘.‘, @table_name) > 0 )
begin
    set @table_schema = replace(replace( substring(@table_name, 0, charindex(‘.‘,@table_name)), ‘[‘, ‘‘), ‘]‘, ‘‘)
    set @table_name = replace(replace( substring(@table_name, charindex(‘.‘,@table_name) + 1,len(@table_name)), ‘[‘, ‘‘), ‘]‘, ‘‘)
END
IF EXISTS ( SELECT 1 FROM tempdb..sysobjects WHERE id = OBJECT_ID(‘tempdb..#tmpJsonTable‘) )
  DROP TABLE #tmpJsonTable
 CREATE TABLE #tmpJsonTable
  (
   json NVARCHAR(max) NULL
  )
  --"dd"
set @query =
    ‘insert into #tmpJsonTable select ‘ + case when @limit is not null then ‘top ‘ + cast(@limit as varchar(32)) + ‘ ‘ else ‘‘ end + ‘‘‘{ ‘‘ + REVERSE(STUFF(REVERSE(‘‘‘ +
    CAST((SELECT ‘ "‘ + column_name + ‘" : ‘ +
  ----处理为null的问题
  case WHEN is_nullable = ‘YES‘ then ‘‘‘ + case when [‘ + column_name + ‘] is null then ‘‘null‘‘ else ‘ +
    --处理前缀"
    case WHEN data_type=‘uniqueidentifier‘or data_type like ‘%date%‘ or data_type like ‘%char%‘ or data_type like ‘%text%‘ then ‘‘‘"‘‘ + ‘ else ‘‘ end +
    /*类型转换*/
    case WHEN data_type like ‘%date%‘ then ‘convert(varchar(23),[‘ + column_name + ‘], 20)+ ‘‘"‘‘‘
     else ‘replace(replace(replace(replace(cast([‘ + column_name + ‘] as varchar(max)),‘‘\‘‘,‘‘\\‘‘),‘‘"‘‘,‘‘\"‘‘),char(10),‘‘\n‘‘),char(13),‘‘\n‘‘) ‘ end +
    --处理后缀"
    case WHEN data_type=‘uniqueidentifier‘ or data_type like ‘%char%‘ or data_type like ‘%text%‘ then ‘+ ‘‘"‘‘‘ else ‘‘ end + ‘ end + ‘‘‘
  ELSE
   --处理前缀"
   case WHEN data_type=‘uniqueidentifier‘ or data_type like ‘%date%‘ or data_type like ‘%char%‘ or data_type like ‘%text%‘ then ‘"‘ else ‘‘ end + ‘‘‘ + ‘ +
   /*类型转换*/
   case WHEN data_type like ‘%date%‘ then ‘convert(varchar(23),[‘ + column_name + ‘], 20)+ ‘‘"‘‘‘
    else ‘replace(replace(replace(replace(cast([‘ + column_name + ‘] as varchar(max)),‘‘\‘‘,‘‘\\‘‘),‘‘"‘‘,‘‘\"‘‘),char(10),‘‘\n‘‘),char(13),‘‘\n‘‘) + ‘‘‘ end +
   --处理后缀"
   case WHEN data_type=‘uniqueidentifier‘ or data_type like ‘%char%‘ or data_type like ‘%text%‘ then ‘"‘ else ‘‘ end end + ‘,‘
  AS [text()]
      from information_schema.columns
  where table_name = @table_name
   --AND (COLUMN_NAME=@ColumnName AND ‘[‘+COLUMN_NAME+‘]‘ IN (SELECT MyValues FROM dbo.funSplitArray(@ColumnValues,‘,‘)))
   AND(@ConvertColumns IS NULL OR COLUMN_NAME IN(SELECT MyValues FROM dbo.funSplitArray(@ConvertColumns,‘,‘)))
   and (@table_schema is null or table_schema = @table_schema) FOR XML PATH(‘‘) ) as varchar(max)) +
        ‘‘‘),1,1,‘‘‘‘)) + ‘‘ }‘‘ as json from ‘ + @table_name + ‘ with(nolock) where 1=1 ‘+@Condition
exec sp_sqlexec @query
SELECT @query
set @json =
    --‘{‘ + char(10) + char(9) +
    --‘"recordCount" : ‘ + Cast((select count(*) from #tmpJsonTable) as varchar(32)) + ‘,‘ + char(10) + char(9) +
    --‘"records" : ‘ + char(10) + char(9) + char(9) +
 ‘[‘ + char(10)
    + REVERSE(STUFF(REVERSE(CAST((SELECT char(9) + char(9) + json + ‘,‘ + char(10) AS [text()] FROM #tmpJsonTable FOR XML PATH(‘‘)) AS varchar(max))),1,2,‘‘))
    + char(10) + char(9) + char(9) + ‘]‘
 -- + char(10) + ‘}‘
drop table #tmpJsonTable
 
 
--DECLARE @table_name varchar(100)=‘SalesOrder‘,
-- @ConvertColumns NVARCHAR(max)=‘SalesOrderNo,SalesOrderID‘,
-- @Condition NVARCHAR(max)=‘‘,
-- @json varchar(max)
 
--EXEC GetJSON @table_name=@table_name,@ConvertColumns=@ConvertColumns,@Condition=@Condition,@json=@json OUTPUT
--SELECT @json
调用这个存储过程使用输出参数就可以得到序列化后的json数据了。
至此,就差在客服端解析了。打算写个方法根据传递过来的json对象参数来动态解析,这样 ui页面上也不需要用每个地方都去解析了....
 

在SQL 中生成JSON数据,布布扣,bubuko.com

在SQL 中生成JSON数据

原文:http://www.cnblogs.com/xiexingen/p/3787663.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!