问题描述
我有一个存储过程返回一个学生是否被锁定与否:
返回@isLocked
我执行这样的存储过程:
公众诠释IsStudentLocked(字符串studentName,诠释lockoutTime)
{
的SqlConnection connObj =新的SqlConnection();
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();
的SqlCommand COMM =新的SqlCommand(uspCheckLockout,connObj);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(新的SqlParameter(@ Studentname,studentName));
comm.Parameters.Add(新的SqlParameter(@ LockoutTime,lockoutTime));
comm.ExecuteNonQuery();
connObj.Close();
//我怎样才能返回低于@isLocked价值?
回报率((INT)(@ isLocked));
}
要使用在T-SQL的返回
语句(只能返回整数值),您必须添加一个参数来检索它:
公众诠释IsStudentLocked(字符串studentName,诠释lockoutTime)
{
的SqlConnection connObj =新的SqlConnection();
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();
的SqlCommand COMM =新的SqlCommand(uspCheckLockout,connObj);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(新的SqlParameter(@ Studentname,studentName));
comm.Parameters.Add(新的SqlParameter(@ LockoutTime,lockoutTime));
变种returnParam =新的SqlParameter
{
参数名称=@返回,
的方向= ParameterDirection.ReturnValue
};
comm.Parameters.Add(returnParam);
comm.ExecuteNonQuery();
VAR isLocked =(INT)returnParam.Value;
connObj.Close();
返回isLocked;
}
不过,这是有点乱(IMO)。通常我在这种情况下,做的是 SELECT
,我想在我的存储过程的最后一个语句的值。然后,我用的ExecuteScalar
的命令对象检索值,而的ExecuteNonQuery
。
PROC:
... ... SQL
选择@ isLocked
方法:
公众诠释IsStudentLocked(字符串studentName,诠释lockoutTime)
{$ b $使用b(SqlConnection的connObj =新的SqlConnection())
{
connObj.ConnectionString =的Util。 StudentDataInsert();
connObj.Open();
的SqlCommand COMM =新的SqlCommand(uspCheckLockout,connObj);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(新的SqlParameter(@ Studentname,studentName));
comm.Parameters.Add(新的SqlParameter(@ LockoutTime,lockoutTime));
回报(INT)comm.ExecuteScalar();
}
}
I have a stored procedure which returns whether a student is locked or not:
RETURN @isLocked
I execute this stored procedure like:
public int IsStudentLocked(string studentName, int lockoutTime)
{
SqlConnection connObj = new SqlConnection();
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();
SqlCommand comm = new SqlCommand("uspCheckLockout", connObj);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@Studentname", studentName));
comm.Parameters.Add(new SqlParameter("@LockoutTime", lockoutTime));
comm.ExecuteNonQuery();
connObj.Close();
//How can I return the @isLocked value below?
return ((int)(@isLocked));
}
To use the RETURN
statement in T-SQL (which can ONLY return integer values), you have to add a parameter to retrieve it:
public int IsStudentLocked(string studentName, int lockoutTime)
{
SqlConnection connObj = new SqlConnection();
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();
SqlCommand comm = new SqlCommand("uspCheckLockout", connObj);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@Studentname", studentName));
comm.Parameters.Add(new SqlParameter("@LockoutTime", lockoutTime));
var returnParam = new SqlParameter
{
ParameterName = "@return",
Direction = ParameterDirection.ReturnValue
};
comm.Parameters.Add(returnParam);
comm.ExecuteNonQuery();
var isLocked = (int)returnParam.Value;
connObj.Close();
return isLocked;
}
However, this is kinda messy (IMO). Usually what I do in this case is to SELECT
the value that I want as the last statement in my stored procedure. Then I use ExecuteScalar
on the command object to retrieve the value instead of ExecuteNonQuery
.
Proc:
... SQL ...
SELECT @isLocked
Method:
public int IsStudentLocked(string studentName, int lockoutTime)
{
using(SqlConnection connObj = new SqlConnection())
{
connObj.ConnectionString = Util.StudentDataInsert();
connObj.Open();
SqlCommand comm = new SqlCommand("uspCheckLockout", connObj);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@Studentname", studentName));
comm.Parameters.Add(new SqlParameter("@LockoutTime", lockoutTime));
return (int)comm.ExecuteScalar();
}
}
这篇关于从存储过程返回值的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!