Click here to Skip to main content
15,894,740 members
Articles / Database Development / SQL Server

Using SQL Server Metadata and Statistics to Build a Table Explorer Application

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
1 Oct 2012CPOL8 min read 22.4K   488   7  
Develops an analogy between database table and file directory information and argues that it could be very useful if it were possible to have a similar application to Windows Explorer for database tables. Demonstrates how to obtain the information from Sql Server system tables and dynamic management
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace DbView
{
	/// <summary>
	/// Summary description for SourceForm.
	/// </summary>
	public class SourceForm2 : System.Windows.Forms.Form
	{
        private String m_SearchText;
        private Rectangle m_HoldBounds;
        private System.Data.DataSet sourceLines;
        private System.Windows.Forms.Panel panel1;
        private System.Windows.Forms.Button btnClose;
        private System.Windows.Forms.Button btnCopy;
        private System.Windows.Forms.TextBox textSearch;
        private System.Windows.Forms.Button btnFind;
        private System.Windows.Forms.RichTextBox theSource;
        private System.Windows.Forms.Button btnNext;
        private System.Windows.Forms.Button btnPrev;
        private Button btnZoom;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public SourceForm2(String ItemName, String theProcSource, String searchText)
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();
			
            // unselect
            this.theSource.Text += theProcSource;
            this.theSource.SelectionLength = 0;
            // this.theSource.ReadOnly = true;
            // fix up caption
            this.Text = String.Format("Source: [{0}]", ItemName);
            m_SearchText = searchText;
            if (m_SearchText.Length > 0)
            {
                this.textSearch.Text = m_SearchText;
                onFind();
            }                
        }

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(SourceForm2));
			this.panel1 = new System.Windows.Forms.Panel();
			this.btnZoom = new System.Windows.Forms.Button();
			this.btnCopy = new System.Windows.Forms.Button();
			this.textSearch = new System.Windows.Forms.TextBox();
			this.btnFind = new System.Windows.Forms.Button();
			this.btnNext = new System.Windows.Forms.Button();
			this.btnPrev = new System.Windows.Forms.Button();
			this.btnClose = new System.Windows.Forms.Button();
			this.theSource = new System.Windows.Forms.RichTextBox();
			this.panel1.SuspendLayout();
			this.SuspendLayout();
			// 
			// panel1
			// 
			this.panel1.Controls.Add(this.btnZoom);
			this.panel1.Controls.Add(this.btnCopy);
			this.panel1.Controls.Add(this.textSearch);
			this.panel1.Controls.Add(this.btnFind);
			this.panel1.Controls.Add(this.btnNext);
			this.panel1.Controls.Add(this.btnPrev);
			this.panel1.Controls.Add(this.btnClose);
			this.panel1.Dock = System.Windows.Forms.DockStyle.Top;
			this.panel1.Location = new System.Drawing.Point(0, 0);
			this.panel1.Name = "panel1";
			this.panel1.Size = new System.Drawing.Size(677, 34);
			this.panel1.TabIndex = 1;
			// 
			// btnZoom
			// 
			this.btnZoom.Location = new System.Drawing.Point(531, 5);
			this.btnZoom.Name = "btnZoom";
			this.btnZoom.Size = new System.Drawing.Size(70, 24);
			this.btnZoom.TabIndex = 6;
			this.btnZoom.Text = "&Enlarge";
			this.btnZoom.Click += new System.EventHandler(this.btnZoom_Click);
			// 
			// btnCopy
			// 
			this.btnCopy.Location = new System.Drawing.Point(3, 4);
			this.btnCopy.Name = "btnCopy";
			this.btnCopy.Size = new System.Drawing.Size(70, 24);
			this.btnCopy.TabIndex = 0;
			this.btnCopy.Text = "Copy &all";
			this.btnCopy.Click += new System.EventHandler(this.btnCopy_Click);
			// 
			// textSearch
			// 
			this.textSearch.Location = new System.Drawing.Point(76, 8);
			this.textSearch.Name = "textSearch";
			this.textSearch.Size = new System.Drawing.Size(224, 20);
			this.textSearch.TabIndex = 1;
			// 
			// btnFind
			// 
			this.btnFind.Location = new System.Drawing.Point(306, 5);
			this.btnFind.Name = "btnFind";
			this.btnFind.Size = new System.Drawing.Size(70, 24);
			this.btnFind.TabIndex = 2;
			this.btnFind.Text = "&Find";
			this.btnFind.Click += new System.EventHandler(this.btnFind_Click);
			// 
			// btnNext
			// 
			this.btnNext.Location = new System.Drawing.Point(381, 5);
			this.btnNext.Name = "btnNext";
			this.btnNext.Size = new System.Drawing.Size(70, 24);
			this.btnNext.TabIndex = 3;
			this.btnNext.Text = "&Next";
			this.btnNext.Click += new System.EventHandler(this.btnNext_Click);
			// 
			// btnPrev
			// 
			this.btnPrev.Location = new System.Drawing.Point(456, 5);
			this.btnPrev.Name = "btnPrev";
			this.btnPrev.Size = new System.Drawing.Size(70, 24);
			this.btnPrev.TabIndex = 4;
			this.btnPrev.Text = "&Prev";
			this.btnPrev.Click += new System.EventHandler(this.btnPrev_Click);
			// 
			// btnClose
			// 
			this.btnClose.DialogResult = System.Windows.Forms.DialogResult.Cancel;
			this.btnClose.Location = new System.Drawing.Point(606, 5);
			this.btnClose.Name = "btnClose";
			this.btnClose.Size = new System.Drawing.Size(70, 24);
			this.btnClose.TabIndex = 5;
			this.btnClose.Text = "C&lose";
			this.btnClose.Click += new System.EventHandler(this.btnClose_Click);
			// 
			// theSource
			// 
			this.theSource.Dock = System.Windows.Forms.DockStyle.Fill;
			this.theSource.Font = new System.Drawing.Font("Courier New", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
			this.theSource.Location = new System.Drawing.Point(0, 34);
			this.theSource.Name = "theSource";
			this.theSource.Size = new System.Drawing.Size(677, 323);
			this.theSource.TabIndex = 0;
			this.theSource.Text = "";
			// 
			// SourceForm2
			// 
			this.AcceptButton = this.btnFind;
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.CancelButton = this.btnClose;
			this.ClientSize = new System.Drawing.Size(677, 357);
			this.Controls.Add(this.theSource);
			this.Controls.Add(this.panel1);
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.Name = "SourceForm2";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
			this.Text = "Source Form";
			this.panel1.ResumeLayout(false);
			this.panel1.PerformLayout();
			this.ResumeLayout(false);

        }
		#endregion

        private void btnClose_Click(object sender, System.EventArgs e)
        {
            this.Close();
        }

        private void theSource_TextChanged(object sender, System.EventArgs e)
        {
        
        }

        private int FindNext(String source, int FromPos)
        {
            // find location of all the seek 
            String usource = source.ToUpper();
            String useek = m_SearchText.ToUpper();
            int found = usource.IndexOf(useek, FromPos);
            // loop around
            if (found == -1)
                found = usource.IndexOf(useek);
            return found;
        }

        private int FindPrev(String source, int CurrentSelPos)
        {
            if (CurrentSelPos < 0)
                return -1; 
            // find location of all the seek 
            String usource = source.ToUpper();
            String useek = m_SearchText.ToUpper();
            // 
            int iLastPos = -1;  // nearest previous match to currenct
            while (true)
            {
                int found = usource.IndexOf(useek, iLastPos+1);
                if (found == -1 || found >= CurrentSelPos)
                    break;
                else
                    iLastPos = found;
            }
                
            return iLastPos;
        }

        private String ModifySource(String source)
        {
            // find location of all the seek 
            String usource = source.ToUpper();
            String useek = m_SearchText.ToUpper();
            ArrayList offsets = new ArrayList();
            int startpos = 0;
            while (true)
            {
                int found = usource.IndexOf(useek, startpos);
                if (found > startpos)
                {
                    offsets.Add(found);
                    startpos = found + useek.Length;
                }
                else 
                    break;
            }
            for (int idx = 0; idx < offsets.Count; ++idx)
            {
                int element = offsets.Count-idx-1;
                // add ending markup first
                source = source.Insert((int)offsets[element]+useek.Length, "\\cf1\\b0 ");
                // make bold and change the colour.
                source = source.Insert((int)offsets[element], "\\b\\cf2 ");
            }
            
            
            return source.Replace("\n","\\line\n");
        }
        
        private void onFind()
        {
            String source = theSource.Text;
            // if empty then set text to non-rtf.
            if (m_SearchText.Length == 0)
            {
                theSource.Text = source;
            }
            else
            {
                // convert to rtf 
                // (see; http://www.pindari.com/rtf1.html or Google "rtf tutorial")
                String  rtfHead  =
                    // header
                    @"{\rtf1\ansi\deff0"+"\n"+   
                    // mark colour
                    @"{\colortbl;\red0\green0\blue0;\red255\green0\blue0;}"+"\n"+
                    // font definition
                    @"{\fonttbl {\f0 Courier new;}}\fs16"+"\n";     // 16 = 8 point

                String  rtfTail  = @"}";  // as per rtf spec
                theSource.Rtf = rtfHead+ModifySource(source)+rtfTail;
                onNext();
            }
        }

        private void onNext()
        {
            // use caret if present
            int pos = theSource.SelectionStart;
            String s = theSource.Text;
            pos = FindNext(s, pos+m_SearchText.Length);
            if (pos >= 0)
            {
                theSource.SelectionStart = pos;
                theSource.SelectionLength = m_SearchText.Length;
                theSource.Focus();
                theSource.ScrollToCaret();
            }
        }

        private void onPrev()
        {
            // use caret if present
            int pos = theSource.SelectionStart;
            String s = theSource.Text;
            pos = FindPrev(s, pos);
            if (pos >= 0)
            {
                theSource.SelectionStart = pos;
                theSource.SelectionLength = m_SearchText.Length;
                theSource.Focus();
                theSource.ScrollToCaret();
            }
        }
        
        private void btnCopy_Click(object sender, System.EventArgs e)
        {
            theSource.SelectAll();
            theSource.Copy();
            this.Close();
        }

        private void btnFind_Click(object sender, System.EventArgs e)
        {
            m_SearchText = this.textSearch.Text;
            onFind();
        }

        private void btnNext_Click(object sender, System.EventArgs e)
        {
            onNext();
        }

        private void btnPrev_Click(object sender, System.EventArgs e)
        {
            onPrev();
        }

        private void btnZoom_Click(object sender, EventArgs e)
        {
            String btnTextEnlarge = "&Enlarge";
            String btnTextRestore = "Restor&e";
            Button Zoom = (Button)sender;
            String buttonText = Zoom.Text;
            if (buttonText == btnTextRestore) // e n l a r g e
            {
                this.SetBounds(m_HoldBounds.Left, m_HoldBounds.Top, m_HoldBounds.Width, m_HoldBounds.Height);
                Zoom.Text = btnTextEnlarge;
            }
            else
            {
                int margin = 20;
                m_HoldBounds = this.Bounds;
                // get screen size
                Rectangle scr = Screen.PrimaryScreen.Bounds;
                // resize window
                this.SetBounds(1, 1, scr.Width - margin, scr.Height - margin);
                Zoom.Text = btnTextRestore;
            }
        }

	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions