应该说,翻译不是我的擅长,转载也不是我的强项。不过这段代码还是比较有用的,就先存在这儿吧。
DNN的UDT(User Defined Table,在DNN5.*中叫Form and List)是要做成一个通用的数据存储功能,可以自定义对象的数据结构,并实现增删改操作。
在不增加新表情况下实现此类通用功能当然要借助一个列转行的概念,也就是将每个字段变成行记录,从而实现这种灵活性。就像双刃剑一样,带来缺点也是必然的,无法使用标准的SQL获得常见的行列结构数据。
下面的存储过程可以将字段数据从行记录进行合并,形成一条记录。
Create Procedure dbo.GetUDTData
@ModuleID int,
@ReturnSelect bit = 0,
@Prefix varchar(100) = 'dbo.'
as
-- Revision History
-- 03-sep-2010 Added @CastFieldValue to allow easy change to way FieldValue is Cast
-- - could use nvarchar(max) in SQL2005+
-- exec dbo.GetUDTData 418
-- exec dbo.GetUDTData 418, 1
-- exec dbo.GetUDTData 418, 0, '{databaseowner}{objectqualifier}'
DECLARE @UserDefinedFieldId int
DECLARE @FieldTitle varchar(50)
DECLARE @FieldType varchar(20)
DECLARE @Visible bit
DECLARE @CastFieldValue varchar(100)
DECLARE @SQL Varchar(8000)
DECLARE @CRLF Char(2)
set nocount on
DECLARE GetFieldsCursor INSENSITIVE CURSOR
FOR
SELECT UserDefinedFieldId, FieldTitle, FieldType, Visible
from UserDefinedFields
where ModuleID = @ModuleID
and Visible = 1
and FieldType <> 'Expression'
order by FieldOrder
OPEN GetFieldsCursor
FETCH NEXT FROM GetFieldsCursor INTO @UserDefinedFieldId, @FieldTitle, @FieldType, @Visible
Set @CRLF = CHAR(13) + CHAR(10)
Set @CastFieldValue = 'Cast(FieldValue as nvarchar(1000))'
Set @SQL = 'Select UserDefinedRowId as RowID' + @CRLF
WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL = @SQL + ' ,(Select '
Set @SQL = @SQL +
Case @FieldType
when 'Decimal' then 'Cast(' + @CastFieldValue + ' as Decimal)'
when 'Date' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'DateTime' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'CreatedAt' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'ChangedAt' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'Email' then '''mailto:' rel="nofollow"' + ' + @CastFieldValue + ' + ''">'' + ' + @CastFieldValue + ' + '''''
when 'Url' then ''''' + ' + @CastFieldValue + ' + '' '''
else '' + @CastFieldValue + ''
End
Set @SQL = @SQL + ' from ' + @Prefix + 'UserDefinedData where UserDefinedFieldId = '
Set @SQL = @SQL + CAST(@UserDefinedFieldId as varchar) + ' '
Set @SQL = @SQL + 'and UserDefinedRowId = UDR.UserDefinedRowId'
Set @SQL = @SQL + ') as [' + @FieldTitle + ']' + @CRLF
FETCH NEXT FROM GetFieldsCursor INTO @UserDefinedFieldId, @FieldTitle, @FieldType, @Visible
END
Set @SQL = @SQL + 'from ' + @Prefix + 'UserDefinedRows UDR where ModuleId = '
Set @SQL = @SQL + CAST(@ModuleID as varchar) + @CRLF
CLOSE GetFieldsCursor
DEALLOCATE GetFieldsCursor
if @ReturnSelect = 1
begin
Print @SQl
Select @SQL
end
else
execute(@SQL)
return @@rowcount
当你执行下面语句时:
exec GetUDTData 390
将会产生下面的SQL语句执行:
Select UserDefinedRowId as RowID
,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 5 and UserDefinedRowId = UDR.UserDefinedRowId) as [EngineID]
,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 6 and UserDefinedRowId = UDR.UserDefinedRowId) as [EngineType]
,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 7 and UserDefinedRowId = UDR.UserDefinedRowId) as [Manufacturer]
,(Select Cast(Cast(FieldValue as varchar) as DateTime) from dbo.UserDefinedData where UserDefinedFieldId = 8 and UserDefinedRowId = UDR.UserDefinedRowId) as [PurchaseDate]
,(Select Cast(Cast(FieldValue as varchar) as Decimal) from dbo.UserDefinedData where UserDefinedFieldId = 9 and UserDefinedRowId = UDR.UserDefinedRowId) as [Cost]
,(Select ' ' + Cast(FieldValue as varchar) + ' ' from dbo.UserDefinedData where UserDefinedFieldId = 11 and UserDefinedRowId = UDR.UserDefinedRowId) as [website]
from dbo.UserDefinedRows UDR where ModuleId = 390