DAAB how to get multiple output parameters ?

Question

User852864959 posted

A stored procedure is returning 2 output parameter. I tried to catch them in DAL using DAAB but couldn't succeed. Actually control did not returned on next lines. Please guide me.

 

using (DbCommand dbCommand = db.GetSqlStringCommand("p_AddLeaveApplication"))
            {
                dbCommand.CommandType = CommandType.StoredProcedure;
                db.AddInParameter(dbCommand, "@EmployeeId", DbType.Int16, leave.EmployeeId);
                db.AddInParameter(dbCommand, "@DateTo", DbType.Date,string.Format("'{0:yyyy-MM-dd HH:mm:ss}'",  leave.DateTo));
                db.AddInParameter(dbCommand, "@DateFrom", DbType.Date, string.Format("'{0:yyyy-MM-dd HH:mm:ss}'", leave.DateFrom));                    
                db.AddInParameter(dbCommand, "@LeaveType", DbType.String, leave.LeaveType);
                db.AddOutParameter(dbCommand, "@Result", DbType.Int16, 1);
                db.AddOutParameter(dbCommand, "@LeaveId", DbType.Int16, 1);
                db.ExecuteNonQuery(dbCommand);
                LeaveId = Convert.ToInt16(db.GetParameterValue(dbCommand, "@LeaveId"));
                return result = Convert.ToInt16(db.GetParameterValue(dbCommand, "@Result"));
            }

Answer 1

User-837620913 posted

If you are creating a DbCommand, you can use that to get the output params:

db.GetParameterValue(dbCommand, "@LeaveID")

 

See if this article helps:  http://www.devx.com/dotnet/Article/30910/0/page/4

Answer 2

User852864959 posted

Thanks DarrellNorton,

 

Please guide me one more thing.

It is when I need to catch 2 output parameters should I do like:

db.GetParameterValue(dbCommand, "@LeaveID");
db.GetParameterValue(dbCommand, "@UserId");

also will it call stored procedure two times ? or for gettign all output paramters it will just call sp once ?

Answer 3

User583989486 posted

Hi Haansi,

It is when I need to catch 2 output parameters should I do like:

It won't execute your procedure when you call these lines.

db.GetParameterValue(dbCommand, "@LeaveID");
db.GetParameterValue(dbCommand, "@UserId");


Actually, it is called when you call db.ExecuteNonQuery(dbCommand); After this, all the output parameters are retrieved from the your procedure.  You can see the steps from this link.   http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx

Best regards,

Jonathan