Using Simple.data for sqlserver in asp.net c# get output parameter value using a stored procedure call

[RAJ JAISWAL]

I have created a stored procedure and executed it directly on sql server getting the desired result. But im not able to get the value using ASP.net C#.

I called the procedure in this way---

var result = db.sp_TestAdminCredentials(email:Email.Text.Trim(),password:Password.Text.Trim());

        if (result!=null)

        {

            var status = result.status;

            if (status == null)

            {

                FailureText.Text = "login";

            }

            else

            {

                FailureText.Text = "Not Logged In";

            }

            //var role=l.role;

            //    FailureText.Text = "Logging In";

            //    Session["email"] = Email.Text.Trim();

        }

        else

            FailureText.Text = "You have been locked by higher authorities";

The procedure I wrote was ----

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[sp_TestAdminCredentials]

(

@email NVARCHAR(255) ,

@password NVARCHAR(255) ,

@role NVARCHAR(max)=admin out,

@salt NVARCHAR(60)=null out,

@status bit=NULL out

)

AS

BEGIN

EXEC sp_GetIsLockedOutByEmail @email, @status OUTPUT;

if(@status is null)

begin

EXEC sp_GetSaltByEmail @email, @salt OUTPUT ;

if( @salt IS NOT NULL)

begin

SELECT @role=role FROM admin WHERE email = @email

end

end

else

SELECT @status=islockedout from admin where email=@email

END

On execution Im getting error that is not in Simple.Data.SimpleResultSet

Then I asked mark rendele and he said ---

"You can call a stored procedure as if it were a method directly on your dynamic Database object:

var result = db.sp_TestAdminCredentials(x,y,z);

Then any OUT parameter values can be access through an OutputValues dictionary on the result object:

var status = result.OutputValues("status");"

But then Im getting that it cant be called like that.

Please help me to fix this.

Thanks.

[wayne hiller]

Advanced Naming Scenarios

When trying to match method and property names with your underlying data-store, Simple.Data will strip out all non-alphanumeric characters and try a case-insensitive match on what's left. There are times when this might not be ideal, such as having tables or columns with similar names:

IsGenerated
Is_Generated

With the non-alphanumeric characters removed, this leads to duplicate columns names: isgenerated.

To work around these scenarios, you simply need to override the regular express that Simple.Data uses to remove non-alphanumeric characters to include the characters which distinguish your columns from being duplicates. In the example above, we need to tell Simple.Data to include the underscore character to avoid the naming conflicts with a one-time call to a static method:

Simple.Data.Extensions.HomogenizeEx.SetRegularExpression(new Regex("[^a-z0-9_]"));

Now that we've told Simple.Data to include the underscore character, it now separates the distinct names correctly: isgenerated andis_generated.

The default regular expression that Simple.Data uses to find valid characters in names is new Regex("[^a-z0-9]").