This was a neat trick.  When working with UAT and STAGE and DEV and however many other environments, it can sometimes be confusing which database your particular web server is actually hooked up to.  Here I set up an HttpHandler to write out a string as an image memory stream, and then with some CSS trickery it shows up repeating with low opacity all over each page, faint enough that it doesn’t bother you, but enough so that you won’t ever mistake yourself for being in a different environment.

First in the BasePage PreRender I check for conditional, in case, for instance, you don’t want to use this on Production:

protected override void OnPreRender(EventArgs e)
        {
 //todo: we could make this a webresource instead of static img
            Image img = new Image();
 try
            {
 string prod = System.Configuration.ConfigurationSettings.AppSettings["dontShowHeaderForThisDatabase"];
 if (!LIB.Gen_Util.getDBName().ToUpper().Contains(prod.ToUpper()))
                {
                    img.ImageUrl = "DBImage.ashx";
                    img.Style.Add("width", "100%");
                    img.Style.Add("height", "100%");
                    img.Style.Add("z-index", "-1");
                    img.Style.Add("position", "absolute");
                    img.Style.Add("top", "20px");
 // this is a pain- if we have <% %> tags in page then this will break
 //this.Form.Controls.Add(img);
 this.Page.Controls.Add(img);
                }
 base.OnPreRender(e);
            }
 

DBImage.ashx is created once then cached in the HttpHandler:

public class HttpHandler :IHttpHandler
    {
        #region IHttpHandler Members
public bool IsReusable
        {
 get { return false; }
        }
public void ProcessRequest(HttpContext context)
        {
 try
            {
 byte[] ba;
 if (HttpContext.Current.Cache["dbimage"] == null)
                {
                    ba = Gen_Util.CreateHeaderImage(Gen_Util.getDBName());
 if (ba != null)
                    {
                        HttpContext.Current.Cache["dbimage"] = ba;
                    }
                }
 else
                {
                    ba = (byte[])HttpContext.Current.Cache["dbimage"];
                }
 if (ba != null)
                {
                    context.Response.BinaryWrite(ba);
                }
                context.Response.End();
            } 
        }
        #endregion
    }

It will get called for each Request, with this line in the web.config:

<httpHandlers>
      ...
<add verb="GET" path="DBImage.ashx" type="CEG.CPS.Settlements.LIB.HttpHandler" />

And the CreateHeaderImage is the tricky CSS part:

public static byte[] CreateHeaderImage(string text)
        { 
 try
            {
                Bitmap bm = new Bitmap(320, 240, PixelFormat.Format32bppArgb);
                Graphics g = Graphics.FromImage(bm);
                g.SmoothingMode = SmoothingMode.HighQuality;// ?
                g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.ClearTypeGridFit;// ?
                g.Clear(Color.White);
                GraphicsPath p = new GraphicsPath();
                Font f = new Font("Impact", 20F);
                Rectangle r = new Rectangle(0, 0, 320, 240);
                StringFormat sf = new StringFormat();
                String repeatedText = string.Empty;
 for (int x = 0; x < 48; x++)  // 8 rows of 6
                { 
 if (x % 6 == 0 && x != 0)
                    {
                        repeatedText += "\n";
                    } repeatedText += text + "";
                } 
                p.AddString(repeatedText, f.FontFamily, (int)f.Style, f.Size, r, sf);
 
 // transparency shade 75
                SolidBrush b = new SolidBrush(Color.FromArgb(75,Color.Gray));
 
                g.FillPath(b, p);
 
                f.Dispose();
                b.Dispose();
                g.Dispose();
                MemoryStream ms = new MemoryStream();
                bm.Save(ms, ImageFormat.Bmp);
                bm.Dispose();
 return ms.GetBuffer();
            }
    }

And that’s it.

The main part of this feature was to show a grid of some data, different database uptimes and other performance metrics, on a web page.  The interesting part was that the databases that were tracked changed often.  Sometimes there were 12, and then one would get killed, and two more were created, and the next week another new one would be built. Not only would it be better to put the datasources in a config of some sort, but it would be even better if a manager could edit that config through an easy-to-use UI on a web page. 

Rather than build two separate pages, I saw that there were some re-useable components in this use case, and so I created a Server control, with an XML config.  Most of the XML I/O I put in an Abstract class, and then from that I inherited and Admin Control and a Display Control.

First the Common Elements:

interface IGrid
    { 
        DataTable getDataSource();
void createColumns(DataTable aTable);
void styleGrid();
    }
public abstract class AGrid : GridView, IGrid
    {
public string xFile{get; set;}
public string headerBackColor{get; set;}
public string gridBackColor {get;set;} 
public string title { get; set; } 
protected DataTable myTable;
protected override void OnInit(EventArgs e)
        {
 try
            {
 base.OnInit(e);
 this.AutoGenerateColumns = false;
                myTable = getDataSource();
 if (Page.IsPostBack)
                {
 this.DataSource = myTable;
 if (this.EditIndex > -1 )
                    {
 this.DataBind();
                    }
                }
            } 
        }
protected override void OnLoad(EventArgs e)
        { 
 base.OnLoad(e);
 if (!Page.IsPostBack)
                {
 if (myTable != null)
                    {
                        createColumns(myTable);
 this.DataSource = myTable;
 this.DataBind();
                        styleGrid();
                    }
                } 
        } 
public DataTable getDataSource()
        { 
            <snip>
        }
public virtual void createColumns(DataTable myTable){
 try
            { <snip>
        }
public void styleGrid()
        {
 try
            {
 if (this.gridBackColor != String.Empty)
                {
 this.BackColor = System.Drawing.Color.FromName(this.gridBackColor);
                }
 else this.BackColor = System.Drawing.Color.Silver;
 this.BorderStyle = BorderStyle.Ridge;
 this.Font.Size = 10;
 this.Font.Name = "Verdana";
 this.Font.Bold = true;
 
 this.GridLines = GridLines.Horizontal;
 this.Style.Add("font-variant", "small-caps");
 this.Style.Add("text-align", "right");
 this.CellPadding = 2;
 if (this.headerBackColor != String.Empty)
                {
 this.HeaderStyle.BackColor = System.Drawing.Color.FromName(this.headerBackColor);
                }
 else this.HeaderStyle.BackColor = System.Drawing.Color.MidnightBlue;
 this.HeaderStyle.ForeColor = System.Drawing.Color.White;
 this.HeaderStyle.Font.Size = 12;
 if (this.title != String.Empty)
                {
 this.Caption = this.title;
                }
 else this.Caption = "Grid Monitor 1.0";
 
 this.CaptionAlign = TableCaptionAlign.Top;
 this.BorderWidth = 1;
            }
 catch (NullReferenceException e)
            {
                Console.WriteLine("Probably xml issue: " + e.ToString());
            }
        }

The Admin Grid was just a simple DataGrid and used the built-in OnDataBound to add editting controls and commands, and the OnRowEditing events to Add/Edit/Remove nodes in the XML file.

    [DefaultProperty("Text")]
    [ToolboxData("<{0}:AdminGrid runat=server></{0}:AdminGrid>")]
public class AdminGrid : AGrid
    {
        [Bindable(true)]
        [Category("Appearance")]
        [DefaultValue("")]
        [Localizable(true)]
 
protected override void OnInit(EventArgs e)
        {
 this.AutoGenerateEditButton = true;
 this.ShowFooter = true;
 
 base.OnInit(e);
        } 
protected override void OnPagePreLoad(object sender, EventArgs e)
        {
 base.OnPagePreLoad(sender, e);
 if (Page.IsPostBack && this.EditIndex == -1)
            {
 this.DataBind();
            }
        } 
protected override void OnDataBound(EventArgs e)
        {
 base.OnDataBound(e);
 foreach (DataControlFieldCell cell in this.FooterRow.Cells)
            {
 if (cell.ContainingField.GetType().Equals(typeof(System.Web.UI.WebControls.BoundField)))
                {
                    TextBox myC = new TextBox();
                    cell.Controls.Add(myC);
                }
 else if (cell.ContainingField.GetType().Equals(typeof(System.Web.UI.WebControls.CheckBoxField)))
                {
                    CheckBox myC = new CheckBox();
                    cell.Controls.Add(myC);
                }
 else if (cell.ContainingField.GetType().Equals(typeof(System.Web.UI.WebControls.CommandField)))
                {
                    LinkButton myC = new LinkButton();
                    myC.Text = "Add New";
                    myC.ID = "btnAddNew";
                    myC.CommandName = "New";
                    myC.CommandArgument = "New";
                    cell.Controls.Add(myC);
                }
            }
protected override void OnRowCommand(GridViewCommandEventArgs e)
        {
 try
            {
 base.OnRowCommand(e);
 if (e.CommandName == "New")
                {
                    DataRow newRow = myTable.NewRow();
 //insert
 for (int x = 0; x < myTable.Columns.Count; x++)
                    {
                        Control myControl = this.FooterRow.Cells[x + 1].Controls[0];
 if (myControl.GetType().Equals(typeof(CheckBox)))
                        {
                            newRow[x] = ((CheckBox)myControl).Checked;
                        }
 else if (myControl.GetType().Equals(typeof(TextBox)))
                        {
                            newRow[x] = ((TextBox)myControl).Text;
                        }
                    }
                    myTable.Rows.Add(newRow);
                    WriteXml(myTable, HttpContext.Current.Server.MapPath(xFile));
 this.DataSource = myTable;
 this.DataBind();
                }
            }
protected override void OnRowUpdating(GridViewUpdateEventArgs e)
        { 
 //DataTable myTable = (DataTable)HttpContext.Current.Session["myTable"];
            DataTable oldTable = (DataTable)this.DataSource;
            GridViewRow myRow = this.Rows[e.RowIndex];
 for (int x = 0; x < myRow.Cells.Count; x++)
            {
                Control myControl = myRow.Cells[x].Controls[0];
 
 if (myControl.GetType().Equals(typeof(CheckBox)))
                {
                    oldTable.Rows[e.RowIndex][x - 1] = ((CheckBox)myControl).Checked;
 //myTable.Rows[e.RowIndex][]
                }
 else if (myControl.GetType().Equals(typeof(TextBox)))
                {
                    oldTable.Rows[e.RowIndex][x - 1] = ((TextBox)myControl).Text;
                }
                    WriteXml(myTable, HttpContext.Current.Server.MapPath(xFile));
 this.DataSource = myTable;
 this.DataBind();
                }
            }
        }

The DisplayGrid has some neat UI components

[assembly: WebResource("DBMonitor.Resources.button_red.png","image/png")]
[assembly: WebResource("DBMonitor.Resources.button_green.png", "image/png")]
[assembly: WebResource("DBMonitor.Resources.button_yellow.png", "image/png")]
namespace DBMonitor
{
    [DefaultProperty("Text")]
    [ToolboxData("<{0}:ServerControl1 runat=server></{0}:ServerControl1>")]
public class DisplayGrid : AGrid
    {
        [Bindable(true)]
        [Category("Appearance")]
        [DefaultValue("")]
        [Localizable(true)]
public string Text <snip>
protected override void OnInit(EventArgs e)
        { 
 this.title = "Database Environments";
 this.headerBackColor = "MidnightBlue";
 this.gridBackColor = "Silver";
 this.xFile = "Config/dbmon.xml";
 base.OnInit(e);
        }
protected override void OnLoad(EventArgs e)
        {
 base.OnLoad(e);
            DataTable myTable = this.DataSource as DataTable;
 if (myTable.Columns.IndexOf("Active") >= 0)
            {
                myTable.DefaultView.RowFilter = "Active = true";
 this.DataBind();
            }
        }
protected override void OnRowDataBound(GridViewRowEventArgs e)
        {
 base.OnRowDataBound(e);
 try
            {
 //find BE Last Refreshed value
 int x = ((DataTable)this.DataSource).Columns.IndexOf("BE_Last_Run");
 if (x >= 0 && e.Row.RowType != DataControlRowType.Header)
                {
                    System.Web.UI.WebControls.Image myLight = new System.Web.UI.WebControls.Image();
 
                    DateTime lastBE = new DateTime();
 if (e.Row.Cells[x].Text.Contains(':'))
                    {
 try
                        {
                            lastBE = DateTime.Parse(e.Row.Cells[x].Text);
                        }
 catch (FormatException ex)
                        {
                            lastBE = DateTime.MinValue;
 
                        }
                        e.Row.Cells[x].Text = lastBE.ToShortTimeString();
                        TimeSpan myAge = DateTime.Now - lastBE;
 if (ConfigurationSettings.AppSettings["debug"] == "true")
                        {
                            logThis("myAge: " + myAge.ToString() + " and now is " + DateTime.Now.ToString() +
                                " and lastBE is " + lastBE.ToString() +
                                " and timespan.fromhours(1) is " +
                            TimeSpan.FromHours(1).ToString() +
                            " and now minus lastBE is " + myAge, EventLogEntryType.Information);
                        }
 if (myAge > TimeSpan.FromHours(1))
                        {
                            myLight.ImageUrl = this.Page.ClientScript.GetWebResourceUrl(this.GetType(),
                                    "DBMonitor.Resources.button_red.png");
                        }
 else if (myAge > TimeSpan.FromMinutes(10))
                        {
                            myLight.ImageUrl = this.Page.ClientScript.GetWebResourceUrl(this.GetType(),
                                "DBMonitor.Resources.button_yellow.png");
                        }
 else
                        {
                            myLight.ImageUrl = this.Page.ClientScript.GetWebResourceUrl(this.GetType(),
                                "DBMonitor.Resources.button_green.png");
                        }
                    }
 else
                    {
                        myLight.ImageUrl = this.Page.ClientScript.GetWebResourceUrl(this.GetType(),
                                    "DBMonitor.Resources.button_red.png");
                    }
                    e.Row.Cells[0].Controls.Add(myLight);
                    e.Row.Cells[0].BackColor = Color.White;
                }
            }

Adding it to a page is just a couple lines then- here the first line registers the namespace, and the last selected line places it in a div.  In this case I populated my attributes in the DisplayGrid class, but if I was to use this in several other places, I could remove those and populate my attributes here in the html element.

<%@ Register Assembly="DBMonitor" Namespace="DBMonitor" TagPrefix="dbm"  %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Status Page</title>
<link href="StyleSheet.css" rel="stylesheet" type="text/css" />
<link href="mobile.css" rel="stylesheet" type="text/css" media="handheld"/>
<script src="stock-ticker.js" type="text/javascript"></script>
<meta http-equiv=Refresh content="120" />
<script type="text/javascript">
        var ticker;
        function loadTicker(){
            ticker = new Ticker('ticker','myTicker',4,15);
            ticker.start();
 
        }
</script>
</head>
<body onload="loadTicker();">
<form id="form1" runat="server">
<table style="table-layout:fixed"><tr><td style="width:200px;overflow:hidden">
<div id="myTicker" class="ticker" style="width:5000px" nowrap="nowrap">
 <asp:Xml DocumentSource="~/Config/ticker.xml" runat="server" ID="ticker" TransformSource="~/ticker.xslt"></asp:Xml>
</div></td></tr></table>
<div id="newEnv">
<dbm:DisplayGrid ID="something" runat="server"></dbm:DisplayGrid>
</div>

And that’s it.

I had written a reporting page in asp.Net that queried the MySql database of our issue tracking system.  Users in different teams could use the page to create reports on the issues created, resolved, and worked on by their team as well as filtering by any number of other attributes. 

The page in itself was neat, and necessary, as the issue tracking system had grown at a pace of 50 new issues weekly for a couple of years.  But it was neater to build a way for different users to save their more complicated report filters, and even neater to allow them to send them an automatic email containing their report on any daily or weekly schedule.  Although it sounded like a lot of work at first, it turned out to be easy to set up.

The first part was to only show each user their own saved reports, and allow them to edit and save again, without affecting or viewing anybody else’s.  Because we were in an intranet environment, this was easy enough, using Windows as our authentication scheme meant I always already had the current user, and I could then save the reports in a table with their Id, and only show links to reports that they owned. 

The report page had over twenty filters and group by’s and sorts so saving all of the settings individually was not the way to go.  Instead I would take the output of that page, the actual query, generated dynamically, and save that.  Then all I had to do was get the query, saved in the database, and run it.  The code that created the query can be a great exercise for refactoring, and it is incredibly long, but you can imagine it was quick to write initially, and I only ever had to add one or two features to it …

using (MySqlDataAdapter myAdp = new MySqlDataAdapter())
        {
            myCmdQuery.Append("select distinct iss_id  Issue_ID ");
 if (project) { myCmdQuery.Append(",prj_title Project "); };
 if (category) { myCmdQuery.Append(",prc_title Category "); };
 if (summary) { myCmdQuery.Append(",iss_summary  Summary "); };
 if (assignee)
            {
                myCmdQuery.Append(", (select group_concat(usr_full_name order by usr_full_name separator ', ') " +
                    " from eventum_user join eventum_issue_user " +
                    " on isu_usr_id = usr_id where isu_iss_id = iss_id) Assignees ");
            };
 if (status) { myCmdQuery.Append(",sta_title Status "); };
 if (priority) { myCmdQuery.Append(",pri_title Priority "); };
 if (createDate) { myCmdQuery.Append(",iss_created_date Created_Date "); };
 if (createAge) { myCmdQuery.Append(",datediff(curdate(), iss_created_date) Days_Old "); };
 if (updateDate) { myCmdQuery.Append(",iss_updated_date Updated_Date "); };
 if (updateAge) { myCmdQuery.Append(", datediff(curdate(),iss_updated_date) Days_Since_Update "); };
 if (updateUser)
            {
                myCmdQuery.Append(",(select usr_full_name from eventum_user join eventum_issue_history " +
                      "on usr_id = his_usr_id where his_id = (select max(his_id) from " +
                      "eventum_issue_history where his_iss_id = iss_id)) Update_User ");};
 if (updateSummary) { myCmdQuery.Append(",(select his_summary from eventum_issue_history where " +
                "his_id = (select max(his_id) from eventum_issue_history where his_iss_id = iss_id)) Update_Summary "); };
 if (abbrev)
            {
 if (notes) { myCmdQuery.Append(", left(n.not_note,200) Last_Internal_Note "); };
 if (closeComments) { myCmdQuery.Append(", left(nc.not_note,200) Closed_Comment "); };
            }
 else
            {
 if (notes) { myCmdQuery.Append(", n.not_note Last_Internal_Note "); };
 if (closeComments) { myCmdQuery.Append(", nc.not_note Closed_Comment "); };
            }
 if (custom) { myCmdQuery.Append(", cfo_value Custom "); };
 if (expResDate) { myCmdQuery.Append(", iss_expected_resolution_date Exp_Res_Date "); };
 if (dupe) {myCmdQuery.Append(", iss_duplicated_iss_id dupe "); };
            myCmdQuery.Append("FROM eventum_issue e ");
            myCmdQuery.Append("join eventum_status s on e.iss_sta_id = s.sta_id ");
            myCmdQuery.Append("join eventum_project p on e.iss_prj_id = p.prj_id ");
            myCmdQuery.Append("join eventum_project_category pc on e.iss_prc_id = pc.prc_id ");
 if (priority) { myCmdQuery.Append("join eventum_project_priority pp on e.iss_pri_id = pp.pri_id "); };
 if (notes)
            {
                myCmdQuery.Append("left outer join eventum_note n on iss_id = not_iss_id and not_id = " +
                    "  (select max(not_id) from eventum_note nn " +
                    "   where n.not_iss_id = nn.not_iss_id and nn.not_title <> 'Issue closed comments') ");
            }
 if (closeComments)
            {
                myCmdQuery.Append("left outer join eventum_note nc on iss_id = nc.not_iss_id and nc.not_id = " +
                    "  (select max(ncc.not_id) from eventum_note ncc " +
                    "   where nc.not_iss_id = ncc.not_iss_id and ncc.not_title = 'Issue closed comments') ");
            }
 if (custom)
            {
                myCmdQuery.Append("left outer join eventum_issue_custom_field on icf_iss_id = iss_id " +
                    "left outer join eventum_custom_field_option on cfo_id = icf_value ");
            } 
 if (this.ddlUserAssignment.SelectedIndex > 0)
            {
                myCmdQuery.Append("join eventum_issue_user on iss_id = isu_iss_id ");
            }
            myCmdQuery.Append("WHERE prj_status = 'active' ");
 if (this.ddlProject.SelectedIndex > 0)
            {
                myCmdQuery.Append("and prj_id = " + this.ddlProject.SelectedValue.ToString() + " ");
            }
 if (this.ddlUserAssignment.SelectedIndex > 0)
            {
                myCmdQuery.Append(" and isu_usr_id = " + this.ddlUserAssignment.SelectedValue.ToString() + " ");
            }
 if (this.rblDate.SelectedIndex == 3)  //NOT touched
            {
                myCmdQuery.Append("and not exists(select 3 from eventum_issue_history where his_iss_id = iss_id " );
 
 if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
 if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
 if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), his_created_date) <= " + numberOfDays + " ");
                }
 else
                {
 if (start != null)
                    {
                        myCmdQuery.Append(" and his_created_date >= '" + start + "' ");
                    }
 if (end != null)
                    {
                        myCmdQuery.Append(" and his_created_date <= '" + end + "' ");
                    }
                }
                myCmdQuery.Append(") ");
            }
 if (this.rblDate.SelectedIndex == 2)  //touched
            {
                myCmdQuery.Append("and exists(select 2 from eventum_issue_history where his_iss_id = iss_id ");
 
 if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
 if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
 
 if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), his_created_date) <= " + numberOfDays + " ");
                }
 else
                {
 if (start != null) { myCmdQuery.Append(" and his_created_date >= '" + start + "' "); };
 if (end != null) { myCmdQuery.Append(" and his_created_date <= '" + end + "' "); }
                }
                myCmdQuery.Append(") ");
            }
 else if (this.rblDate.SelectedIndex == 1)  //closed
            {
                myCmdQuery.Append("and exists(select 1 from eventum_issue_history where his_iss_id = iss_id " +
                    "and his_htt_id = 23 ");
 
 if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
 if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and his_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
 if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), iss_closed_date) <= " + numberOfDays + " ");
                }
 else
                {
 if (start != null) { myCmdQuery.Append(" and iss_closed_date >= '" + start + "' "); };
 if (end != null) { myCmdQuery.Append(" and iss_closed_date <= '" + end + "' "); };
                }
                myCmdQuery.Append(") ");
            }
 else if (this.rblDate.SelectedIndex == 0) //created
            {
 if (this.ddlGroupAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and iss_usr_id in (select usr_id from eventum_user where usr_grp_id = " +
                        ddlGroupAction.SelectedValue + ") ");
                }
 if (this.ddlUserAction.SelectedIndex > 0)
                {
                    myCmdQuery.Append(" and iss_usr_id = " + ddlUserAction.SelectedValue + " ");
                }
 
 if (numberOfDays != null)
                {
                    myCmdQuery.Append(" and datediff(curdate(), iss_created_date) <= " + numberOfDays + " ");
                }
 else
                {
 if (start != null) { myCmdQuery.Append(" and iss_created_date >= '" + start + "' "); };
 if (end != null) { myCmdQuery.Append(" and iss_created_date <= '" + end + "' "); };
                }
 
            }
 if (closed && !open) { myCmdQuery.Append(" and iss_closed_date is not null "); };
 if (open && !closed) { myCmdQuery.Append(" and iss_closed_date is null "); };
 if (!open && !closed) { Response.Write("umm.  Not Closed and Not Open?  Kindly try again."); return; };
 if (this.ddlGroupAssignment.SelectedIndex > 0)
            {
                myCmdQuery.Append(" and iss_id in (select isu_iss_id from eventum_issue_user " +
                    "join eventum_user on isu_usr_id = usr_id " +
                    "where usr_grp_id = " + this.ddlGroupAssignment.SelectedValue + ") ");
            }
 if (this.txtKeyword.Text.Length > 0)
            { 
                myCmdQuery.Append(" and iss_id in ( select * from ((SELECT DISTINCT(iss_id)  FROM evdb.eventum_issue " +
                    "WHERE  MATCH(iss_summary, iss_description) AGAINST ('" + this.txtKeyword.Text + "' IN BOOLEAN MODE) " +
                    ") UNION (  SELECT DISTINCT(not_iss_id)  FROM  evdb.eventum_note " +
                    "WHERE  MATCH(not_note) AGAINST ('" + this.txtKeyword.Text + "' IN BOOLEAN MODE) " +
                    ") UNION ( SELECT DISTINCT(sup_iss_id) FROM evdb.eventum_support_email, " +
                    "evdb.eventum_support_email_body WHERE sup_id = seb_sup_id AND " +
                    "MATCH(seb_body) AGAINST ('" + this.txtKeyword.Text + "' IN BOOLEAN MODE) ) ) a ) ");
            }
 if (this.ddlCategory.SelectedIndex > 0) { myCmdQuery.Append(" and iss_prc_id = " + this.ddlCategory.SelectedValue.ToString()); };
 if (this.ddlPriority.SelectedIndex > 0) { myCmdQuery.Append(" and iss_pri_id = " + this.ddlPriority.SelectedValue.ToString()); };
            myAdp.SelectCommand = new MySqlCommand(myCmdQuery.ToString());
            myAdp.SelectCommand.Connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["evdbConn"].ToString());
            Session["_query"] = myCmdQuery.ToString();
            DataTable myTable = new DataTable();
 try
            {
                myAdp.Fill(myTable);
                DataView myDV = new DataView(myTable);
 // insert sorts in reverse order here  FIRST USER SORTS, THEN GROUPINGS
 if (this.ddlSort3.SelectedIndex > 0)
                {
                    addSort(ddlSort3.SelectedItem.Text.Replace(" ", "_"), ref myDV);
                }
 if (this.ddlSort2.SelectedIndex > 0)
                {
                    addSort(ddlSort2.SelectedItem.Text.Replace(" ", "_"), ref myDV);
                }
 if (this.ddlSort1.SelectedIndex > 0)
                {
                    addSort(ddlSort1.SelectedItem.Text.Replace(" ", "_"), ref myDV);
                }
 
 if (this.chkGroupAssignees.Checked)
                {
                    addSort("Assignees", ref myDV);
                }
 if (this.chkGroupCategory.Checked)
                {
                    addSort("Category", ref myDV);
                }
 if (this.chkGroupProject.Checked)
                {
                    addSort("Project", ref myDV);
                }

The result grid also had grouping on any of three separate attributes and was also written in the long declarative style, which I am not proud of, but it resulted in an organized and detailed report grid.

The second part required the use of the webserver’s Scheduled tasks to run daily and check the database for any reports that were scheduled to go at that time.  The task ran a simple console script.  I saved the task using impersonation and  .Net interfaced with Scheduled Tasks easily.

        ScheduledTasks st = new ScheduledTasks();
        Trigger newTrigger;
        Task newTask;
string username = User.Identity.Name.Replace("SOMECORP\\","").ToLower();
string reportName = "QBR" + username + this.txtScheduledReportName.Text;
if (this.txtScheduledReportName.Text.Length > 0 && username.Length > 0)
        {
 try
            {
 int adjustedHour = (this.ddlAM.SelectedIndex == 0) ? Convert.ToInt16(this.ddlHour.SelectedValue) :
                    (Convert.ToInt16(this.ddlHour.SelectedValue)) + 12;
                st.DeleteTask(reportName); // in case we are saving new version
                DeleteReport(reportName);
                newTask = st.CreateTask(reportName);
 if (newTask != null)
                {
                    newTask.ApplicationName = "c:\\bin\\QbertEmailer.exe";
                    newTask.Parameters = "\"" + reportName + "\"";
                    newTask.SetAccountInformation("someserviceaccount", "password");
 if (this.ddlFrequency.SelectedIndex == 0)
                    {
                        newTrigger = new DailyTrigger(
                            (short)adjustedHour,
                            (short)Convert.ToInt16(this.ddlMinute.SelectedValue.Replace(":", "")));
                    }
 else
                    {
                        newTrigger = new WeeklyTrigger(
                            (short)adjustedHour,
                            (short)Convert.ToInt16(this.ddlMinute.SelectedValue.Replace(":", "")),
                            (DaysOfTheWeek)Convert.ToInt16(this.ddlWeekday.SelectedValue));
                    }
                    newTask.Triggers.Add(newTrigger);
 // Call LogonUser to get a token for the user
                    Utils.ImpersonateUser iu = new Utils.ImpersonateUser();
                    iu.Impersonate("corp", "someserviceaccount", "password");
                    newTask.Save();
                    newTask.Close();
                    iu.Undo();
 this.bindGrid();
 this.SaveReport(reportName, true);
                    resetScheduleForm();
                }
 else
                {
                    Response.Write("Problem creating report name.  Please try again");
                }
            }
 catch (Exception ex)
            {
                logThis(ex.ToString(), EventLogEntryType.Error);
                Response.Write("Could not save report.  Click back and try again or contact your favorite QBERT support representative");
 //continue
            }
 finally
            {
                st.Dispose();
            }
        }
else
        {
            Response.Write("Bad User or Report Name, cannot save.");
        }
    }

AD lookups provided email addresses.  I didn’t put the email addresses in our database, because that would just add another place users had to manage their email addresses, and employees already managed their emails through the company’s AD system. 

 using (DirectorySearcher ds = new DirectorySearcher("(samaccountname=" + user + ")"))
                {
 
                    ds.PropertiesToLoad.Add("mail");
                    SearchResult sr = ds.FindOne();
 if (sr == null)
                    {
 return;
                    }
 if (sr.Path.Length == 0)
                    {
 return;
                    }
                    user = sr.Properties["mail"][0].ToString();
                }

The email was simple html, just formatting the results of the saved report query, run at that moment.

htmlReport.Append("<html><table cellspacing='0' border='1'style='border-collapse:collapse;font-size:9pt'>" +
                        "<tr style='background-color:Silver;font-size:X-Small;'>");
 foreach (DataColumn dc in dt.Columns)
                        {
                            htmlReport.Append("<td>" + dc.ColumnName + "</td>");
                        }
                        htmlReport.Append("</tr><tr>");
 int x = 1;
 foreach (DataRow dr in dt.Rows)
                        {
 if (x == 1)
                            {
                                htmlReport.Append("<tr>");
                                x = x * -1;
                            }
 else
                            {
                                htmlReport.Append("<tr style='background-color:AliceBlue;'>");
                                x = x * -1;
                            }
 foreach (object fieldVal in dr.ItemArray)
                            {
 if (dr.ItemArray[0].ToString().Equals(fieldVal.ToString()))
                                {
                                    htmlReport.Append("<td><a href='http://boitqueue/view.php?id=" +
                                        fieldVal.ToString() + "'>" + fieldVal.ToString() + "</td>");
                                }
 else
                                {
                                    htmlReport.Append("<td>" + fieldVal.ToString() + "</td>");
                                }
                            }
                            htmlReport.Append("</tr>");
                        }
                        htmlReport.Append("</table></html>");

I read once the idea that Exceptions should only be caught if you can handle them, and this makes sense to me.  Besides a Top level, uncaught exception handler for client facing applications, I usually avoid the try catch blocks unless I am going to do something specific at that point in the code.  I wrote  a database monitor that was used to continually run in a Windows Service and involved several factors that called for robust program recovery- database timeouts, file I/O, multi threaded operations. So there were specific failures that I knew about and could write around.  Here are some of the routines:

}
 else
                {
                    myTimer.Dispose();
                    e.Result = filename;
                }
 try
                { 
                    DataTable myTable = new DataTable();
                    myTable.ExtendedProperties["filename"] = filename;
                    myAdp.Fill(myTable);
                    e.Result = myTable;
 if (bw.CancellationPending)
                    {
                        e.Result = filename;
                        e.Cancel = true;
                        myTimer.Dispose();
                    }
                }
 catch (OracleException ex)
                {
                    logThis(ex.ToString(), EventLogEntryType.Warning);
                    e.Result = filename;//move on
                }
 catch (Exception ex)
                {
                    logThis(ex.ToString(), EventLogEntryType.Error);
 throw;
                }
 finally
                {
 if (myAdp.SelectCommand.Connection.State == ConnectionState.Open)
                    {
                        myAdp.Dispose();
                    }
                    myTimer.Dispose();
                }

In this case we are pinging a database batch process every few minutes, and occasionally we might get a timeout, but we don’t want to bring the whole Service down, because it also monitors other data sources, and we can try again in a few minutes and find the same database is now responsive.  The end result of a timeout might be a blip on a graph or chart, or a yellow light on a status gauge, that turns green if the database comes back and turns red the longer it has been since the database was responsive.

Here we are storing our ping result info in local xml files.  In retrospect, there may have been easier options, but this allows us to add monitored sources dynamically, and manage the XML files dynamically, rather than have to create them in a database ahead of time.  I wrote in a level of tolerance for concurrent file access Reads and Writes just using sleep and loops and it works perfectly for this case.  Once again, we don’t need a perfect level of detail since we are running every few minutes.   An occasional lost entry does not get noticed.

private void saveXML(XElement myRoot, string filename)
        {
 int retrySave = 10;
 while (retrySave > 0)
            {
 try
                {
                    myRoot.Save(filename);
                }
 catch (IOException exIO)
                {
                    Thread.Sleep(1000);
                    retrySave--;
 continue;
                }
 break;
            } 
        }
private XElement loadXML(string filename)
        {
            XElement myRoot = null;
 using (FileStream myStream =
 new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
 int retryLoad = 10;
 while (retryLoad > 0)
                {
 try
                    {
 using (XmlReader xr = XmlReader.Create(myStream))
                        {
                            myRoot = XElement.Load(xr);
                        }
                    }
 catch (XmlException x)
                    {
 // we expect some rootElement is missing ... from other threads
                        retryLoad--;
                        Thread.Sleep(1000);
 continue;
                    }
 break;
                }
            }
 return myRoot;
        }

One of the other features of this monitor, was that we would run against an unknown number of data sources, and they weren’t related.  Each source was separate, and so threading out the pings was simple, but some datasources acted differently than others.  Oracle TNS Listener would simply not return if there was no database endpoint, and so we had to run another thread using the Timer to cancel our thread in case this happened. I think at the time I put this together, a parallel concurrency library had just come out, but wasn’t officially part of .Net, or it wasn’t out and so up to this point most of the threading projects I did and I researched were all done with BackgroundWorkers.

private void dbMonStart(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker bw = sender as BackgroundWorker;
            String db = e.Argument.ToString();
 // set a watch timer to cancel thread on sql timeout (used more for dev when taken offline not prod
            System.Threading.Timer myTimer = new System.Threading.Timer(cancelMyThread, bw, 30000, System.Threading.Timeout.Infinite);
 using (OracleDataAdapter myAdp = new OracleDataAdapter())
            {
                myAdp.SelectCommand = cmd;
                myAdp.SelectCommand.Connection = new OracleConnection(conn);
 try
                {
                    DataTable myTable = new DataTable();
                    myTable.ExtendedProperties["dbname"] = db;
                    myAdp.Fill(myTable);
                    e.Result = myTable;
 if (bw.CancellationPending)
                    {
                        e.Result = db;
                        e.Cancel = true;
                        myTimer.Dispose();
                    }
                }

CancelMyThread is simple enough:

private void cancelMyThread(object myThread)
        {
            BackgroundWorker bw = myThread as BackgroundWorker;
 if (bw.IsBusy) { bw.CancelAsync(); }

and that’s it.