Thursday, October 21, 2004

ADO.NET notes & Cache

1. Populating a DataSet 1.

- Prepare the SqlCommand (SQL Server)
- Relate the SqlCommand to an SqlConnection with its connection string
- Set SqlCommand's CommandText (better use SP)

- Prepare SqlDataAdapter
- Relate SqlDataAdapter to an SqlCommand

- Prepare the DataSet
- Open the SqlCommand connection
- Fill the DataSet using the Fill() method of the SqlDataAdapter

- Close the SqlCommand connection
- Dispose SqlDataAdapter's SqlCommand
- Dispose SqlDataAdapter

2. Use DataView to bind a set of DataRow objects to a server control.

Why not use the Select method of the DataTable object directly?
The problem with the Select method is that it returns an arraw or DataRow
objects. It is not easy to bind this array to a server control because the
System.Array class doesn't implement the IListSource and IList interfaces.

3. Keep less-dynamic and often-used data in cache.

It keeps the database server machine and network available for other
important processes. Architecturally, you will have to accept a slight
degredation in performance for higher availability of these critical

4. Refreshing Cached Data.

ASP.NET framework doesn't provide an elaborate mechanism for
refreshing cached data if it is changed in the database.

ASP.NET, having a an elegant mechanism for receiving notification if
the data is stored in a file instead of in the database, it is recommended
to consider keeping your cache candidate data in an XML file.

Exporting data from the database into an XML file is fairly easy.
Create a DataSet by running one or many SQL statements. Once you
have filled all needed data tables, make sure to create appropriate
relationships. This will help greatly when you attempt to filter the
information contained in the dataset. Then simply call WriteXML
method to save its content to an XML file.

It is easier to convert an XML file to a dataset if the file was
originated from a DataSet object. Write the schema to the file as well.

MyFastDataSet.WriteXML(XmlFileName, XmlWriteMode.WriteSchema);

Without saving the schema, we lose relationships between tables, which
can cost several hours of debugging to discover the real cause of the problem.

Important, update the cache correctly.
Some of you might be thinking, what's so hard about updating cache? You
simply get the dataset from cache and call its ReadXML method.

Caution. Never ever directly update the cached DataSet object from the
XML file.

When you are ready to refresh cache, make sure you create a new DataSet
object and populate it by calling ReadXML method. Once the new DataSet
is populated, throw away the current DataSet object that is residing in cache
and insert the newly created DataSet object in its place.

The reason we shouldn't use the currently cached DataSet object to refresh
data is that the ReadXML method can take up to several minutes if the XML
file is large. Of course, we don't want to affect users who are innocently surfing
your website by updating their data as they are using it.

Populating a new DataSEt object from an XML file doesn't affect website users.
You should still be careful while overwriting cache with the new object.
Even though it only takes a fraction of a second to place a new DataSet
object in cache, it is best to synchronize access to the Cache object while
performing this operation.

//An Example of Refreshing Cache Appropriately

string XmlFileName = request.MapPath("CacheFile/MyFastDataSet.xml");
DataSet MyFastDataSet = new DataSet();

//Populating newly created DataSet object from XML file.
//Make sure to use ReadSchema enumeration; otherwise,
//the DataSet object will not have data types and relations.

MyFastDataSet.ReadXML(XmlFileName, XmlReadMode.ReadSchema);

//Synchronize access to the Cache object by using the lock keyword.
//The lock keyword makes sure that no other thread can access the
//Cache object while it's being updated with new DataSet object.
lock (Cache)
Cache["Result"] = MyFastDataSet;

5. Expiring Cache.

The caching mechanism provides flexibility of expiring cached data
by using a variety of methods.

If XML is used to load a dataset in memory and keep it cached, it is
recommended to set the dependency of the cached data with that file.

By using this mechanism, the code can receive notification when the
file is changed. Once a notification is received, you can read this updated
file, create a new DataSet object, and replace the cached object with it.

This approach allows you to refresh the data simply by updating the
underlying XML file and to let the ASP.NET runtime and its caching
mechanism do the rest.

//Setting Up Dependency with a File, Receive Expiration Notification,
//and Set Various Expiration Options.

//Make sure to include System.Web.Caching namespace
private void AddToCache()
string XmlFileName = Request.MapPath("CacheFile/MyFastDataSet.xml");
DataSet MyFastDataSet = new DataSet();

//Populating newly created DataSet object from XML file.
MyFastDataSet.ReadXml(XmlFileName, XmlReadMode.ReadSchema);

CacheDependency MyDependency;
CacheItemRemovedCallback onRemove;

//Setting the dependency object to the XML file.
MyDependency = new CacheDependency(XmlFileName);

//Creating the delegate object and assigning it the
//name of the method that should be called when cached
//dataset is expired.
onRemove = new CacheItemRemovedCallBack(RemoveResultCallback);

//Inserting the newly created DataSet object in cache
//and assigning it the dependency, the delegate, and expiration values.
//In this example, the cached dataset will expire 24 hourse after it is placed
//in cache.
Cache.Insert("Result", MyFastDataSet, MyDependency,
DateTime.Now.AddHours(24), TimeSpan.Zero, CacheItemPriority.Normal,


//This method will be called when the cached data is expunged.
//It receives the expired object, its key, and the reason for
//expiration as specified in CacheItemRemovedCallback delegate.
private void RemoveResultCallback(string key, object removedObject,
CacheItemRemovedReason removeReason)
// We simply call the AddToCache() method to reread the
//XML file and refresh cached data set.