(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

Issue: February 2002
Section: Web Services
Author: Tim Chester

C#	Query an Access Database
Listing 1	The private ExecuteCoursesQuery method contains code that queries a local Access database.   The ExecuteCoursesQuery() method stores the results as a variable in the ASP.NET Application object in XML format. The method also saves a timestamp in an additional variable.

private int ExecuteCoursesQuery() 
{
//private method that queries the Access database
//and puts the results in XML format into your 
//application variables

//set up your connection string for your data 
//source
string strConnectionString = 
	"Provider=Microsoft.Jet.OLEDB.4.0;Data "
	+ "Source=" 
+ Server.MapPath("courses.mdb");
string strSQLStatement = "Select * from Courses "
	+ "where dyear='2001' and term='A'"
	+ " and department='INFO'";

//create a data adapter on your connection and
//sql statement strings
OleDbDataAdapter objDataAdapter = new 
	OleDbDataAdapter(strSQLStatement,
	strConnectionString);

//create a new dataset object
//and set it to the results of your query
DataSet objDataSet = new DataSet();
objDataAdapter.Fill(objDataSet);

//set your application variables to the //XML data
//set a timestamp variable
Application.Set("InfoCoursesCache", 
	objDataSet.GetXml());
Application.Set("InfoCoursesCacheTimestamp", 
	DateTime.Now);
return 1;
}

C#	 Climb the Decision Tree
Listing 2	The GetCourses() Web Service code here represents the business logic expressed in Figure 2.  GetCourses() considers a series of decision points to determine whether cached data exists, and whether it meets the age criteria submitted by the client.

[WebMethod]
public string GetCourses(int 
	intMaximumAgeInSeconds) {

//first you need to determine whether 
//you have cached informationobject objDataCache = Application.Get
	("InfoCoursesCache");
if (objDataCache != null) {
	//You have data in the cache so you must check 
	//to see how old it is. Create a datetime 
	//object and set it to the time saved in the 
	//timestamp application variable.
	DateTime dtSource = Convert.ToDateTime
	(Application.Get("InfoCoursesCacheTimestamp"))
	;


	//Check to see if your data in the //cache is 
	//too old. Add the number of seconds submitted 
	//by client to the timestamp
	dtSource = dtSource.AddSeconds
		(intMaximumAgeInSeconds);
	//compare results to current time to see //if 
	//cache is too old 
	if (DateTime.Compare(DateTime.Now, dtSource) > 
		0) {
		//data in cache is old so you must query 
		//the database, cache the results, and then 
		//return the data
		ExecuteCoursesQuery();
		return "<!-- live data Created at "
			+ Application.Get
			("InfoCoursesCacheTimestamp")
			.ToString() 
		+ "-->" + Application.Get
			("InfoCoursesCache").ToString();
} 
else {
	//age of data is within the number of seconds 
	//submitted by the user, so send back the data 
	//from the cache
	return "<!-- Data from Cache, Cache Created "
		"at "
	+ Application.Get
		("InfoCoursesCacheTimestamp").ToString() 
	+ "-->" + Application.Get
		("InfoCoursesCache").ToString();
}
}

else {
	//no data in cache so you must query the 
	//database, cache the results, and then return 
	//the data
	ExecuteCoursesQuery();
	return "<!-- live data Created at "
	+ Application.Get
		("InfoCoursesCacheTimestamp").ToString() 
	+ "-->" + Application.Get
		("InfoCoursesCache").ToString();
}
}
