SPGen - Stored Procedure Generator

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();
		}
	}
}