/// <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()); }