Register  Login  
  February 5, 2012  
Search_Blog Minimize
Print  

Blog_Archive Minimize
Print  

Blog    
View_Blog Minimize
Sep 2

Written by: host
Thursday, September 02, 2010 6:43 PM 

After working with UDT - User Defined Tables - which is now call Form and List in DNN V5.0+, I was looking for a way to convert the UDT into a conventional table.  By convention table, I mean a table with multiple data column and a single row per data item.  UDT have simple structure that places one field per data item on a row, so if you have 10 data fields then the UDT will have 10 rows per data item.  Compounding the issue is the there is only one UserDefinedTableData table.

Having developed a strong reporting module - SQLGridSelectedView (aka SGSV) - I wanted to be able to access the data in a UDT.  To do this I created a stored procedure that would generate the appropriate T-SQL and then execute it to return the flatten data.  The flatten data has one row per data item and a column for each data field.  So, instead of 10 rows per data item I have one row with 10 columns.

The stored procudure expects the ModuleID of the corresponding Forms and List (or UDT) module. The first optional parameter is the bit field that when set to 1 will return just the generated T-SQL. The default is to have the procedured execute the T-SQL and return the result set.  The second parameter defines a prefix that is prepended to each table reference. The default is "dbo.", but could be the '{databaseowner}{objectqualifier}' substitution values supported by SGSV and the Host/SQL modules.

To determine the appropriate Form and List module use the following select in Host/SQL module to determine which ModuleID to use: (Note: I have not used the '{databaseowner}{objectqualifier}'  in front of the table names)

Select ModuleID, ModuleTitle from TabModules where ModuleID in (
 Select ModuleID from Modules where ModuleDefID in (
  Select ModuleDefID from dbo.ModuleDefinitions where DesktopModuleID in (
   Select [DesktopModuleID] from [dbo].[DesktopModules]
          where FriendlyName = 'Form and List')))

The procedure is written for SQL2000 as my production environment is still SQL2000 (ugh!).  Also I not used the '{databaseowner}{objectqualifier}' substitution values when defining the procedure.  

Update: Based on Mark's comments I have revised the script to use nvarchar(1000) and have also supported Email field type.

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:'' + ' + @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
GO

The stored procedure has some limitation, specifically, it does not support expression.  This may not be an issue as the user can take the generated T-SQL and revise it as necessary to generate the calculated column.

For simple example, Assume module 390 is a UDT with the structure of

EngineID Text
EngineType Text
Manufacturer Text
PurchaseDate Date
Cost Decimal
Website Url

The typical execution of the procedure would something like

exec GetUDTData 390

which would generate and execute the follow T-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

You can either use the stored procedure or the T-SQL generated by the stored procedure in whatever report module you are using (such as SGSV).

Hope this Helps
Paul.
 

Tags:

5 comment(s) so far...

Re: A Procedure to Flattening UDTs

What a fun pattern! It's great to hear from you and see what you've sent up to. All of the projects look great! You make it so simple to this. Thanks

By Abercrombie and Fitch Düsseldorf on   Sunday, January 29, 2012 1:34 AM

Re: A Procedure to Flattening UDTs

It's great to hear from you and see what you've sent up to. All of the projects look great! You make it so simple to this. Thanks

By Abercrombie Düsseldorf on   Sunday, January 29, 2012 1:34 AM

Re: A Procedure to Flattening UDTs

In this modern and fashionable society, people are pursuing for oakley sunglasses online cool, unique, stylish and innovative. Whether it is cheap oakley sunglasses or fashion accessories all means a lot for modern society of today. Same is the case with trendy looking fake oakley sunglasses. When these are tiffany jewellery uk, the excitement just gets doubled. Most chic looking tiffany and co are in fashion now. These are one of the favorite fashion accessories for men and women long time ago. If you have not yet tried tiffany co jewellery, it's time to own one and feel the difference it can make to your personality. These are just brilliant and fabulous tiffany jewellery. They are most iconic and can provide you with a new feeling and enhance confidence. The quality of tiffany uk is just superior to what you have dreamt of.

By links of London on   Tuesday, January 31, 2012 2:00 AM

Re: Chinese Footware - and the removal of...

GameCoach Factory Outlet Onlineare usuallyCoach Factoryin By Coach Outlet Online on   Thursday, February 02, 2012 1:27 AM

beats by dre uk

control beam. The method of attribute"
The author of the paper joint, Cornell university application and engineering physics institute scientists morty-Fred mann according to design a special method, "this method through the change of laser beam frequency and wavelength, Cheap Beats By Dre so the spread in different rate, thus produce a (time) gap." Then, time holes on the other side of the second pulse laser beam, this bunch of pulse laser the function of the one is in the opposite direction to change the attributes of the laser beam, so that the laser beam back to the original attributes. In the experiment, occurs in time of vulnerability of events, can escape the probe detection.
University of rochester institute of optics scientists ShiZhiMin (Zhimin Shi) said, "this is the first real time gap between the produce experiment, is also the first realize objects in space and in time and invisible experiment." ShiZhiMin was not involved in the study.
Although the study is still in the early stages, but this time to the operation will has a broad prospect of application. ShiZhiMin said, "I believe, may the people do not have to this kind of technology exactly how much use thinking too much. auriculares beats Maybe people thinks of above all is to let something invisible don't be detected, or intentionally let some event in 'the cloak of'."
However, time lens technology and may be quietly to the continuous flow of data to insert specific information, this behaviour can be perfect, and it is hard to find. Fred mann explains, "I think you can bring some data is coming into the hidden in optical fiber in data flow. Using the time lens, beats by dr dre australia you can follow one's inclinations to manipulate data, they may be reduction."
Experts think, the technology will can be applied to the research and development due to chip, speed up the Internet and computer internal data and data flow processing speed. ShiZhiMin says, beats by dre headphones any application will depend on whether to expand the time holes. In addition, in the present experiment, simply by a single laser experiments, and the actual application to consider three dimensional light from different point of view to illuminate. ShiZhiMin think, Cornell university experiment has taken the first step in the key.

By beats by dre uk on   Friday, February 03, 2012 1:26 AM

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment   Cancel 

Blog_List Maximize
Print  

  Home | Blog | Forum | Subscriptions | Free Modules | Videos   Page generated in 0.0626016 seconds.
  Copyright 2010 by TressleWorks   Terms Of Use | Privacy Statement