Hi,
I have stored procedure like
CREATE procedure [dbo].[spAddItem]
(
@itemId nvarchar(50),
@itemName nvarchar(500),
@itemDescription nvarchar(500),
@itemImage nvarchar(100),
@cityId bigint,
@Active int,
@Status bigint output
)
as
begin tran
declare @count as int
declare @result as int
Set NOCOUNT ON
select @count = Count(*) from Item where itemName = @itemName and itemId = @itemId
if(@count > 0)
begin
set @result = -1;
end
else
begin
insert into Item(itemId,itemName,itemDescription,itemImage,cityId,Active)
values (@itemId,@itemName,@itemDescription,@itemImage,@cityId,@Active)
set @result = 0
end
if @@error=0
begin
commit tran
select @Status=@result
return @Status
end
else
begin
rollback tran
select @Status=@result
return @Status
end
commit tran
& to get the status paremeter in my function i am using function
public void DALInsertItem(ItemBLL it)
{
int rowNo;
using (SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]))
{
try
{
SqlParameter[] par = new SqlParameter[7];
par[0] = new SqlParameter("@itemId",it.itemId);
par[1] = new SqlParameter("@itemName",it.itemName);
par[2] = new SqlParameter("@itemDescription", it.itemDescription);
par[3] = new SqlParameter("@itemImage", it.itemImage);
par[4] = new SqlParameter("@cityId",it.cityId);
par[5] = new SqlParameter("@Active", it.itemActive);
par[6] = new SqlParameter("@Status", 100);
par[6].Direction = ParameterDirection.Output;
rowNo = SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "spAddItem", par);
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
if (rowNo == -1)
{
it.isExistItem = false;
}
else
{
it.isExistItem = true;
}
}
}
Problem is I am not getting the status(Output parameter) in my function,
How can i access the status parameter in my function that is set in my stored procedure.
Thanks
Loading
Zoran HorvatPosted Nov 27, 2010, 2:15 PM
Also, note that ExecuteNonQuery for SqlStatement (and I expect in SqlHelper operates in the same way) always returns -1 for command type StoredProcedure. This makes sense because stored procedure may operate on different tables, may execute autonomous transactions on its own, change database schema, etc., so defining "affected rows count" may be very difficult to define. Only INSERT, UPDATE and DELETE commands return affected rows count. Value -1 is also returned in case of rollback.