YADDU. Yet Another (MS-SQL) Database Documentation Utility

#region SVN File History
// ***********************************************************
// Copyright �   2012 Craig Greenock.
// Contact     - cgreenock@bcs.org.uk
// Version 0.0 - 20 January 2012 20:03
// Machine     - KEFALLONIA Microsoft Windows NT 6.1.7600.0
//
//
//
// $Author: cig $
// $HeadURL: https://kefallonia/svn/Projects_2008/Schema/trunk/Schema/dataDic.aspx.cs $
// $LastChangedBy: cig $
// $LastChangedDate: 2012-02-15 20:46:06 +0000 (Wed, 15 Feb 2012) $
// $Revision: 367 $
//
// ***********************************************************
#endregion  // SVN File History

#region References
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

using Schema.Constants;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.Xml.Linq;
using System.Linq;

using Schema.Library;
#endregion // References

namespace Schema
{
	/// <summary>
	/// Summary description for dataDic.
	/// </summary>
	public class dataDic : System.Web.UI.Page
	{
    protected System.Web.UI.WebControls.Panel Panel1;

    /// <summary>
    /// Allow selection of appropriate action in 
    /// click handler.
    /// </summary>
    private enum ButtonID
    {
      Apply  = 1,
      Cancel = 2,
      OK     = 3
    }
  
		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
		}

		
    #region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);

      if (!Page.IsPostBack)
        Session[SessionKey.TableUpdate] = Request["table"].ToString();

      // Work out the boxes & prompts & fill them from the DB.
      paintPage();
    
      paintButtons();  
    
    }
		

		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
      this.Load += new System.EventHandler(this.Page_Load);

    }
    #endregion

    private void paintButtons()
    {

        this.Panel1.Controls.Add(new LiteralControl(@"<div style=""float:right;"">"));
        // Now give the user the option of accepting / cancelling.
        Button b = new Button();
        const int buttonWidth = 80;
        b.ID = ButtonID.Apply.ToString();
        b.Text = b.ID;
        b.Width = Unit.Pixel(buttonWidth);
        b.Attributes.Add("runat", "server");
        b.Click += new EventHandler(button_Click);
        this.Panel1.Controls.Add(b);

        b = new Button();
        b.ID = ButtonID.OK.ToString();
        b.Text = b.ID;
        b.Width = Unit.Pixel(buttonWidth);
        b.Attributes.Add("runat", "server");
        b.Click += new EventHandler(button_Click);
        this.Panel1.Controls.Add(b);
        
        b = new Button();
        b.ID = ButtonID.Cancel.ToString();
        b.Text = b.ID;
        b.Width = Unit.Pixel(buttonWidth);
        b.Attributes.Add("runat", "server");
        b.Click += new EventHandler(button_Click);
        this.Panel1.Controls.Add(b);
        this.Panel1.Controls.Add(new LiteralControl("</div>"));

    }


    /// <summary>
    /// Extract column descriptions from dd table & 
    /// use to generate appropriate input form.
    /// </summary>
    private void paintPage()
    {
      string tableName = Session[SessionKey.TableUpdate].ToString();
      string server    = Session[SessionKey.Server].ToString();
      string database  = Session[SessionKey.Database].ToString();
      string uid       = Session[SessionKey.UserId].ToString();
      string pwd       = Session[SessionKey.Password].ToString();

      // Get a single table's schema as an XML fragment...
      string connection = TSQL.dal.ConnectionPath(database, server, uid, pwd);
      DataTable tableInfo = TSQL.dal.TableInformation(tableName, connection);

      // ... now turn the fragment into a well formed XML string...
      string infoAsXML = @"<?xml version=""1.0"" ?><Tables key=""{0}"">{1}</Tables>";
      infoAsXML = string.Format(infoAsXML, tableName, tableInfo.Rows[0][0].ToString());

      this.Panel1.Controls.Add(new LiteralControl(@"<div class=""tableName"">"));
      this.Panel1.Controls.Add(new LiteralControl(tableName));
      this.Panel1.Controls.Add(new LiteralControl("</div>"));

      // ...and turn the XML into something navigable using LINQ.
      XElement info = XElement.Parse(infoAsXML);

      var qry = from tableProps in info.Elements("table").Elements("tableProps")
                  select tableProps;

      foreach(XElement t in qry)
      {
        // Put in a table description text box and a checkbox to 
        this.Panel1.Controls.Add(new LiteralControl(@"<div class=""tableInfo"">"));
        this.Panel1.Controls.Add(new LiteralControl(@"<div class=""cellLeft"">&nbsp;</div>"));
        this.Panel1.Controls.Add(new LiteralControl(@"<div class=""cellRight"">"));
        TextBox tableDescription = new TextBox();
        tableDescription.ID = RequestKey.TableComment;  
        tableDescription.Text = t.AttributeString("Comment");
        tableDescription.Width = Unit.Pixel(500);
        tableDescription.Height = Unit.Pixel(40);
        tableDescription.TextMode = TextBoxMode.MultiLine;
        tableDescription.MaxLength = (int)ExtendedProperty.SizeOf.Name;
        tableDescription.Attributes.Add("runat", "server");
        tableDescription.ToolTip ="Describe the purpose of the table.";
        this.Panel1.Controls.Add(tableDescription);
        this.Panel1.Controls.Add(new LiteralControl("</div>"));
        this.Panel1.Controls.Add(new LiteralControl("</div>"));

        // Put in a table description text box and a checkbox
        this.Panel1.Controls.Add(new LiteralControl(@"<div class=""tableInfo"">"));


        this.Panel1.Controls.Add(new LiteralControl(@"<div class=""cellLeft"">"));
        CheckBox tableHidden = new CheckBox();
        tableHidden.ID =RequestKey.TableHidden;
        tableHidden.Text = "Hide Table";
        tableHidden.TextAlign = TextAlign.Left;
        tableHidden.Checked = (1 == t.AttributeString("Hidden").BoolFudge(0));
        tableHidden.ToolTip = "Hide this table from the schema documentation tool.";
        tableHidden.Attributes.Add("runat", "server");
        this.Panel1.Controls.Add(tableHidden);
        this.Panel1.Controls.Add(new LiteralControl("</div>"));

        this.Panel1.Controls.Add(new LiteralControl(@"<div class=""cellRight"">"));
        this.Panel1.Controls.Add(new LiteralControl("Added at Version:&nbsp;"));
        TextBox tableVersion = new TextBox();
        tableVersion.ID = RequestKey.TableVersion;  
        tableVersion.Text = t.AttributeString("Version");
        tableVersion.Width = Unit.Pixel(100);
        tableVersion.Height = Unit.Pixel(20);
        tableVersion.ToolTip ="DB version in which this table was introduced.";
        tableVersion.TextMode = TextBoxMode.SingleLine;
        tableVersion.MaxLength = (int)ExtendedProperty.SizeOf.Value;
        tableVersion.Attributes.Add("runat", "server");
        this.Panel1.Controls.Add(tableVersion);
        this.Panel1.Controls.Add(new LiteralControl("</div>"));


        this.Panel1.Controls.Add(new LiteralControl("</div>"));

      }

      var query = from column in info.Elements("table").Elements("tableProps").Elements("column")
                  select column;

      Panel ColumnPanel = new Panel();
      ColumnPanel.ScrollBars = ScrollBars.Vertical;
      ColumnPanel.Height = Unit.Pixel(400);

      this.Panel1.Controls.Add(new LiteralControl(@"<hr />"));
      this.Panel1.Controls.Add(ColumnPanel);

      foreach(XElement column in query)
      {
        // each column in its own div
        ColumnPanel.Controls.Add(new LiteralControl(@"<div class=""columnInfo"">"));

        string columnName = column.AttributeString("Name", "!unnamed!");
        string columnComment = "";

        ColumnPanel.Controls.Add(new LiteralControl(@"<div class=""cellLeft"">"));
        ColumnPanel.Controls.Add(new LiteralControl(columnName));
        ColumnPanel.Controls.Add(new LiteralControl(@"</div>"));

        var qryText = from comment in column.Elements("columnProps")
                      select comment;
        
        foreach(var comment in qryText)
        {
          columnComment = comment.AttributeString("Comment", "");
        }

        ColumnPanel.Controls.Add(new LiteralControl(@"<div class=""cellRight"">"));
        TextBox description = new TextBox();
        description.ID = columnName;  
        description.Text = columnComment;
        description.Width = Unit.Pixel(500);
        description.Height = Unit.Pixel(40);
        description.TextMode = TextBoxMode.MultiLine;
        description.ToolTip = string.Format("Describe the purpose of [{0}] and any special rules that may apply to it.", columnName);
        description.MaxLength = (int)ExtendedProperty.SizeOf.Value;
        description.Attributes.Add("runat", "server");
        ColumnPanel.Controls.Add(description);
        ColumnPanel.Controls.Add(new LiteralControl("</div>"));

        this.Panel1.Controls.Add(new LiteralControl("</div>"));

      }

      this.Panel1.Controls.Add(new LiteralControl(@"<hr />"));


    }

    /// <summary>
    /// Apply update and if OK button clicked go back to 
    /// schema page.
    /// </summary>
    /// <param name="sender">The 'apply' or the 'ok' button.</param>
    /// <param name="e"></param>
    private void button_Click(object sender, EventArgs e)
    {
      
      // Retrieve an enum from its string equivalent.
      ButtonID buttonId = (ButtonID)Enum.Parse(typeof(ButtonID), ((Button)(sender)).ID);

      // If Ok or Apply hit the update.
      switch (buttonId)
      {
        case ButtonID.Apply:
        case ButtonID.OK:
          this.update();
          break;
        default:
          break;
          // NOP
      }

      // If OK or cancel hit reload
      switch (buttonId)
      {
        case ButtonID.Cancel:
        case ButtonID.OK:
          this.reload();
          break;
        default:
          break;
          // NOP
      }
    }

    /// <summary>
    /// Pull up the schema page, forcing reload of schema
    /// definition.
    /// </summary>
    private void reload()
    {
      Server.Transfer(@"loadSchema.aspx");
    }

    /// <summary>
    /// Extract column descriptions from form (Request) 
    /// and apply to database.
    /// </summary>
    private void update()
    {


      // Now update the database....
      string tableName = Session[SessionKey.TableUpdate].ToString();
      string server    = Session[SessionKey.Server].ToString();
      string database  = Session[SessionKey.Database].ToString();
      string uid       = Session[SessionKey.UserId].ToString();
      string pwd       = Session[SessionKey.Password].ToString();

      string connection = TSQL.dal.ConnectionPath(database, server, uid, pwd);
      
      string tableComment = Request.Form[RequestKey.TableComment].DefaultValue("");
      string tableHidden  = Request.Form[RequestKey.TableHidden].BoolFudge(0).ToString();
      string tableVersion = Request.Form[RequestKey.TableVersion].DefaultValue("");
      
      using (SqlConnection sqlCon = new SqlConnection(connection))
      {
        sqlCon.Open();
        TSQL.dal.TablePropertyWrite(sqlCon,  tableName, "comment", tableComment);
        TSQL.dal.TablePropertyWrite(sqlCon,  tableName, "version", tableVersion);
        TSQL.dal.TablePropertyWrite(sqlCon,  tableName, "hidden", tableHidden);

        DataView columns = TSQL.dal.ColumnList(sqlCon, tableName);

        // There should be an item in the Request packet for each 
        // column in the table.
        foreach (DataRowView col in columns)
        {
          string colName = col["name"].ToString();
          string colComment = Request.Form[colName].DefaultValue("");
          TSQL.dal.ColumnPropertyWrite(sqlCon, tableName, colName, "comment", colComment);
        }

        sqlCon.Close();
      }

    }

  }
}