How to get value of output parameter from ExecuteNonQuery in corporate library (C #)?

Question

my stored procedure:

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 (in the microfiche corporate 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; } 

The result is checked for the execution of the stored procedure, but I want to get the value @Result (the output parameter of the procedure), how can I get it?

UserFieldClass:

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

Tip. I know I have to use this code to 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 this with this method:

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

Answer 1

Have you tried using ExecuteScalar instead?

Answer 2

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

My advice: do not. Detailed code works and much more: it works for the right reasons - in particular, it uses pass-by-name rather than pass-by-index, which should always be preferred.

My biggest question, however, would be why a corporate library? - this adds very little compared to raw ADO.NET. If I wanted convenience, I would probably look at "dapper", but even with these out options, it's a little 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"); 

This is usually a little lighter than that; eg:

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