Shane Duffy

Archive for the ‘IIS’ Category

Caching is a very useful way for you to retain pages or data across HTTP requests, so that you can reuse them without the recreating them.

In ASP.net 2.0, it makes you easier to implement data caching. SQL cache dependencies are one of ASP.net 2.0 coolest features, which allows you to create dependencies between cached items and database entities. This enables cached query results to be automatically evicted from the cache if the underlying data changed. This feature applies to SQL Server 7, 2000 and 2005.

SQL cache dependencies are more powerful when paired with SQL Server 2005
- you can set the cache dependencies are more granular

- this is not rely on polling but on query notifications from SQL Server 2005
- this require no preparation of the database

Here are the steps on how to enable the SQL cache dependency on a table.

1. Enable notifications using SQL registration tool
To enable SQL cache dependency, first you have to register the database, tables by using the Sql registration tool (C:\Windows\Microsoft.NET\Framework\v2.0.50727)
At the command prompt, type
aspnet_regsql -S [servername] -E -d [databasename] -ed -et -t [tablename]
-S = Server
-E = TrustedConnection
-d = Database
-ed = Enable caching at database

-et = Enable caching at table
-t = table

other options, -u = username, -p = password

After you run the statement, you hit refresh at your database.
You will notice there is an extra table created to store the cache data and a trigger created for the particular table. When there is changes on the table, the trigger will be fired up and write the changes to the cache table.

2. Register the notification in the Web Configuration
Enable the sql cache dependency at the web configuration file and set the poll time.
<system.web>
<
caching>
<
sqlCacheDependency enabled=true pollTime=2000>
<
databases>
<
add name=conn connectionStringName=conn/>
</
databases>
</
sqlCacheDependency>
</
caching>
</
system.web>

The poll time is specifies how often the application checks to see whether the data has changed.

3. Define SQL Dependency
There are two options to define the SQL dependency
- You can specify on the OutputCache directive
<%@ OutputCache Duration=”10″ VaryByParam=”*”
SqlDependency=”[sqldependencyname]:[tablename]“ %>

- or, specify directly on a datasource control
<asp:SqlDataSource EnableCaching=”true” CacheDuration=”Infinite”
SqlCacheDependency=”[sqldependencyname]:[tablename]“ />

After you have defined the SQL dependency, any changes that made to the database will be reflected on your web pages immediately though you have set the cache duration on the page.
This can make sure that you users always get the latest data from the server.

4. Notification-based cache invalidation
If you are using SQL Server 2005, you can use the Notification-based cache invalidation.
Unlike polling based validation, no <sqlCacheDependency> needs to be registered in your application’s configuration. Furthermore, no special configuration using the aspnet_regsql.exe tool is needed.

A notification based dependency is configured on the OutputCache directive using the string CommandNotification.
<%@ OutputCache Duration=”10″ VaryByParam=”*” SqlDependency=”CommandNotification %>
This value indicates to ASP.NET that a notification based dependency should be created for the page or datasource control.

System.Data.SqlClient.SqlDependency.Start() method must be called before the first SQL query is executed. This method could be placed in Application_Start() event in global.asax file.

void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
}

This option is much more efficient as there is no pollung going on but the ASP.net acts as client, only if there is changes, ASP.net will get notified.

You may use SQL profiler to monitor the perfomance of your database to compare before and after you apply this SQL cache dependency.

It is recommended that store your database connection strings in the Web.config file and encrypt the connection strings. In the .NET Framework 2.0, you have the option to enable the configuration encryption in the <protectedData> section in the web.config file.
For example:
<protectedData>
<protectedDataSections>
<add name=”connectionStrings” provider=”RSAProtectedConfigurationProvider”/>
</protectedDataSections>
</protectedData>
There are two predefined providers that you can find in the .NET Framework 2.0:
1. DPAPIProtectedConfigurationProvider: It uses the Windows data protection API (DPAPI) to encrypt and decrypt configuration data.
2. RSAProtectedConfigurationProvider: It uses RSA encryption to encrypt and decrypt configuration data

Both the RSA and DPAPI providers are good options for you to encrypt the sensitive data.
However, DPAPI is not recommended to proctect sections in a web farm scenario. This is because the same encrypted web.config file will be deployed to all the machines. What you can do here is to manually encrypt the web.config files on each machine or copy the same container key to the machines.

To create a key container: aspnet_regiis.exe -pc YourContainerName -exp
Export to an XML file: aspnet_regiis.exe -px YourContainerName YourXmlFile.xml
Move the XML file to other machin: aspnet_regiis.exe -pi YourContainerName YourXmFile.xml

Here is an article discussed on the  Encrypting Connection Strings in Web.config. http://www.ondotnet.com/pub/a/dotnet/2005/02/15/encryptingconnstring.html


Follow

Get every new post delivered to your Inbox.