public List <TableSizeInfo> GetTableRowCount()
{
List <TableSizeInfo> list = new List <TableSizeInfo>();
string sql = "select t.name, i.[rows] from sys.sysindexes i inner join sys.tables t on t.object_id = i.id where i.indid in (0, 1, 255) order by t.name";
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
scsb.DataSource = SqlServer;
scsb.InitialCatalog = Name;
scsb.IntegratedSecurity = IntegratedSecurity;
scsb.UserID = UserName;
scsb.Password = Password;
using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))
{
conn.Open();
using (SqlCommand cmnd = new SqlCommand(sql, conn))
{
cmnd.CommandType = CommandType.Text;
using (SqlDataReader r = cmnd.ExecuteReader())
{
while (r.Read())
{
TableSizeInfo tableSizeInfo = new TableSizeInfo();
tableSizeInfo.Name = r[0].ToString();
tableSizeInfo.Rows = long.Parse(r[1].ToString());
list.Add(tableSizeInfo);
}
}
}
}
return(list);
}
private void RefreshList()
{
lvwDatabases.Items.Clear();
lvwDatabases.Groups.Clear();
if (TableSizeConfig != null)
{
foreach (DatabaseEntry db in TableSizeConfig.DatabaseEntries)
{
List <TableSizeInfo> tableSizes;
try
{
tableSizes = db.GetTableRowCount();
}
catch { tableSizes = new List <TableSizeInfo>(); }
ListViewGroup group = new ListViewGroup(db.ToString(), db.ToString());
lvwDatabases.Groups.Add(group);
foreach (TableSizeEntry tableEntry in (from t in db.TableSizeEntries
orderby t.TableName
select t))
{
TableSizeInfo tableSizeInfo = (from ti in tableSizes
where ti.Name == tableEntry.TableName
select ti).FirstOrDefault();
ListViewItem lvi = new ListViewItem(tableEntry.TableName);
lvi.Group = group;
if (tableSizeInfo == null)
{
lvi.SubItems.Add("Table not found!");
lvi.ImageIndex = 2;
}
else
{
lvi.SubItems.Add(tableSizeInfo.Rows.ToString());
if (tableSizeInfo.Rows >= tableEntry.ErrorValue)
{
lvi.ImageIndex = 2;
}
else if (tableSizeInfo.Rows >= tableEntry.WarningValue)
{
lvi.ImageIndex = 1;
}
else
{
lvi.ImageIndex = 0;
}
}
lvwDatabases.Items.Add(lvi);
}
}
toolStripStatusLabel1.Text = "Last updated " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
}
public override MonitorStates GetState()
{
MonitorStates returnState = MonitorStates.Good;
StringBuilder plainTextDetails = new StringBuilder();
StringBuilder htmlTextTextDetails = new StringBuilder();
LastDetailMsg.PlainText = "Querying table sizes";
LastDetailMsg.HtmlText = "";
int errors = 0;
int warnings = 0;
double totalValue = 0;
try
{
plainTextDetails.AppendLine(string.Format("Querying {0} databases", TableSizeConfig.DatabaseEntries.Count));
htmlTextTextDetails.AppendLine(string.Format("<b>Querying {0} databases</b>", TableSizeConfig.DatabaseEntries.Count));
htmlTextTextDetails.AppendLine("<blockquote>");
foreach (DatabaseEntry databaseSizeEntry in TableSizeConfig.DatabaseEntries)
{
try
{
plainTextDetails.AppendLine(string.Format("\tServer\\Database: {0}\\{1}", databaseSizeEntry.SqlServer, databaseSizeEntry.Name));
htmlTextTextDetails.AppendLine(string.Format("<b>Server\\Database: {0}\\{1}</b>", databaseSizeEntry.SqlServer, databaseSizeEntry.Name));
List <TableSizeInfo> tableSizes = databaseSizeEntry.GetTableRowCount();
htmlTextTextDetails.AppendLine("<ul>");
foreach (TableSizeEntry tableEntry in databaseSizeEntry.TableSizeEntries)
{
plainTextDetails.Append(string.Format("\t\t{0} - ", tableEntry.TableName));
htmlTextTextDetails.Append(string.Format("<li>{0} - ", tableEntry.TableName));
TableSizeInfo tableSizeInfo = (from ti in tableSizes
where ti.Name == tableEntry.TableName
select ti).FirstOrDefault();
if (tableSizeInfo == null)
{
errors++;
plainTextDetails.Append("Table not found!");
htmlTextTextDetails.Append("Table not found!");
}
else if (tableSizeInfo.Rows >= tableEntry.ErrorValue)
{
totalValue += tableSizeInfo.Rows;
errors++;
plainTextDetails.Append(string.Format("{0} - Error (trigger {1})", tableSizeInfo.Rows, tableEntry.ErrorValue));
htmlTextTextDetails.Append(string.Format("{0} - <b>Error</b> (trigger {1})", tableSizeInfo.Rows, tableEntry.ErrorValue));
}
else if (tableSizeInfo.Rows >= tableEntry.WarningValue)
{
totalValue += tableSizeInfo.Rows;
warnings++;
plainTextDetails.Append(string.Format("{0} - Warning (trigger {1})", tableSizeInfo.Rows, tableEntry.WarningValue));
htmlTextTextDetails.Append(string.Format("{0} - <b>Warning</b> (trigger {1})", tableSizeInfo.Rows, tableEntry.WarningValue));
}
else
{
totalValue += tableSizeInfo.Rows;
plainTextDetails.Append(string.Format("{0}", tableSizeInfo.Rows));
htmlTextTextDetails.Append(string.Format("{0}", tableSizeInfo.Rows));
}
plainTextDetails.AppendLine();
htmlTextTextDetails.AppendLine("</li>");
}
}
catch (Exception innerEx)
{
errors++;
plainTextDetails.AppendLine(string.Format(" - {0}", innerEx.Message));
htmlTextTextDetails.AppendLine(string.Format(" - {0}", innerEx.Message));
}
htmlTextTextDetails.AppendLine("</ul>");
}
htmlTextTextDetails.AppendLine("</blockquote>");
if (errors > 0 && warnings == 0) //Are all errors
{
returnState = MonitorStates.Error;
}
else if (errors > 0 || warnings > 0) //any errors or warnings
{
returnState = MonitorStates.Warning;
}
LastDetailMsg.PlainText = plainTextDetails.ToString().TrimEnd('\r', '\n');
LastDetailMsg.HtmlText = htmlTextTextDetails.ToString();
LastDetailMsg.LastValue = totalValue;
}
catch (Exception ex)
{
LastError = 1;
LastErrorMsg = ex.Message;
LastDetailMsg.PlainText = string.Format("Last step: '{0}\r\n{1}", LastDetailMsg.PlainText, ex.Message);
LastDetailMsg.HtmlText = string.Format("<blockquote>Last step: '{0}<br />{1}</blockquote>", LastDetailMsg.PlainText, ex.Message);
returnState = MonitorStates.Error;
}
return(returnState);
}