Exemple #1
0
        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);
        }
Exemple #2
0
        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");
            }
        }
Exemple #3
0
        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);
        }