A Silverlight Dashboard to handle long queries
When you employ Silverlight to displays dashboards, you may encounter problems with long running queries timing out. This sample module demonstrates a method to generate the data needed for a dashboard using multiple queries. One query is used to display the current users, and two queries are used to display the top 10 users.
This module uses Dave Black's Silverlight Odometer and the Bar Chart from the Silverlight Toolkit.
The Module
When you install the module and place it on a page in your DNN site, you may see a message indicating that the Site Log must be enabled to use the module. Clicking Enable Site Log will enable the Site Log. If your Site Log is already enabled, you will not see this message.
You will see:
Current Visitors - looks at the last 20 minutes of the Site Log and counts each unique IP address.
Top 10 Users in the past hour - A count of the entries in the Site Log for each of the top 10 visitors in the past hour.
In the Settings for the module (go to Settings then scroll to the bottom of the page) you will see an option to change the Dashboard Key (each portal gets it's own key) and the Dashboard Refresh Time. The Dashboard Refresh Time controls how long the results for the data queries are cached and how often the Silverlight application will query the website for the latest data.
One Dashboard Multiple Queries
The Silverlight Dashboard 2 module exposes 3 web services:
GetDashboardTopAnonymousUsers - Gets a count of the items in the Site Log for anonymous users for the past hour.
GetDashboardTopUsers - Gets a count of the items in the Site Log for the top 9 users for the past hour.
GetDashboardUserCount - Gets a count of the unique IP addresses for the past 20 minutes.
The results from all three of the web service queries are compiled and displayed on one Silverlight dashboard. The GetDashboardUserCount (in the ShowUserCount method) and GetDashboardTopUsers (in the ShowTopUsers method) are executed asynchronously by the RefreshIteration method in the Silverlight application. It is called at a frequency that is indicated by the Dashboard Refresh Time setting in the module Settings:
#region RefreshIteration
private void RefreshIteration(object sender, EventArgs e)
{
BasicHttpBinding bind = new BasicHttpBinding();
EndpointAddress MyEndpointAddress = new EndpointAddress(strWebServiceBase "Webservice.asmx");
var proxy = new WebServiceSoapClient(bind, MyEndpointAddress);
ShowUserCount(proxy);
ShowTopUsers(proxy);
}
#endregion
When the method called by ShowTopUsers completes (that method is called proxy_GetDashboardTopUsersCompleted), the GetDashboardTopAnonymousUsers web service is called to get the anonymous users:
#region proxy_GetDashboardTopUsersCompleted
void proxy_GetDashboardTopUsersCompleted(object sender, GetDashboardTopUsersCompletedEventArgs e)
{
// Put the results in the collection
colDashboardData = e.Result;
// Get the anonymous requests
BasicHttpBinding bind = new BasicHttpBinding();
EndpointAddress MyEndpointAddress = new EndpointAddress(strWebServiceBase "Webservice.asmx");
var proxy = new WebServiceSoapClient(bind, MyEndpointAddress);
proxy.GetDashboardTopAnonymousUsersCompleted =
new EventHandler(proxy_GetDashboardTopAnonymousUsersCompleted);
proxy.GetDashboardTopAnonymousUsersAsync(strPassword);
}
#endregion
When that web service call is complete, the result (e.Result[0]) is added to the collection of top users (colDashboardData) and the final result is displayed in the Bar Chart:
#region proxy_GetDashboardTopAnonymousUsersCompleted
void proxy_GetDashboardTopAnonymousUsersCompleted(object sender, GetDashboardTopAnonymousUsersCompletedEventArgs e)
{
// Add the results to the collection
colDashboardData.Add(e.Result[0]);
((BarSeries)TopUsers.Series[0]).ItemsSource = colDashboardData;
}
#endregion
If the query was still timing out, the processing could be broken up into smaller and smaller chunks. Perhaps only the last users could be retrieved and then the Site Log entry counts could be retrieved for each user one by one. The Silverlight application will never time. If a single query does time out the Silverlight application can be programmed to try the request again.
Caching the Data
The other concern with long running queries is that they tie up server resources. You will want to cache the data so that the web server saves the last values in memory for a period of time determined by the Dashboard Refresh Time setting in the module settings. The web server will use these values when available, rather than performing a possibly expensive query on the database server.
The following code shows the GetDashboardUserCount method that implements caching:
#region GetDashboardUserCount
[WebMethod]
public int GetDashboardUserCount(string strPassword)
{
int intDashboardUserCount = 0;
string CacheDashboardUserCount = "";
DashboardPortalInfo objDashboardPortalInfo = GetDashboardPortalInfo(strPassword);
// Password is valid only if PortalID is not -1
if (objDashboardPortalInfo.PortalID > -1)
{
// Try to get the value out of the Cache
// The cache key is preceeded by the PortalID
CacheDashboardUserCount = HttpContext.Current.Cache.Get(String.Format("{0}{1}",
objDashboardPortalInfo.PortalID.ToString(), "AdefWebserver_DashboardUserCount")) as String;
if (CacheDashboardUserCount == null)
{
// We only want the data for the 20 minutes
DateTime dtLastRefreshTime = DateTime.Now.AddMinutes(-20);
// The DashboardUserCount does not exist or has expired
// Get the current value
DashboardDALDataContext DashboardDALDataContext = new DashboardDALDataContext();
var SiteLogsResults = (from SiteLogData in DashboardDALDataContext.SiteLogs
where SiteLogData.DateTime >= dtLastRefreshTime
where SiteLogData.PortalId == objDashboardPortalInfo.PortalID
group SiteLogData by SiteLogData.UserHostAddress into UserHostAddress
select UserHostAddress).Count();
intDashboardUserCount = SiteLogsResults;
// Set the new time to expire and insert into Cache
DateTime dtTimeToExpire = DateTime.Now.AddSeconds(Convert.ToDouble(objDashboardPortalInfo.RefreshTime));
HttpContext.Current.Cache.Insert(String.Format("{0}{1}", objDashboardPortalInfo.PortalID.ToString(),
"AdefWebserver_DashboardUserCount"), intDashboardUserCount.ToString(), null, dtTimeToExpire,
Cache.NoSlidingExpiration);
}
else
{
// The value is in the Cache
intDashboardUserCount = Convert.ToInt32(CacheDashboardUserCount);
}
}
return intDashboardUserCount;
}
#endregion
Summary
When you employ Silverlight to displays dashboards, you may encounter problems with long running queries timing out. Creating a query for each item separately can alleviate time out problems because each query can be run asynchronously. You can even perform more than one query for a single item if needed.
Silverlight provides a unique solution for displaying data based on long running queries. The Silverlight application itself will never time out. If a single query does time out, the Silverlight application can be programmed to try the request again.