How to get output parameter value from ExecuteNonQuery in enterprise library (c#)?

Question

my stored procedure is :

create Procedure spSetUser
(
    @Name NVarchar(50),
    @OrganicTitle NVarchar(30),
    @UserName NVarchar(20),
    @Password NVarchar(16),
    @Result Int Output
)
As
Begin
    Set @Result = -1    
    If Not Exists(Select UserId From dbo.Users Where UserName=@UserName)
    Begin
        Insert Into dbo.Users (Name,OrganicTitle,UserName,[Password]) 
        Values(@Name,@OrganicTitle,@UserName,@Password) 
        Set @Result = SCOPE_IDENTITY()
    End
    Return
End
Go

and my class is (in microsft enterprise library):

[DataObjectMethod(DataObjectMethodType.Insert)]
public Int32 SetUser(UserFieldSet Data)
{
    Int32 Result = 0;
    object[] values = new object[] { Data.Name, Data.OrganicTitle, Data.UserName, Data.Password, Data.UserId };
    Result = Db.ExecuteNonQuery("spSetUser", values);
    return Result;
}

Result is roweffected of execute stored procedure, but I want get value of @Result (output parameter of procedure), How can I get it ?

UserFieldClass is :

public class UserFieldSet
{
    public Int32 UserId;
    public String Name;
    public String OrganicTitle;
    public String UserName;
    public String Password;
}

Tip : I know must use this code for get @Result:

public Int32 SetUser(UserFieldSet Data)
{          
    Int32 Result = 0;
    DbCommand DbCmd = Db.GetStoredProcCommand("spSetUser");
    Db.AddInParameter(DbCmd, "@Name", DbType.String, Data.Name);
    Db.AddInParameter(DbCmd, "@OrganicTitle", DbType.String, Data.OrganicTitle);
    Db.AddInParameter(DbCmd, "@UserName", DbType.String, Data.UserName);
    Db.AddInParameter(DbCmd, "@Password", DbType.String, Data.Password);
    Db.AddOutParameter(DbCmd, "@Result", DbType.Int32, Int32.MaxValue);
    Db.ExecuteNonQuery(DbCmd);
    Result = (Int32)Db.GetParameterValue(DbCmd, "@Result");
    return Result;
}

But I can get it with this methd :

Result = Db.ExecuteNonQuery("spSetUser", values);

Have you tried looking at `values[4]` after the call? does it get updated? although: it could well be that when using that signature, all the parameters are added as input only. — Marc Gravell, Feb 06 '13 at 10:34

score 3 · Accepted Answer · answered Feb 06 '13 at 10:31

3

Have you tried using ExecuteScalar instead?

answered Feb 06 '13 at 10:31

bryan

170
3
13

ExecuteScalar might be useful if you are after a single `select` value; I wouldn't expect it to change the `out` parameter handling any – Marc Gravell Feb 06 '13 at 10:32
1

Please update my code, I want insert data to my table and get valueof userid. How can i it ? – Ali Ahmadi Feb 06 '13 at 10:32
Did you get this to work? I'm doing the same thing you are and also changed it to use execute scalar, but still the out parameter is blank. If you got this to work, would you please share what you did. Thanks! – ptn77 Oct 12 '16 at 18:53

score 1 · Answer 2 · answered Feb 06 '13 at 10:46

As I read the question, you have two implementations of SetUser; one that configures the parameters manually using AddInParameter and AddOutParameter, and one that just passes an array. If I understand the question correctly, the first approach works, and the second does not. And you want to know how to make it work with the less verbose second approach.

My advice: don't. The verbose code works, and more: it works for the right reasons - in particular, it is using pass-by-name, rather than pass-by-index, which should always be preferred.

My biggest question, though, would be: why enterprise library? - that adds very little compared to raw ADO.NET. If I wanted convenience, I'd probably be looking at "dapper", but even with this out parameters are a bit of a faff:

var args = new DynamicParameters(
    new { Data.Name, Data.OrganicTitle, Data.UserName, Data.Password});
// note: ^^ are actually specifying implicit names
args.Add("Result", direction: ParameterDirection.Output);

connection.Execute("spSetUser", args, commandType: CommandType.StoredProcedure);
return args.Get<int>("Result");

Normally it is a bit easier than that; for example:

connection.Execute("spFoo", new { id = -1, name },
      commandType: CommandType.StoredProcedure);

How to get output parameter value from ExecuteNonQuery in enterprise library (c#)?

2 Answers2