熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

在asp.net中使用存儲過程

2013-11-13 10:02:06  來源: .NET編程 

  存儲過程的定義

  CREATE procedure pro_buyGoods

  (

  @GoodsName varchar()

  @GoodsNum int

  )

  AS

  declare @count as int

  set @count = (select count(*) from T_Goods where GoodsName=@GoodsName)

  if @count<= begin

  insert into T_Goods values (@GoodsName@GoodsNum)

  end

  else begin

  update T_Goods set GoodsNum=GoodsNum+@GoodsNum where GoodsName=@GoodsName

  end

  GO

  說明語法是通過檢查的這個過程是用來判斷T_Goods表中有沒有指定的商品有的話則增加他的數目沒有的話新添加記錄

  這是在ASPNET中的調用過程

  int BuyGoodsID = ConvertToInt(thistxtBuyIDText);

  string GoodsName = thistxtGoodsNameText;

  int GoodsNum = ConvertToInt(thistxtGoodsNumText);

  int GoodsPrice = ConvertToInt(thistxtGoodsPriceText);

  SqlConnection conn = DBCreateCon();

  connOpen();

  string cmdText = insert into T_BuyGoods values(+BuyGoodsID++GoodsName++GoodsNum++GoodsPrice+);

  SqlCommand cmd = new SqlCommand(cmdTextconn);

  SqlTransaction trans = connBeginTransaction();

  cmdTransaction = trans;

  try

  {

  cmdExecuteNonQuery();

  //插入庫存表的代碼

  SqlCommand cmd = new SqlCommand(pro_buyGoodsconn);

  cmdCommandType = CommandTypeStoredProcedure;

  cmdParametersAdd(@GoodsNameSqlDbTypeVarChar);

  cmdParametersAdd(@GoodsNumSqlDbTypeInt);

  cmdParameters[@GoodsName]Direction=ParameterDirectionInput;

  cmdParameters[@GoodsNum]Direction=ParameterDirectionInput;

  cmdParameters[@GoodsName]Value = GoodsName;

  cmdParameters[@GoodsNum]Value = GoodsNum;

  cmdExecuteNonQuery();

  thisPanelVisible = false;

  thisPanelVisible = true;

  thislblInfoText = 提交成功!;

  transCommit();

  }

  catch(Exception exp)

  {

  transRollback();

  ResponseWrite(<script language=javascript>alert(提交失敗需要重新提交!)<+/script>);

  }

  finally

  {

  connClose();

  }

  ***********************************************************************

  set @count = (select count(*) from T_Goods where GoodsName=@GoodsName)

  改為

  select @count = count(*) from T_Goods where GoodsName=@GoodsName


From:http://tw.wingwit.com/Article/program/net/201311/12336.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.