Experimenting Custom Build Providers

using System;
using System.Collections.Generic;
using System.Text;

namespace ObjectMapper
{
	class SqlStoredProceduresGenerator : StoredProceduresGenerator
	{
		public SqlStoredProceduresGenerator(ClassMapper c)
			: base(c)
		{
		}

		public override string CreateInsertStoredProcedure()
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder insertColumnsBuilder = new StringBuilder();
			StringBuilder insertValuesBuilder = new StringBuilder();

			string procedureName = GetObjectName("Insert");

			if (base.ClassMapper.DropExistingStoredProcedures)
				sb.Append(this.GetDropObjectCode(procedureName));

			List<Property> properties = base.ClassMapper.Properties;
			int propertyCount = properties.Count;

			sb.AppendFormat("CREATE PROCEDURE {0}\r\n", procedureName);

			bool hasIdentityColumn = false;
			for (int i = 0; i < propertyCount; i ++)
			{
				Property p = properties[i];
				if (!p.IsIdentity)
				{
					sb.AppendFormat("@{0} {1}{2},\r\n", p.Column,
						p.SqlLength == 0 ? p.SqlType : string.Format("{0}({1})", p.SqlType, p.SqlLength),
						p.IsPrimaryKey ? "" : " = null");

					insertColumnsBuilder.AppendFormat("[{0}],\r\n", p.Column);
					insertValuesBuilder.AppendFormat("@{0},\r\n", p.Column);
				}
				else
					hasIdentityColumn = true;
			}

			string beginText = sb.ToString();
			string columnText = insertColumnsBuilder.ToString();
			string valuesText = insertValuesBuilder.ToString();

			string procedureDefinition =  string.Format(@"{0} AS
INSERT INTO [{1}]
({2}) 
VALUES 
({3})
{4}
GO
", beginText.Substring(0, beginText.Length - 3), base.ClassMapper.TableName, columnText.Substring(0, columnText.Length - 3),
   valuesText.Substring(0, valuesText.Length - 3), hasIdentityColumn ? "SELECT SCOPE_IDENTITY()" : "");
			return procedureDefinition;
		}

		public override string CreateUpdateStoredProcedure()
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder updateColumnsBuilder = new StringBuilder();
			StringBuilder whereBuilder = new StringBuilder();
			string procedureName = GetObjectName("Update");

			if (base.ClassMapper.DropExistingStoredProcedures)
				sb.Append(this.GetDropObjectCode(procedureName));

			sb.AppendFormat("CREATE PROCEDURE {0}\r\n", procedureName);

			List<Property> properties = base.ClassMapper.Properties;
			int propertyCount = properties.Count;

			for (int i = 0; i < propertyCount; i++)
			{
				Property p = properties[i];
				sb.AppendFormat("@{0} {1}{2},\r\n", p.Column,
					p.SqlLength == 0 ? p.SqlType : string.Format("{0}({1})", p.SqlType, p.SqlLength),
					p.IsPrimaryKey ? "" : " = null");


				if (!p.IsPrimaryKey)
					updateColumnsBuilder.AppendFormat("[{0}] = @{0},\r\n", p.Column);
				else
					whereBuilder.AppendFormat("[{0}] = @{0} AND ", p.Column);
			}

			string beginText = sb.ToString();
			string setValuesText = updateColumnsBuilder.ToString();
			string whereClauseText = whereBuilder.ToString();
			string procedureDefinition = string.Format(@"{0} AS
UPDATE [{1}]
SET
{2}
WHERE {3}
GO
", beginText.Substring(0, beginText.Length - 3), base.ClassMapper.TableName, setValuesText.Substring(0, setValuesText.Length - 3),
   whereClauseText.Substring(0, whereClauseText.Length - 5));
			return procedureDefinition;
		}


		public override string CreateSelectStoredProcedure()
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder whereBuilder = new StringBuilder();
			
			string procedureName = GetObjectName("Select");

			if (base.ClassMapper.DropExistingStoredProcedures)
				sb.Append(this.GetDropObjectCode(procedureName));

			List<Property> properties = base.ClassMapper.Properties;
			int propertyCount = properties.Count;

			sb.AppendFormat("CREATE PROCEDURE {0}\r\n", procedureName);

			for (int i = 0; i < propertyCount; i++)
			{
				Property p = properties[i];
				if (p.IsPrimaryKey)
				{
					sb.AppendFormat("@{0} {1},\r\n", p.Column,
						p.SqlLength == 0 ? p.SqlType : string.Format("{0}({1})", p.SqlType, p.SqlLength));
					whereBuilder.AppendFormat("[{0}] = @{0} AND ", p.Column);
				}
			}
			string beginText = sb.ToString();
			string whereClauseText = whereBuilder.ToString();
			return string.Format("{0} AS \r\nSELECT * FROM [{1}]\r\nWHERE\r\n{2}\r\nGO\r\n", beginText.Substring(0, beginText.Length - 3),
				base.ClassMapper.TableName, whereClauseText.Substring(0, whereClauseText.Length - 5));
		}

		public override string CreateDeleteStoredProcedure()
		{
			StringBuilder sb = new StringBuilder();
			StringBuilder whereBuilder = new StringBuilder();

			string procedureName = GetObjectName("Select");

			if (base.ClassMapper.DropExistingStoredProcedures)
				sb.Append(this.GetDropObjectCode(procedureName));

			List<Property> properties = base.ClassMapper.Properties;
			int propertyCount = properties.Count;

			sb.AppendFormat("CREATE PROCEDURE {0}\r\n", procedureName);

			for (int i = 0; i < propertyCount; i++)
			{
				Property p = properties[i];
				if (p.IsPrimaryKey)
				{
					sb.AppendFormat("@{0} {1},\r\n", p.Column,
						p.SqlLength == 0 ? p.SqlType : string.Format("{0}({1})", p.SqlType, p.SqlLength));
					whereBuilder.AppendFormat("[{0}] = @{0} AND ", p.Column);
				}
			}
			string beginText = sb.ToString();
			string whereClauseText = whereBuilder.ToString();
			return string.Format("{0} AS \r\nDELETE FROM [{1}]\r\nWHERE\r\n{2}\r\nGO\r\n", beginText.Substring(0, beginText.Length - 3),
				base.ClassMapper.TableName,whereClauseText.Substring(0, whereClauseText.Length - 5));
		}


		public override string CreateSelectAllStoredProcedure()
		{
			StringBuilder sb = new StringBuilder();
			string procedureName = GetObjectName("SelectAll");

			if (base.ClassMapper.DropExistingStoredProcedures)
				sb.Append(this.GetDropObjectCode(procedureName));

			List<Property> properties = base.ClassMapper.Properties;
			int propertyCount = properties.Count;

			sb.AppendFormat("CREATE PROCEDURE {0} AS\r\nSELECT * FROM [{1}]\r\nGO\r\n", procedureName, base.ClassMapper.TableName);

			return sb.ToString();
		}

		public override string GetDropObjectCode(string objectName)
		{
			return string.Format("if exists (select * from dbo.sysobjects where id = object_id(N'[{0}]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure {0}\r\nGo\r\n",
				objectName);
		}

		private string GetObjectName(string action)
		{
			return string.Format("{0}{1}_{2}", base.ClassMapper.SqlStoredProceduresPrefix, base.ClassMapper.TableName, action);
		}
	}
}