DBfileBrowserContentProvider migrate to SQL server

2 posts, 0 answers
  1. mattc
    mattc avatar
    21 posts
    Member since:
    Aug 2007

    Posted 10 Feb 2009 Link to this post

    Requirements

    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
  2. Rumen
    Admin
    Rumen avatar
    12851 posts

    Posted 11 Feb 2009 Link to this post

    Hi Matt,

    Thank you for sharing your solution with us. I hope that your fellow developers will benefit from it.

    As a small token of gratitude for your work I updated your Telerik points.

    Best regards,
    Rumen
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
Back to Top