How to create folder structure in database table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to create folder structure in database table

Author

Topic

ADL
Starting Member

1 Post

Posted - 2012-01-27 : 03:57:04

Hi,
What is the best way to:
1.Storer folder structure (tree) and values (e.g. user,access rights) against each folder and its cocntents in database?
2.Retirve values against each folder and its contents along with associated values (e.g. access rights)

My requirement is bit complex:
1. I need to store directory structure and its contents along with user access rights.
2. And then for each user retrieve values (access rights) for each folder/subfolder and its contents in a fastest way.
3. Folder, its subfolder or its contents may have different access rights for user.

For example:[Path][User][Read][Write][Upload][Download]
[C:\Users\Admin] [ADL] [Y][N][N][N]
[C:\Users\Admin\Music] [ADL] [Y][N][N][Y]
[C:\Users\Admin\Pictures] [ADL] [Y][N][Y][Y]
[C:\Users\Admin\Pictures\Company.jpg] [ADL] [N][N][N][N]
[C:\Documents] [ADL] [N][N][N][N]
[C:\Documents\Proposal.docx] [ADL] [N][N][N][N]
[C:\Documents\Design.docx] [ADL] [Y][N][N][N]
[C:\Documents\Requirements.docx] [ADL] [Y][Y][Y][Y]
[C:\Documents] [Admin] [Y][Y][Y][Y]
[C:\Documents\Proposal.docx] [John] [Y][Y][Y][Y]
[C:\Documents\Requirements.docx] [John] [Y][Y][Y][Y]
[C:\Documents\Design.docx] [John] [Y][Y][Y][Y]
[C:\Documents\Design.docx] [Alex] [Y][Y][Y][Y]

Hope this makes it clear.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 10:57:42

Where do you suppose you would get the user access rights FROM?

And I would not store the files in the database since they are already of the file server

Here's something to get directory structures



CREATE PROC [dbo].[isp_Dir]
	  @path				varchar(2000)
	, @Supress			int = 0
AS


SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL
  DROP TABLE #Directory_Contents_Stage
CREATE TABLE #Directory_Contents_Stage(
   [dir]			varchar(255)
 , [dir_output]		varchar(255)
)
--TRUNCATE TABLE #Directory_Contents_Stage


    DECLARE @cmd varchar(4000)
     SELECT @cmd = 'Dir "' + @path + '"'

 INSERT INTO #Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd


  IF EXISTS (SELECT * FROM #Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         GOTO isp_Dir_Error
    END

  IF EXISTS (SELECT * FROM #Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         GOTO isp_Dir_Error
    END         

  IF EXISTS (SELECT * FROM #Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         GOTO isp_Dir_Error
    END

 UPDATE #Directory_Contents_Stage SET [dir] = @path



 DELETE FROM Directory_Contents WHERE [dir] = @path

 INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
 SELECT   
		  CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
		, CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] 
		, SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
		, [dir]
		, 'FILE'
   FROM   #Directory_Contents_Stage
  WHERE   SUBSTRING(dir_output,1,1) <> ' '
    AND  (SUBSTRING(dir_output,1,1) <> ' ' 
    AND   SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62)	) 


 INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
 SELECT 
		  CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
		, SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
		, [dir]
		, CHAR(60)+'Dir'+CHAR(62) AS  [Struct_Type]
   FROM   #Directory_Contents_Stage 
  WHERE   SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62) 



IF @Supress = 0
    SELECT [dir], Create_Time, File_Size, [File_Name], [Struct_Type] 
      FROM Directory_Contents
     WHERE [dir] = @path
  ORDER BY [Struct_Type] DESC, [Create_Time] DESC



SET NOCOUNT OFF


isp_Dir_Exit:
  IF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL
    DROP TABLE #Directory_Contents_Stage
  SET NOCOUNT OFF
  RETURN

isp_Dir_Error:
-- Add Error Handling
  GOTO isp_Dir_Exit

GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sakthi87
Starting Member

1 Post

Posted - 2013-07-31 : 00:31:24

Could you please Explain the code. i tried this code. but am getting file not found results.

quote:

Originally posted by X002548

Where do you suppose you would get the user access rights FROM?

And I would not store the files in the database since they are already of the file server

Here's something to get directory structures



CREATE PROC [dbo].[isp_Dir]
	  @path				varchar(2000)
	, @Supress			int = 0
AS


SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL
  DROP TABLE #Directory_Contents_Stage
CREATE TABLE #Directory_Contents_Stage(
   [dir]			varchar(255)
 , [dir_output]		varchar(255)
)
--TRUNCATE TABLE #Directory_Contents_Stage


    DECLARE @cmd varchar(4000)
     SELECT @cmd = 'Dir "' + @path + '"'

 INSERT INTO #Directory_Contents_Stage(dir_output) EXEC master..xp_cmdshell @cmd


  IF EXISTS (SELECT * FROM #Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the file specified.')
    BEGIN
         PRINT 'The system cannot find the file specified.'
         GOTO isp_Dir_Error
    END

  IF EXISTS (SELECT * FROM #Directory_Contents_Stage
       WHERE dir_output = 'File Not Found')
    BEGIN
         PRINT 'File Not Found'
         GOTO isp_Dir_Error
    END         

  IF EXISTS (SELECT * FROM #Directory_Contents_Stage
       WHERE dir_output = 'The system cannot find the path specified.')
    BEGIN
         PRINT 'The system cannot find the path specified.'
         GOTO isp_Dir_Error
    END

 UPDATE #Directory_Contents_Stage SET [dir] = @path



 DELETE FROM Directory_Contents WHERE [dir] = @path

 INSERT INTO Directory_Contents (Create_Time, File_Size, [File_Name], [dir], [Struct_Type])
 SELECT   
		  CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
		, CONVERT(int,LTRIM(RTRIM(REPLACE(SUBSTRING(dir_output,21,19),',','')))) AS [File_Size] 
		, SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
		, [dir]
		, 'FILE'
   FROM   #Directory_Contents_Stage
  WHERE   SUBSTRING(dir_output,1,1) <> ' '
    AND  (SUBSTRING(dir_output,1,1) <> ' ' 
    AND   SUBSTRING(dir_output,25,5) <> CHAR(60)+'Dir'+CHAR(62)	) 


 INSERT INTO Directory_Contents (Create_Time, [File_Name], [dir], [Struct_Type])
 SELECT 
		  CONVERT(datetime,SUBSTRING(dir_output,1,20)) AS [Create_Time]
		, SUBSTRING(dir_output,40,(LEN(dir_output)-39)) AS [File_Name]
		, [dir]
		, CHAR(60)+'Dir'+CHAR(62) AS  [Struct_Type]
   FROM   #Directory_Contents_Stage 
  WHERE   SUBSTRING(dir_output,25,5) = CHAR(60)+'Dir'+CHAR(62) 



IF @Supress = 0
    SELECT [dir], Create_Time, File_Size, [File_Name], [Struct_Type] 
      FROM Directory_Contents
     WHERE [dir] = @path
  ORDER BY [Struct_Type] DESC, [Create_Time] DESC



SET NOCOUNT OFF


isp_Dir_Exit:
  IF OBJECT_ID('tempdb..#Directory_Contents_Stage') IS NOT NULL
    DROP TABLE #Directory_Contents_Stage
  SET NOCOUNT OFF
  RETURN

isp_Dir_Error:
-- Add Error Handling
  GOTO isp_Dir_Exit

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources