locked
Calling Stored Procedures with parameter as Object in ADO.Net Services RRS feed

  • Question

  • Hello Friends,

    i have stored procedure, named as aspnet_Membership_CreateUser, as Follows,

    USE [umd]

    GO

    /****** Object:  StoredProcedure [dbo].[aspnet_Membership_CreateUser]    Script Date: 05/06/2010 13:16:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ALTER PROCEDURE [dbo].[aspnet_Membership_CreateUser]

        @ApplicationName                        nvarchar(256),

        @UserName                               nvarchar(256),

        @Password                               nvarchar(128),

        @PasswordSalt                           nvarchar(128),

        @Email                                  nvarchar(256),

        @PasswordQuestion                       nvarchar(256),

        @PasswordAnswer                         nvarchar(128),

        @IsApproved                             bit,

        @CurrentTimeUtc                         datetime,

        @CreateDate                             datetime = NULL,

        @UniqueEmail                            int      = 0,

        @PasswordFormat                         int      = 0,

        @UserId                                 uniqueidentifier OUTPUT

    AS

    BEGIN

        DECLARE @ApplicationId uniqueidentifier

        SELECT  @ApplicationId = NULL

     

        DECLARE @NewUserId uniqueidentifier

        SELECT @NewUserId = NULL

     

        DECLARE @IsLockedOut bit

        SET @IsLockedOut = 0

     

        DECLARE @LastLockoutDate  datetime

        SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )

     

        DECLARE @FailedPasswordAttemptCount int

        SET @FailedPasswordAttemptCount = 0

     

        DECLARE @FailedPasswordAttemptWindowStart  datetime

        SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

     

        DECLARE @FailedPasswordAnswerAttemptCount int

        SET @FailedPasswordAnswerAttemptCount = 0

     

        DECLARE @FailedPasswordAnswerAttemptWindowStart  datetime

        SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )

     

        DECLARE @NewUserCreated bit

        DECLARE @ReturnValue   int

        SET @ReturnValue = 0

     

        DECLARE @ErrorCode     int

        SET @ErrorCode = 0

     

        DECLARE @TranStarted   bit

        SET @TranStarted = 0

     

        IF( @@TRANCOUNT = 0 )

        BEGIN

       BEGIN TRANSACTION

       SET @TranStarted = 1

        END

        ELSE

         SET @TranStarted = 0

     

        EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

     

        IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

     

        SET @CreateDate = @CurrentTimeUtc

     

        SELECT  @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId

        IF ( @NewUserId IS NULL )

        BEGIN

            SET @NewUserId = @UserId

            EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT

            SET @NewUserCreated = 1

        END

        ELSE

        BEGIN

            SET @NewUserCreated = 0

            IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )

            BEGIN

                SET @ErrorCode = 6

                GOTO Cleanup

            END

        END

     

        IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

     

        IF( @ReturnValue = -1 )

        BEGIN

            SET @ErrorCode = 10

            GOTO Cleanup

        END

     

        IF ( EXISTS ( SELECT UserId

                      FROM   dbo.aspnet_Membership

                      WHERE  @NewUserId = UserId ) )

        BEGIN

            SET @ErrorCode = 6

            GOTO Cleanup

        END

     

        SET @UserId = @NewUserId

     

        IF (@UniqueEmail = 1)

        BEGIN

            IF (EXISTS (SELECT *

                        FROM  dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )

                        WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))

            BEGIN

                SET @ErrorCode = 7

                GOTO Cleanup

            END

        END

     

        IF (@NewUserCreated = 0)

        BEGIN

            UPDATE dbo.aspnet_Users

            SET    LastActivityDate = @CreateDate

            WHERE  @UserId = UserId

            IF( @@ERROR <> 0 )

            BEGIN

                SET @ErrorCode = -1

                GOTO Cleanup

            END

        END

     

        INSERT INTO dbo.aspnet_Membership

                    ( ApplicationId,

                      UserId,

                      Password,

                      PasswordSalt,

                      Email,

                      LoweredEmail,

                      PasswordQuestion,

                      PasswordAnswer,

                      PasswordFormat,

                      IsApproved,

                      IsLockedOut,

                      CreateDate,

                      LastLoginDate,

                      LastPasswordChangedDate,

                      LastLockoutDate,

                      FailedPasswordAttemptCount,

                      FailedPasswordAttemptWindowStart,

                      FailedPasswordAnswerAttemptCount,

                      FailedPasswordAnswerAttemptWindowStart )

             VALUES ( @ApplicationId,

                      @UserId,

                      @Password,

                      @PasswordSalt,

                      @Email,

                      LOWER(@Email),

                      @PasswordQuestion,

                      @PasswordAnswer,

                      @PasswordFormat,

                      @IsApproved,

                      @IsLockedOut,

                      @CreateDate,

                      @CreateDate,

                      @CreateDate,

                      @LastLockoutDate,

                      @FailedPasswordAttemptCount,

                      @FailedPasswordAttemptWindowStart,

                      @FailedPasswordAnswerAttemptCount,

                      @FailedPasswordAnswerAttemptWindowStart )

     

        IF( @@ERROR <> 0 )

        BEGIN

            SET @ErrorCode = -1

            GOTO Cleanup

        END

     

        IF( @TranStarted = 1 )

        BEGIN

       SET @TranStarted = 0

       COMMIT TRANSACTION

        END

     

        RETURN 0

     

    Cleanup:

     

        IF( @TranStarted = 1 )

        BEGIN

            SET @TranStarted = 0

         ROLLBACK TRANSACTION

        END

     

        RETURN @ErrorCode

     

    END

    i have added this stored procedure to ADO.Net service and i have created Function Import also with return value as NONE.

    so there my function is aspnet_Membership_CreateUser(aspnet_Membership membership)

    under .svc file i wrote like this

    [WebGet]

            public List<Feature> aspnet_Membership_CreateUser(aspnet_Membership membership)

            {

                UserMgmtDataModelContainer ent = new UserMgmtDataModelContainer();

     

                return ent.aspnet_Membership_CreateUser(membership);         // here im getting error like no over loaded parameters

                return null;

            }

    so in what way i need to mention this and how to call this from client,

    please help me here,

    thanks.


    Thursday, May 6, 2010 7:39 AM

All replies

  • Currently, Data Services only supports primitive values as arguments to a service operation, so you will need to change the signature of the method in order to expose it at the Data Service layer.

    As for the error you're getting, is it a compile time error? If so, you should look closely at the generated code for your Entity Framework model (I assume you're using EF because you mentioned a Function Import) and ensure you have the method name/signature correct. It doesn't sound like that issue is related to the data service iteself.


    Matt Meehan, WCF Data Services (Astoria)
    Thursday, May 6, 2010 4:19 PM
    Moderator