/// <summary>
/// Counts the number of rows in a database table
/// A faster alternative to COUNT(*) based on index statistics for the table's primary key
/// Statistics are recomputed if the age of last statistics is larger than <paramref name="maxPeriod"/>
/// </summary>
/// <param name="tableName">Name of database table</param>
/// <param name="connectionString">Connection string</param>
/// <param name="maxPeriod">Maximum age of statistics</param>
/// <returns>The number of rows, or null if no statistics are defined on the database table</returns>
public static long?CountRowsByStatistics(string tableName, string connectionString, TimeSpan maxPeriod)
{
using (var dataContext = new StatisticsDataContext(connectionString))
{
DateTime?lastUpdated = DateTime.MinValue;
var statObj = GetPrimaryKeyStat(dataContext, tableName);
if (statObj != null)
{
lastUpdated = statObj.GetStatsDate(dataContext);
if (!lastUpdated.HasValue || lastUpdated + maxPeriod < DateTime.Now)
{
statObj.UpdateStatistics(dataContext);
}
var ds = statObj.GetStatistics(connectionString);
return((long)ds.Tables[0].Rows[0]["Rows"]);
}
else
{
return(null);
}
}
}
/// <summary>
/// Loads the stats object belonging to the table's primary key
/// </summary>
/// <param name="dataContext">Database context used to run</param>
/// <param name="tableName">Name of database table</param>
/// <returns>The found object</returns>
public static stat GetPrimaryKeyStat(StatisticsDataContext dataContext, string tableName)
{
return((from s in dataContext.stats
where
[email protected] == tableName &&
s.auto_created.HasValue &&
s.auto_created.Value == false
orderby s.stats_id
select s
)
.FirstOrDefault());
}
