473,698 Members | 2,082 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SqlCacheDepende ncy and ObjectDataSourc e

Hi

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

<asp:ObjectData Source ID="odsAccounts " runat="server" ...
EnableCaching=" true" SqlCacheDepende ncy="CommandNot ification">
....
</asp:ObjectDataS ource>

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 ObjectDataSourc e supports only polling'. But what is this:
SqlCacheDepende ncy="CommandNot ification"? It kind of suggests it's using the
SQL 2005 notification?
Aug 30 '06 #1
10 4567
Hello Andrew,

As for the ObjectDataSourc e control, its caching mechnism is managed by the
ObjectDataSourc eView class (used internally) and if you have configured the
objectDataSourc e control to use cache , we can not manually invalid the
underlying cached object.
#ObjectDataSour ce.Selected Event
http://msdn2.microsoft.com/en-us/lib...rols.objectdat
asource.selecte d.aspx
For your three questions, here are my understanding:

1. Can I access the cached DataTable?
=============== ===============
The ObjectDataSourc e 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 ObjectDataSourc e1_Selected(obj ect sender,
ObjectDataSourc eStatusEventArg s e)
{
Response.Write( "<br/>" + e.ReturnValue.G etType());
}

if the objectdatasourc e control is configured to use a
DataSet+TableAd apter, 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+TableAd aper), 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
ObjectDatasourc e control.

The ObjectDataSourc e control has an "ObjectCrea ted" 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).

#ObjectDataSour ce.ObjectCreate d Event
http://msdn2.microsoft.com/en-us/lib...rols.objectdat
asource.objectc reated.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 ObjectDataSourc e supports only polling'. But what is this:
SqlCacheDepende ncy="CommandNot ification"? It kind of suggests it's using
the
SQL 2005 notification?
=============== ===========

SQL 2005 natively support query notification through the
NotificationSer vice while SQL Server 2000 doesn't. When using ASP.NET
SqlCacheDepende ncy 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/subscripti...ult.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/subscripti...t/default.aspx.

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

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


Aug 31 '06 #2
Hi Steven

Thanks for the information.
1. Can I access the cached DataTable?
=============== ===============
The ObjectDataSourc e 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 ObjectDataSourc e1_Selected(obj ect sender,
ObjectDataSourc eStatusEventArg s e)
{
Response.Write( "<br/>" + e.ReturnValue.G etType());
}

if the objectdatasourc e control is configured to use a
DataSet+TableAd apter, the returnValue is underlying datatable.
It looks like these events (Selected and ObjectCreated) don't fire with the
following settings on the ObjectDataSourc e. They only seem to fire if the
cache is empty.

EnableCaching=" true" SqlCacheDepende ncy="CommandNot ification"

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
Aug 31 '06 #3
Thanks for your reply Andrew,

I think you're right. I've omitted that the ObjectDataSourc e.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 ObjectDataSourc e. 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 SqlCacheDepende ncy is not limited to ObjectDataSourc e'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 SqlCacheDepende ncy and
the SQL 2005 query notification feature, you need to manually construct
the "SqlDepende ncy" 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 ObjectDatasourc e's property.

Here is the msdn reference introducing programmaticall y 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/subscripti...ult.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/subscripti...t/default.aspx.

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

This posting is provided "AS IS" with no warranties, and confers no rights.
Sep 1 '06 #4
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.Sql Client namespace so it would
spoil my 3 tier design to put SqlDependency.S tart(GetConnect ionString()) 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.R emove(cacheKeyN ame);
}
2. And since you want to also take advantage of the SqlCacheDepende ncy and
the SQL 2005 query notification feature, you need to manually construct
the "SqlDepende ncy" 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 ObjectDatasourc e's property.
I'm still unclear on how to use this. When I create a new SqlCacheDepende ncy
I either need the command object or database and table name e.g.

SqlCacheDepende ncy dependency = new SqlCacheDepende ncy(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
Sep 5 '06 #5
Thanks for your reply Andrew,

For your further questions:

=============== =============== ===========
The SqlDependency is part of the System.Data.Sql Client namespace so it
would
spoil my 3 tier design to put SqlDependency.S tart(GetConnect ionString()) 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.S tart is required when we use Notification Service and
SqlCacheDepende ncy 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.S tart(...)
in ASP.NET application's application_Sta rt 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.R emove(cacheKeyN ame);
}
=============== =============== ===========

I would suggest you create a wrapper class dedicated for your web
application and use that wrapper calss in ObjectDataSourc e, and in the
ObjectDataSourc e'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.Cach e[key] == null)
{
initializeCache ();
}

return (DataObject)Con text.Cache[key];
}
private void initializeCache ()
{
//get data object from original business class
DataObject obj = BusinessClass.S electMethod();

//get the sqlcommand we need to add cache dependency against
SqlCacheDepende ncy dependency =
new SqlCacheDepende ncy(BusinessCla ss.GetSelectCom mand());
Context.Cache.A dd(Key, obj, dependency, Cache.NoAbsolut eExpiration,
Cache.NoSliding Expiration, CacheItemPriori ty.Normal, null);

}
>>>>>>>>>>>>>>> >>>>>>>>>>


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

SqlCacheDepende ncy dependency = new SqlCacheDepende ncy(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.

Sep 6 '06 #6
Hi Steven
SqlDependency.S tart is required when we use Notification Service and
SqlCacheDepende ncy 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.S tart(...)
in ASP.NET application's application_Sta rt 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.S electMethod();

//get the sqlcommand we need to add cache dependency against
SqlCacheDepende ncy dependency =
new SqlCacheDepende ncy(BusinessCla ss.GetSelectCom mand());
This is the bit I don't understand. I don't have a
'BusinessClass. GetSelectComman d()' 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.A dd(Key, obj, dependency, Cache.NoAbsolut eExpiration,
Cache.NoSliding Expiration, CacheItemPriori ty.Normal, null);

}
Thanks for your help so far.
Andrew
Sep 7 '06 #7
Thanks for your followup Andrew,

For your further questions:

=============== =============
This is the bit I don't understand. I don't have a
'BusinessClass. GetSelectComman d()' 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
ConnectionStrin g 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.Designe r.cs file to determine the class's
name
=============== ===============
public partial class itemsTableAdapt er{

public string ConnectionStrin g
{
get
{
return Connection.Conn ectionString;
}
}

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.Item sDSTableAdapter s.itemsTableAda pter _adapter =
null;
............... ...............

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

return (DataTable)Http Context.Current .Cache[DATA_KEY];

}

protected void InitializeData( )
{

DataTable dt = _adapter.GetDat a();

SqlCacheDepende ncy dependency = new
SqlCacheDepende ncy(_adapter.In ternalAdapter.S electCommand);
Sep 7 '06 #8
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 SqlCacheDepende ncy, the SqlCommand need to be pass into
SqlCacheDepende ncy's constructor(at that time, the query has not bee
executed).

**, we must execute the query (call command.Execute xxxx or
DataAdapter.Fil l.... ) after we create the SqlCacheDepende ncy 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/subscripti...ult.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/subscripti...t/default.aspx.

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

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

Sep 8 '06 #9
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 ObjectDataSourc e 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
Sep 12 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2855
by: stewart | last post by:
I've got the standard SqlCacheDependency working just fine , ie. I've defined (and encrypted) the connectionStrings section in the web.config, and I've also defined an an sqlCacheDependency in the caching section. So, in my code I add an item to the cache with an sqlCacheDependency, referencing the named sqlCacheDependency in the web.config and the database table it is to be based on (have enabled notificiations for that table). Fine. ...
5
2439
by: Adrian Parker | last post by:
I've got the standard SqlCacheDependency working just fine , ie. I've defined (and encrypted) the connectionStrings section in the web.config, and I've also defined an an sqlCacheDependency in the caching section. So, in my code I add an item to the cache with an sqlCacheDependency, referencing the named sqlCacheDependency in the web.config and the database table it is to be based on (have enabled notificiations for that table). Fine. ...
5
2862
by: Joel Barsotti | last post by:
I'm trying to get sql cache dependency working correctly. I've got sql server 2005 for my back end. I've don the "ALTER DATABASE dbName SET broker_enable" command. I made sure the user in my connection string has subscripe notifications permission set to grant. But now it seems that the cache is kicking my product out of the cache
1
1699
by: Yosi | last post by:
Hi, I have a wierd problem about the SqlCacheDependency class. It just works for a couple of minutes. After 2 minutes or more, the cache does not expire anymore. I'm using SQL Server 2005 as database. Here is the code: private string getTime()
2
2067
by: chris | last post by:
Hello all, Background: Using .NET 2.0, Sql Server 2005, SqlCacheDependency is utilized successfully against SqlCommand objects elsewhere in the code (in other words I have successful instances of using SqlCacheDependency). ASP.NET website built in 3 separate tiers: Presentation, Business Logic Layer (BLL), and Data Access Layer (DAL). I have a strongly typed data set that is utilized by my BLL and returns
0
1802
by: Anthony Paul | last post by:
Hello everyone, I'm looking into SQL Server 2005's Notification Services and wasn't able to find a way to create a notification event service based on a DML event such as an Insert or an Update (looking into an alternative to triggers). I see that the SqlCacheDependency is based on some internal event mechanism and I'm wondering whether we can tap into it. Does anyoen have any details on what event or mechanism the SqlCacheDependency is...
4
5836
by: Reinout | last post by:
Hi I set up a sql cache dependency, but always receive a "DependencyChanged" notification straight away (through CacheItemRemoved callback). Modifying the procedure didn't help me. It used to work running on a SQL 2000 database with a SqlDataSource and a few things set in the configuration file. The current setup is using SQL 2005 Express and caching in the datalayer/business objects. Running profiler, I notice events ran by the...
1
3588
by: mark4asp | last post by:
Help: SQLCacheDependency not working. When I step through my code with the debugger I notice that the condition below: (cacheItem == null) is always true. I have setup SQLCacheDependency to work with 11 tables in the database. When I update the database without actually changing the "PensionFund" table then I notice that the Cache is being invalidated
1
2095
by: | last post by:
Hey Guys, I have been struggling with this for few days. when i use SqlCacheDependency sqlDependency=new SqlCacheDependency("test","dbo.USStates"); where test is the name of connectionstring and dbo.USStates is the table
0
8598
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9016
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8887
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8856
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5858
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4613
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2321
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1997
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.