OK, i have fixed the problem with the output parameter, under here is the template for the actionstoredprocedure selfservicing.
But I still have one problem. I can't seem to give a null value where it is expecting a GUID. And since a GUID is FK with another table and not always mandatory I need to be able to pass a NULL value to this in my Stored Procedure.
I have tried to pass a null value string instead of the GUID, which is the 0000-000-00 etc. value, but then I get the message:
"Procedure 'sp_insert_PROJECT' expects parameter '@AREA_OBJ_NO', which was not supplied."
Can you help me to fix this problem?
Thank you in advance,
G.I.
///////////////////////////////////////////////////////////////
// This is generated code. If you modify this code, be aware
// of the fact that when you re-generate the code, your changes
// are lost. If you want to keep your changes, make this file read-only
// when you have finished your changes, however it is recommended that
// you inherit from this class to extend the functionality of this generated
// class or you modify / extend the templates used to generate this code.
//
// Do not try to run this code on another version of the database than the database
// which was used to generate this code. This means that when you used f.e. SqlServer 2000
// to generate this code, it is likely that you will not be able to use that code on
// SqlServer 7 due to SQL syntax mismatches. Most code is generic code which will work
// with any database, but some code relies on a specific database type/vendor/version used.
// This code is located in the DaoClasses which target a specific specified database. Also all
// classes target a specific specified Dynamic Query Engine (DQE) in the using/imports
// directives.
//////////////////////////////////////////////////////////////
// Code is generated using LLBLGen Pro version: <[LLBLGenVersion]>
// Code is generated on: <[Time]>
// Code is generated using templates: <[TemplateName]>
// Templates vendor: Solutions Design.
// Templates version: <[TemplateVersion]>
//////////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Data.SqlClient;
using <[RootNamespace]>.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
namespace <[RootNamespace]>.StoredProcedureCallerClasses
{
/// <summary>
/// Class which contains the static logic to execute action stored procedures in the database.
/// </summary>
public class ActionProcedures
{
/// <summary>
/// private CTor so no instance can be created.
/// </summary>
private ActionProcedures()
{
}
<[Foreach ActionStoredProcedureCall CrLf]>
/// <summary>
/// Delegate definition for stored procedure '<[ActualStoredProcedureName]>' to be used in combination of a UnitOfWork object.
/// </summary>
public delegate int <[CurrentSPCallName]>CallBack(<[Foreach InputParameter Comma]><[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasInputAndOutputParameters]>, <[EndIf]><[Foreach OutputParameter Comma]>ref <[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasParameter]>, <[EndIf]> ITransaction transactionToUse);
/// <summary>
/// Calls stored procedure '<[ActualStoredProcedureName]>'.<br/><br/>
/// <[Foreach CustomProperty ActionStoredProcedureCall]>
/// <[CustomPropertyName]>: <[CustomPropertyValue]><br/><[NextForeach]>
/// </summary>
<[If HasParameter]><[Foreach Parameter CrLf]> /// <param name="<[CaseCamel CurrentParameterName]>"><[CurrentParameterDirection]> parameter of stored procedure</param><[NextForeach]>
<[EndIf]> /// <returns>Amount of rows affected, if the database / routine doesn't surpress rowcounting.</returns>
public static int <[CurrentSPCallName]>(<[Foreach InputParameter Comma]><[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasInputAndOutputParameters]>, <[EndIf]><[Foreach OutputParameter Comma]>ref <[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]>)
{
// create parameters
SqlParameter[] parameters = new SqlParameter[<[AmountOfParameters]>];
<[Foreach InputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
<[Foreach OutputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.InputOutput, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
// Call the stored proc.
int toReturn = DbUtils.CallActionStoredProcedure("<[If HasMultipleCatalogs]>[<[SourceCatalogName]>].<[EndIf]>[<[SourceSchemaName]>].[<[ActualStoredProcedureName]>]", parameters, null);
<[Foreach OutputParameter CrLf]> if(parameters[<[ParameterIndex]>].Value!=System.DBNull.Value)
{
<[CaseCamel CurrentParameterName]> = (<[TypeOfParameter]>)parameters[<[ParameterIndex]>].Value;
}<[NextForeach]>
return toReturn;
}
/// <summary>
/// Calls stored procedure '<[ActualStoredProcedureName]>'.<br/><br/>
/// <[Foreach CustomProperty ActionStoredProcedureCall]>
/// <[CustomPropertyName]>: <[CustomPropertyValue]><br/><[NextForeach]>
/// </summary>
<[If HasParameter]><[Foreach Parameter CrLf]> /// <param name="<[CaseCamel CurrentParameterName]>"><[CurrentParameterDirection]> parameter of stored procedure</param><[NextForeach]>
<[EndIf]> /// <param name="returnValue">Return value of the stored procedure</param>
/// <returns>Amount of rows affected, if the database / routine doesn't surpress rowcounting.</returns>
public static int <[CurrentSPCallName]>(<[Foreach InputParameter Comma]><[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasInputAndOutputParameters]>, <[EndIf]><[Foreach OutputParameter Comma]>ref <[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasParameter]>, <[EndIf]>ref System.Int32 returnValue)
{
// create parameters. Add 1 to make room for the return value parameter.
SqlParameter[] parameters = new SqlParameter[<[AmountOfParameters]> + 1];
<[Foreach InputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
<[Foreach OutputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.InputOutput, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
// return value parameter
parameters[<[AmountOfParameters]>] = new SqlParameter("RETURNVALUE", SqlDbType.Int, 0, ParameterDirection.ReturnValue, true, 10, 0, "", DataRowVersion.Current, returnValue);
// Call the stored proc.
int toReturn = DbUtils.CallActionStoredProcedure("<[If HasMultipleCatalogs]>[<[SourceCatalogName]>].<[EndIf]>[<[SourceSchemaName]>].[<[ActualStoredProcedureName]>]", parameters, null);
<[Foreach OutputParameter CrLf]> if(parameters[<[ParameterIndex]>].Value!=System.DBNull.Value)
{
<[CaseCamel CurrentParameterName]> = (<[TypeOfParameter]>)parameters[<[ParameterIndex]>].Value;
}<[NextForeach]>
returnValue = (int)parameters[<[AmountOfParameters]>].Value;
return toReturn;
}
/// <summary>
/// Calls stored procedure '<[ActualStoredProcedureName]>'.<br/><br/>
/// <[Foreach CustomProperty ActionStoredProcedureCall]>
/// <[CustomPropertyName]>: <[CustomPropertyValue]><br/><[NextForeach]>
/// </summary>
<[If HasParameter]><[Foreach Parameter CrLf]> /// <param name="<[CaseCamel CurrentParameterName]>"><[CurrentParameterDirection]> parameter of stored procedure</param><[NextForeach]>
<[EndIf]> /// <param name="transactionToUse">the transaction to use, or null if no transaction is available.</param>
/// <returns>Amount of rows affected, if the database / routine doesn't surpress rowcounting.</returns>
public static int <[CurrentSPCallName]>(<[Foreach InputParameter Comma]><[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasInputAndOutputParameters]>, <[EndIf]><[Foreach OutputParameter Comma]>ref <[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasParameter]>, <[EndIf]> ITransaction transactionToUse)
{
// create parameters
SqlParameter[] parameters = new SqlParameter[<[AmountOfParameters]>];
<[Foreach InputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
<[Foreach OutputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.InputOutput, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
// Call the stored proc.
int toReturn = DbUtils.CallActionStoredProcedure("<[If HasMultipleCatalogs]>[<[SourceCatalogName]>].<[EndIf]>[<[SourceSchemaName]>].[<[ActualStoredProcedureName]>]", parameters, transactionToUse);
<[Foreach OutputParameter CrLf]> if(parameters[<[ParameterIndex]>].Value!=System.DBNull.Value)
{
<[CaseCamel CurrentParameterName]> = (<[TypeOfParameter]>)parameters[<[ParameterIndex]>].Value;
}<[NextForeach]>
return toReturn;
}
/// <summary>
/// Calls stored procedure '<[ActualStoredProcedureName]>'.<br/><br/>
/// <[Foreach CustomProperty ActionStoredProcedureCall]>
/// <[CustomPropertyName]>: <[CustomPropertyValue]><br/><[NextForeach]>
/// </summary>
<[If HasParameter]><[Foreach Parameter CrLf]> /// <param name="<[CaseCamel CurrentParameterName]>"><[CurrentParameterDirection]> parameter of stored procedure</param><[NextForeach]>
<[EndIf]> /// <param name="returnValue">Return value of the stored procedure</param>
/// <param name="transactionToUse">the transaction to use, or null if no transaction is available.</param>
/// <returns>Amount of rows affected, if the database / routine doesn't surpress rowcounting.</returns>
public static int <[CurrentSPCallName]>(<[Foreach InputParameter Comma]><[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasInputAndOutputParameters]>, <[EndIf]><[Foreach OutputParameter Comma]>ref <[TypeOfParameter]> <[CaseCamel CurrentParameterName]><[NextForeach]><[If HasParameter]>, <[EndIf]>ref System.Int32 returnValue, ITransaction transactionToUse)
{
// create parameters. Add 1 to make room for the return value parameter.
SqlParameter[] parameters = new SqlParameter[<[AmountOfParameters]> + 1];
<[Foreach InputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
<[Foreach OutputParameter CrLf]> parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.InputOutput, true, <[ParameterPrecision]>, <[ParameterScale]>, "", DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);<[NextForeach]>
// return value parameter
parameters[<[AmountOfParameters]>] = new SqlParameter("RETURNVALUE", SqlDbType.Int, 0, ParameterDirection.ReturnValue, true, 10, 0, "", DataRowVersion.Current, returnValue);
// Call the stored proc.
int toReturn = DbUtils.CallActionStoredProcedure("<[If HasMultipleCatalogs]>[<[SourceCatalogName]>].<[EndIf]>[<[SourceSchemaName]>].[<[ActualStoredProcedureName]>]", parameters, transactionToUse);
<[Foreach OutputParameter CrLf]> if(parameters[<[ParameterIndex]>].Value!=System.DBNull.Value)
{
<[CaseCamel CurrentParameterName]> = (<[TypeOfParameter]>)parameters[<[ParameterIndex]>].Value;
}<[NextForeach]>
returnValue = (int)parameters[<[AmountOfParameters]>].Value;
return toReturn;
}
<[NextForeach]>
#region Included Code
<# Custom_ActionProceduresTemplate #>
#endregion
}
}
