sql server -如何从Windows窗体c#中获取存储过程的返回值

本文关键字：获取 存储过程 返回值 窗体 Windows server sql | 更新日期: 2023-09-27 17:53:47

程序如下:

DECLARE @ReturnValue int
SELECT @ReturnValue = idUser
FROM Users
WHERE Alias = @UserAlias AND Password = @UserPassword 
RETURN @ReturnValue

这是我的c#代码，我希望我可以从过程中检索值:

TeamPWSecureBD.Open(); //Open SQL Connection
string alias = textBoxUsernameLogin.Text.Trim();
string pass = textBoxPasswordLogin.Text;
SqlCommand LoginVerify = new SqlCommand("_spUserVerify", TeamPWSecureBD);
LoginVerify.CommandType = CommandType.StoredProcedure;
LoginVerify.Parameters.AddWithValue("@UserAlias", alias);
LoginVerify.Parameters.AddWithValue("@UserPassword", pass);
SqlDataReader UserVerify = LoginVerify.ExecuteReader();
//Se o valor retornado pelo sp for 0 significa que o user não existe.
//O sp retorna o id do utilizador se o username e a password estiverem corretas.
//O id de utilizador menor é o 7.
if (Convert.ToInt32(UserVerify.GetSqlValue(0)) == 0)
    MessageBox.Show("Invalid username/password.", "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
else
{
    GlobalVariables.UserLoggedIn = Convert.ToInt32(UserVerify.GetSqlValue(0));
    PWLists MainForm = new PWLists();
    MainForm.ShowDialog();
    this.Close();
}

当我在SQL Server中执行这个过程时，返回值是正确的

但是一旦我尝试以这种形式读取它，它就会崩溃，并出现错误:

无效的读取尝试当没有数据存在

sql server -如何从Windows窗体c#中获取存储过程的返回值

尝试:

SELECT @ReturnValue

代替:

RETURN @ReturnValue

如果要捕获存储过程执行的RETURN语句的值，则需要向SqlCommand添加另一个参数

 SqlParameter r = LoginVerify.Parameters.Add("@return", SqlDbType.Int);
 r.Direction = ParameterDirection.ReturnValue;
 r.Value = 0;
 SqlDataReader UserVerify = LoginVerify.ExecuteReader();
 // Now you need to complete the reading from the UserVerify reader and 
 // finally you can catch the return value 
 .....
 UserVerify.Close();
 int result = (int)r.Value;

然而，给定StoredProcedure的代码，我认为您可以简单地避免声明另一个参数和SqlDataReader，因为您似乎只返回一行和一个字段。ExecuteScalar的确切作用域

SP现在只是一个select 

SELECT idUser FROM Users 
WHERE Alias = @UserAlias AND Password = @UserPassword

，检索idUser的代码为

object result = LoginVerify.ExecuteScalar();
if (result == null)
    MessageBox.Show("Invalid username/password.", "Login Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
else
{
    GlobalVariables.UserLoggedIn = Convert.ToInt32(result);
    PWLists MainForm = new PWLists();
    MainForm.ShowDialog();
    this.Close();
}
相关文章：