CODEWITHSUNDEEP
databasesql-server-ceenterprise-librarycaching-application-block
nk2003dec
nk2003dec

Reputation: 535

enterprise library cache to local database

I'm trying to find out if it is possible to use the entlib caching block to cache to a local db (SQL Server Compact 3.5 database), however I can't any tutorials or examples for this; only for MSSQL Server. I'm having a lot of problems with the script since a lot of functionality is not supported in SQL Compact:

/****** Object:  Database Caching    Script Date: 8/25/2004 3:28:27 PM ******/

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Caching')
    DROP DATABASE [Caching]
GO

CREATE DATABASE [Caching]
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'Caching', N'autoclose', N'false'
GO

exec sp_dboption N'Caching', N'bulkcopy', N'false'
GO

exec sp_dboption N'Caching', N'trunc. log', N'false'
GO

exec sp_dboption N'Caching', N'torn page detection', N'true'
GO

exec sp_dboption N'Caching', N'read only', N'false'
GO

exec sp_dboption N'Caching', N'dbo use', N'false'
GO

exec sp_dboption N'Caching', N'single', N'false'
GO

exec sp_dboption N'Caching', N'autoshrink', N'false'
GO

exec sp_dboption N'Caching', N'ANSI null default', N'false'
GO

exec sp_dboption N'Caching', N'recursive triggers', N'false'
GO

exec sp_dboption N'Caching', N'ANSI nulls', N'false'
GO

exec sp_dboption N'Caching', N'concat null yields null', N'false'
GO

exec sp_dboption N'Caching', N'cursor close on commit', N'false'
GO

exec sp_dboption N'Caching', N'default to local cursor', N'false'
GO

exec sp_dboption N'Caching', N'quoted identifier', N'false'
GO

exec sp_dboption N'Caching', N'ANSI warnings', N'false'
GO

exec sp_dboption N'Caching', N'auto create statistics', N'true'
GO

exec sp_dboption N'Caching', N'auto update statistics', N'true'
GO

use [Caching]
GO

/****** Object:  Stored Procedure dbo.AddItem    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddItem]
GO

/****** Object:  Stored Procedure dbo.Flush    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Flush]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Flush]
GO

/****** Object:  Stored Procedure dbo.GetItemCount    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetItemCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetItemCount]
GO

/****** Object:  Stored Procedure dbo.LoadItems    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoadItems]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LoadItems]
GO

/****** Object:  Stored Procedure dbo.RemoveItem    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RemoveItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RemoveItem]
GO

/****** Object:  Stored Procedure dbo.UpdateLastAccessedTime    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateLastAccessedTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateLastAccessedTime]
GO

/****** Object:  Table [dbo].[CacheData]    Script Date: 8/25/2004 3:28:27 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CacheData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CacheData]
GO

/****** Object:  Table [dbo].[CacheData]    Script Date: 8/25/2004 3:28:27 PM ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CacheData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [dbo].[CacheData] (
    [StorageKey] [int] NOT NULL ,
    [PartitionName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Key] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Value] [image] NULL ,
    [RefreshAction] [image] NULL ,
    [Expirations] [image] NULL ,
    [ScavengingPriority] [int] NOT NULL ,
    [LastAccessedTime] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

GO

ALTER TABLE [dbo].[CacheData] WITH NOCHECK ADD 
    CONSTRAINT [PK_CacheData] PRIMARY KEY  CLUSTERED 
    (
        [StorageKey],
        [PartitionName]
    )  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.AddItem    Script Date: 8/25/2004 3:28:27 PM ******/



CREATE PROCEDURE dbo.AddItem
(
    @partitionName varchar(128),
    @storageKey int,
    @key varchar(128),
    @value image,
    @scavengingPriority int,
    @refreshAction image,
    @expirations image,
    @lastAccessedTime datetime
)
 AS
        delete from CacheData where StorageKey = @storageKey and PartitionName = @partitionName

        insert into CacheData (PartitionName, StorageKey, [Key], Value, RefreshAction, Expirations, ScavengingPriority, LastAccessedTime)
        values (@partitionName, @storageKey, @key, @value, @refreshAction, @expirations, @scavengingPriority, @lastAccessedTime)





GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.Flush    Script Date: 8/25/2004 3:28:27 PM ******/



CREATE PROCEDURE dbo.Flush
(
    @partitionName varchar(128)
)
AS
    SET NOCOUNT ON

    DELETE [dbo].[CacheData] where PartitionName = @partitionName





GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.GetItemCount    Script Date: 8/25/2004 3:28:27 PM ******/



CREATE PROCEDURE dbo.GetItemCount
(
    @partitionName varchar(128)
)
 AS 
    SET NOCOUNT ON

    SELECT COUNT(StorageKey) 
      FROM [dbo].[CacheData] where PartitionName = @partitionName





GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.LoadItems    Script Date: 8/25/2004 3:28:27 PM ******/



CREATE PROCEDURE dbo.LoadItems
(
    @partitionName varchar(128)
)
AS
    select 
        [Key], 
        Value, 
        RefreshAction, 
        Expirations, 
        ScavengingPriority, 
        LastAccessedTime
    from CacheData where PartitionName = @partitionName

    SET NOCOUNT ON
    RETURN 




GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.RemoveItem    Script Date: 8/25/2004 3:28:27 PM ******/



CREATE PROCEDURE dbo.RemoveItem
    (
        @partitionName varchar(128),
        @storageKey int
    )
AS
    delete from CacheData 
    where StorageKey = @storageKey and PartitionName = @partitionName

    SET NOCOUNT ON 
    RETURN 




GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.UpdateLastAccessedTime    Script Date: 8/25/2004 3:28:27 PM ******/



CREATE PROCEDURE dbo.UpdateLastAccessedTime
    (
        @partitionName varchar(128),
        @storageKey int,
        @lastAccessedTime DateTime
    )
AS
    update CacheData 
    set LastAccessedTime = @lastAccessedTime where [StorageKey] = @storageKey and PartitionName = @partitionName

    SET NOCOUNT ON
    RETURN 




GO

SET QUOTED_IDENTIFIER OFF 

GO
SET ANSI_NULLS ON 

GO

Anybody have any ideas, links or anything?!

Upvotes: 4

Views: 315

Answers (2)

user11629814
user11629814

Reputation:

Stored procedures do not exists in SQL CE as noted, so other options must be explored

Upvotes: 0

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65451

As you note there is functionality that is not supported in SQL CE, therefore not possible to use Entlib caching.

If what you are trying to do is to cache data in a local SQL CE database, then there are other options.

You could try MS Sync framework http://msdn.microsoft.com/en-us/library/ee617382.aspx

There is an article of how to do this here: http://www.codemag.com/article/0712092

Upvotes: 1

Related Questions