11/28/2008

Resolving "fileid=xxx" field values to actual file paths

Following a previous article of mine, The dbo.tabs.IconFile field issue, I would like to share an easy way to resolve any field that holds values of the type "FileID=xxx" (where xxx is the primary key of a record in the Files table) into the actual file path. This is especially useful when you deal with the IconFile field in the Tabs table, as well as with fields of type Image in a User Defined Table, and probably in a lot more places too.

 

I have created a UDF (User Defined Function) which accepts a string value and looks whether it's of type "FileID=xxx". If it is, it constructs the full path (including the portal number) and returns that, otherwise it just returns the initial string with no modification at all. You can use this scalar-valued function inline, in your own SELECT statements, like this:

 

EXAMPLE

SELECT 
tabid, 
tabname, 
dbo.ResolveFileField(iconfile) 
FROM tabs 

 

CODE

CREATE FUNCTION 
dbo.ResolveFileField
(
   
@file varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN
    
declare @retval varchar(1000)
declare @portalid int

if patindex ('FileID=%', @file) >0

begin

   
declare @fileid int
   
set @fileid = 
       
convert(
               
int, 
               
replace
                   
(
                   
@file
                   
, 'fileid='
                   
, ''
                   
)
               
)   

   
select 
       
@retval = 
            'Portals/' 
           
+ convert
                   
(
                     
varchar(10)
                   
, files.portalid
                   
) 
           
+ '/' 
           
+ folders.folderpath 
           
+ files.filename 
   
from 
       
files
   
left outer join folders
   
on files.folderid = folders.folderid
   
where 
       
files.fileid=@fileid
    
end

else

begin
   
set @retval = @file
end

return @retval

END
GO

5 comments:

Anonymous,  January 18, 2009 at 11:34 PM  

Thanks-

Worked perfect!

Gyromyristis January 19, 2009 at 1:34 AM  

You're welcome, I'm glad it proved helpful.

Anonymous,  December 15, 2009 at 6:20 AM  

Thanks, very helpful.
-R

Accuraty/Jeremy April 21, 2011 at 1:11 AM  

Your code does not account for custom portal paths. Instead of '/Portals/1' some people create portals with named paths like '/Portals/ABC'

Here is the slightly updated version of the function that pulls Portals.HomeDirectory in to the solution.

CREATE FUNCTION
[dbo].[ResolveFileField]
(
@file varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN

declare @retval varchar(1000)

if patindex ('FileID=%', @file) > 0

begin

declare @fileid int

set @fileid = convert( int, replace( @file, 'fileid=', '' ) )

SELECT @retval = '/' + Portals.HomeDirectory + '/'
+ Folders.FolderPath
+ Files.FileName
FROM Files INNER JOIN
Folders ON Files.FolderID = Folders.FolderID INNER JOIN
Portals ON Files.PortalId = Portals.PortalID
WHERE (Files.FileId = @fileid)


end

else

begin
set @retval = @file
end

return @retval

END

Gyromyristis April 21, 2011 at 1:31 AM  

Gee, that was indeed a nice enhancement. Named paths never crossed my mind. Thanks for the code!

Related Posts with Thumbnails

Recent Comments

Free DotNetNuke Stuff

Free DotNet Videos

  © Blogger template The Professional Template by Ourblogtemplates.com 2008

Back to TOP