SPGen - Stored Procedure Generator

using System;
using System.Drawing;
using System.Collections;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Windows.Forms;
using System.Configuration;
using Bluegrass.Data;

namespace SPGen
{
	/// <summary>
	/// Main UI for SPGen
	/// </summary>
	public class frmMain : System.Windows.Forms.Form
	{		
		protected SQLDMOHelper dmoMain = new SQLDMOHelper();

		private System.Windows.Forms.StatusBar statbarMain;
		private System.Windows.Forms.StatusBarPanel statbarpnlMain;
		private System.Windows.Forms.Button cmdConnect;
		private System.Windows.Forms.TextBox txtPassword;
		private System.Windows.Forms.TextBox txtUser;
		private System.Windows.Forms.ComboBox selServers;
		private System.Windows.Forms.Splitter spltrMain;
		private System.Windows.Forms.Panel pnlConnectTo;
		private System.Windows.Forms.TreeView tvwServerExplorer;
		private System.Windows.Forms.TextBox txtGeneratedCode;
		private System.Windows.Forms.ImageList imglstMain;
		private System.ComponentModel.IContainer components;

		public frmMain()
		{			
			InitializeComponent();			

			// List Registered Servers
			object[] objServers = (object[])dmoMain.RegisteredServers;
			selServers.Items.AddRange(objServers);

			// Default connection details, if provided
			NameValueCollection settingsAppSettings = (NameValueCollection)ConfigurationSettings.AppSettings;			

			if (settingsAppSettings["ServerName"] != null && settingsAppSettings["ServerName"] != "")
			{
				selServers.Text = settingsAppSettings["ServerName"];
				dmoMain.ServerName = settingsAppSettings["ServerName"];
			}
			if (settingsAppSettings["UserName"] != null && settingsAppSettings["UserName"] != "")
			{
				txtUser.Text = settingsAppSettings["UserName"];
				dmoMain.UserName = settingsAppSettings["UserName"];
			}
			if (settingsAppSettings["Password"] != null && settingsAppSettings["Password"] != "")
			{
				char chPassword = '*';
				txtPassword.PasswordChar = chPassword;
				txtPassword.Text = settingsAppSettings["Password"];
				dmoMain.Password = settingsAppSettings["Password"];
			}
		}

		/// <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()
		{
			this.components = new System.ComponentModel.Container();
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(frmMain));
			this.statbarMain = new System.Windows.Forms.StatusBar();
			this.statbarpnlMain = new System.Windows.Forms.StatusBarPanel();
			this.pnlConnectTo = new System.Windows.Forms.Panel();
			this.cmdConnect = new System.Windows.Forms.Button();
			this.txtPassword = new System.Windows.Forms.TextBox();
			this.txtUser = new System.Windows.Forms.TextBox();
			this.selServers = new System.Windows.Forms.ComboBox();
			this.tvwServerExplorer = new System.Windows.Forms.TreeView();
			this.imglstMain = new System.Windows.Forms.ImageList(this.components);
			this.spltrMain = new System.Windows.Forms.Splitter();
			this.txtGeneratedCode = new System.Windows.Forms.TextBox();
			((System.ComponentModel.ISupportInitialize)(this.statbarpnlMain)).BeginInit();
			this.pnlConnectTo.SuspendLayout();
			this.SuspendLayout();
			// 
			// statbarMain
			// 
			this.statbarMain.Location = new System.Drawing.Point(0, 325);
			this.statbarMain.Name = "statbarMain";
			this.statbarMain.Panels.AddRange(new System.Windows.Forms.StatusBarPanel[] {
																																																																															this.statbarpnlMain});
			this.statbarMain.ShowPanels = true;
			this.statbarMain.Size = new System.Drawing.Size(608, 22);
			this.statbarMain.TabIndex = 5;
			// 
			// statbarpnlMain
			// 
			this.statbarpnlMain.AutoSize = System.Windows.Forms.StatusBarPanelAutoSize.Spring;
			this.statbarpnlMain.Text = "Awaiting your orders...";
			this.statbarpnlMain.Width = 592;
			// 
			// pnlConnectTo
			// 
			this.pnlConnectTo.Controls.AddRange(new System.Windows.Forms.Control[] {
																																																																											this.cmdConnect,
																																																																											this.txtPassword,
																																																																											this.txtUser,
																																																																											this.selServers});
			this.pnlConnectTo.Dock = System.Windows.Forms.DockStyle.Top;
			this.pnlConnectTo.Location = new System.Drawing.Point(0, -10);
			this.pnlConnectTo.Name = "pnlConnectTo";
			this.pnlConnectTo.Size = new System.Drawing.Size(608, 42);
			this.pnlConnectTo.TabIndex = 9;
			// 
			// cmdConnect
			// 
			this.cmdConnect.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
			this.cmdConnect.Location = new System.Drawing.Point(528, 16);
			this.cmdConnect.Name = "cmdConnect";
			this.cmdConnect.Size = new System.Drawing.Size(64, 21);
			this.cmdConnect.TabIndex = 7;
			this.cmdConnect.Text = "Connect";
			this.cmdConnect.Click += new System.EventHandler(this.cmdConnect_Click);
			// 
			// txtPassword
			// 
			this.txtPassword.Location = new System.Drawing.Point(352, 16);
			this.txtPassword.Name = "txtPassword";
			this.txtPassword.Size = new System.Drawing.Size(160, 20);
			this.txtPassword.TabIndex = 6;
			this.txtPassword.Text = "Password";			
			this.txtPassword.Leave += new System.EventHandler(this.txtPassword_Leave);
			this.txtPassword.Enter += new System.EventHandler(this.txtPassword_Enter);
			// 
			// txtUser
			// 
			this.txtUser.Location = new System.Drawing.Point(184, 16);
			this.txtUser.Name = "txtUser";
			this.txtUser.Size = new System.Drawing.Size(160, 20);
			this.txtUser.TabIndex = 5;
			this.txtUser.Text = "User";
			this.txtUser.Leave += new System.EventHandler(this.txtUser_Leave);
			this.txtUser.Enter += new System.EventHandler(this.txtUser_Enter);
			// 
			// selServers
			// 
			this.selServers.Location = new System.Drawing.Point(8, 16);
			this.selServers.Name = "selServers";
			this.selServers.Size = new System.Drawing.Size(160, 21);
			this.selServers.TabIndex = 4;
			this.selServers.Text = "Server Name";
			this.selServers.Leave += new System.EventHandler(this.selServers_Leave);
			// 
			// tvwServerExplorer
			// 
			this.tvwServerExplorer.Dock = System.Windows.Forms.DockStyle.Left;
			this.tvwServerExplorer.FullRowSelect = true;
			this.tvwServerExplorer.ImageList = this.imglstMain;
			this.tvwServerExplorer.Location = new System.Drawing.Point(0, 32);
			this.tvwServerExplorer.Name = "tvwServerExplorer";
			this.tvwServerExplorer.Size = new System.Drawing.Size(208, 293);
			this.tvwServerExplorer.TabIndex = 10;
			this.tvwServerExplorer.AfterSelect += new System.Windows.Forms.TreeViewEventHandler(this.tvwServerExplorer_AfterSelect);
			this.tvwServerExplorer.BeforeExpand += new System.Windows.Forms.TreeViewCancelEventHandler(this.tvwServerExplorer_BeforeExpand);
			// 
			// imglstMain
			// 
			this.imglstMain.ColorDepth = System.Windows.Forms.ColorDepth.Depth8Bit;
			this.imglstMain.ImageSize = new System.Drawing.Size(16, 16);
			this.imglstMain.ImageStream = ((System.Windows.Forms.ImageListStreamer)(resources.GetObject("imglstMain.ImageStream")));
			this.imglstMain.TransparentColor = System.Drawing.Color.Transparent;
			// 
			// spltrMain
			// 
			this.spltrMain.Location = new System.Drawing.Point(208, 32);
			this.spltrMain.Name = "spltrMain";
			this.spltrMain.Size = new System.Drawing.Size(3, 293);
			this.spltrMain.TabIndex = 11;
			this.spltrMain.TabStop = false;
			// 
			// txtGeneratedCode
			// 
			this.txtGeneratedCode.Dock = System.Windows.Forms.DockStyle.Fill;
			this.txtGeneratedCode.HideSelection = false;
			this.txtGeneratedCode.Location = new System.Drawing.Point(211, 32);
			this.txtGeneratedCode.Multiline = true;
			this.txtGeneratedCode.Name = "txtGeneratedCode";
			this.txtGeneratedCode.ScrollBars = System.Windows.Forms.ScrollBars.Vertical;
			this.txtGeneratedCode.Size = new System.Drawing.Size(397, 293);
			this.txtGeneratedCode.TabIndex = 12;
			this.txtGeneratedCode.Text = "";
			// 
			// frmMain
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(608, 347);
			this.Controls.AddRange(new System.Windows.Forms.Control[] {
																																																														this.txtGeneratedCode,
																																																														this.spltrMain,
																																																														this.tvwServerExplorer,
																																																														this.pnlConnectTo,
																																																														this.statbarMain});
			this.DockPadding.Top = -10;
			this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
			this.Name = "frmMain";
			this.Text = "SPGen: Stored Procedure Generator";
			((System.ComponentModel.ISupportInitialize)(this.statbarpnlMain)).EndInit();
			this.pnlConnectTo.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new frmMain());
		}

		private void cmdConnect_Click(object sender, System.EventArgs e)
		{						
			// First ensure connection details are valid
			if (dmoMain.ServerName == "" || dmoMain.UserName == "")
			{
				MessageBox.Show("Please enter in valid connection details.",this.Text);				
			}
			else
			{
				this.Cursor = Cursors.WaitCursor;
				statbarpnlMain.Text = "Connecting to SQL Server...";

				//Valid connection details				
				tvwServerExplorer.Nodes.Clear();

				// List Databases
				try
				{				
					dmoMain.Connect();
					Array aDatabases = (Array)dmoMain.Databases;
					dmoMain.DisConnect();

					for (int i = 0; i < aDatabases.Length; i++)
					{
						TreeNode treenodeDatabase = new TreeNode(aDatabases.GetValue(i).ToString(), 0, 0);
						treenodeDatabase.Nodes.Add("");
						tvwServerExplorer.Nodes.Add(treenodeDatabase);
					}

					this.Cursor = Cursors.Default;
					statbarpnlMain.Text = "Connectiong successful, databases listed...";
				}
				catch
				{				
					this.Cursor = Cursors.Default;
					statbarpnlMain.Text = "Connectiong un-successful...";
					MessageBox.Show("Connection to database failed. Please check your Server Name, User and Password.", this.Text);
				}
			}
		}

		private void txtPassword_Enter(object sender, System.EventArgs e)
		{
			if (txtPassword.Text == "Password") 
			{
				txtPassword.Text = "";
				char chPassword = '*';
				txtPassword.PasswordChar = chPassword;
			}
		}

		private void txtUser_Enter(object sender, System.EventArgs e)
		{
			if (txtUser.Text == "User") txtUser.Text = "";
		}

		private void tvwServerExplorer_BeforeExpand(object sender, System.Windows.Forms.TreeViewCancelEventArgs e)
		{
			// List all Tables for selected Database

			if (e.Node.ImageIndex == 0)
			{
				this.Cursor = Cursors.WaitCursor;
				statbarpnlMain.Text = "Listing Tables...";
				// Set database to get tables from						
				dmoMain.Database = e.Node.Text;				
						
				// Clear dummy node
				e.Node.Nodes.Clear();

				try
				{
					// List Tables
					dmoMain.Connect();
					Array aTables = (Array)dmoMain.Tables;				
					dmoMain.DisConnect();

					for (int i = 0; i < aTables.Length; i++)
					{
						TreeNode treenodeTable = new TreeNode(aTables.GetValue(i).ToString(), 1, 1);
						TreeNode treenodeTableUPDATE = new TreeNode("UPDATE Stored Procedure", 2, 2);
						TreeNode treenodeTableInsert = new TreeNode("INSERT Stored Procedure", 2, 2);
						treenodeTable.Nodes.Add(treenodeTableUPDATE);
						treenodeTable.Nodes.Add(treenodeTableInsert);
						e.Node.Nodes.Add(treenodeTable);
					}
					
					this.Cursor = Cursors.Default;
					statbarpnlMain.Text = "Tables listed...";
				}
				catch
				{
					this.Cursor = Cursors.Default;
					statbarpnlMain.Text = "Problem listing Tables...";

					MessageBox.Show("Problem connecting to database. Cannot list tables, reconnect advised.", this.Text);
				}
			}
		}

		private void tvwServerExplorer_AfterSelect(object sender, System.Windows.Forms.TreeViewEventArgs e)
		{
			TreeNode tnodeSelected = (TreeNode)e.Node;
			
			if (tnodeSelected.ImageIndex == 2)
			{
				this.Cursor = Cursors.WaitCursor;
				statbarpnlMain.Text = "Generating Stored Procedure, please wait...";
				// SP selected, generate SP
				TreeNode tnodeTable = (TreeNode)tnodeSelected.Parent;				
				dmoMain.Table = tnodeTable.Text;

				StoredProcedureTypes spType = new StoredProcedureTypes();
				if (tnodeSelected.Text.IndexOf("INSERT") != -1)
					spType = StoredProcedureTypes.INSERT;
				else
					spType = StoredProcedureTypes.UPDATE;
													
				dmoMain.Connect();
				txtGeneratedCode.Text = new StoredProcedure().Generate(spType, dmoMain.Fields, dmoMain.Table);								
				dmoMain.DisConnect();
				
				this.Cursor = Cursors.Default;
				statbarpnlMain.Text = "Stored Procedure generated...";

				txtGeneratedCode.Focus();
				txtGeneratedCode.SelectAll();
			}
		}

		private void txtUser_Leave(object sender, System.EventArgs e)
		{
			if (txtUser.Text == "")
				txtUser.Text = "User";
			else
				dmoMain.UserName = txtUser.Text;
		}

		private void txtPassword_Leave(object sender, System.EventArgs e)
		{
			if (txtPassword.Text == "")
			{
				txtPassword.Text = "Password";
				char chResetPassword = (char)0;
				txtPassword.PasswordChar = chResetPassword;
				dmoMain.Password = "";
			}
			else
				dmoMain.Password = txtPassword.Text;
		}

		private void selServers_Leave(object sender, System.EventArgs e)
		{			
			if (selServers.Text == "")
				selServers.Text = "Select server";
			else
				dmoMain.ServerName = selServers.Text;
		}		
	}
}