如何从asp.net中的存储过程获取特定的返回值 [英] How to get Specific return value from stored procedure in asp.net

问题描述

大家好，有人能告诉我如何从存储过程中获取返回值。

我在dataaccesslayer中使用了databasehelper。我的storedProcedure如下，

Hello all, can anyone tell me how can i get the return value from a stored procedure.
I am using a databasehelper in my dataaccesslayer. My storedProcedure is as follows,

ALTER proc [dbo].[spi_UserExamHistoryInsert]
(
	@GoForExamID int,
	@ExamTakenDate date,
	@NoOfAttempt int,
	@NoOfLeft int,
	@NoOfCorrect int,
	@OptionsScore int,
	@FreeTextScore int,
	@IpAddress varchar(30),
	@ExamScheduleID int,
	@IsExamSubmitted bit,
	@ReturnID int output,
	@ErrorCode int output
	)
	as
	begin 
	set nocount on
	insert into UserExamHistory values(@GoForExamID,@ExamTakenDate,@NoOfAttempt,@NoOfLeft,@NoOfCorrect,
										@OptionsScore,@FreeTextScore,@IpAddress,@ExamScheduleID,@IsExamSubmitted)
	set @ReturnID=SCOPE_IDENTITY() 
	return @ReturnID
	set @ErrorCode=@@ERROR
	end

i只想在我的asp中检索当前插入的ID。网络应用程序。这就是我到目前为止所做的...

i just want to retrieve the current inserted ID in my asp.net web application. This is what i have done so far...

public static bool Insert(UserExamRecordBO objUserExamRecordBO, out int ReturnVal)
        {
            bool bIsInserted = false;
            string strExamTakenDate = null;
            int nReturnID = 0;

            DatabaseHelper dbHelper = new DatabaseHelper();
            try
            {
                dbHelper.AddParameter("@GoForExamID", objUserExamRecordBO.GoForExamID);

                strExamTakenDate = objUserExamRecordBO.ExamTakenDate.ToString("yyyy-MM-dd");

                if (objUserExamRecordBO.ExamTakenDate != null)
                    dbHelper.AddParameter("@ExamTakenDate", strExamTakenDate);
                else
                    dbHelper.AddParameter("@ExamTakenDate", DBNull.Value);

                dbHelper.AddParameter("@NoOfAttempt",objUserExamRecordBO.NoOfAttempt);
........................
dbHelper.AddParameter("@ReturnID",System.Data.ParameterDirection.Output);
               **** nReturnID = (int)dbHelper.Command.Parameters["@ReturnID"].Value;
                
                dbHelper.AddParameter("@ErrorCode", -1, System.Data.ParameterDirection.Output);

                dbHelper.ExecuteNonQuery("spi_UserExamHistoryInsert", ref bIsInserted);
               **** //nReturnID = (int)dbHelper.ExecuteScalar("spi_UserExamHistoryInsert", ref bIsInserted);

                ****//nReturnID =(int)dbHelper.Command.Parameters["@ReturnID"].Value;

第一行星标返回一个明确的整数不是刚刚插入的ID ....第二行星号返回0 ...而执行后查询后的第三行星...抛出异常....参数集合不包含参数@ ReturnID ...这很简单，因为在executiontenonquery例程中，命令清除了它的所有参数......因此我将该行放在执行查询（第一行启动）之上。 Plz，如果你有任何想法如何获得插入的ID？（对于这种情况）....或者也许我做错了...



DatabaseHelper

The First line of stars returns an integer which is clearly not the ID that was just inserted....the Second line of stars returns a 0...and the third line of stars after the executenonquery...throws an exception....the parameter collection does not contain the parameter @ReturnID...this is prbly because in the executenonquery routine, the command clears all it''s parameters...for that reason i put the line above the executenonquery(the First line of starts). Plz, if you have any ideas how to get the Inserted ID?(for this case)....or maybe iam doing something wrong...

DatabaseHelper

 public int AddParameter(string name, object value, ParameterDirection direction)
        {
            int result = 0;
            DbParameter p = _newFactory.CreateParameter();

            p.ParameterName = name;
            p.Value = value;
            p.Direction = direction;

            result = _newCommand.Parameters.Add(p);

            return result;
        }

public int ExecuteNonQuery(string sqlStatement, CommandType commandType, ConnectionState connectionState, ref bool executionSucceeded)
        {
            _newCommand.CommandText = sqlStatement;
            _newCommand.CommandType = commandType;
            int i = 0;
            try
            {
                if (_newConnection.State == System.Data.ConnectionState.Closed)
                {
                    _newConnection.Open();
                }
                i = _newCommand.ExecuteNonQuery();
                executionSucceeded = true;
            }
            catch (Exception ex)
            {
                executionSucceeded = false;
                ProcessException(ex);
            }
            finally
            {
                _newCommand.Parameters.Clear();
                if (connectionState == ConnectionState.CloseOnExit && _newCommand.Transaction == null)
                {
                    _newConnection.Close();
                }
            }

            return i;
        }

推荐答案

试试这个：

try this:

//Setup SPROC
Command = new SqlCommand();
Command.Connection = Connection;
 
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "abc";
 
Command.Parameters.Add(new SqlParameter("@pcode", SqlDbType.VarChar,5000));
Command.Parameters.Add(new SqlParameter("@RETURNVALUE", SqlDbType.Int));
 
Command.Parameters["@pcode"].Value = abcValue.pcode;
 
Command.Parameters["@RETURNVALUE"].Direction = ParameterDirection.Output;
 
if (Command.Connection.State != ConnectionState.Open)
    Command.Connection.Open();
 
Command.ExecuteNonQuery();
 
return (int)Command.Parameters["@RETURNVALUE"].Value;

如果你想要获得主键值，你总是可以使用标识值。

这里 [ ^ ]是完成此任务的一个很好的例子。

If you want to get the primary key value, you can always use identity value.
Here[^] is a good example that accomplishes this task.

尝试使用这个

Try to use this

cmdHeader.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

参考以下链接



http://stackoverflow.com/questions/2342806/getting-a-return-value-in-c-sharp-asp-net-from-a-stored-procedure -syntax-issue [ ^ ]



谢谢

take reference from following link

http://stackoverflow.com/questions/2342806/getting-a-return-value-in-c-sharp-asp-net-from-a-stored-procedure-syntax-issue[^]

Thanks

这篇关于如何从asp.net中的存储过程获取特定的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！