Using ASP.NET Caching with SQL Server

Using ASP.NET Caching with SQL Server

1. Enabling Cache Notification for SQL Server
   

   
   

• On the Windows Start menu, point to All Programs, point to Accessories, and then click Command Prompt to open a command prompt window.
  

• Locate the Aspnet_regsql.exe executable file on your disk drive. This file is installed with the .NET Framework version 2.0 in the following location:
  

%windir%\Microsoft.NET\Framework\FrameworkVersion

Be sure that %windir% represents your Windows directory and that the .NET Framework version is 2.0 or later. The path might look like the following:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.40217

• Use the following command to enable cache notification for the dimProductCategory table in the AdventureWorksDW database:
  

aspnet_regsql.exe -S <Server> -U <Username> -P <Password> -ed -d AdventureWorksDW -et –t dimProductCategory

1. Configuring the Web Page for Caching
   

   <%@ OutputCache Duration="3600" SqlDependency=" AdventureWorksDW: DimProductCategory
   

   " VaryByParam="none" %>
   

1. Setting Caching Configuration in the Web.config File
   

   Add the following XML to the Web.config file as a child of the system.web element:
   

   
   

<!-- caching section group -->

<caching>

<sqlCacheDependency
enabled = "true"
pollTime = "1000" >

<databases>

<add
name="AdventureWorksDW" connectionStringName="AdventureWorksDWConnectionString"

pollTime = "1000"/>

</databases>

</sqlCacheDependency>

</caching>

1. To configure application-level caching
   

   <!-- caching section group -->
   

   <caching>
   

   <outputCacheSettings>
   

   <outputCacheProfiles>
   

   <add
   name="AppCache1"
   enabled="true"
   duration="60"/>
   

   </outputCacheProfiles>
   

   </outputCacheSettings>
   

   </caching>
   

   Add the OutputCache into the page
   

<%@ OutputCache CacheProfile="AppCache1" VaryByParam="none" %>

The sqlDependency table can also specify in the web config as below:

<!-- caching section group -->

<caching>

<outputCacheSettings>

<outputCacheProfiles>

<add
name="AppCache1"
enabled="true" sqlDependency="AdventureWorksDW:DimProductCategory"

duration="60"/>

</outputCacheProfiles>

</outputCacheSettings>

</caching>

1. Cache the data object
   

   The sample codes below can set up the SqlCacheDependency with the tables or the sql queries
   

   
   

// Declare the SqlCacheDependency instance, SqlDep.

SqlCacheDependency SqlDep = null;

// Check the Cache for the SqlSource key.

// If it isn't there, create it with a dependency

// on a SQL Server table using the SqlCacheDependency class.

if (Cache["SqlSource"] == null)

{

// Because of possible exceptions thrown when this

// code runs, use Try...Catch...Finally syntax.

try

{

// Instantiate SqlDep using the SqlCacheDependency constructor.

SqlDep = new SqlCacheDependency("AdventureWorksDW", "DimProductCategory");

}

// Handle the DatabaseNotEnabledForNotificationException with

// a call to the SqlCacheDependencyAdmin.EnableNotifications method.

catch (DatabaseNotEnabledForNotificationException exDBDis)

{

try

{

SqlCacheDependencyAdmin.EnableNotifications("AdventureWorksDW");

}

// If the database does not have permissions set for creating tables,

// the UnauthorizedAccessException is thrown. Handle it by redirecting

// to an error page.

catch (UnauthorizedAccessException exPerm)

{

throw new Exception(exPerm.Message, exPerm);

}

}

// Handle the TableNotEnabledForNotificationException with

// a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.

catch (TableNotEnabledForNotificationException exTabDis)

{

try

{

SqlCacheDependencyAdmin.EnableTableForNotifications("Northwind", "Categories");

}

// If a SqlException is thrown, redirect to an error page.

catch (SqlException exc)

{

throw new Exception(exc.Message, exc);

}

}

// If all the other code is successful, add MySource to the Cache

// with a dependency on SqlDep. If the Categories table changes,

// MySource will be removed from the Cache. Then generate a message

// that the data is newly created and added to the cache.

finally

{

Cache.Insert("SqlSource", this.SqlDataSource1, SqlDep);

//The data object was created explicitly;

}

}

else

{

//The data was retrieved from the Cache;

}

3. Multiple Table Dependencies

SqlCacheDependency
  dep1 = new SqlCacheDependency ("MyDb", "Users"),
  dep2 = new SqlCacheDependency ("MyDb", "UserRoles"),
  dep3 = new SqlCacheDependency ("MyDb", "Policies");

AggregateCacheDependency aggDep = new AggregateCacheDependency ();
aggDep.Add (dep1, dep2, dep3);

Cache.Insert ("SomeKey", someData, aggDep);

Create a SQL cache dependecy using a custom (non-dbo) schema

The store procedure "AspNet_SqlCacheRegisterTableStoredProcedure" 'enables' change notification on the table you specify (in simpler terms, it just creates a trigger on the table you specify)

This procedure has the dbo schema hardcoded in it

SET @fullTriggerName =
'dbo.['
+ @triggerName +
']'

Just tweaked this portion of the procedure to extract the Schema Name specified by the user and use that to create the trigger rather than the hardcoded 'dbo'. The modified stored proc is below. You can alter the procedure on your database instance and you are all set. Ofcourse, I find similar hardcoding of 'dbo' in the procedure AspNet_SqlCacheUnRegisterTableStoredProcedure, which is responsible for disabling notifications on your table, so you will need to tweak it a bit to make unregistration work.

-- ===========================================================================================

USE [TestDB]

GO

/****** Object: StoredProcedure [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure] Script Date: 04/18/2008 16:05:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[AspNet_SqlCacheRegisterTableStoredProcedure]

@tableName NVARCHAR(450)

AS

BEGIN

 

DECLARE @triggerName AS NVARCHAR(3000)

DECLARE @fullTriggerName AS NVARCHAR(3000)

DECLARE @canonTableName NVARCHAR(3000)

DECLARE @quotedTableName NVARCHAR(3000)

        

--Vivek Start --Added A

         DECLARE @schemaName NVARCHAR(3000)

        IF(CHARINDEX('.',@tableName) <> 0)

        BEGIN

            SET @schemaName = SUBSTRING(@tableName,0,CHARINDEX('.',@tableName))

            SET @tableName = SUBSTRING(@tableName,CHARINDEX('.',@tableName) + 1,LEN(@tableName) - CHARINDEX('.',@tableName))

        END

--Vivek END A

        
 

 

/* Create the trigger name */

SET @triggerName = REPLACE(@tableName, '[', '__o__')

SET @triggerName = REPLACE(@triggerName, ']', '__c__')

SET @triggerName = @triggerName + '_AspNet_SqlCacheNotification_Trigger'

--Vivek Commented --SET @fullTriggerName = 'dbo[' + @triggerName + ']'

         IF(@schemaName IS NOT NULL)

            SET @fullTriggerName ='[' + @schemaName + '].[' + @triggerName + ']'

         ELSE

            SET @fullTriggerName = 'dbo.[' + @triggerName + ']'

 

/* Create the cannonicalized table name for trigger creation */

/* Do not touch it if the name contains other delimiters */

IF (CHARINDEX('.', @tableName) <> 0 OR

CHARINDEX('[', @tableName) <> 0 OR

CHARINDEX(']', @tableName) <> 0)

SET @canonTableName = @tableName

ELSE

SET @canonTableName = '[' + @schemaName + '].[' + @tableName + ']'

 

         /* First make sure the table exists */

--Vivek Commented --IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL

             --BEGIN

-- RAISERROR ('00000001', 16, 1)

-- RETURN

--END

--Vivek Start --Added B

         IF(@schemaName IS NULL)

            BEGIN

                IF (SELECT OBJECT_ID(@tableName, 'U')) IS NULL

                BEGIN

RAISERROR ('00000001', 16, 1)

RETURN

END

            END

         ELSE

            BEGIN

                IF (SELECT OBJECT_ID(@schemaName + '.' + @tableName, 'U')) IS NULL

                BEGIN

                    RAISERROR ('00000001', 16, 1)

                    RETURN

                END

            END

        

--Vivek End B

        
 

 

BEGIN TRAN

/* Insert the value into the notification table */

IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (NOLOCK) WHERE tableName = @tableName)

IF NOT EXISTS (SELECT tableName FROM dbo.AspNet_SqlCacheTablesForChangeNotification WITH (TABLOCKX) WHERE tableName = @tableName)

INSERT dbo.AspNet_SqlCacheTablesForChangeNotification

VALUES (@tableName, GETDATE(), 0)

 

/* Create the trigger */

SET @quotedTableName = QUOTENAME(@tableName, '''')

IF NOT EXISTS (SELECT name FROM sysobjects WITH (NOLOCK) WHERE name = @triggerName AND type = 'TR')

IF NOT EXISTS (SELECT name FROM sysobjects WITH (TABLOCKX) WHERE name = @triggerName AND type = 'TR')

EXEC('CREATE TRIGGER ' + @fullTriggerName + ' ON ' + @canonTableName +'

FOR INSERT, UPDATE, DELETE AS BEGIN

SET NOCOUNT ON

EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N' + @quotedTableName + '

END

')

COMMIT TRAN

END

 

Ajax enabled WCF Service stops working with Undefined error

I got a javascript underfine error when I use the wcf service call. I found out that it will work after I remove or comment the extendedProtectionPolicy from the web configuration.

 

Use AspNet_Merge to Version Website Assemblies

If you want to add versioning information to the assemblies generated for a precompiled Web site, you can do the following:

• Add an AssemblyInfo.cs or AssemblyInfo.vb file to the App_Code folder. The assembly generated for the App_Code folder will then be versioned. You can also add version attributes to the code-behind files for pages and user controls. However, adding version attributes to individual files is tedious and applies only to code-behind files in an updatable precompilation layout.
  
• Add references to an AssemblyInfo.cs or AssemblyInfo.vb file to the compilerOptions attribute of the <compiler> element in the Web.config file. This enables you to add versioning for all assemblies generated for the Web site. In that case, you must include both C# and Visual Basic compilers because ASP.NET might select a compiler for non-code type files, such as WSDL, when compiling the proxies.
  

Aspnet_merge.exe can version the assemblies it creates by either copying the attributes from the App_Code assembly or copying them from a specific assembly specified using the –copyattrs option. This is therefore treated like any other assembly attribute. These options for specifying an attribute for the merged assembly or assemblies ensures that you can define all version attributes, including the file version, the assembly version, and any other assembly attributes you require.

Figure 25 shows the merge command used to apply attributes from the App_Code assembly, using the Web site shown in Figure 1. The AssemblyInfo.cs file shown in Figure 22 has been added to the Web site's App_Code folder.

Example

Aspnet_merge c:\precompileWebsite –copyattrs –a

IE Modal Dialog and ASP.NET PostBack solution

 

Put the following HTML code inside of your page header in HTML source,

1	<head>
2	<title>My Page</title>

 

3	<base target="_self"></base>
4	</head>

The modal dialog will be able to successfully postback to itself

Multiple Onclick JavaScript Events on a Single Control

You can't add two identical events to the same Html control. For example, if you have a button with an onclick event, and you add another onclick at the server, the second event will overwrite the first. This becomes a problem if you need to add an event to some variable control, but you don't know what events that control already has. However, .Net provides a way to handle this.

We can:

1. See if the variable html control already has an onclick event.
   
2. If it does, then dynamically register a new script that calls both the original onclick script as well as the new script.
   
3. Replace the variable control's onclick value with that new script.
   

The following code snippets this. This first block shows a simple JavaScript (with a parameter) being called by a button's onclick event.

function DoStuff1(var1) {     alert('1: ' + var1); } ... <INPUT id="Button1" onclick="DoStuff1('Hello1')" type="button" value="Button" name="Button1" runat="server"> ...

This snippet shows the server code to check if an onclick event already exists, and add a new wrapper if needed. Note that it persists the onclick values in viewstate to ensure that the wrapper function doesn't wrap itself upon postback. For example, if the first onclick event called DoStuff1(), and we wanted to dynamically add a new onclick function DoStuff2(), we could create a wrapper function Wrapper1() that called both functions, and was called from the onclick. Wrapper1() becomes the new value of the button's onclick.

private void Page_Load(object sender, System.EventArgs e) {     string s = this.Button1.Attributes["onclick"];     if (!Page.IsPostBack)         OriginalJSFunction = s;     if (s == null)         this.Button1.Attributes.Add("onclick","DoStuff2()");     else     {         if (!Page.IsClientScriptBlockRegistered("JS1"))             Page.RegisterClientScriptBlock("JS1",GetJS(OriginalJSFunction));         this.Button1.Attributes.Add("onclick","Wrapper1()");     } } private string OriginalJSFunction {     get     {         object o = ViewState["OriginalJSFunction"];         if (o == null)             return "";         else             return o.ToString();     }     set     {         ViewState["OriginalJSFunction"] = value;     } } private string GetJS(string strOriginalFn) //will handle initial JS with params {     System.Text.StringBuilder sb = new System.Text.StringBuilder();     sb.Append(@"         <script language='javascript'>         function Wrapper1() {         " + strOriginalFn + @";         DoStuff2();         }         </script>     ");     return sb.ToString(); }

 While this approach is tedious, it lets you dynamically add an event to a control without overwriting that control's existing event. This is useful when making your own custom controls that integrate with existing Html controls already on the page.

http://timstall.dotnetdevelopersjournal.com/multiple_onclick_javascript_events_on_a_single_control.htm

Multiple select values in Cognos Report with store procedure

 

1. Import the stored procedure in Framework Manager and set the parameters to:
   

   1^st parameter = #prompt('param1')#
   

   2nd parameter = #prompt('param2')#
   

    
    

2. Create a report in Report Studio with a report page calling the SP and a prompt page containing the multiple select value prompt
   

 
 

1. Add a text box prompt object to the prompt page
   

 
 

1. Set the following ID's for the objects on the prompt page:
   

   Multiple select value prompt ID = Preprocessing_ID1
   

   Textbox prompt ID = Postprocessing_ID1
   

    
    

2. Add an HTML item with the following javascript to the report:
   

 
 

<script>

// IBM Cognos 8.3 specific

 
 

function assignParamValue()

{

// get the reference to the Cognos form

var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() :

document.forms["formWarpRequest"]);

 
 

var i, tmp;

tmp = "";

 
 

// get the handle for the 1st checkbox prompt - add div around them to distinguish

 
 

var prompt1 = document.getElementById('checkboxPrompt1');

 
 

// find all the children of the div of type checkboxes.

for (i=0; i < prompt1.childNodes.length; i++)

{

var node_list = prompt1.getElementsByTagName('input');

for (var i = 0; i < node_list.length; i++)

{

var node = node_list[i];

if (node.getAttribute('type') == 'checkbox')

{

if (node.checked)

{

if( tmp == "" )

{

tmp = node.value;

}

else

{

tmp = tmp + "," + node.value;

}

}

}

}

}

fW._textEditBoxPostprocessing_ID.value = tmp;

 
 

canSubmitPrompt();

promptButtonFinish();

}

</script>

1. Add an HTML item before the checkbox with the contents of
   

   <div id="checkboxPrompt1">
   

   and add an HTML item right after the checkbox prompt with the contents of
   

   </div>
   

    
    

2. Add a virtual "finish" button to call the javascript function before proceeding to the report itself by adding another HTML item and inserting the following code:
   

 
 

<input type="BUTTON" class="clsPromptButton" onClick="assignParamValue()"

value="Finish">