asp.net实现调用存储过程并带返回值的方法

前台调用

string[,] sArray = new string[3,2];
sArray[0,0]="@parent_id";
sArray[1,0]="@cn_name";
sArray[2,0]="@en_name";
sArray[0,1]="5";
sArray[1,1]="aaaab";
sArray[2,1]="cccccc";
Factory.SqlModel sm = new Factory.SqlModel();
sm.Proc_Return_Int("Product_Category_Insert", sArray);

存储过程内容

ALTER PROCEDURE [dbo].[Product_Category_Insert]
  @parent_id int,
  @cn_Name nvarchar(50),
  @en_Name nvarchar(50)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @ERR int
  SET @ERR=0
  BEGIN TRAN
  IF @parent_id<0 OR ISNULL(@cn_Name,'')=''
    BEGIN
      SET @ERR=1
      GOTO theEnd
    END
  IF(NOT EXISTS(SELECT Id FROM Product_Category WHERE Id=@parent_id))
    BEGIN
      SET @ERR=2
      GOTO theEnd
    END
  DECLARE @Id int,@Depth int,@ordering int
  SELECT @Id=ISNULL(MAX(Id)+1,1) FROM Product_Category--计算@Id
  IF @Parent_Id=0
    BEGIN
      SET @Depth=1--计算@Depth
      SELECT @Ordering=ISNULL(MAX(Ordering)+1,1) FROM Product_Category--计算@OrderId
    END
  ELSE
    BEGIN
      SELECT @Depth=Depth+1 FROM Product_Category WHERE Id=@Parent_Id--计算@Depth,计算@Ordering时需要用到
      SELECT @Ordering=MAX(Ordering)+1 FROM Product_Category--计算@Ordering
        WHERE Id=@Parent_Id
      UPDATE Product_Category SET Ordering=Ordering+1 WHERE Ordering>=@Ordering--向后移动插入位置后面的所有节点
    END
  INSERT INTO Product_Category(Id,Parent_Id,cn_Name,en_name,Depth,Ordering) VALUES (@Id,@Parent_Id,@cn_Name,@en_name,@Depth,@Ordering)
  IF @@ERROR<>0
    SET @ERR=-1
  theEnd:
  IF @ERR=0
    BEGIN
    COMMIT TRAN
    RETURN 0
    END
  ELSE
    BEGIN
    ROLLBACK TRAN
    RETURN @ERR
    END
END

希望本文所述对大家asp.net程序设计有所帮助。