CODEWITHSUNDEEP
c#wcfsql-server-2008stored-procedures
Ajay
Ajay

Reputation: 6590

Passing value to ObjectParameter variables

I have created a stored procedure with some parameters. These are my parameters for my procedure:

ALTER Procedure [dbo].[SaveInvitation]
(
    @InvitationID INT OUTPUT,
    @UserID INT,
    @Email NCHAR(100),
    @InvitationGUID  UNIQUEIDENTIFIER OUTPUT
)
AS

My function.cs:

public SaveInvitation_Result SaveInvitation(int userID,string email)
{
        SaveInvitation_Result result = null;

        var results = this.DataContext.SaveInvitation(null,userID,email,null);

        if (results != null)
        {
            result = results.FirstOrDefault();
        }

        return result;
    }

I am passing some parameters to my function SaveInvitation like this:

My DataBaseModel.Designer.cs:

public ObjectResult<SaveInvitation_Result> SaveInvitation(ObjectParameter invitationID, Nullable<global::System.Int32> userID, global::System.String email, ObjectParameter invitationGUID)
{
        ObjectParameter userIDParameter;
        if (userID.HasValue)
        {
            userIDParameter = new ObjectParameter("UserID", userID);
        }
        else
        {
            userIDParameter = new ObjectParameter("UserID", typeof(global::System.Int32));
        }

        ObjectParameter emailParameter;
        if (email != null)
        {
            emailParameter = new ObjectParameter("Email", email);
        }
        else
        {
            emailParameter = new ObjectParameter("Email", typeof(global::System.String));
        }

        return base.ExecuteFunction<SaveInvitation_Result>("SaveInvitation", invitationID, userIDParameter, emailParameter, invitationGUID);
}

When I execute this function/procedure I get an exception

The parameter at index 0 in the parameters array is null.

How can I pass values to invitationID and invitationGUID?

Upvotes: 4

Views: 13511

Answers (1)

jam40jeff
jam40jeff

Reputation: 2596

When userID or email is null, you must set the value of the parameter to DBNull.Value:

public ObjectResult<SaveInvitation_Result> SaveInvitation(ObjectParameter invitationID, Nullable<global::System.Int32> userID, global::System.String email, ObjectParameter invitationGUID)
{
    ObjectParameter userIDParameter;
    if (userID.HasValue)
    {
        userIDParameter = new ObjectParameter("UserID", userID);
    }
    else
    {
        userIDParameter = new ObjectParameter("UserID", typeof(global::System.Int32));
        userIDParameter.Value = DBNull.Value;
    }

    ObjectParameter emailParameter;
    if (email != null)
    {
        emailParameter = new ObjectParameter("Email", email);
    }
    else
    {
        emailParameter = new ObjectParameter("Email", typeof(global::System.String));
        emailParameter.Value = DBNull.Value;
    }

    return base.ExecuteFunction<SaveInvitation_Result>("SaveInvitation", invitationID, userIDParameter, emailParameter, invitationGUID);
}

Upvotes: 9

Related Questions