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

SPGen - Stored Procedure Generator

, 12 Dec 2002
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.Text;

namespace Bluegrass.Data
{	
	/// <summary>
	/// Supported Stored Procedure types
	/// </summary>
	public enum StoredProcedureTypes
	{
		UPDATE,
		INSERT
	}

	/// <summary>
	/// Stored Procedure Helper class
	/// </summary>
	public class StoredProcedure
	{		
		/// <summary>
		/// Generates code for an UPDATE or INSERT Stored Procedure
		/// </summary>
		/// <param name="sptypeGenerate">The type of SP to generate, INSERT or UPDATE</param>
		/// <param name="colsFields">A SQLDMO.Columns collection</param>
		/// <returns>The SP code</returns>
		public string Generate(StoredProcedureTypes sptypeGenerate, SQLDMO.Columns colsFields, string sTableName)
		{
			StringBuilder sGeneratedCode = new StringBuilder();
			StringBuilder sParamDeclaration = new StringBuilder();
			StringBuilder sBody = new StringBuilder();			
			StringBuilder sINSERTValues = new StringBuilder();

			// Setup SP code, begining is the same no matter the type
			sGeneratedCode.AppendFormat("CREATE PROCEDURE {0}_{1}", new string[]{sTableName, sptypeGenerate.ToString()});			
			sGeneratedCode.Append(Environment.NewLine);

			// Setup body code, different for UPDATE and INSERT
			switch (sptypeGenerate)
			{
				case StoredProcedureTypes.INSERT:
					sBody.AppendFormat("INSERT INTO [{0}] (", sTableName);
					sBody.Append(Environment.NewLine);


					sINSERTValues.Append("VALUES (");
					sINSERTValues.Append(Environment.NewLine);
					break;
				
				case StoredProcedureTypes.UPDATE:
					sBody.AppendFormat("UPDATE [{0}]", sTableName);					
					sBody.Append(Environment.NewLine);
					sBody.Append("SET");
					sBody.Append(Environment.NewLine);
					break;
			}
			 						
			foreach (SQLDMO.Column colCurrent in colsFields)
			{
				// Param Declaration construction
				sParamDeclaration.AppendFormat("    @{0} {1}", new string[]{colCurrent.Name, colCurrent.Datatype});				
								
				// Only binary, char, nchar, nvarchar, varbinary and varchar may have their length declared								
				if (
					colCurrent.Datatype == "binary" || 
					colCurrent.Datatype == "char" || 
					colCurrent.Datatype == "nchar" || 
					colCurrent.Datatype == "nvarchar" || 
					colCurrent.Datatype == "varbinary" || 
					colCurrent.Datatype == "varchar")
					sParamDeclaration.AppendFormat("({0})", colCurrent.Length);
				
				sParamDeclaration.Append(",");
				sParamDeclaration.Append(Environment.NewLine);

				// Body construction, different for INSERT and UPDATE
				switch (sptypeGenerate)
				{
					case StoredProcedureTypes.INSERT:						
						sINSERTValues.AppendFormat("    @{0},", colCurrent.Name);						
						sINSERTValues.Append(Environment.NewLine);

						sBody.AppendFormat("    {0},", colCurrent.Name);						
						sBody.Append(Environment.NewLine);
						break;

					case StoredProcedureTypes.UPDATE:
						sBody.AppendFormat("    {0} = @{0},", new string[]{colCurrent.Name, });											
						sBody.Append(Environment.NewLine);
						break;
				}
			}

			// Now stitch the body parts together into the SP whole			
			sGeneratedCode.Append(sParamDeclaration.Remove(sParamDeclaration.Length - 3, 3));			
			sGeneratedCode.Append(Environment.NewLine);
			sGeneratedCode.Append("AS");
			sGeneratedCode.Append(Environment.NewLine);
			sGeneratedCode.Append(sBody.Remove(sBody.Length -3, 3));			
			if (sptypeGenerate == StoredProcedureTypes.INSERT)
			{
				sGeneratedCode.Append(")");
				sGeneratedCode.Append(Environment.NewLine);
				sGeneratedCode.Append(sINSERTValues.Remove(sINSERTValues.Length - 3, 3));
				sGeneratedCode.Append(")");				
			}
			sGeneratedCode.Append(Environment.NewLine);
			sGeneratedCode.Append("GO");
					
			return sGeneratedCode.ToString();
		}
	}
}

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
Web01 | 2.8.140905.1 | Last Updated 13 Dec 2002
Article Copyright 2002 by Paul Watson
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid