SqlCacheDependency and ObjectDataSource

[J055]

Hi

I've been trying out SqlCacheDependency using the ObjectDataSource and SQL
Server 2005. It all works quite well with the minimum of configuration, e.g.

<asp:ObjectDataSource ID="odsAccounts" runat="server" ...
EnableCaching="true" SqlCacheDependency="CommandNotification">
....
</asp:ObjectDataSource>

My SelectMethod returns a Typed DataTable (using the DataSet designer). My
questions are:

1. Can I access the cached DataTable?
2. Can I invalidate the cache?
3. What approach should I use if I need to filter the DataTable before
returning the results? I'd like to query the cached DataTable.

Many thanks
Andrew

PS. The documentation on using this is sketchy. Some mention's that I need
to add some infomation to the web.config file. I didn't. The documentation
says 'The ObjectDataSource supports only polling'. But what is this:
SqlCacheDependency="CommandNotification"? It kind of suggests it's using the
SQL 2005 notification?

 

[Steven Cheng[MSFT]]

Hello Andrew,

As for the ObjectDataSource control, its caching mechnism is managed by the
ObjectDataSourceView class (used internally) and if you have configured the
objectDataSource control to use cache , we can not manually invalid the
underlying cached object.


#ObjectDataSource.Selected Event
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.objectdat
asource.selected.aspx


For your three questions, here are my understanding:

1. Can I access the cached DataTable?
==============================
The ObjectDataSource control has defined a "Selected" event, and in this
event we can get the returned data object through the event argument. e.g.

protected void ObjectDataSource1_Selected(object sender,
ObjectDataSourceStatusEventArgs e)
{
Response.Write("<br/>" + e.ReturnValue.GetType());
}

if the objectdatasource control is configured to use a
DataSet+TableAdapter, the returnValue is underlying datatable.


2. Can I invalidate the cache?
==============================
You can not manually invalid the underlying cached object.


3. What approach should I use if I need to filter the DataTable before
returning the results? I'd like to query the cached DataTable.
==============================
You can consider use the ReturnValue mentioned in #1

or more general, you can define a custom wrapper class around the original
data access class(like the DataSet+TableAdaper), on this wrapper class you
can define custom methods which will customize how to filter the raw
resultset returned by original data access class and return it to
ObjectDatasource control.

The ObjectDataSource control has an "ObjectCreated" event and we can access
the created class object( configured through "TypeName" attribute), thus
you can call any custom methods on that object(your wrapper class).

#ObjectDataSource.ObjectCreated Event
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.objectdat
asource.objectcreated.aspx


BTW, for the following things you mentioned:

=========================
PS. The documentation on using this is sketchy. Some mention's that I need
to add some infomation to the web.config file. I didn't. The documentation
says 'The ObjectDataSource supports only polling'. But what is this:
SqlCacheDependency="CommandNotification"? It kind of suggests it's using
the
SQL 2005 notification?
==========================

SQL 2005 natively support query notification through the
NotificationService while SQL Server 2000 doesn't. When using ASP.NET
SqlCacheDependency against SQL Server 2000, we need to add configuration in
web.config, and ASP.NET will use a background thread to poll the SQL Server
2000 database so as to make it work.

Hope this helps. If there is any other information you wonder, please feel
free to let me know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[J055]

Hi Steven

Thanks for the information.

1. Can I access the cached DataTable?
==============================
The ObjectDataSource control has defined a "Selected" event, and in this
event we can get the returned data object through the event argument. e.g.

protected void ObjectDataSource1_Selected(object sender,
ObjectDataSourceStatusEventArgs e)
{
Response.Write("<br/>" + e.ReturnValue.GetType());
}

if the objectdatasource control is configured to use a
DataSet+TableAdapter, the returnValue is underlying datatable.

It looks like these events (Selected and ObjectCreated) don't fire with the
following settings on the ObjectDataSource. They only seem to fire if the
cache is empty.

EnableCaching="true" SqlCacheDependency="CommandNotification"

Also, I would like to use the cached DataTable without altering it so that
it is available to other users of the application. I'm wondering if I need
to apply my own caching to my Business Object which returns the Typed
DataSet. The problem with this is that I can't benefit from the SQL 2005
notifications so would have to manually invalidate the cache for updates,
inserts and deletes in the Business Object.

Can you make any suggestions on the best approach?

Thanks
Andrew

 

[Steven Cheng[MSFT]]

Thanks for your reply Andrew,

I think you're right. I've omitted that the ObjectDataSource.Selected event
won't get fired when the result is cached.

For your scenario, you want to share the DataTable to other component in
addition to the ObjectDataSource. I think the reasonable way is creating a
wrapper business object (around your original busniess object whch return
the DataTable), and in this object, you can first manually cache the
DataTable for later use.

The SqlCacheDependency is not limited to ObjectDataSource's static
confriguration, but also support programmatic interface.

#Using SqlDependency in an ASP.NET Application
http://msdn2.microsoft.com/en-us/library/9dz445ks.aspx

What you can do is as below:

1. Customize the wrapper class or your original business/dataaccess class.
In its "select" method, instead of directly return the DataTable queried
from TableAdapter, you can first store the DataTable into the application
cache, and always let your business object return DataTable from cache. e.g.

if(Cache["_datatable"] == null)
{
//initialize the cached datatable
}

return Cache["_datatable"]


2. And since you want to also take advantage of the SqlCacheDependency and
the SQL 2005 query notification feature, you need to manually construct
the "SqlDependency" class and add it as the cache dependency of your
datatable. All of this can be done through code in your own custom
business class. Therefore, the datatable object stored in Cache will
invalid when the SqlDependency expires, it is exactly the same as you
statically declare in ObjectDatasource's property.

Here is the msdn reference introducing programmatically using sqldependcy
in ASP.NET application:

#Using SqlDependency in an ASP.NET Application
http://msdn2.microsoft.com/en-us/library/9dz445ks.aspx


Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[J055]

Hi Steven

Thanks for the replay and follow-up email. I've looked into your suggestions
and have the following comments/questions.

I couldn't quite see how to best use this in my business class.

The SqlDependency is part of the System.Data.SqlClient namespace so it would
spoil my 3 tier design to put SqlDependency.Start(GetConnectionString()) in
my business or ASP.NET tier. Can you recommend a way to use it with a typed
dataset? Also, is this definitely required for SQL 2005?

1. Customize the wrapper class or your original business/dataaccess class.
In its "select" method, instead of directly return the DataTable queried
from TableAdapter, you can first store the DataTable into the application
cache, and always let your business object return DataTable from cache.
e.g.

This is what I do at the moment. I then add this to the delete, insert and
update methods.

if (context.Cache[cacheKeyName] != null)
{
context.Cache.Remove(cacheKeyName);
}

2. And since you want to also take advantage of the SqlCacheDependency and
the SQL 2005 query notification feature, you need to manually construct
the "SqlDependency" class and add it as the cache dependency of your
datatable. All of this can be done through code in your own custom
business class. Therefore, the datatable object stored in Cache will
invalid when the SqlDependency expires, it is exactly the same as you
statically declare in ObjectDatasource's property.

I'm still unclear on how to use this. When I create a new SqlCacheDependency
I either need the command object or database and table name e.g.

SqlCacheDependency dependency = new SqlCacheDependency(command);

This information isn't really available in the business tier. I'm wondering
how this can be used with the Typed DataSet.

I think what I need is some good advise about implementing SQL Server
notifications and caching within an n'tier application. Hope you can help.

Thanks
Andrew

 

[Steven Cheng[MSFT]]

Thanks for your reply Andrew,

For your further questions:

=========================================
The SqlDependency is part of the System.Data.SqlClient namespace so it
would
spoil my 3 tier design to put SqlDependency.Start(GetConnectionString()) in
my business or ASP.NET tier. Can you recommend a way to use it with a typed
dataset? Also, is this definitely required for SQL 2005?
=========================================

SqlDependency.Start is required when we use Notification Service and
SqlCacheDependency in ASP.NET 2.0 against SQL Server 2005. If you're using
it against SQL Server 2000, there is different setting need to be configure
in web.config. Generally, you can simply add the SqlDependency.Start(...)
in ASP.NET application's application_Start event.


============================================
This is what I do at the moment. I then add this to the delete, insert and
update methods.

if (context.Cache[cacheKeyName] != null)
{
context.Cache.Remove(cacheKeyName);
}
=========================================

I would suggest you create a wrapper class dedicated for your web
application and use that wrapper calss in ObjectDataSource, and in the
ObjectDataSource's Select method you can use the following code logic:

#The DataObject here could be typed DataSet/DataTable or other type
according to your original Business class
<<<<<<<<<<<<<<<<<<<<<<<<,

public DataObject Select(..)
{
if(Context.Cache[key] == null)
{
initializeCache();
}

return (DataObject)Context.Cache[key];
}


private void initializeCache()
{
//get data object from original business class
DataObject obj = BusinessClass.SelectMethod();

//get the sqlcommand we need to add cache dependency against
SqlCacheDependency dependency =
new SqlCacheDependency(BusinessClass.GetSelectCommand());


Context.Cache.Add(Key, obj, dependency, Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);

}


=============================================
I'm still unclear on how to use this. When I create a new
SqlCacheDependency
I either need the command object or database and table name e.g.

SqlCacheDependency dependency = new SqlCacheDependency(command);

This information isn't really available in the business tier. I'm wondering
how this can be used with the Typed DataSet.
==========================================

As I mentioned above, we can wrapper the code logic on adding the
dataobject into cache in a wrapper class. Of course, this wrapper class is
only usable in ASP.NET runtime context because we're using the ASP.NET
application Cache here. This code can not be context neutural. If you want
to use SQL Query notification in winfrom applciatino, the codelogic is
different from this.

Please feel free to let me know if there is anything unclear.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[J055]

Hi Steven

SqlDependency.Start is required when we use Notification Service and
SqlCacheDependency in ASP.NET 2.0 against SQL Server 2005. If you're using
it against SQL Server 2000, there is different setting need to be
configure
in web.config. Generally, you can simply add the SqlDependency.Start(...)
in ASP.NET application's application_Start event.

Thanks, that's clearer now.

#The DataObject here could be typed DataSet/DataTable or other type
according to your original Business class
OK

private void initializeCache()
{
//get data object from original business class
DataObject obj = BusinessClass.SelectMethod();

//get the sqlcommand we need to add cache dependency against
SqlCacheDependency dependency =
new SqlCacheDependency(BusinessClass.GetSelectCommand());

This is the bit I don't understand. I don't have a
'BusinessClass.GetSelectCommand()' in my business object. I don't know how
to get the underlying SqlCommand. This is all dealt with the Typed DataSet
table adapter code in the data tier. Sorry if I'm missing something rather
obvious. Please explain further.

Context.Cache.Add(Key, obj, dependency, Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.Normal, null);

}

Thanks for your help so far.
Andrew

 

[Steven Cheng[MSFT]]

Thanks for your followup Andrew,

For your further questions:

============================
This is the bit I don't understand. I don't have a
'BusinessClass.GetSelectCommand()' in my business object. I don't know how
to get the underlying SqlCommand. This is all dealt with the Typed DataSet
table adapter code in the data tier. Sorry if I'm missing something rather
obvious. Please explain further.
============================

If you're not using custom business object class , but use the
TypedDataSet/TableAdapter, it may be abit harder here. Is the typed DataSet
and TableAdapter also developed by you or can you customize its code? If
you can modify the TableAdapter's code, you can consider expose the
ConnectionString property and SelectCommand property of inner DataAdapter
member of the TableAdapter(by using a partial class file). e.g

#you need to lookup the DataSet.Designer.cs file to determine the class's
name
==============================
public partial class itemsTableAdapter{

public string ConnectionString
{
get
{
return Connection.ConnectionString;
}
}

public SqlDataAdapter InternalAdapter
{
get
{
return _adapter;
}
}

}
=====================

then, in our wrapper class(put in the web application's App_Code dir), you
can use the TableAdapter like below

=============================
public class WrapperClass
{
static URLSiteLib.ItemsDSTableAdapters.itemsTableAdapter _adapter =
null;
..............................

public DataTable Select()
{
if (HttpContext.Current.Cache[DATA_KEY] == null)
{
InitializeData();
}

return (DataTable)HttpContext.Current.Cache[DATA_KEY];

}

protected void InitializeData()
{

DataTable dt = _adapter.GetData();

SqlCacheDependency dependency = new
SqlCacheDependency(_adapter.InternalAdapter.SelectCommand);
.

DateTime date = DateTime.Now.AddSeconds(30);
HttpContext.Current.Cache.Add(DATA_KEY, dt,
dependencyache.NoSlidingExpiration, CacheItemPriority.Normal, null);


}
===================================

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steven Cheng[MSFT]]

Hello Andrew,

After some further test, I'm afraid the the code I provided previously can
not quite work for TableAdapter/TypedDataSet scenario.

The problem here is:

** when using SqlCacheDependency, the SqlCommand need to be pass into
SqlCacheDependency's constructor(at that time, the query has not bee
executed).

**, we must execute the query (call command.Executexxxx or
DataAdapter.Fill.... ) after we create the SqlCacheDependency and add it
into CacheItem into ASP.NET Cache.

The above two requirement is easy to meet when we directly manipulate the
underlying SqlCommand ourself. However, when using TypedDataSet, the
underlying SqlCommand object is encapsulated by TableAdapter. And I did
successfully to expose the underlying sqlCommand by customzie the
TableAdapter(as I mentioned in the last reply). However, I find it still
quite hard to meet the second requirement.

Anyway, please feel free to let me know your opnion and ideas on this. If
you think it possible that we completely customize the TableAdapter or
you'll change to use other DataAccess class, we can still consider using
this approach.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[J055]

Hi Steven

I think we have to use alternative caching techniques as we want to use the
VS Typed DataSet code for our Data access layer. It would be good if MS
could update the DataSet code generation tool to allow use with
notifications at some point.

One question - how does the ObjectDataSource use SQL notifications if it is
using a business object derived from a Typed DataTable? Is there something
happening internally which we could copy perhaps?

Thanks for your help.
Andrew

 

[Steven Cheng[MSFT]]

Hello Andrew,

Thanks for your followup. For your question below:

One question - how does the ObjectDataSource use SQL notifications if it is
using a business object derived from a Typed DataTable? Is there something
happening internally which we could copy perhaps?
===============================
Good question. Actually I should have mentioned this already in previous
replies(but I forgot to include it ).

For ObjectDataSource or ASP.NET 2.0 Page(outputcache) which use the
"CommandNotification" as SqlCacheDependency value, it will make the ASP.NET
runtime to regiser a context variable in the remoting's CallContext and
then the variable(a flag) will be read by Sqlcommand or its caller
component which esstablish a SqlCacheDependency against the SQLSErver 2005
database table. Here is a web article which describes this feature:

#How CommandNotification works with OutputCaching
http://pluralsight.com/blogs/fritz/archive/2006/06/15/27694.aspx

Unfortunately, this is an internal and undocumented behavior, therefore I
don't think we could simply apply it in our own data access/business class.

If there is any other information you wonder, please feel free to let me
know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.