Hello All,
A really cool feature of ASP.NET 2.0 in conjuction with a SQL 2005 database is the SqlDependency class to cache data (through SqlCacheDependency). It allows your web application to listen for notifications of changed data from the SQL 2005 server. Whereas with SQL 2000, your web application will have to poll the SQL server on a regular basis.
Here's an example on how to set up a web application for SqlDependency.
1. Connect to your database with SQL Management Studio, and run this query to enable Service Broker for your database:
ALTER DATABASE <database name> SET enable_broker
2. In your application's web.config file, add a connectionString. For example,
<connectionStrings>
<add
name="connString1"
connectionString="Server=<server_name>;Initial Catalog=<database_name>;User ID=<username>;Password=<password>;"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
3. In the global.asax file, add the SqlDependency.Start and Stop methods in Application_Start and End, respectively. So when your application process loads it will also start listening for SQL notifications from the specific connection string instance.
<%@ Application Language="VB" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
SqlDependency.Start(ConfigurationManager.ConnectionStrings("connString1").ConnectionString)
End Sub
Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
SqlDependency.Stop(ConfigurationManager.ConnectionStrings("connString1").ConnectionString)
End Sub
</script>
4. You can then enable caching and SqlCacheDependency in the SqlDataSource control or even the entire page. In this example it's for the SqlDataSource control, which a GridView control uses. You can only cache SELECT commands.
<asp:SqlDataSource
ID="sqlTestSource"
Runat="server"
DataSourceMode="DataSet"
EnableCaching="true"
CacheDuration="20"
SqlCacheDependency="CommandNotification"
ConnectionString="<%$ ConnectionStrings:connString1 %>"
SelectCommand="SELECT column1 FROM dbo.Table1"
/>
<asp:GridView id="GridView1" runat="server" DataSourceID="sqlTestSource" />
What will happen is that the SELECT result will be cached by the web application, and will only refresh the data after 20 minutes (as indicated in the CacheDuration value) or when it is notified by the SQL 2005 server that the result of the SELECT query has changed. Your pages will load faster since it saves time by not querying the SQL server all the time.
You can check your SQL 2005 database to see if the notification gets posted. Connect to your database with SQL Management Studio, go to Service Broker > Queues. You should see a new SqlQueryNotificationService entry.