存储过程返回值问题
请问一下，问题是这样的我现在执行了一个存储过程，是一个注册用户帐号的存储过程，当用户注册帐号时，帐号是程序返回的，而不是用户填的，用户只填一些个人资料就行了，就和声请QQ帐号一样，是程序返回的用户帐号
那么我现在就是这个存储过程，用户表示这样的
UserNum int identity(1000,1)
从1000开始递增，我在存储里执行了添加用户资料的存储过程，要返回该用户的UserNum,请问我在程序里面怎么去返回，@@identity怎么去定义和返回这个递增的用户名变量，而在程序里面是怎样获取这个变量的呢，啊
看一下吧
USE [M_shop]GO
/****** 对象: StoredProcedure [dbo].[SPADDBOOK] 脚本日期: 05/06/2008 16:58:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SPADDBOOK]
@Title nvarchar(50)=null,
@Author nvarchar(50)=null,
@Pubs nvarchar(50)=null,
@Category nvarchar(50)=null,
@EntryDate nvarchar(50)=null,
@Price float=null,
@PurchasedFrom nvarchar(50)=null,
@BookFor nvarchar(50)=null,
@BookId int output,
@Status int output
AS
BEGIN
SET NOCOUNT ON;
set @Price= cast(@Price as float)
INSERT INTO BookMaster(Title,Author,Pubs,Category,EntryDate,Price,PurchasedFrom,BookFor) values(@Title,@Author,@Pubs,@Category,@EntryDate,@Price,@PurchasedFrom,@BookFor)
SET @BookId =@@identity
IF @@error=0
SET @Status=1
else
SET @Status=0
END
程序部分
public class DataAccess{
SqlConnection Conn;
SqlDataAdapter DA = new SqlDataAdapter();
SqlCommandBuilder CB;
DataTable DT;
public SqlConnection OpenConnection()
{
Conn = new SqlConnection("data source=172.27.26.61;database=M_shop;User ID=webplus;Password=webplus");
return Conn;
}
public Array CallStoredProcedure(string[,] MyINPram, string[,] MyOUTPram, string MySPName)
{
string Result;
Conn = OpenConnection();
SqlCommand CmdSp = new SqlCommand();
CmdSp.Connection = Conn;
CmdSp.CommandText = MySPName.ToString();
CmdSp.CommandType = CommandType.StoredProcedure;
//input parameters
for (int i = 0; i < MyINPram.Length / 2; i++)
{
CmdSp.Parameters.AddWithValue(MyINPram[i, 0], MyINPram[i, 1]);
}
//outparameters
for (int i = 0; i < MyOUTPram.Length / 2; i++)
{
if (MyOUTPram[i, 1] == "SqlDbType.VarChar")
{
CmdSp.Parameters.Add(MyOUTPram[i, 0], SqlDbType.VarChar, 150);
}
else
{
CmdSp.Parameters.Add(MyOUTPram[i, 0], SqlDbType.Int, 4);
//CmdSp.Parameters.Add(MyOUTPram[i, 0], MyOUTPram[i,1]);
}
CmdSp.Parameters[MyOUTPram[i, 0]].Direction = ParameterDirection.Output;
}
Conn.Open();
CmdSp.ExecuteNonQuery();
Conn.Close();
string[,] Temparray = new string[MyOUTPram.Length / 2, 2];
for (int i = 0; i < MyOUTPram.Length / 2; i++)
{
Result = Convert.ToString(CmdSp.Parameters[MyOUTPram[i, 0]].Value);
//Result = (string)CmdSp.Parameters["@Msg"].Value;
Temparray[i, 0] = MyOUTPram[i, 0];
Temparray[i, 1] = Result;
}
return Temparray;
}
public DataTable CallStoredProcedure(string MySPName)
{
Conn = OpenConnection();
SqlCommand cm = new SqlCommand(MySPName, Conn);
cm.CommandType = CommandType.StoredProcedure;
DA.SelectCommand = cm;
DT = new DataTable();
DA.Fill(DT);
return DT;
}
}
调用部分,只说原理
try{
DataAccess DA = new DataAccess();
OutResult = (string[,])DA.CallStoredProcedure(PramIn, PramOut, "SPADDBOOK");
NewBookId = Convert.ToInt32(OutResult[0, 1]);
StatusId = Convert.ToInt32(OutResult[1, 1]);
}
catch (Exception ex)
{
LblMsg.Text = "::ERROR:: " + ex.Message;
}
页:
[1]