Stored Procedure Invocation Code Generator for VB, C# and JScript.NET

//================================================================================
//
//  Copyright (c) objectnation GmbH
//
//================================================================================
//
//  Author          simon.wilson
//  Creation Date   12.09.2002
//  Creation Time   18:28:24
//  IDE Version     Microsoft Development Environment Enterprise Edition 7.00
//  OS Version      Microsoft Windows NT 5.1.2600.0
//
//================================================================================

#region Copyright � objectnation GmbH

// This software is provided 'as-is'. While the greatest care has been taken to 
// ensure that the software functions correctly, no guarantee can be made to this effect.
//
// objectnation grants permission to anyone to use this software for any purpose as long as 
// it is in compliance with the following restrictions:
//
// 1.	This software is the intellectual property and copyright of objectnation GmbH. 
//    Under no circumstances may the origin of the software be misrepresented.
//
// 2.	Should this software be used in the development of a commercial product, 
//    then an acknowledgement in that product's documentation is a requirement. 
//    The following text should be used:
//
//    This product contains code generated by objectnation SP/Invoke, 
//    copyright � objectnation GmbH (http://www.objectnation.com).
//
// 3.	The software may not be redistributed without the express written permission of 
//    the copyright holder, namely objectnation GmbH, Switzerland.

#endregion

using System;
using System.CodeDom;
using System.CodeDom.Compiler;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;

namespace Objectnation.SPInvoke
{
	public class StoredProcedureTypeGenerator : TypeGeneratorWithSource
	{
		CodeTypeReference _rowType;
		CodeTypeReference _collectionType;
		CodeExpression _factoryExpression;
		RowTypeGenerator _rowTypeGenerator;

		public StoredProcedureTypeGenerator(
			StoredProcedure storedProc,
			CodeDomProvider provider,
			string typeName,
			TypeAttributes visibility) : base(storedProc, provider, typeName, visibility)
		{
			if(storedProc.HasResultSet)
				throw new ApplicationException("Stored procedure " + storedProc.Name + " has a result set.");
		}

		//============================================================================

		public StoredProcedureTypeGenerator(
			StoredProcedure storedProc,
			CodeDomProvider provider,
			string typeName,
			TypeAttributes visibility,
			RowTypeGenerator rowTypeGenerator) : base(storedProc, provider, typeName, visibility)
		{
			if(rowTypeGenerator == null)
				throw new ArgumentNullException("rowTypeGenerator");

			_rowTypeGenerator = rowTypeGenerator;

			_rowType = new CodeTypeReference(RowTypeGenerator.TypeName);
			_collectionType = new CodeTypeReference(_rowTypeGenerator.CollectionTypeName);

			_factoryExpression = new CodeObjectCreateExpression(
				_rowTypeGenerator.TypeName, new CodeVariableReferenceExpression("reader"));
		}

		//============================================================================

		public StoredProcedureTypeGenerator(
			StoredProcedure storedProc,
			CodeDomProvider provider,
			string typeName,
			TypeAttributes visibility,
			CodeTypeReference rowType,
			CodeTypeReference collectionType) : base(storedProc, provider, typeName, visibility)
		{
			if(rowType == null)
				throw new ArgumentNullException("rowType");
			else if(collectionType == null)
				throw new ArgumentNullException("collectionType");

			_rowType = rowType;
			_collectionType = collectionType;

			_factoryExpression = new CodeObjectCreateExpression(
				_rowType, new CodeVariableReferenceExpression("reader"));			
		}

		//============================================================================

		public StoredProcedureTypeGenerator(
			StoredProcedure storedProc,
			CodeDomProvider provider,
			string typeName,
			TypeAttributes visibility,
			CodeTypeReference collectionType,
			CodeSnippetExpression factoryExpression) : base(storedProc, provider, typeName, visibility)
		{
			if(collectionType == null)
				throw new ArgumentNullException("collectionType");
			else if(factoryExpression == null)
				throw new ArgumentNullException("factoryExpression");

			_collectionType = collectionType;
			_factoryExpression = factoryExpression;
		}

		//============================================================================

		public StoredProcedureTypeGenerator() : base()
		{
		}

		//============================================================================

		public CodeExpression FactoryExpression
		{
			get { return _factoryExpression; }
			set { _factoryExpression = value; }
		}

		//============================================================================

		public RowTypeGenerator RowTypeGenerator
		{
			get { return _rowTypeGenerator; }
			set { _rowTypeGenerator = value; }
		}

		//============================================================================

		public override CodeTypeDeclarationCollection Generate()
		{
			CodeTypeDeclarationCollection types = new CodeTypeDeclarationCollection();

			// create a new class for the stored procedure

			CodeTypeDeclaration type = new CodeTypeDeclaration(TypeName);
			type.TypeAttributes = Visibility;

			// add StoredProcedure attribute:
			//
			// [ StoredProcedure("<name>") ]

			type.CustomAttributes.Add(new CodeAttributeDeclaration("StoredProcedure", 
				new CodeAttributeArgument [] { new CodeAttributeArgument(new CodePrimitiveExpression(Source.Name)) } ));

			// add comment pre-amble

			type.Comments.Add(new CodeCommentStatement("<summary>Wrapper class for stored procedure [" + Source.Name + "]</summary>", true));

			if(Source.Parameters.Count > 0)
			{
				type.Comments.Add(new CodeCommentStatement("<remarks>", true));
				type.Comments.Add(new CodeCommentStatement("Original stored procedure parameters:", true));
				type.Comments.Add(new CodeCommentStatement("<list type=\"table\">", true));
				type.Comments.Add(new CodeCommentStatement("<listheader><term>Name</term><description>Type</description></listheader>", true));

				foreach(StoredProcedureParameter param in Source.Parameters)
				{
					type.Comments.Add(new CodeCommentStatement("<item><term>" + param.Name + "</term><description>" + param.RawSqlTypeName + "</description></item>", true));
				}

				type.Comments.Add(new CodeCommentStatement("</list>", true));
				type.Comments.Add(new CodeCommentStatement("</remarks>", true));
			}

			// if provider supports nested types then add to sp class. otherwise
			// create at namespace level with name of the sp class + Result

			if(IsResultTypeNested)
				type.Members.Add(GenerateResultType());
			else
				types.Add(GenerateResultType());

			// add events

			ICodeGenerator generator = Provider.CreateGenerator();

			if(generator.Supports(GeneratorSupport.DeclareEvents))
				AddEvents(type.Members);

			// add methods

			type.Members.Add(GenerateInvokeMethod());
			type.Members.Add(GenerateConnectionOverride());
			type.Members.Add(GenerateTransactionOverride());

			if(_rowTypeGenerator != null)
			{
				// does the provider support nested types?

				if(!generator.Supports(GeneratorSupport.NestedTypes))
				{
					throw new ApplicationException(
						Provider.GetType().Name + " does not support nested types. Move nested rowClass elements out of storedProcedureClass element");
				}

				CodeTypeDeclarationCollection nestedTypes = _rowTypeGenerator.Generate();

				foreach(CodeTypeDeclaration nestedType in nestedTypes)
					type.Members.Add(nestedType);
			}

			types.Add(type);

			return types;
		}
	
		//============================================================================

		CodeTypeDeclaration GenerateResultType()
		{
			CodeTypeDeclaration type = new CodeTypeDeclaration(ResultTypeName);

			type.Comments.Add(new CodeCommentStatement("<summary>Result type for " + TypeName + ".</summary>", true));
			type.TypeAttributes = TypeAttributes.Public;

			// private int _returnValue;

			CodeMemberField field = new CodeMemberField(typeof(int), "_returnValue");
			field.Attributes = MemberAttributes.Private;
			field.InitExpression = new CodePrimitiveExpression(0);

			type.Members.Add(field);

			// private int _rowsAffected;

			field = new CodeMemberField(typeof(int), "_rowsAffected");
			field.Attributes = MemberAttributes.Private;
			field.InitExpression = new CodePrimitiveExpression(-1);

			type.Members.Add(field);

			// public int ReturnValue { get { return _returnValue; } }

			CodeMemberProperty prop = new CodeMemberProperty();

			prop.Comments.Add(new CodeCommentStatement("<summary>Returns the stored procedure's return value</summary>", true));
			prop.Type = new CodeTypeReference(typeof(int));
			prop.Attributes = MemberAttributes.Public | MemberAttributes.Final;
			prop.Name = "ReturnValue";
			prop.GetStatements.Add
			(
				new CodeMethodReturnStatement
				(
					new CodeFieldReferenceExpression
					(
						new CodeThisReferenceExpression(), 
						"_returnValue"
					)
				)
			);

			type.Members.Add(prop);

			// public int ReturnValue { get { return _returnValue; } }

			prop = new CodeMemberProperty();

			prop.Comments.Add(new CodeCommentStatement("<summary>Returns the number of rows affected by the stored procedure</summary>", true));
			prop.Type = new CodeTypeReference(typeof(int));
			prop.Attributes = MemberAttributes.Public | MemberAttributes.Final;
			prop.Name = "RowsAffected";
			prop.GetStatements.Add
			(
				new CodeMethodReturnStatement
				(
					new CodeFieldReferenceExpression
					(
						new CodeThisReferenceExpression(), 
						"_rowsAffected"
					)
				)
			);

			type.Members.Add(prop);

			// initialization constructor

			CodeConstructor ctor = new CodeConstructor();

			ctor.Comments.Add(new CodeCommentStatement("<summary>Initialization constructor</summary>", true));
			ctor.Attributes = MemberAttributes.Public;
			ctor.Parameters.Add(new CodeParameterDeclarationExpression(typeof(int), "returnValue"));
			ctor.Parameters.Add(new CodeParameterDeclarationExpression(typeof(int), "rowsAffected"));
			ctor.Statements.Add
			(
				new CodeAssignStatement
				(
					new CodeFieldReferenceExpression
					(
						new CodeThisReferenceExpression(), 
						"_returnValue"
					),
					new CodeArgumentReferenceExpression("returnValue")
				)
			);

			ctor.Statements.Add
			(
				new CodeAssignStatement
				(
					new CodeFieldReferenceExpression
					(
						new CodeThisReferenceExpression(), 
						"_rowsAffected"
					),
					new CodeArgumentReferenceExpression("rowsAffected")
				)
			);

			if(Source.HasResultSet)
			{
				// private <CollectionType> _rows;

				field = new CodeMemberField(_collectionType, "_rows");
				field.Attributes = MemberAttributes.Private;
				field.InitExpression = new CodePrimitiveExpression(null);

				type.Members.Add(field);

				ctor.Parameters.Add(new CodeParameterDeclarationExpression(_collectionType, "rows"));
				ctor.Statements.Add
				(
					new CodeAssignStatement
					(
						new CodeFieldReferenceExpression
						(
							new CodeThisReferenceExpression(), 
							"_rows"
						),
						new CodeArgumentReferenceExpression("rows")
					)
				);

				// public <CollectionType> Rows { get { return _rows; } }

				prop = new CodeMemberProperty();

				prop.Comments.Add(new CodeCommentStatement("<summary>The rows which were returned by the stored procedure</summary>", true));
				prop.Type = _collectionType;
				prop.Attributes = MemberAttributes.Public | MemberAttributes.Final;
				prop.Name = "Rows";
				prop.GetStatements.Add
				(
					new CodeMethodReturnStatement
					(
						new CodeFieldReferenceExpression
						(
							new CodeThisReferenceExpression(), 
							"_rows"
						)
					)
				);

				type.Members.Add(prop);
			}

			type.Members.Add(ctor);

			return type;
		}

		//============================================================================

		void AppendParameterList(CodeParameterDeclarationExpressionCollection parameters)
		{
			foreach(StoredProcedureParameter param in Source.Parameters)
			{
				string paramName = param.CreateCodeCompatibleName(Provider);

				FieldDirection paramDir = FieldDirection.In;
				CodeParameterDeclarationExpression paramDecl = new CodeParameterDeclarationExpression(param.Type.ClientType, paramName);

				if(param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output)
				{ 
					// OUTPUT parameters can also be in/out so always use ref

					paramDir = FieldDirection.Ref;
				}

				paramDecl.Direction = paramDir;

				parameters.Add(paramDecl);
			}
		}
	
		// ===========================================================================

		CodeMemberMethod GenerateInvokeMethod()
		{
			CodeMemberMethod invokeMethod = new CodeMemberMethod();

			invokeMethod.Comments.Add(new CodeCommentStatement("<summary>Invokes [" + Source.Name + "] on a connection with an optional local transaction (specify null if the connection does not have an active local transaction).</summary>", true));
			invokeMethod.ReturnType = new CodeTypeReference("Result");
			invokeMethod.Name = "Invoke";

			// 2003-04-28
			//
			// make Invoke(IDbConnection, IDbTransaction, ...) override public
			// (suggested by Efran Cobisi)

			invokeMethod.Attributes = MemberAttributes.Static | MemberAttributes.Public;
		
			invokeMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(IDbConnection), "connection"));
			invokeMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(IDbTransaction), "transaction"));

			AppendParameterList(invokeMethod.Parameters);

			// construct a new IDbCommand reference

			// 2003-05-01, simon.wilson
			//
			// intialize to null and assign to IDbConnection.CreateCommand return value within try block

			invokeMethod.Statements.Add(new CodeVariableDeclarationStatement(typeof(IDbCommand), "cmd", new CodePrimitiveExpression(null)));

			CodeVariableReferenceExpression cmdVariable = new CodeVariableReferenceExpression("cmd");

			// add a try-catch-finally clause for command object

			CodeTryCatchFinallyStatement cmdTry = new CodeTryCatchFinallyStatement();
			invokeMethod.Statements.Add(cmdTry);

			// if(cmd != null) { cmd.Dispose(); }

			CodeConditionStatement ifCmdNotNull = new CodeConditionStatement();
			ifCmdNotNull.Condition = new CodeBinaryOperatorExpression(cmdVariable, CodeBinaryOperatorType.IdentityInequality, new CodePrimitiveExpression(null));
			ifCmdNotNull.TrueStatements.Add(new CodeMethodInvokeExpression(cmdVariable, "Dispose"));

			cmdTry.FinallyStatements.Add(ifCmdNotNull);

			// cmd = connection.CreateCommand();

			cmdTry.TryStatements.Add(new CodeAssignStatement(cmdVariable, new CodeMethodInvokeExpression(new CodeArgumentReferenceExpression("connection"), "CreateCommand")));

			// add if statement to assign transaction to command object if not null

			CodeStatement assignTransactionStatement = new CodeAssignStatement(
				new CodePropertyReferenceExpression(cmdVariable, "Transaction"),
				new CodeVariableReferenceExpression("transaction"));

			CodeConditionStatement conditionalStatement = new CodeConditionStatement(
				new CodeBinaryOperatorExpression(new CodeVariableReferenceExpression("transaction"), CodeBinaryOperatorType.IdentityInequality, new CodePrimitiveExpression(null)),
				assignTransactionStatement);

			cmdTry.TryStatements.Add(conditionalStatement);

			// assign command-type

			CodePropertyReferenceExpression cmdType = new CodePropertyReferenceExpression(cmdVariable, "CommandType");
			CodeFieldReferenceExpression enumRef = new CodeFieldReferenceExpression(new CodeTypeReferenceExpression("CommandType"), "StoredProcedure");

			cmdTry.TryStatements.Add(new CodeAssignStatement(cmdType, enumRef));
			cmdTry.TryStatements.Add(new CodeAssignStatement(
				new CodePropertyReferenceExpression(cmdVariable, "CommandText"), new CodePrimitiveExpression(Source.Name)));

			// add return value parameter

			cmdTry.TryStatements.Add(new CodeCommentStatement("Prepare @RETURN_VALUE parameter"));
			CodeVariableDeclarationStatement returnValueParam = new CodeVariableDeclarationStatement(typeof(IDbDataParameter), "returnValueParam", new CodeMethodInvokeExpression(cmdVariable, "CreateParameter"));
			cmdTry.TryStatements.Add(returnValueParam);

			CodeVariableReferenceExpression returnValueParamVariable = new CodeVariableReferenceExpression("returnValueParam");

			// returnValueParam.ParameterName = @<returnValue>;

			cmdTry.TryStatements.Add(new CodeAssignStatement(
				new CodePropertyReferenceExpression(returnValueParamVariable, "ParameterName"), new CodePrimitiveExpression(Source.ReturnValue.Name)));

			// returnValueParam.DbType = System.Data.DbType.Int32;

			cmdTry.TryStatements.Add(new CodeAssignStatement(
				new CodePropertyReferenceExpression(returnValueParamVariable, "DbType"), 
				new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(DbType)), Source.ReturnValue.Type.DbType.ToString())));

			// returnValueParam.Direction = ParameterDirection.ReturnValue;

			CodeExpression directionAssignmentLeft = new CodePropertyReferenceExpression(returnValueParamVariable, "Direction");
			CodeExpression directionAssignmentRight = new CodeFieldReferenceExpression(new CodeTypeReferenceExpression("ParameterDirection"), Source.ReturnValue.Direction.ToString());

			cmdTry.TryStatements.Add(new CodeAssignStatement(directionAssignmentLeft, directionAssignmentRight));

			// cmd.Parameters.Add(returnValueParam);

			cmdTry.TryStatements.Add(new CodeMethodInvokeExpression(
				new CodePropertyReferenceExpression(cmdVariable, "Parameters"),
				"Add", 
				new CodeExpression [] { returnValueParamVariable } ));

			// add parameters

			foreach(StoredProcedureParameter param in Source.Parameters)
			{
				cmdTry.TryStatements.Add(new CodeCommentStatement("Prepare " + param.Name + " parameter"));

				string paramName = param.CreateCodeCompatibleName(Provider);

				// IDbParameter param = cmd.CreateParameter();

				CodeVariableDeclarationStatement sqlParamInstantiation = new CodeVariableDeclarationStatement(
					typeof(IDbDataParameter),
					paramName + "Param",
					new CodeMethodInvokeExpression(cmdVariable, "CreateParameter"));

				cmdTry.TryStatements.Add(sqlParamInstantiation);

				CodeVariableReferenceExpression paramVariable = new CodeVariableReferenceExpression(paramName + "Param");

				// param.ParameterName = @<name>;

				cmdTry.TryStatements.Add(new CodeAssignStatement(
					new CodePropertyReferenceExpression(paramVariable, "ParameterName"), new CodePrimitiveExpression(param.Name)));

				// param.DbType = System.Data.DbType.<type>;

				cmdTry.TryStatements.Add(new CodeAssignStatement(
					new CodePropertyReferenceExpression(paramVariable, "DbType"), 
					new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(DbType)), param.Type.DbType.ToString())));

				// param.Size = <size>;

				cmdTry.TryStatements.Add(new CodeAssignStatement(
					new CodePropertyReferenceExpression(paramVariable, "Size"), 
					new CodePrimitiveExpression(param.Size)));

				// param.Direction = <direction>;

				cmdTry.TryStatements.Add(new CodeAssignStatement(
					new CodePropertyReferenceExpression(paramVariable, "Direction"), 
					new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(ParameterDirection)), param.Direction.ToString())));

				// cmd.Parameters.Add(param);

				cmdTry.TryStatements.Add(new CodeMethodInvokeExpression(
					new CodePropertyReferenceExpression(cmdVariable, "Parameters"),
					"Add", 
					new CodeExpression [] { paramVariable } ));

				// assign value

				CodeConditionStatement ifNull = new CodeConditionStatement
				(
					new CodeBinaryOperatorExpression
					(
						new CodePropertyReferenceExpression
						(
							new CodeArgumentReferenceExpression(paramName),
							"IsNull"
						),
						CodeBinaryOperatorType.ValueEquality,
						new CodePrimitiveExpression(true)
					)
				);

				// <param>Param.Value = System.DBNull.Value;

				ifNull.TrueStatements.Add
				(
					new CodeAssignStatement
					(
						new CodePropertyReferenceExpression
						(
							new CodeVariableReferenceExpression(paramName + "Param"),
							"Value"
						),
						new CodeFieldReferenceExpression	
						(
							new CodeTypeReferenceExpression(typeof(DBNull)),
							"Value"
						)
					)
				);

				// <param>Param.Value = <param>.Value;

				ifNull.FalseStatements.Add
				(
					new CodeAssignStatement
					(
						new CodePropertyReferenceExpression
						(
							new CodeVariableReferenceExpression(paramName + "Param"),
							"Value"
						),
						new CodeFieldReferenceExpression	
						(
							new CodeArgumentReferenceExpression(paramName),
							"Value"
						)
					)
				);

				cmdTry.TryStatements.Add(ifNull);

				// if byref then specify in/out for direction

				if(param.Direction != ParameterDirection.Input)
				{
					CodeAssignStatement assignment = new CodeAssignStatement();

					// xyzParam.Direction

					assignment.Left = new CodePropertyReferenceExpression
					(
						new CodeVariableReferenceExpression(paramName + "Param"), 
						"Direction"
					);

					// ParameterDirection.InOut

					assignment.Right = new CodeFieldReferenceExpression
					(
						new CodeTypeReferenceExpression
						(
							typeof(ParameterDirection)
						), 
						ParameterDirection.InputOutput.ToString()
					);

					cmdTry.TryStatements.Add(assignment);
				}
			}

			ICodeGenerator generator = Provider.CreateGenerator();

			if(generator.Supports(GeneratorSupport.DeclareEvents))
			{
				// Events.FirePreExecute(typeof(<class>), connection, transaction, cmd, typeof(<class>));

				cmdTry.TryStatements.Add(new CodeCommentStatement("Fire PreExecute event to registered handlers"));
				cmdTry.TryStatements.Add(new CodeMethodInvokeExpression(
					new CodePropertyReferenceExpression(null, "Events"),
					"FirePreExecute", 
					new CodeTypeOfExpression(TypeName),
					new CodeArgumentReferenceExpression("connection"),
					new CodeArgumentReferenceExpression("transaction"),
					cmdVariable,
					new CodeTypeOfExpression(TypeName)));
			}

			if(Source.HasResultSet)
			{
				// execute reader

				cmdTry.TryStatements.Add(new CodeCommentStatement("Execute command and extract rows from result set"));
				cmdTry.TryStatements.Add
				(
					new CodeVariableDeclarationStatement
					(
						typeof(IDataReader),
						"reader",
						new CodePrimitiveExpression(null)
					)
				);

				// instantiate an object of type _collectionType for rows

				cmdTry.TryStatements.Add
				(
					new CodeVariableDeclarationStatement
					(
						_collectionType,
						"rows",
						new CodeObjectCreateExpression
						(
							_collectionType,
							new CodeExpression [0]
						)
					)
				);

				CodeVariableReferenceExpression readerVariable = new CodeVariableReferenceExpression("reader");

				// add a new try-finally block for the reader to close reader in all cases

				CodeTryCatchFinallyStatement readerTry = new CodeTryCatchFinallyStatement();
				cmdTry.TryStatements.Add(readerTry);

				// execute reader and assign to readerVariable

				readerTry.TryStatements.Add(new CodeAssignStatement(readerVariable, new CodeMethodInvokeExpression(cmdVariable, "ExecuteReader", 
					new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(CommandBehavior)), CommandBehavior.Default.ToString()))));

				// 2003-05-01, simon.wilson
				// 
				// reader.Close() -> if(reader != null) { reader.Dispose(); }
				//
				// 2003-05-11, simon.wilson
				//
				// reader.Dispose() -> reader.Close() in order that we can access RecordsAffected after closure.

				CodeConditionStatement ifReaderNotNull = new CodeConditionStatement();
				ifReaderNotNull.Condition = new CodeBinaryOperatorExpression(readerVariable, CodeBinaryOperatorType.IdentityInequality, new CodePrimitiveExpression(null));
				ifReaderNotNull.TrueStatements.Add(new CodeMethodInvokeExpression(readerVariable, "Close"));
				
				readerTry.FinallyStatements.Add(ifReaderNotNull);

				// iterate through reader

				CodeIterationStatement iteration = new CodeIterationStatement();

				iteration.InitStatement = new CodeSnippetStatement("");
				iteration.TestExpression = new CodeMethodInvokeExpression(readerVariable, "Read");
				iteration.IncrementStatement = new CodeSnippetStatement("");

				readerTry.TryStatements.Add(iteration);

				// use factory expression to create object and then add it to the collection

				iteration.Statements.Add
				(
					new CodeMethodInvokeExpression
					(
						new CodeVariableReferenceExpression("rows"),
						"Add",
						FactoryExpression
					)
				);
			}
			else
			{
				// invoke stored procedure without data reader

				cmdTry.TryStatements.Add(new CodeCommentStatement("Execute command. The command does not return a result set"));
				cmdTry.TryStatements.Add(new CodeVariableDeclarationStatement(typeof(int), "rowsAffected", new CodeMethodInvokeExpression(cmdVariable, "ExecuteNonQuery")));
			}

			CodeExpression [] parameters = null;

			if(Source.HasResultSet)
			{
				// Result result = new Result ((int) returnValueParam.Value, reader.RecordsAffected, rows);

				parameters = new CodeExpression []
				{
					new CodeCastExpression(Source.ReturnValue.Type.ClrType,
						new CodePropertyReferenceExpression(returnValueParamVariable, "Value")),
					new CodePropertyReferenceExpression(new CodeVariableReferenceExpression("reader"), "RecordsAffected"),
					new CodeVariableReferenceExpression("rows")
				};
			}
			else
			{
				// Result result = new Result ((int) returnValueParam.Value, rowsAffected);

				parameters = new CodeExpression [] 
				{
					new CodeCastExpression(Source.ReturnValue.Type.ClrType,
						new CodePropertyReferenceExpression(returnValueParamVariable, "Value")),
					new CodeVariableReferenceExpression("rowsAffected")
				};
			}

			cmdTry.TryStatements.Add(new CodeVariableDeclarationStatement(
				"Result", "result", new CodeObjectCreateExpression(ResultTypeName, parameters)));

			if(generator.Supports(GeneratorSupport.DeclareEvents))
			{
				// Events.FirePostExecute(typeof(<class>), connection, transaction, cmd, typeof(<class>), returnValue, rowsAffected, result);
				//
				// do this before extraction of OUTPUT parameters in order that handlers
				// can modify out parameters in the command.

				cmdTry.TryStatements.Add(new CodeCommentStatement("Fire PostExecute event to registered handlers"));
				cmdTry.TryStatements.Add(new CodeMethodInvokeExpression(
					new CodePropertyReferenceExpression(null, "Events"),
					"FirePostExecute", 
					new CodeTypeOfExpression(TypeName),
					new CodeArgumentReferenceExpression("connection"),
					new CodeArgumentReferenceExpression("transaction"),
					cmdVariable,
					new CodeTypeOfExpression(TypeName),
					new CodePropertyReferenceExpression(new CodeVariableReferenceExpression("result"), "ReturnValue"),
					new CodePropertyReferenceExpression(new CodeVariableReferenceExpression("result"), "RowsAffected"),
					new CodeVariableReferenceExpression("result")));
			}

			// assign values to in/out parameters

			foreach(StoredProcedureParameter outParam in Source.Parameters)
			{
				if(outParam.Direction == ParameterDirection.Output || outParam.Direction == ParameterDirection.InputOutput)
				{
					string paramName = outParam.CreateCodeCompatibleName(Provider);

					// if(object.ReferenceEquals(<param>Param.Value, System.DBNull.Value))

					CodeConditionStatement ifNull = new CodeConditionStatement();

					ifNull.Condition = new CodeBinaryOperatorExpression(
						new CodeMethodInvokeExpression(new CodeTypeReferenceExpression(typeof(object)), "ReferenceEquals", 
						new CodePropertyReferenceExpression(new CodeVariableReferenceExpression(paramName + "Param"), "Value"),
						new CodeFieldReferenceExpression(new CodeTypeReferenceExpression(typeof(DBNull)), "Value")),
						CodeBinaryOperatorType.ValueEquality,
						new CodePrimitiveExpression(true));

					// <param> = <SqlType>.Null;

					ifNull.TrueStatements.Add
					(
						new CodeAssignStatement
						(
							new CodeArgumentReferenceExpression(paramName),
							new CodeFieldReferenceExpression	
							(
								new CodeTypeReferenceExpression(outParam.Type.ClientType),
								"Null"
							)
						)
					);

					// <param> = new <SqlType>((<ClrType>)<param>Param.Value);

					ifNull.FalseStatements.Add
					(
						new CodeAssignStatement
						(
							new CodeArgumentReferenceExpression(paramName),
							new CodeObjectCreateExpression
							(
								outParam.Type.ClientType,
								new CodeCastExpression
								(
									outParam.Type.ClrType,
									new CodePropertyReferenceExpression
									(
										new CodeVariableReferenceExpression(paramName + "Param"),
										"Value"
									)
								)
							)
						)
					);

					cmdTry.TryStatements.Add(new CodeCommentStatement("Extract OUTPUT value for " + outParam.Name));
					cmdTry.TryStatements.Add(ifNull);
				}
			}

			// add catch block and fire Exception event

			CodeCatchClause catchClause = new CodeCatchClause("e", new CodeTypeReference(typeof(Exception)));

			if(generator.Supports(GeneratorSupport.DeclareEvents))
			{
				// Events.FireException(typeof(<class>), connection, transaction, cmd, typeof(<class>), e);

				catchClause.Statements.Add(new CodeCommentStatement("Fire Exception event to registered handlers"));
				catchClause.Statements.Add(new CodeMethodInvokeExpression(
					new CodePropertyReferenceExpression(null, "Events"),
					"FireException", 
					new CodeTypeOfExpression(TypeName),
					new CodeArgumentReferenceExpression("connection"),
					new CodeArgumentReferenceExpression("transaction"),
					cmdVariable,
					new CodeTypeOfExpression(TypeName),
					new CodeVariableReferenceExpression("e")));
			}

			// throw e;

			catchClause.Statements.Add(new CodeThrowExceptionStatement(new CodeVariableReferenceExpression("e")));
			cmdTry.CatchClauses.Add(catchClause);

			// return result;

			cmdTry.TryStatements.Add(new CodeMethodReturnStatement(new CodeVariableReferenceExpression("result")));

			return invokeMethod;
		}

		// ===========================================================================

		CodeMemberMethod GenerateConnectionOverride()
		{
			// delegate to private Invoke method passing in connection and null transaction

			CodeMemberMethod invokeMethod = new CodeMemberMethod();

			invokeMethod.Comments.Add(new CodeCommentStatement("<summary>Invokes [" + Source.Name + "] on a connection which does not have an active local transaction.</summary>", true));
			invokeMethod.ReturnType = new CodeTypeReference("Result");
			invokeMethod.Name = "Invoke";
			invokeMethod.Attributes = MemberAttributes.Static | MemberAttributes.Public;

			invokeMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(IDbConnection), "connection"));
			AppendParameterList(invokeMethod.Parameters);

			// add RowFactory parameter for delegate-based stored procedures

			CodeExpression [] parameters = new CodeExpression [Source.Parameters.Count + 2];
			int i = 2;

			parameters[0] = new CodeArgumentReferenceExpression("connection");
			parameters[1] = new CodePrimitiveExpression(null);

			foreach(StoredProcedureParameter param in Source.Parameters)
			{
				string paramName = param.CreateCodeCompatibleName(Provider);

				FieldDirection paramDir = FieldDirection.In;
				CodeParameterDeclarationExpression paramDecl = new CodeParameterDeclarationExpression(param.Type.ClientType, paramName);

				if(param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output)
				{ 
					// OUTPUT parameters can also be in/out so always use ref

					paramDir = FieldDirection.Ref;
				}

				parameters[i++] = new CodeDirectionExpression(paramDir, new CodeArgumentReferenceExpression(paramName));
			}

			CodeMethodReferenceExpression method = new CodeMethodReferenceExpression();
			method.MethodName = "Invoke";

			CodeMethodReturnStatement returnStatement = new CodeMethodReturnStatement(
				new CodeMethodInvokeExpression(method, parameters));

			invokeMethod.Statements.Add(returnStatement);

			return invokeMethod;
		}

		// ===========================================================================

		CodeMemberMethod GenerateTransactionOverride()
		{
			// delegate to private Invoke method passing in connection and null transaction

			CodeMemberMethod invokeMethod = new CodeMemberMethod();

			invokeMethod.Comments.Add(new CodeCommentStatement("<summary>Invokes [" + Source.Name + "] within the specified local transaction.</summary>", true));
			invokeMethod.ReturnType = new CodeTypeReference("Result");
			invokeMethod.Name = "Invoke";
			invokeMethod.Attributes = MemberAttributes.Static | MemberAttributes.Public;

			invokeMethod.Parameters.Add(new CodeParameterDeclarationExpression(typeof(IDbTransaction), "transaction"));
			AppendParameterList(invokeMethod.Parameters);

			// add RowFactory parameter for delegate-based stored procedures

			CodeExpression [] parameters = new CodeExpression [Source.Parameters.Count + 2];
			int i = 2;

			parameters[0] = new CodePropertyReferenceExpression(new CodeArgumentReferenceExpression("transaction"), "Connection");
			parameters[1] = new CodeArgumentReferenceExpression("transaction");

			foreach(StoredProcedureParameter param in Source.Parameters)
			{
				string paramName = param.CreateCodeCompatibleName(Provider);

				FieldDirection paramDir = FieldDirection.In;
				CodeParameterDeclarationExpression paramDecl = new CodeParameterDeclarationExpression(param.Type.ClientType, paramName);

				if(param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output)
				{ 
					// OUTPUT parameters can also be in/out so always use ref

					paramDir = FieldDirection.Ref;
				}

				parameters[i++] = new CodeDirectionExpression(paramDir, new CodeArgumentReferenceExpression(paramName));
			}

			CodeMethodReferenceExpression method = new CodeMethodReferenceExpression();
			method.MethodName = "Invoke";

			CodeMethodReturnStatement returnStatement = new CodeMethodReturnStatement(
				new CodeMethodInvokeExpression(method, parameters));

			invokeMethod.Statements.Add(returnStatement);

			return invokeMethod;
		}

		// ===========================================================================

		bool IsResultTypeNested
		{
			get
			{
				ICodeGenerator generator = Provider.CreateGenerator();
				return generator.Supports(GeneratorSupport.NestedTypes);
			}
		}

		// ===========================================================================

		string ResultTypeName
		{
			get
			{
				if(IsResultTypeNested)
					return "Result";
				else
					return TypeName + "Result";
			}
		}

		// ===========================================================================

		void AddEvents(CodeTypeMemberCollection members)
		{
			// private static StoredProcedureEvents _events = new StoredProcedureEvents();

			CodeMemberField field = new CodeMemberField("StoredProcedureEvents", "_events");

			field.Attributes = MemberAttributes.Private | MemberAttributes.Static;
			field.InitExpression = new CodeObjectCreateExpression("StoredProcedureEvents", new CodeExpression [0] {} );

			members.Add(field);

			// public static StoredProcedureEvents Events { get { return _events; } }

			CodeMemberProperty prop = new CodeMemberProperty();

			prop.Name = "Events";
			prop.Comments.Add(new CodeCommentStatement("<summary>The events supported by the stored procedure</summary>", true));
			prop.Attributes = MemberAttributes.Public | MemberAttributes.Static;
			prop.Type = new CodeTypeReference("StoredProcedureEvents");
			prop.GetStatements.Add(new CodeMethodReturnStatement(
				new CodeFieldReferenceExpression(null, "_events")));

			members.Add(prop);
		}
	}
}