Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Utilities » Downloads
 
Add your own
alternative version

SPGen - Stored Procedure Generator

, 12 Dec 2002 MIT
A simple app which generates INSERT and UPDATE SQL Stored Procedure code
spgen_exe.zip
SPGen.exe
Interop.SQLDMO.dll
spgen_src.zip
app.ico
table.jpg
database.jpg
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;
		}		
	}
}

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 MIT License

Share

About the Author

Paul Watson
Web Developer TSSG
Ireland Ireland
Paul is an internet developer living in Dublin, Ireland though home is still South Africa.
 
He believes in self-taught programming skills, standards based thinking and in the power of the common man.
 
Oh, and he loves photography. Make sure you don't get caught in the corner of a party when he has that photographic gleam in his eye. And if you were wondering about that bed-head photograph, wonder no longer...
Follow on   Twitter

| Advertise | Privacy | Mobile
Web02 | 2.8.141015.1 | Last Updated 13 Dec 2002
Article Copyright 2002 by Paul Watson
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid