|
RadControls version
|
2008 Q3 |
| .NET version |
2.0 |
| Visual Studio version |
2005 |
| programming language |
any |
| browser support |
all browsers supported by RadControls
|
PROJECT DESCRIPTION
I wanted to use this
telerik sample to create a DB driven image upload and management system.
I converted it to use SQL server 2005 but found that when you deleted a record in my system that any child folders/items were not deleted as they were in the access based system. Lini at Telerik pointed out that this was because of the DB platform (Access handles the cascading delete but SQL server doesn't on a self referencing table).
So I wrote a query to get all the child objects (including the starting object) so you can get a list of all the child items you need to delete when a folder is deleted.
I am also using this to make sure when lots of users are storing images in the same system that when you populate the image manager you aren't looking up all records in the DB, just the records for that user, ie their folder contents and all sub folders they may have created. The system will have thousands of users but all with few images so this is important.
I'm using a slightly modified table from the access DB in the sample, I added a datestamp so I could tell when a record was creteated/updated and used guids for the Id's rather than autoNumber in access. You just pass the itemID to the query and it returns all items that are children including subfolders and contents.
Posting it as it hurt my head and took a while to get the query right as I'm no SQL expert!
Hopefully it will save someone some time :)
Matt
SQL table:
USE [yourDB]
GO
/****** Object: Table [dbo].[pics] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[pics](
[itemID] [uniqueidentifier] NOT NULL,
[content] [image] NULL,
[dateStamp] [datetime] NULL,
[mimeType] [nvarchar](50) NULL,
[isDirectory] [bit] NULL,
[size] [int] NULL,
[parentID] [uniqueidentifier] NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_pics] PRIMARY KEY CLUSTERED
(
[itemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SQL QUERY:
WITH picsDB
(itemID,[Name],dateStamp, mimeType, isDirectory, [size], parentID)
AS
(SELECT a.itemID, a.Name, a.dateStamp, a.mimetype, a.isDirectory, a.size, a.parentID
FROM dbo.Pics a WHERE a.itemID = '" & strID & "'
UNION ALL SELECT a.itemID, a.Name, a.dateStamp, a.mimetype, a.isDirectory, a.size, a.parentID
FROM dbo.pics a INNER JOIN picsDB b ON a.parentID = b.itemID ) SELECT * FROM picsDB