Data Access Application Block - Stored Procedure output parameters with paramArray

Topics: Data Access Application Block
Aug 7, 2009 at 7:47 PM

 

I am wondering whether anyone has used the combination of paramArray with an output parameter sucessfully?

I am using the Data Access Application Block with a stored procedure.  I have a paramArray for my input parameters.  I have also

added an output parameter using AddOutParameter().  My stored procedure returns a parameter value.  Somehow I keep getting

an error message. 

Thanks,

Lloyd

 

My C# code and stored procedure is as follows:

        object[] paramArray = new object[4];
        paramArray[0] = orgCode;
        paramArray[1] = ProgramCD;
        paramArray[2] = ElementCD;
        paramArray[3] = ComponentCD;

        Database db = DatabaseFactory.CreateDatabase();
        DbCommand dbCmd = db.GetStoredProcCommand(@"Test_ProjectExists", paramArray);
        db.AddOutParameter(dbCmd, @"ProjectID", DbType.Int32, 100);
        db.ExecuteNonQuery(dbCmd);

        ProjectID = Convert.ToInt32((db.GetParameterValue(dbCmd, @"ProjectID")));

---------------------------------------------------------

ALTER PROCEDURE [proj].[Test_ProjectExists]
 @OrgCode int,
 @ProgramCD char(2),
 @ElementCD char(2),
 @ComponentCD char(3),
 @ProjectID int output
AS
BEGIN
 declare @OrgID int
 select @OrgID = Org_ID
 from FiscalCodes.fiscal.ORGANIZATION
 where (ORG_CD = @OrgCode)

 select @ProjectID = ProjectID
 from Project
 where ((OrgID = @OrgID) and (ProgramCD = @ProgramCD) and (ElementCD = @ElementCD) and (ComponentCD = @ComponentCD))
END

 

Aug 10, 2009 at 7:03 AM
Edited Aug 10, 2009 at 7:04 AM

Hi,

Can you please try adding the out parameter to the paramArray. Then remove your call to db.AddOutParameter(dbCmd, @"ProjectID", DbType.Int32, 100);.

object[] paramArray = new object[5];
int projId = 0;
paramArray[0] = orgCode;
paramArray[1] = ProgramCD;
paramArray[2] = ElementCD;
paramArray[3] = ComponentCD;
paramArray[4] = projId;

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCmd = db.GetStoredProcCommand(@"Test_ProjectExists", paramArray);
//db.AddOutParameter(dbCmd, @"ProjectID", DbType.Int32, 100);
db.ExecuteNonQuery(dbCmd);

ProjectID = Convert.ToInt32((db.GetParameterValue(dbCmd, @"ProjectID")));

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com 

Aug 10, 2009 at 5:08 PM

Thanks Valiant.

Your sugesstion worked.

 

Lloyd