Output Paramter and BLL

[Tamer Ibrahim]

Hi,
I'm writing a web application using Scott Mitchell's Data Access Tutorials
as my principal guide.
In my BLL I'm trying to write a AddGiftItem method that has ItemCode as an
output paramter
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert,
true)]

public string AddGiftItem(string title, string detail, DateTime createdOn,
Byte sectionID, string categoryID)

{

DataSetViewGrp.GiftItemsDataTable giftItems = new
DataSetViewGrp.GiftItemsDataTable();

DataSetViewGrp.GiftItemsRow giftItem = giftItems.NewGiftItemsRow();

if (title == null) giftItem.SetTitleNull(); else giftItem.Title = title;

if (detail == null) giftItem.SetDetailNull(); else giftItem.Detail = detail;

giftItem.CreatedOn = createdOn;

giftItem.SectionID = sectionID;

giftItem.CategoryID = categoryID;

giftItems.AddGiftItemsRow(giftItem);

string itemCode = Adapter.Update(giftItems).ToString();

return itemCode ;

}

and here is my stored procedure that do the insert

ALTER PROCEDURE dbo.spGiftItemsInsert

(

@Title nvarchar(300),

@Detail nvarchar(4000),

@CreatedOn datetime,

@SectionID tinyint,

@ItemCode nvarchar(6) output,

@CategoryID nvarchar(2)

)

AS

SET NOCOUNT OFF;

BEGIN TRAN

INSERT INTO [Contents] ([Title], [Detail], [CreatedOn], [SectionID]) VALUES
(@Title, @Detail, @CreatedOn, @SectionID);

INSERT INTO GiftItems (ContentID, ItemCode, CategoryID) VALUES
(SCOPE_IDENTITY(),dbo.fnItemCode(@CategoryID),@CategoryID);

SELECT @ItemCode;

COMMIT TRAN

How can I get this work out ?

Thank You.

 

[NitRaGs]

Hi,
You will have to call the storedprocedure by adding parameter objects to the
command object. You will have to set the ParameterDirection as Output for the
output type parameter. After you call the Stored Proc you should be able to
read the returned value in the Parameter Object.

Thanks
NitRaGs

 

[NitRaGs]

Something like this:

SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters["@ProductName"].Value);
Console.WriteLine(cmd.Parameters["@Quantity"].Value.ToString());

hope that helps.

-NitRaGs